BCNF in DBMS

BCNF stands for Boyce–Codd Normal Form.

What is the Normal form?

The normal form is mainly used to reduce the redundancy of the database tables.

Or we can say that the normal form is a step-by-step process and each step is known as the normal form.

In other words, the normal form is a stage at which we organize the database tables.

There is a different types of normal forms:

• 1NF( First normal form)
• 2NF( Second normal form)
• 3NF( Third normal form)
• BCNF( Boyce-Codd normal form)

What is Normalization?

Normalization is a process in the database management system that is used to organize the data with its attributes and it also performs data redundancy to check if the data is stored logically or not.

What is BCNF (Boyce-Codd Normal Form)?

BCNF is known as Boyce-Codd Normal Form, it is a special case of 3NF (normal form) and we can also say that it is also known as 3.5 NF. A relation to BCNF (Boyce-Codd normal form) exists if and only if the determinants are candidate key.

Or we can say that BCNF is a normal form in the normalization of databases and its rules are stricter than the 3 NF. It is based on the functional dependencies that take all the determinants of all candidate keys in a relationship.

As we all know, the third normal form doesn’t remove all the redundancy from the database tables in cases where a functional dependency says that B->C, in this case, B is not a candidate key of the database table. BCNF was introduced to deal with such situations.

A table is said to be in form of BCNF if the table is already in the form of the third normal form (3NF) in the DBMS.

And we also know that for every functional dependency, we should have an attribute in the from of candidate keys, let's say (A->B), A is either the super key or the candidate key. In the simple words, for any case, A cannot be a non-prime attribute.

Some Rules for BCNF in DBMS

Suppose, we want to check if the table satisfies the conditions of BCNF, then we need to follow two conditions as follows:

• First, the table should be in the form of the third normal form (3 NF).
• For any dependency A→B, in this A must be a super key or a candidate key. In other words, for the function dependency A→ B, if B is a prime attribute of the table, then A cannot be a non-prime attribute of the table.

In the state of 3NF, the transitive dependency must not exist. Transitive dependency is known as the Left-hand side (LHS) of the functional dependency.

It must contain a super key or the candidate key or functional dependency right-hand side (RHS) should be a prime attribute.

Example of BCNF in DBMS

Example 1:

Suppose we have a hospital table where the employees work in more than one department.

EMPLOYEE TABLE

EMP_ID	NATIONALITY	EMP_DEPT	DEPT_TYPE	DEPT_NO
E001	India	Surgery	X12	301
E002	Canada	Dental	X12	402
E001	India	General Medicine	X97	212
E004	Pakistan	Radiology	X97	356

Functional dependencies of this table:

• EMP_ID → Nationality
• EMP_DEPT → {DEPT_TYPE, DEPT_NO}

Candidate key of this table:

• {EMP_ID, EMP_DEPT}

In this example, the table is not BCNF form as both EMP_ID and EMP_DEPT alone are not keys. If we want to convert this table into BCNF form, we need to decompose the table into three tables based on the functional dependency of the table.

NATIONALITY TABLE

EMP_ID	NATIONALITY
E001	India
E002	Canada
E004	Pakistan

DEPT TABLE

EMP_DEPT	DEPT_TYPE	DEPT_NO
Surgery	X12	301
Dental	X12	402
General Medicine	X97	212
Radiology	X97	356

DEPT Mapping table

EMP_ID	EMP_DEPT
E001	Surgery
E002	Dental
E001	General Medicine
E004	Radiology

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.

Example: Let's assume we have a company where employees work in more than one department.

EMPLOYEE TABLE

EMP_ID	EMP_COUNTRY	EMP_DEPT	DEPT_TYPE	EMP_DEPT_NO
250	India	IT	D395	183
250	India	Digital Marketing	D395	200
362	Pakistan	Stores	D284	132
362	Pakistan	Developing	D284	449

In the above table, Functional dependencies are as follows:

• EMP_ID  →  EMP_COUNTRY  
• EMP_DEPT  →   {DEPT_TYPE, EMP_DEPT_NO}  

Candidate key: {EMP-ID, EMP-DEPT}

The table is not in BCNF because neither EMP_DEPT nor EMP_ID alone are keys.

To convert the given table into BCNF, we decompose it into three tables:

EMP_COUNTRY table:

EMP_ID	EMP_COUNTRY
250	India
250	India

EMP_DEPT table:

EMP_DEPT	DEPT_TYPE	EMP_DEPT_NO
IT	D395	183
Digital Marketing	D395	200
Stores	D284	132
Developing	D284	449

EMP_DEPT_MAPPING table:

EMP_ID	EMP_DEPT
250	IT
250	Digital Marketing
362	Stores
362	Developing

Functional dependencies:

1. EMP_ID   →    EMP_COUNTRY  
2. EMP_DEPT   →   {DEPT_TYPE, EMP_DEPT_NO}  

Candidate keys:

For the first table: EMP_ID
For the second table: EMP_DEPT
For the third table: {EMP_ID, EMP_DEPT}

Now, this is in BCNF because left side part of both the functional dependencies is a key.