Identifying non-trivial functional dependencies in database tables

Question

Which non-trivial functional dependencies can be discovered in this table? Are there any systematic approaches that can be used to do so?

|  first_name  |  surname  |  student_id  |  program_code  |  name_of_study  |

Answer 1

Take a look at database normalization, especially at following concepts

• 1. normal form (1NF)
• 2. normal form (2NF)
• 3. normal form (3NF)
• Boyce–Codd normal form (BCNF).

It's been a while for me, but here we go:

first_name, surname can be derived by student_id so we put them in an own table named students, student_id is the foreign key, that's why it's marked with an asterisk.

------------students---------------
| *student_id* | first_name| surname |

The mapping from student to their subject should be placed in a different table

-----------subjects-----------
| *student_id* | name_of_study  |

and the last table contains the code submissions

-----------submissions-------
| *student_id* | program_code |

Nevertheless, it's been I while since I had my database lecture, so I'd recommend you to read through the links I provided.

The columns in the table are (or should be if I did it right), functionally dependent from the foreign key.