BCNF (Boyce Codd Normal Form) : Introduction

• BCNF is known as “Boyce Codd Normal Form” and is a successor to “Third Normal Form”. It is used to check whether a given relation is in “BCNF” or not.
• Any relation to be in BCNF must follow below mentioned two rules.
  1. The relation/table needs to be in 3NF.
  2. For a functional dependency P Q, “P” should be a super key.
• BCNF deals with the cases where “Non-Prime in a functional dependency derives a prime attribute”. How? Let’s take an example to clear this doubt.

 

BCNF : Example

• Consider a relation R(X,Y,Z) having following functional dependencies :

FD1 : XY Z

FD2 : Z Y

  Step-1 : Check whether given relation is in 3NF or not. 

Attribute “Z” is fully dependent upon the candidate key “XY” and there is not transitive dependency too. Also, “X & Y” are prime attributes whereas “Z & T” are non-prime attributes.

  Step-2 : As it is confirmed that the relation is in 3NF, we can move further. Now, check for FD’s where a non-prime attribute determines a prime attribute. In this example “Z  Y” is that FD.

FD1 : XY  Z (“XY” is the candidate key, so no problem in this FD)

FD2 : Z  Y (Here, a non-prime attribute is deriving a prime attribute)

Hence, we can conclude by observing “FD2” that this relation “R” is not in BCNF. Follow step-3 to convert this into BCNF.

  Step-3 : Decompose the relation R(X,Y,Z,T) into R1(X,Y,Z) and R2(Z, Y) to convert the relation in BCNF.

R1 : XY  Z

R2 : Z  Y

“XY” and “Z” are super keys of their respective relations i.e. “R1” and “R2” as each of them can uniquely determine the attributes present in the relations.

Hence after decomposition, both the relations R1 and R2 are in BCNF.

Previous Learn More