Third Normal Form Decompositions
---------------------------------
Given a relation R and a minimal set F of fds is not in 3NF,
we compute the following decomposition:
- Create a new relation for each functional dependency
- If no relation has all the attributes in one of the keys, then
add one more relation with attributes of one of the keys
- If one relation has a subset of the attributes of another relation
then remove the smaller relation.
3NF decomposition is:
- it is always lossless
- it is dependency preserving.
- all resulting relations are in 3NF
Ex:
R(A,B,C,D,E,F)
AB->C
CD->EF
F->A
Keys: ABD, BDF
3NF decomposition:
R1(A,B,C) F1 = {AB->C} Key: AB
R2(C,D,E,F) F2 = {CD->EF} Key: CD
R3(A,F) F3 = {F->A} Key: F
R4(A,B,D) F4 = {} Key: ABD
S(A,B,C,D)
AB->C
A->D
C->A
Key:AB
S1(A,B,C) F1 = {AB->C, C->A} Keys: AB, BC (in 3NF, not in BCNF)
S2(A,D) F2 = {A->D} Keys: A (in 3NF, BCNF)