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

## 0 comments:

## Post a Comment

Note: only a member of this blog may post a comment.