归一化后的功能依赖性 [英] Functional dependencies after normalization
问题描述
我正在努力理解规范化过程的一个方面，在我对2NF（或3NF）的关系进行规范化之后，我不太了解如何处理功能依赖性。
让我用一个例子来描述它。
I'm struggling to understand one aspect of the normalization process, I don't quite understand what to do with functional dependencies after I normalize a relation to 2NF (or 3NF). Let me describe it using an example.
我的课本中的练习要求将与2NF，然后与3NF的关系归一化。给予关系
The exercise from my school book asks to normalize the relation to 2NF and then to 3NF. We are given a relation
R =（ABCDEF），F = {AD-> FE，BC-> E，FEA-> D ，AC-> DE，F-> E，BD-> A，F-> C，ABC-> AEF，B-> F}
R=(ABCDEF) , F={AD->FE, BC->E, FEA->D, AC->DE, F->E, BD->A, F->C, ABC->AEF, B->F}
在本书之后，我将功能依赖性最小化为
Following the book, I minimized the Functional Dependencies to
F = {AD-> F，AC-> D，BD -> A，F-> E，F-> C，B-> F}
F = {AD->F, AC->D, BD->A, F->E, F->C, B->F}
应该正确，因为我检查了它几次。现在我们可以找到键：
Which should be correct, since I checked it a few times. Now we can find the keys :
{AB}和{BD}
{AB} and {BD}
接下来，我可以看到
B-> F
B->F
破坏2NF，因为非密钥属性取决于密钥的一部分。因此，我们需要将我们的关系划分为两个新表：
Breaks 2NF, since a non-key attribute depends on a part of the key. So we need to "divide" our relation into two new tables :
R1 =（BFEC）和R2 =（ABD）
R1 = (BFEC) and R2 = (ABD)
在这里我迷路了。这些新表需要继承功能依赖关系，所以我的想法是这样做的：
Here's where I get lost. Those new tables need to "inherit" the functional dependencies, so my idea was to do it like this :
F1 = {F-> E，F-> C，B-> F}和F2 = {BD-> A}
F1 = {F->E, F->C, B->F} and F2 = {BD->A}
我试图以此为基础关系仅通过查看属性然后将它们配对即可获得哪个依赖项。但是，这给我们留下了来自原始集合的两个功能依赖性：
I was trying to base which relation gets which dependency just by looking at the attributes and then pairing them together. However, that leaves us with two functional dependencies from the original set :
AD-> F，AC-> D
AD->F, AC->D
我不知道哪里适合。我试图寻找一些背后的理论，但到目前为止还没有运气。
That I have no idea where to fit. I tried to look up some theory behind it, but no luck so far.
有人可以向我解释如何为每个新表找出新的函数依赖项吗？我在规范化过程中创建吗？
Could someone explain to me how do I figure out the new functional dependencies for each new table that I create in the process of normalization?
谢谢。
推荐答案
假设我们要无损地分解，以便保持原始关系的FD（功能依赖项）也保留在一个组件中，因此也保留在它们的联接中。只要组件共享一个组件的CK（候选关键字）的属性，我们就可以通过使用FD的一个组件的属性来实现这一目的，而通过从原始组件中删除确定的属性来获取另一个组件。您将被告知是这样。对于有问题的FD B，这里-表示分解{ABCDE}&的F {BF}，因为公共属性集{B}是后者的CK。
Suppose we want to losslessly decompose so that an FD (functional dependency) that holds in an original relation also holds in one of the components and so holds in their join. We can do this using the FD's attributes for one component while getting another component by dropping the determined attributes from the original, as long as the components share the attributes of a CK (candidate key) of one of them. You will have been told that that is so. Here for problematic FD B -> F that suggests decomposition {ABCDE} & {BF}, since common attribute set {B} is a CK of the latter.
因此，我们需要对 b进行划分。我们的关系分为两个新表：
R1 =（BFEC）和R2 =（ABD）
So we need to "divide" our relation into two new tables : R1 = (BFEC) and R2 = (ABD)
鉴于这些FD，这不是无损分解，但不清楚为什么会这样。 （共享列{B}不包括那些组件之一的CK。）
That is not a lossless decomposition given these FDs, and it's not clear why you think it is. (The shared columns {B} don't include a CK of one of those components.)
（该术语不是除，而是分解。不是清楚为什么不使用正确的术语而不是使用不表示该术语的不同术语，甚至使用吓人的引号表明您知道也不意味着这一点，甚至不说您的意思
(The term is not "divide", it is "decompose". It's not clear why instead of using the right term you used a different term that doesn't mean that, while even using scare quotes to show that you know it doesn't mean that, while not even saying what you do mean by it.)
您不能只将包含组件的FD的子集作为组件的封面。您需要查看持有的所有原始FD中的哪个具有组件的属性&所以握住组件。为什么？封面是一组FD，它表示所有持有&的FD。没有其他人。组件中可能有一个FD支撑物，但不在盖中，而是由存在的FDs集合隐含。如果所有这些集合都包含其属性不在组件中的FD，则该FD将不会被在组件中具有属性的封面FD的子集所隐含。因此，该子集将不会覆盖该组件。
You can't just take the subset of FDs in a cover that hold in a component as a cover for the component. You need to see which of all the original FDs that hold have the component's attributes & so hold in the component. Why? A cover is a set of FDs that imply all the FDs that hold & no others. There might be a FD holding in the component that isn't in the cover but is implied by sets of FDs that are. If all those sets include FDs whose attributes aren't in the component then that FD won't be implied by the subset of FDs of the cover that have attributes in the component. So that subset won't be a cover for the component.
通过如上所述拆分一些FD进行分解，可能意味着原始文档中包含的一些非平凡FD没有其所有属性都位于任一组件中，因此不包含在其中。 （还请注意，持有的FD包含列出的所有FD，而不仅仅是阿姆斯特朗公理给出的，而不仅仅是列出的FD。）尽管我们将进行无损分解，但我们无法检查后一个FD是否持有通过检查组件是否包含在组件中来进行联接。这称为不保留该FD。但是事实证明，在保留FD的同时，我们始终可以将其归一化为3NF。这就是为什么我们不通过遍历较低的NF或通过随机选择FD将其标准化为NF（正常形式），而是使用专门设计的算法来保留该NF的同时达到该NF的原因。
Decomposing by splitting off some FD as described above might mean that some non-trivial FD that holds in the original doesn't have all its attributes in either component and so doesn't hold in them. (Notice also that the FDs that hold include all the ones implied by the listed ones, given by Armstrong's axioms, not just the listed ones.) Then although we would have a lossless decomposition, we couldn't check that the latter FD holds in the join of the components by checking that it holds in the components. This is called not preserving that FD. But it turns out that we can always normalize to 3NF while preserving FDs. That is why we don't normalize to a NF (normal form) by going through lower NFs or by randomly picking FDs, but instead use an algorithm specially designed to get to that NF while preserving FDs.
查找&遵循信息建模和关系数据库教科书。 （数十个免费在线版本，还有学术幻灯片和课程。）
Find & follow an information modelling & relational database textbook. (Dozens are free online, also academic slides & courses.)
这里是Ullman的一些幻灯片中的Berstein的3NF（表示2NF）合成算法：
Here is Berstein's 3NF (which implies 2NF) synthesis algorithm from some slides by Ullman:
给出：关系R和持有R的FD的封面F
Given: a relation R and a cover F for the FDs that hold in R
- 查找C，它是F的规范封面
- 对于每个FD X-> C中的Y，创建与架构XY的关系
- 如果其架构是另一个架构的子集，则消除关系
- 如果没有创建架构，则取消关联far包含一个CK的R，添加一个包含CK的R的关系模式
- Find C, a canonical cover for F
- For each FD X -> Y in C, create a relation with schema XY
- Eliminate a relation if its schema is a subset of another
- If none of the schemas created so far contains a CK of R, add a relation schema containing a CK of R
这篇关于归一化后的功能依赖性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
