该表使用哪种最高范式？ [英] Which highest normal form is this table in?
问题描述
Ticket Vname Nname
1 Oli Seitz
1 Andi Hofmann
2 Oli Seitz
2 Oli Schmidt
2 Tim Schmidt
3 Tim Hofmann
此表表示人员映射（ Vname，Nname ）和票证（ Ticket ）。 Vname 和 Nname 共同标识一个人，但每个人（ Vname，Nname ）可以有多个票证（ Ticket ），并且票证可以分配给多个人。
This table represents a mapping of persons (Vname, Nname) and tickets (Ticket). Vname and Nname together identify a person, but every Person (Vname, Nname) can have multiple tickets (Ticket), and a ticket can be assigned to multiple people.
此表中的PK都是三列在一起。因此此表应为1NF，因为在一列中没有多维数据。
The PK in this table are all three columns together. So this table should be 1NF because there is no multi dimensional data in one column.
但是我很挣扎。我认为是2NF和3NF，因为我找不到任何功能依赖项。 （希望它们在英语和德语中都被称为功能性的）。
But then I struggle. I think it is 2NF and 3NF because I can´t find any functional dependencies. (Hope they are called functional in English as well as in German)
有人可以解释这张表的最高NF值，为什么吗？而要在5NF中制作它，我需要改变什么呢？
Can someone explain which highest NF this table is and why? And what would I have to change to make it in 5NF?
注意：这不是功课，这个问题来自讨论。
Note: This is not homework, this question emerged from a discussion.
推荐答案
1NF（第一范式）
由于定义上关系具有一个值每行每列，一列中的多维数据的概念没有意义。随意问别人有道理。随时问他们做什么意味着什么。
Since by definition a relation has one value per column per row, notions of "multi dimensional data in one column" don't make sense. Feel free to ask people to make sense. Feel free to ask them how whatever they do mean matters.
归一化为较高NF（标准形式）
对较高NF进行标准化的唯一事情与 1NF有关，因为它们都在试图简化设计以改进设计。
The only thing that normalization to higher NFs has to do with "1NF" is that they are both trying to simplify to improve designs.
您的关系不满足非平凡的FD（功能依赖性）。因此，它在 BCNF 中。
Your relation satisfies no non-trivial FDs (functional dependencies). So it is in BCNF.
您的关系不满足非平凡的MVD（多值依赖关系）。即，它不满足非平凡的二进制JD（联接依赖项）。也就是说，除了包括自身的一对投影之外，它不是任何一对投影的成员的连接。因此它是 4NF 。您可以通过将成对的投影并加入它们来查看。您也可以通过应用FD& MVD并识别它们，然后为它们应用推理规则。
Your relation satisfies no non-nontrivial MVDs (multi-valued dependencies). Ie it satisfies no non-trivial binary JDs (join dependencies). Ie it is not the join of the members of any pair of its projections other than a pair that includes itself. So it is in 4NF. You can see this by taking pairs of projections and joining them. You can also do it by applying definitions of FD & MVD and identifying them, then applying the rules of inference for them.
您的关系满足非平凡的JD * {{Ticket，Vname} ，{Vname，Nname}，{Ticket，Nname}} 。因此，它是一组投影的成员的联接，而不是包含自身的一组投影的联接。但是它的CK并不暗示JD。也就是说，投影的连接没有链，每个连接的共同属性都包括原始的CK。因此它不在 5NF 中。您可以通过获取多组投影并将其合并来查看。没有任何一种算法可以确定关系能够比蛮力更好地满足非平凡的JD。
Your relation satisfies the non-nontrivial JD *{{Ticket, Vname}, {Vname, Nname}, {Ticket, Nname}}. So it is the join of the members of a set of its projections other than a set that includes itself. But that JD is not implied by its CKs. Ie there is no chain of joins of its projections where every join's common attributes includes a CK of the original. So it is not in 5NF. You can see this by taking sets of projections and joining them. There is no algorithm to determine what non-trivial JDs a relation satisfies with complexity better than brute force.
关系含义/谓词
另一方面，假设您知道关系的含义，以至您知道该关系包含元组，而这些元组可以根据做出正确的陈述（特征）谓词可表示为其他事物的谓词，例如
On the other hand, suppose you knew the relation's meaning to the extent that you knew that it holds tuples that make a true statement from a (characteristic) predicate expressible as the conjunction of others, say
ticket Ticket was submitted by a person with first name Vname
AND there is a person with name Vname Nname
AND ticket Ticket was submitted by a person with last name Nname
$ b的人提交的
$ b
连接的设计目的是使其输出谓词与输入谓词的与。因此，您会知道要检查原始文件的任何对应分解是否满足JD（即，来自合词的关系是否是原始文件的投影），并检查原始文件的CK是否隐含JD。
Join is designed so that the predicate of its output is the AND of the predicates of its inputs. So you would know to check for whether any corresponding decompositions of the original satisfy the JD (ie whether the relations from the conjuncts are projections of the original) and so to check whether the JD is implied by the original's CKs.
归一化为较高NF的要点是，当一个关系的谓词可以表示为其他关系的连接，并且它们的关系是原始关系的投影时，JD成立。因此，我们可以使用更简单的分离相反，因为我们仍然没有更新异常，所以我们最好在成对共享的CK上加入/和关系/谓词。 （如果FD {x，...}-> a 成立，则某个MVD成立&某个二进制JD成立，并且该关系的谓词可以表示为 ... AND a = f（x，...）。）
The point of normalization to higher NFs is that a JD holds when a relation's predicate can be expressed as the conjunction of others and their relations are projections of the original, so we can use the simpler separate relations instead, except we might as well JOIN/AND the relations/predicates on pairwise shared CKs because there are still no update anomalies. (If FD {x, ...} -> a holds then a certain MVD holds & a certain binary JD holds and the predicate of the relation can be expressed as ... AND a = f(x, ...).)
请注意，与5NF是为了减少更新异常，事实证明，它们从BCNF和AMP之间的ETNF开始消失。 5NF。但是5NF设计在从以谓词添加AND为代价的关系较少的意义上来说仍然更简单。请注意，MVD和持有JD的人很难找到，这是因为与其直观地比较，它们的设计显然很糟糕，因此它们从未被提出，因为它们的谓词是其他谓词的结合。因此，与声称因违反JD很少见5NF并不重要的说法相反，5NF是唯一重要的NF。 （SQL系统不支持处理可能由5NF设计引起的所有完整性约束，因此，由于无知导致人们声称应该满足3NF的要求。）
Note that contrary to claims that 5NF is to reduce update anomalies, it turned out that they disappear as of ETNF which lies between BCNF & 5NF. But a 5NF design is still simpler in the sense that there are fewer relations at the cost of adding ANDs to predicates. Note that MVDs & JDs that hold are hard to find only because designs with them are intuitively obviously bad so they never get proposed, because their predicates are the conjunction of others. Thus contrary to claims that 5NF is unimportant because violating JDs are rare, 5NF is the only NF that matters. (SQL systems don't support dealing with all the integrity constraints that can arise from 5NF designs, so that and ignorance leads to claims that one should settle for 3NF.)
您需要找到NF的定义以及它们为何重要。
You need to find definitions of the NFs and why they matter.
更多关于谓词&关系模型。
（我之所以回答这个问题，是因为即使在教科书中，所获得的智慧也是如此混乱。）
(I only answered this question because received wisdom, even in textbooks, is such a mess.)
附录
Projections&加入。 （我打算将<a href=\"https://stackoverflow.com/help/mcve\">最小，完整和可验证的示例留给您。但是JD的持有遭到另一名答复者的质疑，因此< a href = http://sqlfiddle.com/#!9/3fb025/25 rel = nofollow noreferrer>这是一个sqlfiddle 。）
Projections & joins. (I was going to leave the Minimal, Complete, and Verifiable Example to you. But the JD holding was disputed by another answerer so here is an sqlfiddle.)
T
1 Oli Seitz
1 Andi Hofmann
2 Oli Seitz
2 Oli Schmidt
2 Tim Schmidt
3 Tim Hofmann
project Ticket, Vname (T)
1 Oli
1 Andi
2 Oli
2 Tim
3 Tim
project Vname, Nname (T)
Oli Seitz
Andi Hofmann
Oli Schmidt
Tim Schmidt
Tim Hofmann
project Ticket, Vname (T) join project Vname, Nname (T)
1 Oli Seitz
1 Oli Schmidt
1 Andi Hofmann
2 Oli Seitz
2 Oli Schmidt
2 Tim Schmidt
2 Tim Hofmann
3 Tim Schmidt
3 Tim Hofmann
project Ticket, Nname (T)
1 Seitz
1 Hofmann
2 Seitz
2 Schmidt
3 Hofmann
project Ticket, Vname (T) join project Vname, Nname (T)
join project Ticket, Nname (T)
1 Oli Seitz
1 Andi Hofmann
2 Oli Seitz
2 Oli Schmidt
2 Tim Schmidt
3 Tim Hofmann
这篇关于该表使用哪种最高范式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
