什么是Boyce-Codd正常形式的良好KISS描述?[英] What is a good KISS description of Boyce-Codd normal form?

本文是小编为大家收集整理的关于什么是Boyce-Codd正常形式的良好KISS描述?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

什么是一个吻(保持简单,愚蠢)记住哪种boyce-codd的正常形式是什么,以及如何拿一张不正常的桌子和bcnf?

wikipedia

推荐答案

Chris Date的定义实际上相当不错,只要您了解他的含义:

每个属性

您的数据必须分为不同的,不同的属性/列/值,这些属性/列/值不取决于任何其他属性.您的全名是一个属性.您的生日是一个属性.您的年龄不是属性,而取决于当前日期,而不是您的生日.

必须代表事实

每个属性都是一个事实,而不是事实的集合.在属性中更改一位会改变整个含义.您的生日是事实.您的全名是事实吗?好吧,在某些情况下是,因为如果您更改姓氏,您的全名是不同的,对吗?但是对于家谱学家来说,您有一个姓氏和姓氏,如果您更改姓氏,您的姓氏不会改变,因此它们是单独的事实.

关于钥匙,

一个属性是特殊的,这是关键.关键是数据中所有信息必须唯一的属性,并且绝不能更改.您的全名不是关键,因为它可以更改.您的社交保险号不是关键,因为它们被重复使用.您的SSN Plus出生日期不是关键,即使不能重复使用该组合,因为属性不能成为两个事实的组合. GUID是关键.您增加的数字,永不重复使用是关键.

整个密钥,

单独的密钥必须足够[和必要!]来识别您的值;您不能拥有由不同键表示的相同数据,也不能足以确定事实的子集. 假设您有一个带有GUID密钥,名称和地址值的地址簿.如果他们代表不同的人并且不是"相同的数据",则可以将相同的名称带有不同的键两次. 如果玛丽·琼斯(Mary Jones)的会计名称更改给玛丽·史密斯(Mary Smith),那么销售中的玛丽·琼斯(Mary Jones)也不会更改她的名字. 另一方面,如果玛丽·史密斯(Mary Smith)和约翰·史密斯(John Smith)拥有相同的街道地址,并且确实是同一个地方,则不允许这样做.您必须与街道地址和一个新密钥创建一个新的键/价值对.

您也不允许将这个新单街地址的钥匙用作地址簿中的值,因为现在将两次代表同一街道地址密钥. 取而代之的是,您必须制作第三个密钥/值对,并使用通讯录密钥和街道地址密钥的值进行.您可以通过匹配他们的书籍密钥和地址密钥来找到一个人的街道地址.

,只有键

除了识别您的价值的钥匙外,必须没有其他内容.例如,如果允许您的地址为"泰姬陵"(假设只有一个),则不允许在同一记录中的城市价值 因为如果您知道地址,您也会知道这座城市.这也将使在另一个城市中有不止一个泰姬陵的可能性打开了可能性. 取而代之的是,您必须再次创建一个具有独特价值的次要位置键,例如TAJ,DC中的白宫等,依此类推,及其城市. 或禁止城市独有的"地址".

所以帮助我,codd.

其他推荐答案

以下是Wikipedia页面上的一些有用的摘录/p>

比尔·肯特(Bill Kent)以这种方式定义第三个正常形式:

每个非关键属性必须提供 关于密钥,整个密钥的事实, 除了钥匙外,别无其他."

要求非钥匙属性是 取决于"整个密钥"确保 该桌子在2NF中;更远 要求非钥匙属性是 取决于"钥匙" 确保表格为3nf.

chris date适应肯特的助记符来定义boyce-codd正常形式:

"每个属性必须代表事实 关于钥匙,整个密钥和 除了钥匙."这里 要求与每个人有关 表格中的属性,而不仅仅是 非钥匙属性.

当表具有多个复合候选密钥时,这将发挥作用,一个候选密钥中的属性对另一个候选密钥的部分具有依赖性.第三个正常形式不会禁止这一点,因为它不包括关键属性.但是BCNF也将规则应用于关键属性.

至于如何使表满足bcnf,您需要用另一个属性来表示额外的依赖性,并可能通过将属性拆分到另一个表中.

其他推荐答案

我搜索了" Boyce Codd正常形式",Wikipedia之后,这是第二个结果.我的教科书在关系数据库管理系统方面给出了一个非常简单的定义:

每个非平凡的FD的左侧都必须是超键.

- Garcia-Molina,Ullman和Widom的"数据库系统".

本文地址:https://www.itbaoku.cn/post/597457.html

问题描述

What is a KISS (Keep it Simple, Stupid) way to remember what Boyce-Codd normal form is and how to take a unnormalized table and BCNF it?

Wikipedia's info: not terribly helpful for me.

推荐答案

Chris Date's definition is actually quite good, so long as you understand what he means:

Each attribute

Your data must be broken into separate, distinct attributes/columns/values which do not depend on any other attributes. Your full name is an attribute. Your birthdate is an attribute. Your age is not an attribute, it depends on the current date which is not part of your birthdate.

must represent a fact

Each attribute is a single fact, not a collection of facts. Changing one bit in an attribute changes the whole meaning. Your birthdate is a fact. Is your full name a fact? Well, in some cases it is, because if you change your surname your full name is different, right? But to a genealogist you have a surname and a family name, and if you change your surname your family name does not change, so they are separate facts.

about the key,

One attribute is special, it's a key. The key is an attribute that must be unique for all information in your data and must never change. Your full name is not a key because it can change. Your Social Insurance Number is not a key because they get reused. Your SSN plus birthdate is not a key, even if the combination can never be reused, because an attribute cannot be a combination of two facts. A GUID is a key. A number you increment and never reuse is a key.

the whole key,

The key alone must be sufficient [and necessary!] to identify your values; you cannot have the same data represented by different keys, nor can a subset of the key columns be sufficient to identify the fact. Suppose you had an address book with a GUID key, name and address values. It is OK to have the same name appearing twice with different keys if they represent different people and are not the "same data". If Mary Jones in accounting changes her name to Mary Smith, Mary Jones in Sales does not change her name as well. On the other hand, if Mary Smith and John Smith have the same street address and it really is the same place, this is not allowed. You have to create a new key/value pair with the street address and a new key.

You are also not allowed to use the key for this new single street address as a value in the address book since now the same street address key would be represented twice. Instead, you have to make a third key/value pair with values of the address book key and the street address key; you find a person's street address by matching their book key and address key in this group of values.

and nothing but the key

There must be nothing other than the key that identifies your values. For example, if you are allowed an address of "The Taj Mahal" (assuming there is only one) you are not allowed a city value in the same record, since if you know the address you would also know the city. This would also open up the possibility of there being more than one Taj Mahal in a different city. Instead, you have to again create a secondary Location key with unique values like the Taj, the White House in DC, and so on, and their cities. Or forbid "addresses" that are unique to a city.

So help me, Codd.

其他推荐答案

Here are some helpful excerpts from the Wikipedia page on Third Normal Form:

Bill Kent defines Third Normal Form this way:

Each non-key attribute "must provide a fact about the key, the whole key, and nothing but the key."

Requiring that non-key attributes be dependent on "the whole key" ensures that a table is in 2NF; further requiring that non-key attributes be dependent on "nothing but the key" ensures that the table is in 3NF.

Chris Date adapts Kent's mnemonic to define Boyce-Codd Normal Form:

"Each attribute must represent a fact about the key, the whole key, and nothing but the key." Here the requirement is concerned with every attribute in the table, not just non-key attributes.

This comes into play when a table has multiple compound candidate keys, and an attribute within one candidate keys has a dependency on a part of another candidate key. Third Normal Form wouldn't prohibit this, because it excludes key attributes. But BCNF applies the rule to key attributes as well.

As for how to make a table satisfy BCNF, you need to represent the extra dependency, with another attribute and possibly by splitting attributes into another table.

其他推荐答案

I googled "boyce codd normal form" and after wikipedia this is the second result. My textbook gives a very simple definition in terms of relational database management systems:

The left side of every nontrivial FD must be a superkey.

-"Database Systems The Complete Book" by Garcia-Molina, Ullman and Widom.