Essential Notes on Database(III) Normalization 范式 (I)

ゝ一纸荒年。 2022-08-18 11:41 113阅读 0赞

***Normalization*** 范式：产生满足客户需求的数据关系

*  Minimal number of necessary attributes (redundancy considered)
 *  Attributes are logically & closely related

Two approach using normalization to do database design: （两种设计数据库的方法）

![Center][]

An example of data redundancy 数据冗余示例 （每行柜台号和地址重复出现）

![Center 1][]

Potentially suffer from update anomalies （可能出现异常的表更新操作）

*  Insertion
 *  Deletion
 *  Modification

冗余处理方法：把一张表分为多张表

1. 原表的数据关系可以在新的表中找到（Lossless-join property）

2. 原表和新表满足相同约束 ( Dependency preservation property)

**Functionally dependency** (To find primary key)

![Center 2][]

举例：每个员工和都和一个branch对应，branchNo is functionally dependent on staffNo, denoted as staffNo -> branchNo

Determinants should have the minimal number of attributes necessary to maintain the functional dependency with the attribute(s) on the right hand-side. This requirement is called *full functional dependency.*（需要能使依赖成立的 最小左侧字段 的集合）

*Partial dependency*: (staffNo, staffName） -> branchNo

*Transitive Dependency***:** potentially cause update anomalies

A->B& B->C, then C is transitively dependent on A via B. (A is not functionally dependent on B or C) (A 可以决定 C)

![Center 3][]

上图中：branchNo -> bAddress

**To identify all candidate key**(s): 找到候选键

identify the attribute(or group of attributes) that uniquely identifies each tuple in this relation.

**Process of normalization** （范式化步骤）

![Center 4][]

由外向内进行

*Unnormalized Form (UNF) 原始形式*

A table that contains one or more repeating groups, implicated from demand. （从需求出发建表）

*UNF to First Normal Form (1NF)*

The intersection of each rows and columns contain one and only one value. （每个字段和实例的交叉处只写一个值）

![Center 5][]

（上图中原始数据每个客户可能借了多个房产，通过拆分的方式将每个房产和客户一一对应）

*1NF to Second Normal Form (2NF)*

A relation that is in 1NF and every non-primary-key attribute is fully functionally dependent on the primary key.

2NF to *Third Normal Form* (3NF)

A relation in 2NF where no non-primary-key attributes is transitively dependent on the primary key.

![Center 6][]

（上图中的表满足 1NF, 在去除 Partial/Transtive dependency 后就可以满足 2NF & 3NF）

[Center]: /images/20220731/bf429f36c4894c549f12496556015d79.png
[Center 1]: /images/20220731/fb0dfc2caaba4e38a705dc86de70de07.png
[Center 2]: /images/20220731/359769e10c1f468084a7664c1193a752.png
[Center 3]: /images/20220731/b922180490624d9e99c4e43c94b827ae.png
[Center 4]: /images/20220731/67a1383880b54f1f8136c9ec0516f3fb.png
[Center 5]: /images/20220731/09277beee10d4fcfa6da64987e57a646.png
[Center 6]: /images/20220731/79fd83cc64b94f66811db3a103708d04.png