· **Based on functional dependencies that take into account all candidate keys in a relation, however BCNF also has additional constraints compared with general definition of 3NF.**

· **BCNF – A relation is in BCNF if and only if every determinant is a candidate key.**** **

· **Difference between 3NF and BCNF is that for a functional dependency A ****–> B, 3NF allows this dependency in a relation if B is a primary-key attribute and A is not a candidate key. **

· **Whereas, BCNF insists that for this dependency to remain in a relation, A must be a candidate key. **

· **Every relation in BCNF is also in 3NF. However, relation in 3NF may not be in BCNF.**

**Two FDs exist in the relation TEACH:**

– **fd1: { student, course} ****à instructor**

– **fd2: instructor ****à course **

**{student, course} is a candidate key for this relation and that the dependencies shown follow the pattern in Figure 10.12 (b).**

– **So this relation is in 3NF but not in BCNF **

**A relation NOT in BCNF should be decomposed so as to meet this property, while possibly forgoing the preservation of all functional dependencies in the decomposed relations.**- Main steps of algorithm 11.3:

– Find the FD XàY in Q that violates BCNF

– Replace Q by two relations with schemas (Q-Y) and (X,Y).

– TEACH relation is decomposed into:

- T1(instructor, course)

- T2(instructor, student)

– FD fd1 is lost after decomposition.

## Leave a Reply