3NF
BCNF requires that all nontrivial dependencies be of the form α → β, where α is a superkey.
3NF relaxes this constraint slightly by allowing certain nontrivial functional dependencies whose left side is not a superkey
- Definition
- It first need to be in 2NF.
- For all functional dependencies in F+ of the form α → β, at least one of the following holds:
- α → β is a trivial functional dependency.
- α is a superkey for R.
- Each attribute A in β − α is contained in a candidate key for R. (Or , it should only depend on superkey)
- 3NF eliminate transitive dependency because if a non-prime attribute A depend on non-prime attribute B, since B depends on K it leads to (k → B → A)
- To check if a relation is 3NF, check every non-prime attribute and see if it only rely on superkey. If a non-prime attribute does not depend on superkey then it’s not 3NF.
3NF 分解
- 先求出 Canonical Cover Fc
- 对于 Fc 里面的所有函数依赖 a→b,均转化为 Ri=ab
- 对于所有的模式 Ri
- 如果包含候选码，进行第 4
- 如果都不包含候选码， 将任意一个候选码添加到模式 Ri 里面
- 如果一个模式被另一个模式包含，则去掉此被包含的模式。
例子关系模式 r(A,B,C,D,E,F),函数依赖 集 F： A→BCD,BC→DE,B→D,D→A
- 函数依赖是：A→BC.B→DE,D→A
- R1=ABC,R2=BDE,R3=DA,不包含候选码（AF,BF,DF）中任意一个，所以任意添加一个 R4=AF
- 3NF 分解为{ABC,BDE,DA,AF}
2NF VS 3NF
- 2NF is concerned with eliminating partial dependencies. This means that if a table has a composite primary key (consisting of multiple columns), no column should be dependent on just a part of the primary key. Every non-key attribute should be fully functionally dependent on the whole primary key.
- 3NF goes a step further by eliminating transitive dependencies. This means that no non-key attribute should depend on another non-key attribute. Each non-key attribute must be directly dependent on the primary key and only on the primary key.