database-design - 3NF 与 BCNF : Example

Question

大家好，

好的，所以我有一个数据库类，我有这个例子让我觉得我真的没有完全理解这两个 NF 之间的区别。

我知道

A relation, R, is in 3NF iff for every nontrivial FD (X->A) satisfied by R at least ONE of the following conditions is true:

(a) X is a superkey for R, or

(b) A is a key attribute for R

和

A relation, R, is in BCNF iff for every nontrivial FD (X->A) satisfied by R the following condition is true:

(a) X is a superkey for R

例子如下:

R = (A, B, C, D) F = {AB->C, AB->D, C->A, D->B} 1. Is R in 3NF, why? If it is not, decompose it into 3NF

Sol:
Yes. Find all the Candidate Keys:
AB, BC, CD, AD
Check all FDs in F for 3NF condition

那么，C->A 不会导致违规吗？ C 不是 super key ，也不是微不足道的，D->B 也是如此吗？

但它不是 BCNF:

1. Is R in BCNF, why? If it is not, decompose it into BCNF No. Because for C->A, C is not a superkey. Similar for D->B R1 = {C, D}, R2 = {A, C}, R3 = {B, D}

Answer 1

那么，C->A 不会导致违规吗？ C 不是 super key ，也不是微不足道的......

那么 3NF 的第二个条件呢？ A 是 R 中某个键的属性吗？

(提示:此类练习的部分目的也是为了向您展示为什么 3NF 不是非常有用的范式，以及 3NF 和 BCNF 之间的区别很重要的情况)