Given the table:
- Student_subject(studentid, subjectid, username, grade)
Primary key consisting of a combination of:
- studentid, subjectid
Candidate key consisting of a combination of:
- subjectcode, username
Now it can be saide the following functional dependencies exist:
- studentid, subjectid -> grade (BCNF)
- username, subjectid -> grade (BCNF)
- studentid -> username (3NF)
- username -> studentid (3NF)
BCNF - Boyce–Codd normal form (3.5NF)
So the NF of the table will equal the lowest NF found among all FDs, right? There seems to be some different explanations around for how to find the NF levels.
As I've seen from one explanation on how to satisfy 2NF, it is so that all data/columns need to depend on the whole/entire primary key, but I can't see that is the case (since the primary key is defined as the combination of studentid, subjectid) with
- studentid -> username (3NF)
- username -> studentid (3NF)
And if it doesn't fulfill 2NF, it can't satisfy a higher level 3NF. isn't it so that you start to see if 1NF is satisfied, and if it is you check if 2NF is as well and so on...
Also superkeys are anything that identifies a row uniquely, incl. candidate keys, primary key and all possible combinations of these with the rest of the attributes?