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:

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

T_Id Dept_Name Dept_HOD
T1 Computer Allan
T2 Maths Alisha
T1 English Belle
T2 Computer Allan
T3 English Belle

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

Boyce-Codd Normal Form
Boyce-Codd Normal Form 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.

Composite keys

This will be done as follows:

Table 1: Teachers

T_Id Dept_Name
T1 Computer
T2 Maths
T1 English
T2 Computer
T3 English

Table 2: Depts

Dept_Name Dept_HOD
Computer Allan
Maths Alisha
English Belle

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.

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *