Normalization – How to Satisfy 2NF and Understand Superkeys

normalization

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?

Best Answer

to satisfy 2NF, it is so that all data/columns need to depend on the whole/entire primary key

That's incorrect. A relation R is in second normal if every non-prime attribute of R is fully dependent on each candidate key of R. In the case of studentid -> username and username -> studentid both username and studentid are prime attributes (attributes that are included in a candidate key). So those dependencies are not a violation of 2NF and Student_subject does satisfy 2NF with respect to those dependencies. As already pointed out, Normal Forms apply to relations, not to FDs.

Related Question