3NF 和 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 中并且没有非主属性传递依赖于主键，则该关系在 3NF 中.换句话说，如果对于 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 中的主要属性

示例

给定以下关系:

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 ⟶ 名字、出生日期、地址、部门编号
2. 部门编号⟶部门名称

根据上面的定义，可以得出关系 EMP_DEPT 不在 3NF 中的结论，因为第二个函数依赖不满足 3NF 的 2 个条件中的任何一个:

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，X是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. studentNumber, courseNumber

考虑以下函数依赖:

1. studentNumber ⟶ socialSecurityNumber
2. socialSecurityNumber ⟶ studentNumber

根据上面的定义，可以得出结论 STUDENT_COURSE 不在 BCNF 中，因为至少 studentNumber 不是 STUDENT_COURSE 中的键或超键.

Given the definition 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 有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！