数据库规范化流程

流程一：考试做题流程 / 项目设计模式

1. 形式化：问题描述（自然语言）
   • 将需求方的口头要求或文字需求记录下来。
2. 应用语义归纳：补充潜在语义（默认）
   • 分析业务规则。例如：“一个员工只能属于一个部门”，这决定了数据之间的对应关系。
3. 绘制 E-R 图
   • 使用实体（框）、属性（圆）和联系（菱形）直观地表示数据结构。
4. 转换关系模式
   • 将 E-R 图转化为逻辑表格（关系模式）。
5. 说明范式，说明不合理性
   • 检查当前的表格属于第几范式（通常是 1NF 或 2NF），并指出存在的数据冗余、插入异常或删除异常。
6. 规范化推导（到 BCNF）
   • 运用算法将表格拆分，逐步提升范式级别。
7. 给出设计结果：
   • $R_1 (\dots)$, 码：, 外码：
   • $R_2 (\dots)$, 码：, 外码：
   • ……

结论： 经过以上规范化推导，问题已经设计到 BCNF (Boyce-Codd Normal Form)，达到函数依赖范畴的最高级范式。

---

流程二：书本上做题流程

1. 形式化：问题描述（自然语言）
2. 应用语义归纳：补充潜在语义（默认）
3. 形式化定义：$R <U, F>$
   • 其中：
     • $U = \{ \dots \}$ （属性全集，即表里所有的列）
     • $F = \{ \dots \}$ （函数依赖集，即列与列之间的决定关系）
     • 码： （Primary Key，能唯一标识一行的最小属性组）
     • 非主属性集 $= \{ \dots \}$ （不包含在任何候选码中的属性）
4. 说明范式，说明不合理性
5. 规范化推导（到 BCNF）
6. 规范化结果：
   • $R_1 (\dots)$, 码：, 外码：
   • $R_2 (\dots)$, 码：, 外码：
   • ……

结论： 经过以上规范化推导，问题已经设计到 BCNF，达到函数依赖范畴的最高级范式。

---

深度解析：这些步骤是什么意思？

1. 为什么要从“语义”开始？

数据库设计不是直接画表。语义决定了函数依赖 ($F$)。

• 例子： 如果业务规定“身份证号可以确定姓名”，那么就存在函数依赖：$\text{身份证号} \rightarrow \text{姓名}$。
• 如果你不清楚业务逻辑（语义），你就无法确定谁是“码”（主键），也就无法进行后续推导。

2. 什么是“不合理性”？

当一个表的设计不好时（范式过低），会出现以下问题：

• 数据冗余： 同一个部门的名字在每一个员工行都存一遍，浪费空间。
• 插入异常： 如果一个部门刚成立还没员工，由于主键（员工号）不能为空，你可能无法把这个部门存进数据库。
• 删除异常： 如果把最后一个员工删了，结果连同这个部门的信息也一起消失了。

3. 为什么目标是 BCNF？

• 1NF (第一范式)： 属性不可再分（列不能再拆）。
• 2NF (第二范式)： 消除了非主属性对码的部分依赖。
• 3NF (第三范式)： 消除了非主属性对码的传递依赖。
• BCNF (BC范式)： 在 3NF 基础上进一步消除主属性对码的部分或传递依赖。
• 意义： 到达 BCNF 意味着在处理“一个变量决定另一个变量”这种逻辑关系时，你的数据库已经达到了物理结构上的“最干净”状态。

4. 转换流程的差异

• 实战（流程一） 强调 E-R 图，因为它是连接人类思维和计算机逻辑的桥梁。
• 理论（流程二） 强调 数学定义 ($U, F$)，它是为了方便使用算法（如模式分解算法）进行严谨的证明。

建议

在实际操作中，请务必先写出所有的 函数依赖 ($F$)。这是所有推导的根基。如果 $F$ 写错了，后面的范式判断和拆表（规范化推导）都会全盘皆错。

例题 （Week 8. HW）

面向农业的电子商务系统部分需求概述： 注册客户能看见农户基本信息、生产场地基本信息、生产的农产品及规格、价格、产能等信息，能查询往年生产统计信息（乡镇，镇长电话，农产品、总产能、平均价格）；

答题要求：

1. 用 E-R 图设计尽可能好的概念模型；（4 分）
2. 将 E-R 图转换成关系模型；（3 分）
3. 写出各个关系的函数依赖关系；（3 分）
4. 写出各个关系的主码和外码；（2 分）
5. 写出各个关系满足的范式级别；（2 分）
6. 将规范级别低的关系转成高级别范式的关系。（1 分）

---

1. 需求剖析与概念模型设计 (E-R 图)

在设计前，我们首先识别核心实体及其逻辑关联：

• 乡镇 (Township)：作为地理管理单元，拥有唯一的镇长联系方式。
• 农户 (Farmer)：属于某个乡镇，拥有基本信息。
• 生产场地 (Site)：由农户经营，是农产品的产出地。
• 农产品 (Product)：挂载于场地之下，具有规格、单价、产能。
• 生产统计 (Statistics)：这是一个派生或聚合信息，通常涉及“乡镇、年份、产品”三个维度的关联。

---

2. 关系模型转换

基于 E-R 图，转换得到的逻辑关系模式如下（初始状态）：

1. 乡镇表 $R_1(\underline{TownName}, MayorPhone)$
2. 农户表 $R_2(\underline{FarmerID}, FarmerInfo, TownName)$
3. 场地表 $R_3(\underline{SiteID}, SiteInfo, FarmerID)$
4. 农产品表 $R_4(\underline{ProductID}, ProductName, Spec, Price, Capacity, SiteID)$
5. 往年统计表（混合模式） $R_5(\underline{TownName, ProductName, Year}, MayorPhone, TotalCapacity, AvgPrice)$> 注：为了符合题目后续“规范化”的要求，我们在 $R_5$ 中故意引入了冗余。

---

3. 函数依赖关系分析 ($F$)

针对上述关系，其函数依赖集 $F$ 如下：

• 对于 $R_1$：$TownName \rightarrow MayorPhone$
• 对于 $R_2$：$FarmerID \rightarrow FarmerInfo, TownName$
• 对于 $R_3$：$SiteID \rightarrow SiteInfo, FarmerID$
• 对于 $R_4$：$ProductID \rightarrow ProductName, Spec, Price, Capacity, SiteID$
• 对于 $R_5$：
  1. $(TownName, ProductName, Year) \rightarrow TotalCapacity, AvgPrice$
  2. $TownName \rightarrow MayorPhone$ （关键：部分函数依赖）

---

4. 主码与外码标识

关系模式	主码 (Primary Key)	外码 (Foreign Key)
$R_1$ (乡镇)	$TownName$	无
$R_2$ (农户)	$FarmerID$	$TownName$
$R_3$ (场地)	$SiteID$	$FarmerID$
$R_4$ (产品)	$ProductID$	$SiteID$
$R_5$ (统计)	$(TownName, ProductName, Year)$	$TownName, ProductName$

---

5. 范式级别判定与不合理性说明

• $R_1, R_2, R_3, R_4$：均满足 BCNF。非主属性对码既不存在部分依赖，也不存在传递依赖。
• $R_5$ (往年统计表)：仅满足 1NF，不满足 2NF。
  • 判定理由：$R_5$ 的码为 $(TownName, ProductName, Year)$。然而，存在函数依赖 $TownName \rightarrow MayorPhone$。这意味着非主属性 $MayorPhone$ 只依赖于码的一部分（$TownName$），即存在部分函数依赖。
  • 不合理性：如果一个镇有 100 种农产品，镇长的电话就会重复存储 100 次（数据冗余）；如果镇长换了，需要修改大量记录（更新异常）。

---

6. 规范化推导：向高级别范式转换

针对低规范化级别的 $R_5$ 进行投影分解，消除部分函数依赖：

分解结果：

1. $R_{51}$ (统计核心表)：$(\underline{TownName, ProductName, Year}, TotalCapacity, AvgPrice)$

   • 范式级别：BCNF
   • 说明：所有非主属性完全函数依赖于码。

2. $R_{52}$ (镇长联系表)：$(\underline{TownName}, MayorPhone)$

   • 范式级别：BCNF
   • 说明：由于该表与 $R_1$ 结构一致，实际开发中应直接合并入 $R_1$。

---

结论与建议

经过以上规范化推导，问题已经设计到 BCNF (Boyce-Codd Normal Form)，达到函数依赖范畴的最高级范式。

设计一个不是传递依赖的 $X, Y, Z$ 关系

根据数据库权威教材（如 Database System Concepts by Silberschatz），传递依赖的定义是：

在关系模式 $R$ 中，如果存在函数依赖 $X \to Y$ 和 $Y \to Z$，且满足以下条件，则称 $Z$ 对 $X$ 传递函数依赖：

1. $Y \not\to X$（即 $Y$ 不决定 $X$，两者不是等价的码）。
2. $Z \notin Y$（即 $Z$ 不是 $Y$ 的子集）。

要设计一个非传递依赖，我们可以从以下两个逻辑角度出发：

---

方案一：分支直接依赖 (Branching Direct Dependency)

这是最常见的符合 3NF (Third Normal Form) 的设计。在这种设计中，$X$ 同时决定 $Y$ 和 $Z$，但 $Y$ 和 $Z$ 之间没有任何函数依赖关系。

1. 变量定义

• $X$ (主码)：EmployeeID (员工编号)
• $Y$ (非主属性)：Name (姓名)
• $Z$ (非主属性)：Salary (薪水)

2. 函数依赖集 $F$

• $X \to Y$ (员工编号决定姓名)
• $X \to Z$ (员工编号决定薪水)

3. 为什么不是传递依赖？

• 虽然 $X \to Y$ 成立，但 $Y \not\to Z$。
• 在应用语义中，姓名（$Y$）无法唯一决定薪水（$Z$），因为可能存在同名的人且薪水不同。
• 由于不存在 $Y \to Z$ 这个中间环节，因此 $X \to Z$ 是直接函数依赖，而非传递依赖。

---

方案二：部分函数依赖 (Partial Functional Dependency)

这种设计通常出现在 2NF (Second Normal Form) 以下的模式中。它违反了 2NF，但从定义上看，它并不构成 3NF 所针对的“传递依赖”。

1. 变量定义

• $X$ (复合主码)：$\{StudentID, CourseID\}$ (学号, 课程号)
• $Y$ (非主属性)：StudentName (学生姓名)
• $Z$ (非主属性)：Grade (成绩)

2. 函数依赖集 $F$

• $X \to Z$：即 $(StudentID, CourseID) \to Grade$（完全函数依赖）
• $(StudentID) \to Y$：即学号决定姓名（部分函数依赖）

3. 为什么不是传递依赖？

• 要构成传递依赖，必须满足 $X \to Y$ 且 $Y \to Z$。
• 在这个例子中，虽然 $X \to Y$ 成立（通过部分依赖），但 $Y \not\to Z$。
• 学生姓名（$Y$）无法决定某门课的成绩（$Z$）。
• 结论：该关系模式存在部分依赖（违反 2NF），但不存在传递依赖。

---

对比分析

类型	函数依赖链	范式状态	核心特征
直接依赖	$X \to Y, X \to Z, Y \not\to Z$	满足 3NF	$Y$ 和 $Z$ 互不干扰，都是 $X$ 的直接描述。
部分依赖	$(A, B) \to Z, A \to Y$	违反 2NF	非主属性只依赖于复合码的一部分。
传递依赖	$X \to Y, Y \to Z$ (且 $Y \not\to X$)	违反 3NF	$Z$ 通过中间人 $Y$ 间接依赖于 $X$。