3NF和BCNF有什么区别？ [英] What is the difference between 3NF and BCNF?

问题描述

有人可以向我解释3NF和BCNF之间的区别吗？这将是巨大的，如果你也可以提供一些例子。谢谢。

Can someone please explain the difference between 3NF and BCNF to me? It would be great if you could also provide some examples. Thanks.

推荐答案

3NF和BCNF之间的区别是微妙的。

The difference between 3NF and BCNF is subtle.

如果关系位于2NF中，取决于主键。换句话说，如果对于R中的每个函数依赖性X⟶A满足以下条件中的至少一个，则关系R在3NF中：

A relation is in 3NF if it is in 2NF and no non-prime attribute transitively depends on the primary key. In other words, a relation R is in 3NF if for each functional dependency X ⟶ A in R at least one of the following conditions are met:

1. X是R中的键或超级键


2. A是R中的素数属性

Example

给定以下关系：

Example

Given the following relation:

EMP_DEPT（firstName，employeeNumber，dateOfBirth，address，departmentNumber，departmentName ）

EMP_DEPT(firstName, employeeNumber, dateOfBirth, address, departmentNumber, departmentName)

员工只能在一个部门工作，每个部门有许多员工。

An employee can only work in one department and each department has many employees.

候选键 employeeNumber 。

考虑以下功能依赖项：

1. employeeNumber⟶firstName，dateOfBirth，address，departmentNumber


2. departmentNumber⟶departmentName

上面的定义是可能的，因为第二函数依赖不满足3NF的2个条件中的任何一个，所以关系EMP_DEPT不在3NF中：

Given the definition above it is possible to conclude that the relation EMP_DEPT is not in 3NF because the second functional dependency does not meet any of the 2 conditions of the 3NF:

1. departmentNumber不是EMP_DEPT中的键或超级键


2. departmentName不是EMP_DEPT中的素数属性

BCNF

定义

关系R在BCNF中，如果它在3NF中， R中的每个函数依赖性X⟶A是R中的密钥或超级密钥。换句话说，3NF和BCNF之间的唯一区别是在BCNF中它不存在3NF的第二条件。这使得BCNF比3NF更严格，因为在BCNF中的任何关系将在3NF中，但是不一定每个在3NF中的关系将在BCNF中。

BCNF

Definition

A relation R is in BCNF if it is in 3NF and for each functional dependency X ⟶ A in R, X is a key or superkey in R. In other words, the only difference between 3NF and BCNF is that in BCNF it is not present the second condition of the 3NF. This makes BCNF stricter than 3NF as any relation that is in BCNF will be in 3NF but not necessarily every relation that is in 3NF will be in BCNF.

给定以下关系：

STUDENT_COURSE（studentNumber，socialSecurityNumber，courseNumber）

STUDENT_COURSE(studentNumber, socialSecurityNumber, courseNumber)

学生可以帮助许多课程，在一门课程中可以有许多学生。

A student can assist to many courses and in a course there can be many students.

候选键是：

1. socialSecurityNumber ， courseNumber


2. ， courseNumber

1. socialSecurityNumber, courseNumber
2. studentNumber, courseNumber

考虑以下功能依赖项：

1. studentNumber⟶socialSecurityNumber


2. socialSecurityNumber⟶studentNumber

鉴于上述定义，可以得出STUDENT_COURSE不在BCNF中的结论，因为至少studentNumber不是STUDENT_COURSE中的键或超级键。

Given the definitioin above it is possible to conclude that STUDENT_COURSE is not in BCNF as at least studentNumber is not a key or superkey in STUDENT_COURSE.

这篇关于3NF和BCNF有什么区别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！