The previous normalization forms are considered elementary, and should be applied on tables during our design process. This normalization form however, and the following forms, are done in special tables.

A table is considered in BCNF (Boyce-Codd Normal Form) if it’s already in 3NF AND doesn’t contain any nontrivial functional dependencies. That is it doesn’t contain any field (other than the primary key) that can determine the value of another field. Let’s take the following table:

Student Subject Teacher
Smith Math Dr. White
Smith English Dr. Brown
Jones Math Dr. White
Jones English Dr. Brown
Doe Math Dr. Green

By taking into consideration the following conditions:

  • For each subject, every student is educated by one teacher.
  • Every teacher teaches one subject only.
  • Each subject can be teached by more than one teacher.

It’s clear we have the following functional dependency:
Teacher ->  Subject

And the left side of this dependency is not the primary key.

So, to convert the table from 3NF to BCNF, we do these steps:

  • Determine in the table, a key other than the primary key. That can be left side to the functional dependency.
  • Delete the key in the right side of our functional dependency in the main table.
  • Make a table for this dependency, with it’s key being the left side of the dependency, as the following:
Student Teacher
Smith Dr. White
Smith Dr. Brown
Jones Dr. White
Jones Dr. Brown
Doe Dr. Green

And

Teacher Subject
Dr. White Math
Dr. Brown English
Dr. Green Math