理解3NF：请用普通英语 [英] Understanding 3NF: plain English please
问题描述
我正在研究一个示例问题，在该问题中，我们试图确定以下哪个关系为第三范式（3NF）。这是我们得到的关系：
I am working through an example problem in which we are trying to identify which of the following relations is in Third Normal Form (3NF). Here are the relations we are given:
R1（ABCD）
ACD-> B AC-> D D-> C AC-> B
R2（ABCD）
AB-> C ABD-> C ABC-> D AC-> D
R2(ABCD)
AB -> C ABD -> C ABC -> D AC -> D
R3（ABCD）
C-> B A-> B CD-> A BCD-> A
R3(ABCD)
C -> B A -> B CD -> A BCD -> A
R4（ABCD）
C-> B B-> A AC-> D AC-> B
R4(ABCD)
C -> B B -> A AC -> D AC -> B
我知道答案是3NF中的R1，但是我很难理解确定违反3NF的步骤。有人可以用通俗易懂的英语将每种关系分解吗？如果您可以逐步向我展示每种关系如何违反3NF规则之一，那将非常有帮助：
I know the answer is R1 is in 3NF, but I'm having a hard time understanding the steps to go about determining what violates 3NF. Can someone break it down in plain English for each of the relations? It would be extremely helpful if you can show me in steps how each relation might violate one of the 3NF rules:
- X-> A，那么A是X的子集
- X是超键
- A是R
某些键的一部分
- X -> A, then A is a subset of X
- X is a superkey
- A is a part of some key for R
对于R1，我要做的第一步是将其分解为闭包：
For R1, the first step I take is breaking it down into closures:
ACD + = ABCD
AC + = ABCD
D + = C
ACD+ = ABCD
AC+ = ABCD
D+ = C
ACD和AC是超键，它们满足规则2。
1. D-> C，但C不是D的子集。违反了规则1。
2. D不是超键。违反了规则2。
3. C是R的某些键的一部分。C是AC和ACD的一部分。那么，规则3是否成立？
ACD and AC are superkeys, which satisfy rule 2.
1. D -> C, but C is not a subset of D. Rule 1 violated.
2. D is not a superkey. Rule 2 is violated.
3. C is a part of some key for R. C is a part of AC and ACD. So, rule 3 is upheld?
不确定我是否正确地执行了这些步骤，所以对于那些苦于这些概念的人，请尽可能简单地将其分解。谢谢。
Not sure if I'm even doing these steps right, so please break it down as simple as possible for someone struggling with these concepts. Thanks.
推荐答案
对于第三种形式的关系，我找到了最好的定义（ 3NF）如下：
The best definition I've found for a relation that is in third normal form (3NF) is the following:
A relation schema R is in 3NF if, whenever a function dependency X -> A holds in R, either
(a) X is a superkey of R, or
(b) A is a prime attribute of R.
现在需要澄清三个定义，键，超级键和原始属性。
Now there are three definitions that need clarification, key,superkey, and prime attribute.
对于定义，我们将使用R1关系中的示例来描述它们：
For the definitions we will use examples from the R1 relation to describe them:
R1(ABCD)
ACD -> B AC -> D D -> C AC -> B
键：键是属性确定关系的每个属性。换句话说，这组属性将为您提供关系中未包含的所有其他属性。在以上示例的关系R1中，键为 AC 和 AD 。为什么 AC 是密钥？因为通过了解属性 A 和 C ，您可以确定其余属性 B 和 D 。为什么 AD 是键？相同的原因。 A 和 D 将最终确定 B 和 C 。
key: A key is the attribute that determines every attribute of the relation. In other words, it is the set of attributes that will give you all the other attributes of the relation that are not in the set. In relation R1 of the above example, the keys are AC and AD. Why is AC a key? Because by knowing attributes A and C you can determine the remaining attributes, B and D. Why is AD a key? The same reason. A and D will ultimately determine B and C.
超级键：超级键基本上是a的超集。键。超级键将始终包含该键，并且可能包含更多属性。在上一个示例中， AC 是键。因此， AC ， ACD ， ACB 等都是超键。请注意，键本身就是一个超键。
superkey: A superkey is basically a superset of a key. A superkey will contain the key always and potentially more attributes. In the previous example, AC is a key. Thus AC, ACD, ACB, etc. are superkeys. Note that a key itself is a superkey.
素数属性：素数属性基本上是属于其中一部分的属性的关键。因此 A 和 C 是主要属性，因为它们是键 AC 。但是请注意，键和超级键之间的区别。对于超级键 ACB ， B 不是主要属性，因为 B 不是密钥的一部分。只需将素数属性视为键的子集即可。
prime attribute: A prime attribute is basically an attribute that is part of a key. Thus A and C are prime attributes as they are part of the key AC. Take note however, the difference between a key and superkey. For the super key ACB, B is not a prime attribute since B is not part of the key. Just think of a prime attribute as a subset of a key.
现在让我们看一下四个关系：
Now let's look at the four relations:
R1(ABCD)
ACD -> B AC -> D D -> C AC -> B
R2(ABCD)
AB -> C ABD -> C ABC -> D AC -> D
R3(ABCD)
C -> B A -> B CD -> A BCD -> A
R4(ABCD)
C -> B B -> A AC -> D AC -> B
对于每个关系，我们将写下键和原始属性。然后我们将查看定义是否满足。
For each relation we will write down the keys and the prime attributes. Then we will see if the definition is satisfied.
R1:
keys: AC, AD
prime attributes: A, C, D
ACD-> B：左侧是一个超键。满足（a）。
ACD -> B: Left side is a superkey. Satisfies (a).
AC-> D：左侧是键，因此是超级键。满足（a）。
AC -> D: Left side is a key and thus a superkey. Satisfies (a).
D-> C：左侧不是超级键。不满足（a）。但是，右侧是主要属性。满足（b）。
D -> C: Left side is not a superkey. Does not satisfy (a). However, right side is a prime attribute. Satisfies (b).
AC-> B：左侧是键。满足（a）。
AC -> B: Left side is a key. Satisfies (a).
在所有情况下都满足（a）或（b）。因此 R1 在 3NF 中。
Either (a) or (b) is satisfied in all cases. Thus R1 is in 3NF.
R2:
keys: AB
prime attributes: A, B
AB-> C：左侧是键，因此是超级键。满足（a）。
AB -> C: Left side is a key and thus a superkey. Satisfies (a).
ABD-> C：左侧是一个超键。满足（a）。
ABD -> C: Left side is a superkey. Satisfies (a).
ABC-> D：左侧是一个超键。满足（a）。
ABC -> D: Left side is a superkey. Satisfies (a).
AC-> D：左侧不是超级键。不满足（a）。右侧不是主要属性。不满足（b）。
AC -> D: Left side is not a superkey. Does not satisfy (a). Right side is not a prime attribute. Does not satisfy (b).
由于在所有情况下都不满足（a）或（b），因此 R2 不在 3NF 中。
Since (a) or (b) is not satisfied in all cases, R2 is not in 3NF.
R3:
keys: CD,
prime attributes: C, D
C- > B：左侧不是超级键。不满足（a）。右侧不是主要属性。不满足（b）。
C -> B: Left side is not a superkey. Does not satisfy (a). Right side is not a prime attribute. Does not satisfy (b).
由于我们已经找到了不满足（a）或（b）的情况，因此我们可以立即得出结论： R3 不在 3NF 中。
Since we have already found a case that does not satisfy either (a) or (b), we can immediately conclude that R3 is not in 3NF.
R4:
keys: C
prime attributes: C
C-> B：左侧是键，因此是超级键。满足（a）。
C -> B: Left side is a key and thus a superkey. Satisfies (a).
B->答：左侧不是超级键。不满足（a）。右侧不是主要属性。不满足（b）。
B -> A: Left side is not a superkey. Does not satisfy (a). Right side is not a prime attribute. Does not satisfy (b).
同样，我们可以在这里停下来，因为第二种情况都不满足（a）和（b）。关系 R4 不在 3NF 中。
Again, we can stop here as the second case satisfies neither (a) nor (b). The relation R4 is not in 3NF.
这篇关于理解3NF：请用普通英语的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
