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

问题描述

我已阅读报价:数据取决于密钥[1NF]，整个密钥[2NF]，除了密钥[3NF]什么都没有.

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

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

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

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

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.

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

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

推荐答案

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

Your pizza can have exactly three topping types:

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

所以我们点了两个披萨并选择以下配料:

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 always be 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

这是一个 8 岁的孩子可能会理解的解释.这是技术含量更高的版本.

---

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

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

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

The reason is that the functional dependency X -> Y is of course true if Y is 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.

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

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.

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

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 Type which is not a valid dependency for BCNF for this table. It's a dependency on something other than a whole candidate key.

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

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

这篇关于3NF和BCNF的简单区别(必须能解释给一个8岁的孩子)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！