EBU5602 Database week8笔记整理

目标：

Understand there are normal forms that go beyond Third Normal Form (3NF).了解有超越正常形式的第三正常形式

Understand and be able to identify BoyceCodd Normal Form (BCNF).理解并能够识别BoyceCodd的正常形式

Be able to decompose relations to BCNF.能够分解与BCNF的关系

Understand and be able to explain Multivalued Dependency (MVD).理解并能够解释多值依赖关系

Understand and be able to identify Fourth Normal Form (4NF)理解并能够识别第四法形式

Normalization归一化过程

 

2NF -- second normal form第二范式：

第一个范式和每个非候选键属性的关系在功能上完全依赖于任何候选键。A relation that is in first normal form and every non-candidate-key attribute is fully functionally dependent on any candidate key.

3NF -- Third normal form 第三范式：

一种第一和第二正常形式的关系，其中没有非候选键属性传递依赖于任何候选键。A relation that is in first and second normal form and in which no non-candidate-key attribute is transitively dependent on any candidate key.

BCNF -- Boyce–Codd Normal Form

基于在一个关系中考虑到所有候选键的函数依赖关系，但是与3NF的一般定义相比，BCNF也有额外的约束条件// BCNF is a normal form that for a relation, if and only if every determinant is candidate key.

当且仅当每个行列式都是一个候选键时，关系为BCNF

比较3NF和BCNF：

对函数的依赖项：A→B（3NF：B是候选键时A可以不是；BCNF：A必须是候选键Candidate Key）

BCNF举例：

1. Client (clientNo, cName)

clientNo -> cName (primary key)

This relation is in BCNF

1. Rental(clientNo, propertyNo, rentStart, rentFinish)

clientNo, propertyNo -> rentStart, rentFinish (primary key)

clientNo, rentStart -> propertyNo, rentFinish (candidate key)

propertyNo, rentStart -> clientNo, rentFinish (candidate key)

All determinants are candidate key, this relation is in BCNF

1. PropertyForRent(propertyNo, pAddress, rent, ownerNo)

propertyNo -> pAddress, rent, ownerNo (primary key)

This relation is in BCNF

1. Owner(ownerNo, oName)

ownerNo -> oName (primary key)

This relation is in BCNF

 

MVD：

A->BC，BC相互独立

Trivial // Nontrivial

Trivial：B是A的子集或A并B构成全集，二者之一就是。否则就是Nontrivial

4NF：

就是BCNF+无Nontrivial MVD(判断依据）

AB->C,C->B,不能变成BCNF，是特殊情况EKNF

Full Functional Dependency

1. >B，B不是A的子集

大部分Fd都是FFD

Partial Functional Dependency

A中消除一部分attributes仍能保持FD的关系

Transitive FD

传递性，A->B,B->C则A->C

特点：

1:1关系，任何情况下都成立，attibutes尽可能少

如何找FD：

题意（假设、关系）、信息、自觉（先单后多找fd，先列单个关系，排除可能重复的选项，得出结论）

Normalization

 

 

UNF Table contains one or more repeating groups，一格出现多数据

1NF表内全是单个数据，存在partinal dependencies

2NF based on 1NF， 去掉子集关系， 无partial dependencies，有transitive

3Nf based on 1NF、2NF， 消去transitively ， 无partial dependencies和transitive

转变成xNF：

首先前序问题已经知道目前在几NF，然后就一步一步转到2NF或者3NF。

固定话：First, we convert from 1NF to 2NF. We need to remove the partial dependencies. From the functional dependencies, we know that fdx is a partial dependencies on primary key... So, we decompose into. ......

Then, from 2NF to 3NF, we need to remove the transitive dependencies. From the functional dependencies, we know that fdx is a transitive dependencies, so we decompose into.....

最后指明，Rx1,Rx2,Rx3 are now in 3NF.