Converting 3NF to BCNF when there is a circular de

2020-04-18 05:20发布

问题:

If we have a relational schema R(A, B, C, D), with the set of dependencies:

ABC -> D
D -> A

How is it possible to decompose R into BCNF relations? The only plausible way seems to be to discard one of the FDs, no matter how I think about it. Is there any other way?

回答1:

That's right, one 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".