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:

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:

And