DBMS – Boyce-Codd Normal Form (BCNF)

BCNF is an acronym for Boyce-Codd Normal Form as it was developed by Raymond F Boyce and Edgar Codd in the year 1974.BCNF is a stronger definition of the third normal form (3NF). Because of this reason, it is also referred to as  3.5NF.

Constraints to be followed in BCNF

Constraints which should be followed for a relation to be in Boyce-Codd Normal Form are:

• All the redundancy relating to functional dependencies must be removed.

• If X -> Y then Y с X and X should be the superkey for the relation R.

To summarize we can also say that, an attribute of a composite key should not be dependent on an attribute of the other composite key. If there is any such dependency then the relation is not in BCNF. For a better understanding, let us consider an example.

Example

Let us assume a relation named TEACHER. This relation stores information regarding teachers working in a Department and each Department has its own HOD. Every teacher has a specific TeacherID.

In this table, teachers with IDs T1, T2, T3 are working in different departments with their respective at HODs.

This relation has two possible composite keys. These are: (T_Id, Dept_Name) and (T_Id, Dept_HOD).

Now let us have a look at the dependency diagram for the above relation.

Dependency diagram

If you take a closer look at the dependency diagram you will notice that there are two composite keys namely (T_Id, Dept_Name) and (T_Id, Dept_HOD) and there is a dependency between the attributes of the composite keys itself. That is Dept_Name and Dept_HOD are dependent attributes. Because of this dependency the above relation is not in BCNF.

Converting the relation into Boyce-Codd Normal Form (BCNF)

In order to normalize the relation here we will create two distinct relations so as to remove the dependency between the attributes of the composite keys.

This will be done as follows:

Table 1: Teachers

Table 2: Depts

Once normalized the tables will have the following dependencies:

Table 1: Teachers

Table 2: Depts

Hence, normalizing a relation to Boyce-Codd Normal Form will help reduce redundancy to a great extent and hence increase the efficiency of the relations too.

Anupreet Kalsi