关系数据库设计步骤
无损连接
有损连接：拆分再恢复时出现多余错误记录
如果R1、R2没有公共属性，则join时会退化成笛卡尔积
规范化理论
数据依赖
在逻辑DBS设计过程中，将E-R图转换，得到面向特定应用领域的初始关系模式集。这些初始关系模式集中可能存在多种作为完整性约束的关系模式属性间的数据依赖 (Data Dependencies) 关系
- 函数依赖 (Functional Dependencies, FD, §7.2) 多值依赖 (Mutivalued Dependencies, MVD, §7.4, 7.6) 连接依赖 (Join Dependencies, JD) （略）
函数依赖是特殊的多值依赖 多值依赖又是连接依赖的特例
范式关系
保证关系模式的函数无损连接性、函数依赖保持性进行关系规范化处理即模式分解
1NF
原子属性
2NF
由1NF消除非主属性对键的部分（函数）依赖得到 不包含非主属性对键的部分依赖
3NF
由2NF消除非主属性对键的传递依赖得到 不包含非主属性的对键的部分、传递依赖
BCNF
由3NF消除主属性对键的部分和传递依赖得到 不包含主属性、非主属性对键的部分和传递依赖
4NF
由BCNF消除非平凡且非函数依赖的多值依赖得到
5NF
由4NF消除非平凡的连接依赖得到
概念
函数依赖
可参考 Chapter 7 函数依赖
函数依赖保持
区分satisfy与holds on
满足是局部满足，保持是所有合法关系实例都必须满足此FD
平凡FD
主属性与传递依赖
部分依赖
左端非最小化
函数依赖闭包$F^{+}$
目的：从已知函数依赖，推导出全部隐含函数依赖
函数依赖定义的无损连接分解
范式
1NF
所有属性都是原子的 A relational schema R is in first normal form if the domains of all attributes of R are atomic. 比如应该把学院放进属性列而非使用学号编码
2NF
不存在非主属性对候选键的部分依赖 非主属性要么不依赖，要么完全依赖于候选键
BCNF
不存在非主属性、主属性对候选键的部分、传递依赖
A relation schema R is in BCNF with respect to a set F of functional dependencies, if for all functional dependencies in $F^{+}$ of the form $\alpha \rightarrow \beta$
where $\alpha \subset \beta $ R and $\beta \subseteq R$, at least one of the following holds:
$\alpha \rightarrow \beta$ is trivial (i.e., $\beta \subseteq \alpha$)
$\alpha$ is a superkey for R
如何分解成BCNF？
说白了就是让不属于原模式超键的属性都变为新模式的主键，并从原模式中去掉依赖式右端的属性
分解BCNF也存在函数依赖保持和非保持
总之就一句话：
the functional dependency
s_ID, dept name → i_ID
can only be checked by computing the join of the decomposed relations
3NF
Because it is not always possible to achieve both BCNF and dependency preservation, we consider a weaker normal form, known as third normal form (3NF).
A relation schema R is in third normal form with respect to a set F of functional dependencies, if for all functional dependencies in $F^{+}$ of the form $\alpha \rightarrow \beta$
where $\alpha \subset \beta $ R and $\beta \subseteq R$, at least one of the following holds:
$\alpha \rightarrow \beta$ is trivial (i.e., $\beta \subseteq \alpha$)
$\alpha$ is a superkey for R
Each attribute A in $\beta - \alpha$ is part of some candidate key for R
If a relation is in BCNF, it is also in 3NF (since in BCNF one of the first two conditions above must hold).
Third condition is a minimal relaxation（放宽） of BCNF to ensure dependency preservation (will see why later)
3NF缺点
Redundancy in 3NF【缺点:数据冗余】
3NF对比BCNF
Advantages to 3NF over BCNF
- It is always possible to obtain a 3NF design without sacrificing losslessness or dependency preservation 3NF分解可以保证无损连接、函数依赖保持 BCNF分解保证无损连接，但不保证函数依赖保持
Disadvantages to 3NF
- We may have to use null values to represent some of the possible meaningful relationships among data items.
- There is the problem of repetition of information
分解目标
分解要求：从函数依赖角度 1. 分解为2NF, 3NF, BCNF 2. 必修保证无损连接分解，尽可能保证函数依赖保持