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:


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