3NF和BCNF之间的区别在简单的术语(必须能够解释为8岁) [英] Difference between 3NF and BCNF in simple terms (must be able to explain to an 8-year old)

查看:368
本文介绍了3NF和BCNF之间的区别在简单的术语(必须能够解释为8岁)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我已阅读引文:
数据取决于键[1NF],整个键[2NF],除了键[3NF]。

I have read the quote : data depends on the key [1NF], the whole key [2NF] and nothing but the key [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

  • 该表必须是一个超级键(或至少一个候选键)

那么为什么会有一些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:


  • 一种奶酪

  • 一种类型的肉类

  • li>
  • 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

稍等一下,mozzarella不能同时是奶酪和肉类!和香肠不是奶酪!

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

我们需要防止这些错误,使mozzarella总是是奶酪。

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.

我显示一个异常,我们将mozarella标记为错误的顶部类型。我们知道这是错误的,但是错误的规则是依赖 Topping - >顶部类型,它不是此表的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表中取出顶部类型,并将其作为非关键属性a浇头表。

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岁)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆