An Example of Database Normalization (Cont.)

Second Normal Form (2NF)
For a table to be in second normal form, the two conditions are to be met:
  • The table should be in the first normal form (1NF).
  • Every non-primary-key attribute is fully functionally dependent on the primary key.
What it says is, there should not be partial dependency between primary key and non-primary key. Let’s try to set the primary key for previous table. For that, let’s list out some functional dependencies: 
StudentCode, Course → Name, Town, Province, Course, DateRegistered
StudentCode → Name
Town → Province
Considering above identified functional dependencies, we can easily pick the first one, that is (StudentCode, Course), as my primary key.

It is because the combination of them can be used for identifying the tuple easily.

Now the primary key is StudentCode + Course for the above table. However, we know that “StudentCode → Name” relationship still exists. This means that Name can be determined by part of the primary key, that is partial dependency, which volates second normal form. We can decompose the relation now into two for making sure that relations do not violating the 2NF.


Note that you will not see violation of 2NF if the primary key is based on just one attribute.