Here is an example on 3NF decomposition that is different
from the ones we discussed
in class (brought to my attention by Russ Fink)
R(ABCDE)
F = {A->BC, B->C, A ->B, AB->C, BD ->A} Keys are
BDE, ADE
Minimal cover Fc = {A ->B, B->C, BD ->A}
B ->C is a 3NF violation, therefore R needs to be decomposed.
Using the minimal cover we get
decomposition {(BC), (ABD)} -- we don't need a separate
relation for A->B since we have (ABD) and
the projected FD on it namely A ->B is not a violation
because B is part of a key. We need to add a third relation that is the
key -- add (BDE) or (ADE). Therefore, working directly from the minimal
cover, the 3NF decomposition set includes {(BC), (ABD), (BDE)}. Further
adjustments can be made as follows.
(ABD) and (BDE) (or (ADE)) can be combined into one relation
(ABDE) and here is why.
(ABDE) is in 3NF because the projected dependencies are
{A->B (RHS is part of key), BD ->A (RHS is part of key), ADE->B (LHS
is key), BDE -> A(LHS is key)}.
SO the final 3NF decomposition is {(BC), (ABDE)}.
Note: here Steven Rook's suggestion of using the tree
approach works -- (BC) and the rest (ABDE).
But working with the FDs in the minimal cover to guide
you through the decomposition process and
then making further adjustments/optimization is what
I prefer.
---------------------------------------------------------
Minimal cover example discussed in class.
F = { A -> BC, ACD -> E, B -> D, C -> D, AB -> E, E -> BC}
1. ACD -> E can be replaced by AC -> E since C -> D is
given.
2. AB -> E can be replaced by A -> E since A -> B is
given.
3. AC -> E can be replaced by A -> E since A -> C is
given. (in class I combined it as ABC -> E to be replaced
with A -> E because A -> BC was given.)
Modified F up to this point:
= {A -> BC, A -> E, B -> D, C -> D, E -> BC}
Note: do not combine 1st and the 5th FD and reduce it
to A -> BC. Instead,
split them into FD's with a single attribute on the right.
So, set of FDs now is:
= { A -> B, A -> C, A -> E, B -> D, C -> D, E -> B, E
-> C}
You can now eliminate A -> B because of A -> E, and E
-> B;
eliminate A -> C because of A -> E and E -> C.
Note: you could have eliminated A -> BC because of A
-> E and E -> BC.
Now the minimal cover is:
Fc = {A -> E, E -> B, E -> C, B -> D, C -> D}