Database(10) -- Third Normal Form

Definition of Third Normal Form

A relation R is in third normal form (3NF) if:

• Whenever A1A2…An -> B1B2…Bm is a nontrivial FD, either {A1,A2,…,An} is a superkey, or those of B1,B2,…,Bm that are not among the A’s, are each a member of some key (not necessarily the same key).

In other words:

• For each nontrivial FD, either the left side is a superkey, or the right side consists of prime attributes only. 对于任意的FD，要么它左边是superkey，要么它右边每一个属性都是某个key的一个成员。

An attribute that is a memeber of some key is often said to be prime.

Algorithm 3.26: Synthesis of Third-Normal-Form Relations With a Lossless Join and Dependency Preservation.

Method:

1. Find a minimal basis for F, say G.
2. For each functional dependency X -> A in G, use XA as the schema of one of the relaions in the decomposition.
3. If none of the relation schemas from Step 2 is a superkey for R, add another relation whose schema is a key for R.

用每个FD的左右两边所有属性作为一张表。比如R(A,B,C,D,E)有以下三个FD:

1. AB -> C
2. C -> B
3. A -> D

就新建三张表S1(A,B,C), S2(B,C), S3(A,D)。因为S2是S1的一个子集，所有直接扔掉。

此时还应考虑是否需要添加另一张表作为superkey。上面这个例子中，R有两个key: {A,B,E} 和 {A,C,E}。我们上面建的三张表中，没有任何一张表包含它们。因此，我们必须添加一张新表，比如S4(A,B,E).

因此，最终我们拆分成三张表：S1(A,B,C), S3(A,D), S4(A,B,E)

这样拆分后它们一定符合第三范式。