database 3NF 和 BCNF 的简单区别（必须能解释给一个 8 岁的孩子）

Difference between 3NF and BCNF in simple terms (must be able to explain to an 8-year old)

提问by Arnab Datta

I have read the quote : data depends on the key [1NF], the whole key [2NF] and nothing but the key [3NF].

我已经阅读了引用： 数据取决于密钥 [1NF]，整个密钥 [2NF] 和密钥 [3NF] 除外。

However, I am having trouble understanding 3.5NF or BCNF as it's called. Here is what I understand :

但是，我无法理解所谓的 3.5NF 或 BCNF。这是我的理解：

• BCNF is stricter than 3NF
• left side of any FD in the table must be a superkey (or at least a candidate key)

• BCNF 比 3NF 更严格
• 表中任何 FD 的左侧必须是超键（或至少是候选键）

So why is it then, that some 3NF tables are not in BCNF? I mean, the 3NF quote explicitly says "nothing but the key" meaning that all attributes depend solely on the primary key. The primary key is, after all, a candidate key until it is chosen to be our primary key.

那么为什么有些 3NF 表不在 BCNF 中呢？我的意思是，3NF 引用明确表示“只有键”，这意味着所有属性都仅依赖于主键。毕竟，主键是一个候选键，直到它被选为我们的主键。

If anything is amiss regarding my understanding so far, please correct me and thanks for any help you can provide.

如果到目前为止我的理解有任何问题，请纠正我，并感谢您提供的任何帮助。

回答by Bill Karwin

Your pizza can have exactly three topping types:

您的比萨饼可以正好有三种配料类型：

• one type of cheese
• one type of meat
• one type of vegetable

• 一种奶酪
• 一种肉
• 一种蔬菜

So we order two pizzas and choose the following toppings:

因此，我们订购了两个比萨饼并选择以下配料：

Pizza    Topping     Topping Type
-------- ----------  -------------
1        mozzarella  cheese
1        pepperoni   meat
1        olives      vegetable
2        mozzarella  meat
2        sausage     cheese
2        peppers     vegetable

Wait a second, mozzarella can't be both a cheese and a meat! And sausage isn't a cheese!

等一下，马苏里拉奶酪不能既是奶酪又是肉！香肠不是奶酪！

We need to prevent these sorts of mistakes, to make mozzarella alwaysbe cheese. We should use a separate table for this, so we write down that fact in only one place.

我们需要防止这些错误，让马苏里拉奶酪永远是奶酪。我们应该为此使用一个单独的表格，所以我们只在一个地方写下这个事实。

Pizza    Topping
-------- ----------
1        mozzarella
1        pepperoni
1        olives
2        mozzarella 
2        sausage
2        peppers

Topping     Topping Type
----------  -------------
mozzarella  cheese
pepperoni   meat
olives      vegetable
sausage     meat
peppers     vegetable

---

---

That was the explanation that an 8 year-old might understand. Here is the more technical version.

这是一个8岁的孩子可能会理解的解释。这是更具技术性的版本。

BCNF acts differently from 3NF only when there are multiple overlapping candidate keys.

仅当存在多个重叠的候选键时，BCNF 的行为与 3NF 不同。

The reason is that the functional dependency X -> Yis of course true if Yis a subset of X. So in any table that has only one candidate key and is in 3NF, it is already in BCNF because there is no column (either key or non-key) that is functionally dependent on anything besides that key.

原因是X -> Y如果Y是 的子集，函数依赖当然是正确的X。因此，在任何只有一个候选键且在 3NF 中的表中，它已经在 BCNF 中，因为没有任何列（键或非键）在功能上依赖于除该键之外的任何内容。

Because each pizza must have exactly one of each topping type, we know that (Pizza, Topping Type) is a candidate key. We also know intuitively that a given topping cannot belong to different types simultaneously. So (Pizza, Topping) must be unique and therefore is also a candidate key. So we have two overlapping candidate keys.

因为每个比萨饼必须有每种配料类型中的一种，所以我们知道 (Pizza, Topping Type) 是一个候选键。我们也直观地知道给定的浇头不能同时属于不同的类型。所以 (Pizza, Topping) 必须是唯一的，因此也是一个候选键。所以我们有两个重叠的候选键。

I showed an anomaly where we marked mozarella as the wrong topping type. We know this is wrong, but the rule that makes it wrong is a dependency Topping -> Topping Typewhich is not a valid dependency for BCNF for this table. It's a dependency on something other than a whole candidate key.

我展示了一个异常，我们将马苏里拉奶酪标记为错误的浇头类型。我们知道这是错误的，但是导致错误的规则是一个依赖项Topping -> Topping Type，它不是该表的 BCNF 的有效依赖项。它依赖于除整个候选键之外的其他东西。

So to solve this, we take Topping Type out of the Pizzas table and make it a non-key attribute in a Toppings table.

因此，为了解决这个问题，我们从 Pizzas 表中取出 Topping Type，并将其设为 Toppings 表中的非键属性。

回答by nvogel

The subtle difference is that 3NF makes a distinction between key and non-key attributes (also called non-primeattributes) whereas BCNF does not.

细微的区别是 3NF 区分了关键和非关键属性（也称为非主要属性），而 BCNF 没有。

This is best explained using Zaniolo's definitionof 3NF, which is equivalent to Codd's:

这最好使用Zaniolo 的3NF定义来解释，它相当于 Codd 的定义：

A relation, R, is in 3NF iff for every nontrivial FD (X->A) satisfied by R at least ONE of the following conditions is true:

(a) X is a superkey for R, or

(b) A is a key attribute for R

关系 R 在 3NF 中，当当 R 满足每个非平凡的 FD (X->A) 时，至少满足以下条件之一：

(a) X 是 R 的超键，或

(b) A 是 R 的关键属性

BCNF requires (a) but doesn't treat (b) as a special case of its own. In other words BCNF requires that every nontrivial determinant is a superkey even its dependent attributes happen to be part of a key.

BCNF 需要 (a) 但不将 (b) 视为其自身的特殊情况。换句话说，BCNF 要求每个非平凡行列式都是一个超级键，即使它的依赖属性碰巧是键的一部分。

A relation, R, is in BCNF iff for every nontrivial FD (X->A) satisfied by R the following condition is true:

(a) X is a superkey for R

关系 R 在 BCNF 中，当条件是 R 满足的每个非平凡 FD (X->A) 满足以下条件：

(a) X 是 R 的超键

BCNF is therefore more strict.

因此，BCNF 更加严格。

The difference is so subtle that what many people informally describe as 3NF is actually BCNF. For example, you stated here that 3NF means "data depends on the key[s]... and nothing but the key[s]", but that is really an informal description of BCNF and not 3NF. 3NF could more accurately be described as "non-key datadepends on the keys... and nothing but the keys".

差异是如此微妙，以至于许多人非正式地描述为 3NF 实际上是 BCNF。例如，您在此处表示 3NF 的意思是“数据取决于密钥 [s]……而仅是密钥 [s]”，但这实际上是对 BCNF 而不是 3NF 的非正式描述。3NF 可以更准确地描述为“非密钥数据取决于密钥......除了密钥之外什么都没有”。

You also stated:

你还说：

the 3NF quote explicitly says "nothing but the key" meaning that all attributes depend solely on the primary key.

3NF 引用明确表示“只有键”，这意味着所有属性仅依赖于主键。

That's an oversimplification. 3NF and BCNF and all the Normal Forms are concerned with allcandidate keys and/or superkeys, not just one "primary" key.

这过于简单化了。3NF 和 BCNF 以及所有范式都涉及所有候选键和/或超键，而不仅仅是一个“主”键。

回答by AGéoCoder

The difference between BCNF and 3NF

BCNF和3NF的区别

Using the BCNF definition

使用 BCNF 定义

If and only if for every one of its dependencies X → Y, at least one of the following conditions hold:

当且仅当对于它的每个依赖项 X → Y，至少满足以下条件之一：

• X → Y is a trivial functional dependency (Y ? X), or
• X is a super key for schema R

• X → Y 是一个平凡的函数依赖 (Y ? X)，或
• X 是模式 R 的超级键

and the 3NF definition

和 3NF 定义

If and only if, for each of its functional dependencies X → A, at least one of the following conditions holds:

当且仅当，对于其每个函数依赖项 X → A，至少满足以下条件之一：

• X contains A (that is, X → A is trivial functional dependency), or
• X is a superkey, or
• Every element of A-X, the set difference between A and X, is a prime attribute (i.e., each attribute in A-X is contained in some candidate key)

• X 包含 A（即 X → A 是平凡的函数依赖），或
• X 是一个超级键，或者
• AX 的每个元素，即 A 和 X 之间的集合差，是一个素数属性（即 AX 中的每个属性都包含在某个候选键中）

We see the following difference, in simple terms:

我们看到以下区别，简单来说：

• In BCNF: Every partial key (prime attribute) can onlydepend on a superkey,

• 在 BCNF 中：每个部分键（主属性）只能依赖于一个超键，

whereas

然而

• In 3NF: A partial key (prime attribute) can alsodepend on an attribute that is nota superkey (i.e. another partial key/prime attribute or even a non-prime attribute).

• 在 3NF 中：部分键（主属性）也可以依赖于不是超键的属性（即另一个部分键/主属性甚至非主属性）。

Where

在哪里

1. A prime attributeis an attribute found in a candidate key, and
2. A candidate keyis a minimal superkey for that relation, and
3. A superkeyis a set of attributes of a relation variable for which it holds that in all relations assigned to that variable, there are no two distinct tuples (rows) that have the same values for the attributes in this set.Equivalently a superkey can also be defined as a set of attributes of a relation schema upon which all attributes of the schema are functionally dependent. (A superkey always contains a candidate key/a candidate key is always a subset of a superkey. You can add any attribute in a relation to obtain one of the superkeys.)

1. 一个主属性是一个候选键找到一个属性，
2. 一个候选键是该关系的最小的超密钥，并
3. 甲超密钥是一组的关系可变的属性的用于其保持在分配给该变量的所有关系，不存在两个不同的元组具有在此set.Equivalently一个超密钥对的属性相同的值（行）也可以被定义为关系模式的一组属性，模式的所有属性在功能上都依赖于这些属性。（超键始终包含候选键/候选键始终是超键的子集。您可以在关系中添加任何属性以获得其中一个超键。）

That is, no partial subset (any non trivial subset except the full set) of a candidate key can be functionally dependent on anything other than a superkey.

也就是说，候选键的任何部分子集（除了完整集之外的任何非平凡子集）都不能在功能上依赖于除超级键之外的任何东西。

A table/relation not in BCNF is subject to anomalies such as the update anomalies mentioned in the pizza example by another user. Unfortunately,

不在 BCNF 中的表/关系会受到异常的影响，例如另一个用户在比萨示例中提到的更新异常。很遗憾，

• BNCF cannotalways be obtained, while
• 3NF can alwaysbe obtained.

• BNCF不能总是获得，而
• 3NF总是可以得到的。

3NF Versus BCNF Example

3NF 与 BCNF 示例

An example of the difference can currently be found at "3NF table not meeting BCNF (Boyce–Codd normal form)" on Wikipedia, where the following table meets 3NF but not BCNF because "Tennis Court" (a partial key/prime attribute) depends on "Rate Type" (a partial key/prime attribute that is nota superkey), which is a dependency we could determine by asking the clients of the database, the tennis club:

目前可以在维基百科上的“ 3NF table not meet BCNF (Boyce-Codd normal form)”中找到差异的一个例子，下表满足 3NF 但不满足 BCNF，因为“网球场”（部分键/主要属性）取决于关于“Rate Type”（部分键/主要属性，不是超级键），这是我们可以通过询问数据库客户端网球俱乐部来确定的依赖关系：

Today's Tennis Court Bookings(3NF, notBCNF)

今天的网球场预订（3NF，不是BCNF）

Court   Start Time  End Time    Rate Type
------- ----------  --------    ---------
1       09:30       10:30       SAVER
1       11:00       12:00       SAVER
1       14:00       15:30       STANDARD
2       10:00       11:30       PREMIUM-B
2       11:30       13:30       PREMIUM-B
2       15:00       16:30       PREMIUM-A

The table's superkeys are:

该表的超级键是：

S1 = {Court, Start Time}
S2 = {Court, End Time}
S3 = {Rate Type, Start Time}
S4 = {Rate Type, End Time}
S5 = {Court, Start Time, End Time}
S6 = {Rate Type, Start Time, End Time}
S7 = {Court, Rate Type, Start Time}
S8 = {Court, Rate Type, End Time}
ST = {Court, Rate Type, Start Time, End Time}, the trivial superkey

The 3NF problem: The partial key/prime attribute "Court" is dependent on something other than a superkey. Instead, it is dependent on the partial key/prime attribute "Rate Type". This means that the user must manually change the rate type if we upgrade a court, or manually change the court if wanting to apply a rate change.

3NF 问题：部分键/主属性“Court”依赖于除超级键以外的其他东西。相反，它取决于部分键/主要属性“Rate Type”。这意味着如果我们升级球场，用户必须手动更改费率类型，或者如果想要应用费率更改，则必须手动更改球场。

• But what if the user upgrades the court but does not remember to increase the rate? Or what if the wrong rate type is applied to a court?

• 但是如果用户升级了球场但不记得提高费率怎么办？或者如果将错误的费率类型应用于法院怎么办？

(In technical terms, we cannot guarantee that the "Rate Type" -> "Court" functional dependency will not be violated.)

（在技术方面，我们不能保证“Rate Type”->“Court”函数依赖不会被违反。）

The BCNF solution: If we want to place the above table in BCNF we can decompose the given relation/table into the following two relations/tables (assuming we know that the rate type is dependent on only the court and membership status, which we could discover by asking the clients of our database, the owners of the tennis club):

BCNF 的解决方案：如果我们想把上面的表放在 BCNF 中，我们可以将给定的关系/表分解成以下两个关系/表（假设我们知道费率类型只取决于法院和会员身份，我们可以通过询问我们数据库的客户，网球俱乐部的所有者来发现）：

Rate Types(BCNFand the weaker 3NF, which is implied by BCNF)

速率类型（BCNF和较弱的 3NF，由 BCNF 隐含）

Rate Type   Court   Member Flag
---------   -----   -----------
SAVER       1       Yes
STANDARD    1       No
PREMIUM-A   2       Yes
PREMIUM-B   2       No

Today's Tennis Court Bookings(BCNFand the weaker 3NF, which is implied by BCNF)

今天的网球场预订（BCNF和较弱的 3NF，这是 BCNF 所暗示的）

Member Flag     Court     Start Time   End Time
-----------     -----     ----------   --------
Yes             1         09:30        10:30
Yes             1         11:00        12:00
No              1         14:00        15:30
No              2         10:00        11:30
No              2         11:30        13:30
Yes             2         15:00        16:30

Problem Solved: Now if we upgrade the court we can guarantee the rate type will reflect this change, and we cannot charge the wrong price for a court.

问题已解决：现在，如果我们升级球场，我们可以保证费率类型会反映这一变化，并且我们不会为球场收取错误的价格。

(In technical terms, we can guarantee that the functional dependency "Rate Type" -> "Court" will not be violated.)

（在技术上，我们可以保证不会违反“Rate Type”->“Court”的函数依赖。）

回答by smartnut007

All good answers. To put it in simple language [BCNF] No partial key can depend on a key.

所有的好答案。简而言之 [BCNF] 没有部分密钥可以依赖于密钥。

i.e No partial subset ( i.e any non trivial subset except the full set ) of a candidate key can be functionally dependent on some candidate key.

即，候选键的任何部分子集（即，除完整集之外的任何非平凡子集）都不能在功能上依赖于某个候选键。

回答by KGhatak

Answers by ‘smartnut007', ‘Bill Karwin', and ‘sqlvogel' are excellent. Yet let me put an interesting perspective to it.

“ smartnut007”、“ Bill Karwin”和“ sqlvogel”的回答非常出色。然而，让我提出一个有趣的观点。

Well, we have prime and non-prime keys.

好吧，我们有质数和非质数键。

When we focus on how non-primes depend on primes, we see two cases:

当我们关注非素数如何依赖素数时，我们会看到两种情况：

Non-primes can be dependent or not.

非质数可以是依赖的，也可以不是。

• When dependent:we see they must depend on a full candidate key. This is 2NF.

• When not dependent:there can be no-dependency or transitive dependency

  • Not even transitive dependency:Not sure what normalization theory addresses this.
  • When transitively dependent:It is deemed undesirable. This is 3NF.

• 当依赖时：我们看到它们必须依赖于一个完整的候选键。这是2NF。

• 当不依赖时：可以有无依赖或传递依赖

  • 甚至不是传递依赖：不确定规范化理论是如何解决这个问题的。
  • 当传递依赖时：它被认为是不可取的。这是3NF。

What about dependencies among primes?

素数之间的依赖关系呢？

Now you see, we're not addressing the dependency relationship among primesby either 2nd or 3rd NF. Further such dependency, if any, is not desirable and thus we've a single rule to address that. This is BCNF.

现在你看，我们没有通过 2nd 或 3rd NF 来解决素数之间的依赖关系。此外，这种依赖（如果有的话）是不可取的，因此我们有一个规则来解决这个问题。这是BCNF。

Referring to the example from Bill Karwin's post here, you'll notice that both ‘Topping', and ‘Topping Type' are prime keys and have a dependency. Had they been non-primes with dependency, then 3NF would have kicked in.

参考Bill Karwin在此处的帖子中的示例，您会注意到“ Topping”和“ Topping Type”都是主键并且具有依赖性。如果它们不是具有依赖性的非质数，那么 3NF 就会出现。

Note:

笔记：

The definition of BCNF is very generic and without differentiating attributes between prime and non-prime. Yet, the above way of thinking helps to understand how some anomaly is percolated even after 2nd and 3rd NF.

BCNF 的定义非常通用，没有区分质数和非质数之间的属性。然而，上述思维方式有助于理解即使在第 2 次和第 3 次 NF 之后某些异常是如何渗透的。

Advanced Topic: Mapping generic BCNF to 2NF & 3NF

高级主题：将通用 BCNF 映射到 2NF 和 3NF

Now that we know BCNF provides a generic definition without reference to any prime/non-prime attribues, let's see how BCNF and 2/3 NF's are related.

现在我们知道 BCNF 提供了一个没有参考任何质数/非质数属性的通用定义，让我们看看 BCNF 和 2/3 NF 是如何相关的。

First, BCNF requires (other than the trivial case) that for each functional dependency X -> Y(FD), X should be super-key. If you just consider any FD, then we've three cases - (1) Both X and Y non-prime, (2) Both prime and (3) X prime and Y non-prime, discarding the (nonsensical) case X non-prime and Y prime.

首先，BCNF 要求（除了微不足道的情况）对于每个函数依赖X -> Y（FD），X 应该是超级键。如果你只考虑任何 FD，那么我们有三种情况 - (1) X 和 Y 非素数，(2) 两个素数和 (3) X 素数和 Y 非素数，丢弃（无意义的）情况 X 非-素数和Y素数。

For case (1), 3NF takes care of.

对于情况（1），3NF 负责。

For case (3), 2NF takes care of.

对于情况（3），2NF 负责。

For case (2), we find the use of BCNF

对于情况（2），我们发现使用 BCNF

回答by jferard

This is an old question with valuable answers, but I was still a bit confused until I found a real life example that shows the issue with 3NF. Maybe not suitable for an 8-year old child but hope it helps.

这是一个带有有价值答案的老问题，但在我找到一个显示 3NF 问题的真实示例之前，我仍然有点困惑。也许不适合 8 岁的孩子，但希望它有所帮助。

Tomorrow I'll meet the teachers of my eldest daughter in one of those quarterly parent/teachers meetings. Here's what my diary looks like (names and rooms have been changed):

明天，我将在其中一个季度的家长/教师会议上见到我大女儿的老师。这是我的日记的样子（名称和房间已更改）：

Teacher   | Date             | Room
----------|------------------|-----
Mr Smith  | 2018-12-18 18:15 | A12 
Mr Jones  | 2018-12-18 18:30 | B10 
Ms Doe    | 2018-12-18 18:45 | C21 
Ms Rogers | 2018-12-18 19:00 | A08 

There's only one teacher per room and they never move. If you have a look, you'll see that: (1) for every attribute Teacher, Date, Room, we have only one value per row. (2) super-keys are: (Teacher, Date, Room), (Teacher, Date)and (Date, Room)and candidate keys are obviously (Teacher, Date)and (Date, Room).

每个房间只有一名老师，他们从不搬家。如果您看一看，您会看到： (1) 对于每个属性Teacher, Date, Room，每行只有一个值。(2) 超级键是：(Teacher, Date, Room), (Teacher, Date)and(Date, Room)和 候选键显然是(Teacher, Date)and (Date, Room)。

_{(Teacher, Room)is not a superkey because I will complete the table next quarter and I may have a row like this one (Mr Smith did not move!):}

_{(Teacher, Room)不是超级键，因为我将在下个季度完成表格，我可能会有这样的一行（史密斯先生没有动！）：}

Teacher  | Date             | Room
---------|------------------| ----
Mr Smith | 2019-03-19 18:15 | A12

What can we conclude? (1) is an informal but correct formulation of 1NF. From (2) we see that there is no "non prime attribute": 2NF and 3NF are given for free.

我们能得出什么结论？(1) 是 1NF 的非正式但正确的表述。从（2）我们看到没有“非素数属性”：2NF和3NF是免费给出的。

My diary is 3NF. Good! No. Not really because no data modeler would accept this in a DB schema. The Roomattribute is dependant on the Teacherattribute (again: teachers do not move!) but the schema does not reflect this fact. What would a sane data modeler do? Split the table in two:

我的日记是3NF。好的！不。不是真的，因为没有数据建模者会在 DB 模式中接受这一点。该Room属性取决于Teacher属性（再次：老师不要动！），但该模式并不能反映这一事实。一个理智的数据建模师会做什么？将表一分为二：

Teacher   | Date
----------|-----------------
Mr Smith  | 2018-12-18 18:15
Mr Jones  | 2018-12-18 18:30
Ms Doe    | 2018-12-18 18:45
Ms Rogers | 2018-12-18 19:00

And

和

Teacher   | Room
----------|-----
Mr Smith  | A12
Mr Jones  | B10
Ms Doe    | C21
Ms Rogers | A08

But 3NF does not deal with prime attributes dependencies. This is the issue: 3NF compliance is not enough to ensurea sound table schema design under some circumstances.

但是 3NF 不处理主要属性依赖关系。这就是问题所在：在某些情况下，3NF 合规性不足以确保合理的表模式设计。

With BCNF, you don't care if the attribute is a prime attribute or not in 2NF and 3NF rules. For every non trivial dependency (subsets are obviously determined by their supersets), the determinant is a complete super key. In other words, nothing is determined by something else than a complete super key(excluding trivial FDs). (See other answers for formal definition).

使用 BCNF，您不关心该属性是否是 2NF 和 3NF 规则中的主要属性。对于每个非平凡依赖（子集显然由它们的超集决定），行列式是一个完整的超键。换句话说，除了一个完整的超级密钥（不包括琐碎的 FD）之外，没有什么是由其他东西决定的。（有关正式定义，请参阅其他答案）。

As soon as Roomdepends on Teacher, Roommust be a subset of Teacher(that's not the case) or Teachermust be a super key (that's not the case in my diary, but thats the case when you split the table).

只要Room取决于Teacher,Room必须是的子集Teacher（不是这种情况）或Teacher必须是超级键（在我的日记中不是这种情况，但在拆分表格时就是这种情况）。

To summarize: BNCF is more strict, but in my opinion easier to grasp, than 3NF:

总结一下：BNCF 比 3NF 更严格，但在我看来更容易掌握：

• in most of cases, BCNF is identical to 3NF;
• in other cases, BCNF is what you think/hope 3NF is.

• 在大多数情况下，BCNF 与 3NF 相同；
• 在其他情况下，BCNF 就是您认为/希望 3NF 的样子。