Consider the relation R(A, B, C, D, E), with the functional dependencies being {BE->A, D->E}. What is R's decomposition in 2NF?

The answer is R1(D, E); R2(B, E, A); R3(B, C, D)

I've derived that the candidate key is {B, C, D}. Since the only problematic part that violates 2NF's definition is D, E being in the same relation, using lossless conversion formula (if X->Y, can decompose into XUY and {Attribute1, Attribute2 ... AttributeN}-Y), why can't 2NF be just R1(D, E); R2(A, B, C, D)?

I've suspected it might be because the BE->A relationship would then not be included (ignore this part if it's not the case), but then with the same logic, for another question R(A, B, C, D, E) with FD {AD->E, C->AB} should then be decomposed into 2NF as R1(C, A, B); R2(A, D, E); R3(C, D) instead of the answer which is R1(C, A, B); R2(C, D, E) right?


1 Answer 1


Your "I have these FDs" doesn't make sense. "These are all the FDs that hold"?--Not possible. "These are all the non-trivial FDs that hold"?--Not possible. "These are some FDs that hold"?--Question can't be answered. When some FDs hold, the FDs implied by them per Armstrong's axioms also hold. Moreover, others might hold. Find out what a cover is and what the exact conditions are to apply a particular definition/rule/algorithm. (Sometimes it's a minimal/irreducible cover.) To determine CKs & NFs we must be able to determine FDs that form a cover. (That includes that the set of all attributes be given.)

(The given FDs imply BD->A.)

Even if your 2 components were in 2NF, it wouldn't preserve FDs. Its components--projections of R--would natural join to R. But given proposed values for the components, merely enforcing that each original FD that has all its attributes in a component hold in that component would not necessarily enforce that all the original FDs hold in the natural join of those components. We prefer decompositions that preserve FDs so that enforcing FDs in components enforces them in their natural join. There is always a 3NF FD-preserving decomposition, hence a 2NF one. Picking random FDs is not a good way to get a good decomposition. So we use a proven algorithm.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.