博伊斯·科德范式（BCNF）

Boyce-Codd Normal Form or BCNF is an extension to the third normal form, and is also known as 3.5 Normal Form.

Boyce-Codd范式或BCNF是第三范式的扩展，也称为3.5范式。

演示地址

Follow the video above for complete explanation of BCNF. Or, if you want, you can even skip the video and jump to the section below for the complete tutorial.

请按照上面的视频完整了解BCNF。 或者，如果您愿意，甚至可以跳过视频并跳至下面的部分，以获取完整的教程。

In our last tutorial, we learned about the third normal form and we also learned how to remove transitive dependency from a table, we suggest you to follow the last tutorial before this one.

在上一篇教程中，我们了解了第三种范式，并且还学习了如何从表中删除传递依赖关系 ，建议您在学习本教程之前阅读上一篇教程。

BCNF规则 (Rules for BCNF)

For a table to satisfy the Boyce-Codd Normal Form, it should satisfy the following two conditions:

为了使表满足Boyce-Codd范式，它应满足以下两个条件：

1. It should be in the Third Normal Form.

   它应该是第三范式 。

2. And, for any dependency A → B, A should be a super key.

   并且，对于任何依赖关系A→B，A应该是一个超级键 。

The second point sounds a bit tricky, right? In simple words, it means, that for a dependency A → B, A cannot be a non-prime attribute, if B is a prime attribute.

第二点听起来有些棘手，对吗？ 简而言之，这意味着对于从属A→B，如果B是素 数属性 ，则A不能是非素 数属性 。

实例时间 (Time for an Example)

Below we have a college enrolment table with columns student_id, subject and professor.

下面我们有一个大学入学表格，其中包含student_id ， subject和professor列。

student_id	subject	professor
101	Java	P.Java
101	C++	P.Cpp
102	Java	P.Java2
103	C#	P.Chash
104	Java	P.Java

学生卡	学科	教授
101	Java	Java
101	C ++	科普
102	Java	P.Java2
103	C＃	查什
104	Java	Java

As you can see, we have also added some sample data to the table.

如您所见，我们还向表中添加了一些示例数据。

In the table above:

在上表中：

• One student can enrol for multiple subjects. For example, student with student_id 101, has opted for subjects - Java & C++

  一个学生可以报名参加多个学科。 例如，学生与student_id数据 101，选择了科目- Java和C ++

• For each subject, a professor is assigned to the student.

  对于每个科目，都会为学生分配一位教授。

• And, there can be multiple professors teaching one subject like we have for Java.

  而且，可能有多位教授像我们在Java领域一样教授一门学科。

What do you think should be the Primary Key?

您认为什么是主键 ？

Well, in the table above student_id, subject together form the primary key, because using student_id and subject, we can find all the columns of the table.

好吧，在student_id, subject上方的表中student_id, subject一起构成了主键，因为使用student_id和subject ，我们可以找到该表的所有列。

One more important point to note here is, one professor teaches only one subject, but one subject may have two different professors.

这里要注意的另一重要点是，一位教授只教授一门学科，但是一个学科可能有两位不同的教授。

Hence, there is a dependency between subject and professor here, where subject depends on the professor name.

因此，这里的subject和professor之间存在依存关系，其中subject取决于教授的姓名。

This table satisfies the 1st Normal form because all the values are atomic, column names are unique and all the values stored in a particular column are of same domain.

该表满足第一范式，因为所有值都是原子的，列名是唯一的，并且存储在特定列中的所有值都属于同一域。

This table also satisfies the 2nd Normal Form as their is no Partial Dependency.

该表还满足第二范式，因为它们没有部分依赖关系 。

And, there is no Transitive Dependency, hence the table also satisfies the 3rd Normal Form.

并且，由于没有传递依存关系 ，因此该表也满足第三范式 。

But this table is not in Boyce-Codd Normal Form.

但是此表的格式不是Boyce-Codd正常形式 。

为什么此表不在BCNF中？ (Why this table is not in BCNF?)

In the table above, student_id, subject form primary key, which means subject column is a prime attribute.

在上表中， student_id, subject形式的主键，这意味着subject列是主要属性 。

But, there is one more dependency, professor → subject.

但是，还有一个依赖性， professor → subject 。

And while subject is a prime attribute, professor is a non-prime attribute, which is not allowed by BCNF.

并且虽然subject是主要属性，但是professor 是非主要属性 ，这是BCNF不允许的。

如何满足BCNF？ (How to satisfy BCNF?)

To make this relation(table) satisfy BCNF, we will decompose this table into two tables, student table and professor table.

为了使该关系(表)满足BCNF，我们将把这个表分解为两个表， 学生表和教授表。

Below we have the structure for both the tables.

下面我们有两个表的结构。

Student Table

学生桌

student_id	p_id
101	1
101	2
and so on...

学生卡	p_id
101	1个
101	2
等等...

And, Professor Table

而且， 表教授

p_id	professor	subject
1	P.Java	Java
2	P.Cpp	C++
and so on...

p_id	教授	学科
1个	Java	Java
2	科普	C ++
等等...

And now, this relation satisfy Boyce-Codd Normal Form. In the next tutorial we will learn about the Fourth Normal Form.

现在，该关系满足博伊斯·科德范式。 在下一个教程中，我们将学习第四范式 。

更一般的解释 (A more Generic Explanation)

In the picture below, we have tried to explain BCNF in terms of relations.

在下图中，我们试图用关系来解释BCNF。

翻译自: https://www.studytonight.com/dbms/boyce-codd-normal-form.php