BoyceCodd Normal Form
In this lesson, we will learn about BoyceCodd normal form with the help of an example.
We'll cover the following
BoyceCodd normal form (BCNF)
For a table to satisfy the BoyceCodd normal form, it should satisfy the following two conditions:

It should be in the third normal form.

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 nonprime attribute if B is a prime attribute.
Example
ENROLMENT table
Std_Id  Subject  Professor 

101  CS100  Eddie Jessup 
101  MATH101  Charles Kingsfield 
102  CS100  Robert Langdon 
102  CHEM101  John Keating 
103  CS100  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: CS100 and MATH101. 
Std_Id
andSubject
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, CS100 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 BoyceCodd 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 nonprime 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.
Get handson with 1200+ tech skills courses.