Boyce-Codd Normal Form

Boyce-Codd normal form (BCNF)

For a table to satisfy the Boyce-Codd normal form, it should satisfy the following two conditions:

1. It should be in the third normal form.

2. And, for any dependency A → B, A should be a super key.

The second point sounds a bit tricky, right? In simple words, it means that for a dependency A $\rightarrow$ B, A cannot be a non-prime attribute if B is a prime attribute.

Example

ENROLMENT table

Std_Id	Subject	Professor
101	CS-100	Eddie Jessup
101	MATH-101	Charles Kingsfield
102	CS-100	Robert Langdon
102	CHEM-101	John Keating
103	CS-100	Eddie Jessup

In the table above:

• One student can enroll in multiple subjects. For example, the student with Std_Id 101, has opted for two subjects: CS-100 and MATH-101.

• Std_Id and Subject together form the primary key as we can uniquely identify all the tuples in the table.

• One more important point to note here is that one professor teaches only one subject, but one subject may have two different professors. For example, CS-100 is taught by two different professors.

Hence, there is a dependency between the subject and professor where the subject depends on the professor’s name.

This table satisfies the 1NF because all the values are atomic, column names are unique and all the values stored in a particular column are of the same domain.

This table also satisfies the 2NF as there is no partial dependency. There is also no transitive dependency, hence the table also satisfies the 3NF.

But this table is not in the Boyce-Codd normal form. The reason being that Std_Id and Subject form a composite primary key, which means Subject is a prime attribute.

But, there is one more dependency, Professor$\rightarrow$ Subject, and while Subject is a prime attribute, Professor is a non-prime attribute, which is not allowed by BCNF.

So, to make this relation (table) satisfy BCNF, we will decompose this table into two tables: the STUDENT table and the PROFESSOR table.