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:
  1. Course, Subject → Lecturer
  2. Course, Lecturer → Subject
  3. Lecturer → Subject

Now, based on the identified functional dependencies, see whether you can make determinants as candidate keys.
  1. If you take the first one, we can clearly say that Course + Subject is a candidate key.

  2. Second one that is Course + Lecturer is also a candidate key as we can identify tuples uniquely using it.

  3. 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 it.