What is BCNF?

Overview

BCNF stands for Boyce-Codd normal form, which is a special case of 3NF and is also known as 3.5 NF. BNCF is a normal form used in the normalization of databases and has more strict rules as compared to 3NF.

BCNF rules

To check if the table satisfies the conditions of BCNF, the following two conditions should exist:

• The table must be in 3NF form.
• For any dependency X → Y, X must be a candidate key or super key. In other words, for dependency X → Y, if Y is a prime attribute, X cannot be a non-prime attribute.

3NF states that the transitive dependency must not exist. Transitive dependency is that the LHS (left-hand side) of the functional dependency must consist of a super key/candidate key or the RHS (right-hand side) must have a prime attribute. BCNF adds more restrictions by stating that LHS of functional dependency must have a super key and removes the RHS condition.

Example of BCNF

Assume there is a hospital where an employee works in more than one department.

Functional dependencies

• Emp_ID → Nationality
• Emp_Dept → {Dept_Type, Dept_No}

Candidate key

• {Emp_ID, Emp_Dept}

In this example, the table is not in BCNF form as both the Emp_ID and Emp_Dept alone are not keys. To convert the table into BCNF form, decompose the table into three tables based on the functional dependency.

Functional dependencies

• Emp_ID → Nationality
• Emp_Dept → {Dept_Type, Dept_No}

Candidate key

• Nationality Table: Emp_ID
• Dept Table: Emp_Dept
• Dept Mapping Table: {Emp_ID, Emp_Dept}

The relation is now in BCNF form because it satisfies both conditions which are that the table is already in 3NF form and on the LHS of the functional dependency there is a candidate key.