BCNF Boyce Codd normal form

by

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.