简述3NF和BCNF的区别?

发布网友 发布时间：2022-04-22 04:49

我来回答

共2个回答

热心网友 时间：2022-04-11 07:36

最简洁的：
Difference between 3NF and BCNF is that for a functional dependency A->B, 3NF allows this dependency in a relation if B is a ***** attribute and A is not a candidate key

稍长点的：
consider that:
It should be noted that most relations that are in 3NF are also in BCNF. Infrequently, a 3NF relation is not in BCNF and this happens only if
(a) the candidate keys in the relation are composite keys (that is, they are not single attributes),
(b) there is more than one candidate key in the relation, and
(c) the keys are not disjoint, that is, some attributes in the keys are common.

超长的：
Comparison of BCNF and 3NF

We have seen BCNF and 3NF.
It is always possible to obtain a 3NF design without sacrificing lossless-join or dependency-preservation.
If we do not eliminate all transitive dependencies, we may need to use null values to represent some of the meaningful relationships.
Repetition of information occurs.
These problems can be illustrated with Banker-schema.
As banker-name bname , we may want to express relationships between a banker and his or her branch.

Figure 7.4: An instance of Banker-schema.

（http://www.cs.sfu.ca/CC/354/zaiane/material/notes/Chapter7/node12.html）

Figure 7.4 shows how we must either have a corresponding value for customer name, or include a null.
Repetition of information also occurs.
Every occurrence of the banker's name must be accompanied by the branch name.
If we must choose between BCNF and dependency preservation, it is generally better to opt for 3NF.
If we cannot check for dependency preservation efficiently, we either pay a high price in system performance or risk the integrity of the data.
The limited amount of rendancy in 3NF is then a lesser evil.
To summarize, our goal for a relational database design is
BCNF.
Lossless-join.
Dependency-preservation.
If we cannot achieve this, we accept
3NF
Lossless-join.
Dependency-preservation.
A final point: there is a price to pay for decomposition. When we decompose a relation, we have to use natural joins or Cartesian procts to put the pieces back together. This takes computational time.

热心网友 时间：2022-04-11 08:54

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、第四范式（4NF）、第五范式（5NF）和第六范式（6NF）。满足最低要求的范式是第一范式（1NF），在第一范式的基础上进一步满足更多要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式（3NF）就行了。
BCNF是在3NF基础上的改进式，BCNF比3NF更为严格。

3NF——只消除非主属性对主属性的传递依赖；
BCNF——消除所有属性对主属性的传递依赖。