database 归一化 - 2NF 与 3NF
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6023091/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Normalisation - 2NF vs 3NF
提问by Marcus
Struggling to see the differences between them. I know we say 2NF is "the whole key" and 3NF "nothing but the key".
努力发现它们之间的差异。我知道我们说 2NF 是“整个关键”，而 3NF 是“关键”。
Referencing this great answer by Smashery: What are 1NF, 2NF and 3NF in database design?
参考 Smashery 的这个很棒的答案：数据库设计中的 1NF、2NF 和 3NF 是什么？
The example used for 3NF is exactly the same as 2NF - its a field which is dependant on only one key attribute. How is the example for 3NF different from the one for 2NF?
用于 3NF 的示例与 2NF 完全相同——它是一个仅依赖于一个关键属性的字段。3NF 的示例与 2NF 的示例有何不同？
Thanks
谢谢
采纳答案by nvogel
Suppose that some relation satisifies a non-trivial functional dependency of the form A->B, where B is a nonprime attribute.
假设某个关系满足 A->B 形式的非平凡函数依赖，其中 B 是非素数属性。
2NF is violated if A is not a superkey but is a proper subset of a candidate key
如果 A 不是超键而是候选键的真子集，则违反 2NF
3NF is violated if A is not a superkey
如果 A 不是超键，则违反 3NF
You have spotted that the 3NF requirement is just a special case (but not really so special) of the 2NF requirement. 2NF in itself is not very important. The important issue is whether A is a superkey, not whether A just happens to be some part of a candidate key.
您已经发现 3NF 要求只是 2NF 要求的一个特例（但并不是那么特殊）。2NF本身并不是很重要。重要的问题是 A 是否是超级键，而不是 A 是否恰好是候选键的某个部分。
回答by Lordferrous
2NFallows non-prime attributes to be functionally dependent on non-prime attributes
2NF允许非主属性在功能上依赖于非主属性
but
但
3NFallows non-prime attributes to be functionally dependent only on super key
3NF允许非主属性在功能上仅依赖于超级键
Thus,when a table is in 3NF it is in 2NF and 3NF is stricter than 2NF
因此，当一个表在 3NF 中时它在 2NF 中并且 3NF 比 2NF 更严格
Hope this helps...
希望这可以帮助...
回答by Unreason
Since you ask very specific question about an answer for existing so questionhere is an explanation of that (and basically I'll say what dportas already said in his answer, but in more words).
由于您提出了关于现有答案的非常具体的问题，因此这里的问题是对此的解释（基本上我会说 dportas 在他的回答中已经说过的内容，但更多的话）。
The examples of design that is not in 2NF and not in 3NF are not the same.
不属于 2NF 和不属于 3NF 的设计示例是不一样的。
Yes, the dependency in both cases is on a single field.
是的，这两种情况下的依赖都在一个字段上。
However, in non 2NF example:
但是，在非 2NF 示例中：
- dependency is on the part of the primary key
- 依赖是主键的一部分
while in non 3NF example (which is in 2NF):
而在非 3NF 示例中（在 2NF 中）：
- dependency is on a field that is not a part of the primary key(and also notice that in that example it does satisfy2NF; this is to show that even if you check for 2NF you should also check for 3NF)
- 依赖项不是主键的一部分（还要注意，在该示例中它确实满足2NF；这是为了表明即使您检查 2NF，您也应该检查 3NF）
In both cases to normalize you would create additional table which would not exhibit update anomalies (example of update anomaly: in 2NF example, what happens if you update Coursenamefor IT101|2009-2, but not for IT101|2009-1? You get inconsistent=meaningless=unusable data).
在这两种情况下，要规范化，您将创建不会出现更新异常的附加表（更新异常的示例：在 2NF 示例中，如果您更新Coursenamefor IT101|2009-2，而不是 for会发生什么IT101|2009-1？您会得到不一致=无意义=不可用数据）。
So, if you memorize the key, the whole key and nothing but the key, which covers both 2NF and 3NF, that should work for you in practice when normalizing. The distinction between 2NF and 3NF might seem subtle to you (question if in the additional dependency the attribute(s) on which the data is dependent are part of candidate key or not) - and, well, it is - so just accept it.
因此，如果您记住了 key、整个 key 并且只记住了 key ，它涵盖了 2NF 和 3NF，那么在规范化时在实践中应该对您有用。2NF 和 3NF 之间的区别对您来说似乎很微妙（问题是在附加依赖项中，数据所依赖的属性是否是候选键的一部分） - 好吧，它是 - 所以就接受它。
回答by tiktak
You have achieved the 3rd NF when there are no relations between the key and other columns that don't depend on it.
当键和其他不依赖于它的列之间没有关系时，您已经实现了第三个 NF。
Not sure my professor would have said that like this but this is what it is.
不确定我的教授会不会这样说，但事实就是这样。
If you're "in the field". Forget about the definitions. Look for "best practices". One is DRY : Don't Repeat Yourself.
如果你“在现场”。忘记定义。寻找“最佳实践”。一种是 DRY：不要重复自己。
If you follow that principle, you already master everything you need for NF.
如果您遵循该原则，您就已经掌握了 NF 所需的一切。
Here is an example. Your table has the following schema:
这是一个例子。您的表具有以下架构：
PERSONS : id, name, age, car make, car model
Age and name are related to the person entry (=> id) but the model depends to the car and not the person.
年龄和姓名与人物条目（=> id）有关，但模型取决于汽车而不是人物。
Then, you would split it in two tables:
然后，您将其拆分为两个表：
PERSONS : id, name, age, car_models_id (references CAR_MODELS.id)
CAR_MODELS : id, name, car_makes_id (references CAR_MAKES.id)
CAR_MAKES : id, name
You can have replication in 2FN but not in 3FN anymore.
您可以在 2FN 中进行复制，但不能再在 3FN 中进行复制。
Normalization is all about non-replication, consistency, and from another point of view foreign keys and JOINs.
规范化是关于非复制、一致性以及从另一个角度来看外键和 JOIN。
The more normalized the better for data but not for performance nor understanding if it gets really too complicated.
数据越规范化越好，但对于性能或理解是否真的太复杂了。
回答by rashedcs
2NF follows the partial dependency whereas 3NF follows the transitive functional dependency. It is important to know that the 3NF must be in 2NF and support transitive functional dependency.
2NF 遵循部分依赖，而 3NF 遵循传递函数依赖。重要的是要知道 3NF 必须在 2NF 中并支持传递函数依赖。
