# Boyce-Codd Normal Form

In this lesson, we will learn about Boyce-Codd normal form with the help of an example.

We'll cover the following

## 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.

Get hands-on with 1200+ tech skills courses.