BCNF decomposition query

Question

I have a relation R(A, B, C, D) with functional dependencies ABC --> D and D --> A.

The question was, does this have a BCNF, and the answer was as follows:

enter image description here

(c) ABCD is not in BCNF since D --> A and D is not a key. However, if we split up R as AD, BCD, we cannot preserve the dependency ABC --> D. So there is no BCNF decomposition.

Now my question is, I understand that ABC->D cannot be preserved if you split the relation R into R1(A,D) and R2(BCD) but what if you changed R2(BCD) to R2(ABCD). Wouldn't that preserve it then? Is there any reason we cannot do that?

Yes, your R2(ABCD) would preserve the FD, but it wouldn't be a decomposition and R1(BCD) would be completely superfluous, wouldn't it? — Jonathan Leffler, Jun 06 '14 at 03:38
My mistake, but it doesn't affect my argument: the R2(ABCD) is the same relation you started with, so your other relation is superfluous. — Jonathan Leffler, Jun 06 '14 at 05:11
As a side, the fact that a decomposition does not preserve all the FDs does not automatically entails that the decomposition isn't in BCNF: check out http://en.wikipedia.org/wiki/BCNF#Achievability_of_BCNF — laurids, Jun 06 '14 at 12:52

score 3 · Accepted Answer · edited May 23 '17 at 11:43

You are taking "So there is no BCNF decomposition" out of context in two ways. There is no (lossless) BCNF decomposition (1) into relations that are all smaller (per comment) (2) that preserves all FDs (per comment).

From this answer:

[O]ne can always losslessly decompose to 3NF while preserving FDs but BCNF might not preserve them. Nevertheless it's a lossless decomposition: the components, if holding projections of the original, will join to the original. But whenever the original would have had a given value, the components should be projections of it. (If they're not, an error has been made, so we want the DBMS to constrain the components appropriately.) So it is necessary but sufficient to constrain the components to be projections of the original. ABC is trivially so (because it is a key). This leaves us needing to require that AD = ABCD PROJECT {DA}. We say that the components must satisfy that "equality dependency".

You can always non-loss decompose to 5NF. You just might have to add equality dependency constraints if you want to constrain the components per the unpreserved FDs.

BCNF decomposition query

1 Answers1