An Example of Database Normalization (Cont.)

Boyce-Codd Normal Form (Cont.)
If you consider the primary key of this table is Course + Subject, then no violation of 1NF, 2NF, and 3NF. Let’s list out all possible functional dependencies:
  • Course, Subject → Lecturer
  • Course, Lecturer → Subject
  • Lecturer → Subject

Now, based on the identified functional dependencies, see whether you can make determinants as candidate keys. If you take the first one, we can clearly say that Course + Subject is a candidate key. Second one that is Course + Lecturer is also a candidate key as we can identify tuples uniquely using it. However the determinant of the third one cannot be used as a candidate key because it has duplicates. You cannot make Lecturer as a primary key.

Now you have a determinant that cannot be set as a primary key, hence it violates BCNF. In order to make the table BCNF table, need to decompose.

Assume that business rules related to this relation are as follows: If you consider the primary key of this table is Course + Subject, then no violation of 1NF, 2NF, and 3NF. Let’s list out all possible functional dependencies:

This normal form speaks about transitive dependency.
A database relation (a database table) is said to meet third normal form standards if all the attributes (database columns) are functionally dependent on solely the primary key.
That is 3NF is a relation in second normal form where all non-prime attributes depend only on the candidate keys and do not have a transitive dependency on another key. This says that we should remove transitive dependency if they are exist. What is transitive dependency? In other words, if you see that Attribute A determines B (A→B) and B determines C (B→C), then A determines C (A→C). For removing transitive dependency, we need to decompose the relation.





      “Success - keeping your mind awake and your desire asleep.”    
      — Walter Scott