CodesJava

Easy learning with example program codes

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:

 

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:

 

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.

Please follow and like us:
error
Posted in DBMS   









Copyright © 2019 CodesJava DMCA.com Protection Status