存在循环依赖项时将3NF转换为BCNF [英] Converting 3NF to BCNF when there is a circular dependency

问题描述

如果我们有一个关系模式R（A，B，C，D），并且具有一组依赖项：

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

ABC -> D
D -> A

如何将R分解为BCNF关系？无论我如何考虑，似乎唯一可行的方法似乎是丢弃其中一个FD。还有其他方法吗？

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?

推荐答案

是的，在保留FD的同时始终可以无损地分解为3NF，但是BCNF可能不会保留它们。不过，这是一种无损的分解：如果组件保留了原图的投影，则它们将合并到原图。但是，只要原件具有给定的值，组件就应该是它的投影。 （如果不是，则会出错，因此我们希望DBMS适当地约束组件。）因此，有必要但有必要将组件约束为原始投影。 ABC很简单（因为这是关键）。这使我们需要要求AD = ABCD PROJECT {DA}。我们说这些组件必须满足平等依赖。

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".

这篇关于存在循环依赖项时将3NF转换为BCNF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！