3NF 归一化问题，我可以使用派生 FD 来确定关系不在 3NF 中吗？
我有以下问题：
考虑具有以下函数依赖性的关系 R(A, B, C, D, E, F, H)：
一个--> D、AE——> H、DF——> BC、E --> C、H——>电子
考虑三个关系模式 R1(A, D)、R2(E, C) 和 R3(A, B, E, F, H)。它们形成 R 的分解。
(a) 原始函数依赖关系是否适用于 R1、R2 和 R3？
(b) 这是 3NF 分解吗？解释你的答案。
我的尝试：
(a) 只要关系包含函数依赖中的属性，原始函数依赖就适用于 R1、R2 和 R3。
(b) R3 中的编号键 = {AEF, AFH}。由R中的{AF}+ = {ABCDF}，在R3中{AF}+ = {ABF}。因此我们可以形成函数依赖 AF --> B，并且该函数依赖的LHS不包含键。 RHS 也不只包含关键属性。
所提供的解决方案没有直接解决（a），并且指出分解是在 3NF 中，因为原始 FD 不违反 3NF。想知道我在这里做错了什么。谢谢你！
I have the following question:
Consider relation R(A, B, C, D, E, F, H) with the following functional dependencies:
A --> D, AE --> H, DF --> BC, E --> C, H --> E
Consider three relational schema R1(A, D), R2(E, C), and R3(A, B, E, F, H). They form a decomposition for R.
(a) Do the original functional dependencies apply in R1, R2, and R3?
(b) Is this decomposition in 3NF? Explain your answer.
My attempt:
(a) The original functional dependencies apply in R1, R2, and R3 as long as the relation contains the attributes in the functional dependencies.
(b) No. Keys in R3 = {AEF, AFH}. From {AF}+ = {ABCDF} in R, in R3 {AF}+ = {ABF}. Hence we can form a functional dependency AF --> B, and the LHS of this functional dependency does not contain a key. The RHS also does not contain only key attributes.
The solution provided did not address (a) directly, and stated that the decomposition is in 3NF because the original FDs do not violate 3NF. Would like to know what I did wrongly here. Thank you!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在下文中，我假设给出的依赖项是
R依赖项的覆盖。当分解一个关系时，覆盖的依赖关系不一定适用于分解的关系。当分解的关系不包含依赖关系的所有属性时，就会发生这种情况。例如，在您的示例中，
DF -> BC在R1、R2、R3中都不成立，因为属性DFBC是并非全部都存在于单个关系中（我们知道函数依赖仅在关系内部才有意义）。这并不一定意味着分解会遭受“依赖性损失”，因为定义更加复杂：具有属性 A 的关系模式 R 的分解和依赖性覆盖 F 保留依赖性当且仅当投影的并集F 对分解关系的依赖关系是 F 的覆盖。
在 Ullman，数据库系统原理，计算机科学出版社，1983 年中，显示了一种算法来计算一组依赖关系的投影的并集的闭包分解。在您的特定情况下，通过应用该算法，我们可以发现依赖项
DF -> BC实际上已经丢失了。这里你的答案是正确的，因为第三个分解关系不在 3NF 中。正如您正确指出的那样，该关系的候选键是
{AEF, AFH}，而在关系中，依赖项AF -> B成立，这是违反 3NF 的依赖关系，因为AF不是超级键，并且B也不是素数属性。In the following I assume that the dependencies given are a cover of the dependencies of
R.When one decomposes a relation, not necessarily the dependencies of a cover applies to the decomposed relations. This happens when a decomposed relation does non contains all the attributes of a dependency. For instance, in your example,
DF -> BCdoes not hold in any ofR1,R2,R3, since the attributesDFBCare not all present in a single relation (we know that functional dependencies are meaningful only inside a relation).This not necessarily means that the decomposition suffers from a “loss of dependency”, since the definition is more complex: A decomposition of a relation schema R with attributes A and cover of dependencies F preserves the dependencies if and only if the union of the projections of the dependencies of F over the decomposed relation is a cover of F.
In Ullman, Principles of Database Systems, Computer Science Press, 1983, an algorithm is shown to compute the closure of the union of the projection of a set of dependency over a decomposition. In your particular case, by applying that algorithm we can find that the dependency
DF -> BCis actually lost.Here you answer is correct, since the third decomposed relation is not in 3NF. As you have correctly pointed out, the candidate keys for this relation are
{AEF, AFH}, while in the relation the dependencyAF -> Bhold, and this is a dependency that violates the 3NF sinceAFis not a superkey andBis not a prime attribute.