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

 

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

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

 

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

 

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

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

• Insertion
• Deletion
• Modification

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

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

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

 

Functionally dependency (To find primary key)

 

举例：每个员工和都和一个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)

上图中：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 （范式化步骤）

 由外向内进行

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. （每个字段和实例的交叉处只写一个值）

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

 

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.

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