Boycod normal form (BCNF)

 When a table has more than one candidate key, anomalies may result even though the relation is in 3NF. Boyce-Codd normal form is a special case of 3NF. A relation is in BCNF if, and only if, every determinant is a candidate key.

A relation schema 'R' is in BCNF if every every non-trivial FD X-->Y in relation 'R' with determinant 'X' must be candidate key / super key.

The decomposition of 3NF is loss less join but may not be dependency preserving.

 

                              X ----Y ;  

'X' must be a super key / CK. Determinant  of all FD's must be a super key.

Example:-

Consider the relation R(ABC) and FD ={ AB->C, C->B}

Solution: 

R(ABC)

{ AB->C, C->B}

Key: 'A' but alone it cant determine entire relation. To check find A+ ={A} only. So, take combinations of 'A' that is AB, AC

AB+={ ABC}

AC+{ABC}

Therefore keys are: AB, BC

1) AB->C ; since AB is CK/SK it is in BCNF

2) C->B ; since is 'C' not CK/SK it not in BCNF. So, decompose relation  R1(CB) and R2(AC)

R1(CB)

C->B ; in this relation 'C' is key and it preserves 

C+={CB}

Therefore final relations are 

R1(CB) with FD C->D  and R2(AC) with no FD.

This is loss less but not dependency preserving