BCNF Boyce Codd normal form

Boyce Codd normal form (BCNF)

• Boyce Codd Normal Form is an advanced form of the third natural form and hence is quite stricter than it.
• If every functional dependency is in the form X → Y, the table is in BCNF. Here, X is the super key to the table.
• For a table to be in BCNF, it should be in 3NF. For every FD, LHS is the super key.

For example, let us consider a company which has employees in more than one department.

EMP_ID	EMP_COUNTRY	EMP_DEPT	DEPT_TYPE	EMP_DEPT_NO
2	India	UI	D1	5
2	India	QA	D1	6
3	UK	Store	D2	7
3	UK	DEV	D2	8

In this table, functional dependencies are:

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

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

This table is not in BCNF because EMP_DEPT or EMP_ID are not alone keys.

To convert to BCNF, we break it down into three tables.

EMP_ID	EMP_COUNTRY
2	India
2	India

 

EMP_DEPT	DEPT_TYPE	EMP_DEPT_NO
UI	D1	5
QA	D1	6
Store	D2	7
DEV	D2	8

 

EMP_ID	EMP_DEPT
D1	5
D1	6
D2	7
D2	8

Here the functional dependencies are:

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}

This is in BCNF because the left side of the two functional dependencies is a key.