Skip to main content

任务六 关系模式的规范化

一、规范化的核心背景与定义​

1. 问题根源​

未规范化的关系模式会引发四大问题：数据冗余大、数据更新不一致、插入异常、删除异常。

2. 规范化定义​

关系模式的规范化是指通过模式分解，将低级范式的关系转换为若干个高级范式关系的过程，以此消除数据冗余和操作异常。

3. 范式（Normal Form, NF）​

范式是衡量关系模式优劣的规则集合，级别越高，约束条件越严格，数据冗余和异常的风险就越小。高一级范式是在低一级范式的基础上增加额外约束，因此满足高一级范式必然满足低一级范式。 常见的范式包括：1NF、2NF、3NF、4NF、5NF、BCNF，一般数据库设计满足**第三范式（3NF）**即可。

二、核心范式详解​

1. 第一范式（1NF）​

  • 定义：关系模式中的所有属性都是不可再分的数据项（即原子性）。
  • 地位：关系数据库的最基本要求，任何关系必须满足1NF。
  • 局限：仅满足1NF仍可能存在数据冗余和操作异常，需要进一步规范化。

2. 第二范式（2NF）​

  • 定义：在满足1NF的基础上，每个非主属性都完全依赖于关系的码（消除非主属性对码的部分依赖）。
  • 判断方法：分析非主属性对码的函数依赖，检查是否存在仅依赖码的一部分的情况。
  • 分解方法：将存在部分依赖的非主属性分离到新的关系中，使每个关系的非主属性都完全依赖于自身的码。
  • 示例：“学生课程成绩”关系（学号，课程号，姓名，成绩等）分解为： 学生_班级(学号, 姓名, 性别, 专业, 班级名, 班主任) 课程(课程号, 课程名, 课时) 成绩(学号, 课程号, 成绩)
  • 结论：满足2NF必然满足1NF；若关系无主属性、候选码为单属性，或仅包含两个属性，则自动满足2NF。
-- 学生选课成绩表（复合主键：学号+课程号）
成绩表：
学号   | 课程号 | 课程名 | 学生姓名 | 成绩
-------|--------|--------|----------|------
001    | CS101  | 数据库 | 张三     | 90
001    | MA201  | 高数   | 张三     | 85
002    | CS101  | 数据库 | 李四     | 88

-- 问题分析：
主键是：(学号, 课程号)
但：
1. 课程名 只依赖 课程号（部分依赖）❌
2. 学生姓名 只依赖 学号（部分依赖）❌
3. 成绩 依赖 (学号, 课程号)（完全依赖）✅

违反2NF的问题

  • 数据冗余：张三选了3门课，他的姓名就重复存储3次
  • 更新异常：如果张三改名，需要修改多行
  • 插入异常：没选课的学生无法录入姓名
  • 删除异常：删除某门课成绩，可能丢失课程信息

符合第二范式

-- 拆分为三个表
-- 1. 学生表（消除学生姓名冗余）
学生表：
学号(PK) | 姓名
---------|-----
001      | 张三
002      | 李四

-- 2. 课程表（消除课程名冗余）  
课程表：
课程号(PK) | 课程名
-----------|--------
CS101      | 数据库
MA201      | 高数

-- 3. 成绩表（只保留完全依赖的字段）
成绩表：
学号(FK) | 课程号(FK) | 成绩
---------|------------|------
001      | CS101      | 90
001      | MA201      | 85
002      | CS101      | 88

2NF检查口诀

“部分依赖要拆分，非主字段靠全键”

判断方法：

  • 表必须有单列主键 → 自动满足2NF
  • 如果是复合主键 → 检查每个非主键列是否依赖整个主键

3. 第三范式（3NF）​

  • 定义：在满足2NF的基础上，每个非主属性既不部分依赖于码，也不传递依赖于码（消除非主属性对码的传递依赖）。
  • 判断方法：分析非主属性对码的函数依赖，检查是否存在“码→属性A→属性B”的传递依赖。
  • 分解方法：将存在传递依赖的非主属性分离到新的关系中，消除传递依赖。
  • 示例：“学生_班级”关系（学号，姓名，班级名，班主任）分解为： 学生(学号, 姓名, 性别, 专业, 班级名) 班级(班级名, 班主任)
  • 结论：满足3NF必然满足2NF和1NF；若关系无主属性或仅包含两个属性，则自动满足3NF。

三、范式层级与设计原则​

  • 层级关系：1NF ⊂ 2NF ⊂ 3NF ⊂ BCNF ⊂ 4NF ⊂ 5NF，高一级范式是低一级范式的子集。
  • 设计原则：一般数据库设计只需满足3NF即可，它在消除冗余和保证性能之间取得了较好的平衡。

四、第一范式（1NF）​

解决问题：原子性

核心思想​

想象一个Excel表格：每个单元格只能有一个值，不能把多个东西塞进一个格子里。

违反1NF的例子​

-- 错误设计：一个单元格有多个值
订单表：
订单ID | 客户     | 产品
-------|----------|-------------------
1001   | 张三     | 手机,耳机,充电宝  -- ❌ 一个格子里有三个产品

学生表：
学号   | 姓名     | 联系方式
-------|----------|---------------------------
001    | 李四     | 电话:13800138000,微信:abc  -- ❌ 复合数据

符合1NF的设计​

-- 正确：拆分为多行或多列
订单表：
订单ID | 客户     | 产品
-------|----------|----------
1001   | 张三     | 手机
1001   | 张三     | 耳机      -- ✅ 每个格子一个值
1001   | 张三     | 充电宝

-- 或者拆分为多列（如果数量固定）
学生表：
学号   | 姓名     | 电话         | 微信
-------|----------|-------------|--------
001    | 李四     | 13800138000 | abc    -- ✅ 原子数据

1NF检查口诀​

“一列一值，不可再分；类型一致，列名唯一”

记住：1NF是所有范式的基础，必须先满足它才能谈其他范式。

五、第二范式（2NF）：​

任务：“消除部分依赖”

核心思想​

“每个非主键列必须完全依赖于整个主键，而不能只依赖主键的一部分”

先理解几个概念​

-- 学生选课成绩表（复合主键：学号+课程号）
成绩表：
学号   | 课程号 | 课程名 | 学生姓名 | 成绩
-------|--------|--------|----------|------
001    | CS101  | 数据库 | 张三     | 90
001    | MA201  | 高数   | 张三     | 85
002    | CS101  | 数据库 | 李四     | 88

-- 问题分析：
主键是：(学号, 课程号)
但：
1. 课程名 只依赖 课程号（部分依赖）❌
2. 学生姓名 只依赖 学号（部分依赖）❌
3. 成绩 依赖 (学号, 课程号)（完全依赖）✅

违反2NF的问题​

  1. 数据冗余：张三选了3门课，他的姓名就重复存储3次
  2. 更新异常：如果张三改名，需要修改多行
  3. 插入异常：没选课的学生无法录入姓名
  4. 删除异常：删除某门课成绩，可能丢失课程信息

符合2NF的设计​

-- 拆分为三个表
-- 1. 学生表（消除学生姓名冗余）
学生表：
学号(PK) | 姓名
---------|-----
001      | 张三
002      | 李四

-- 2. 课程表（消除课程名冗余）  
课程表：
课程号(PK) | 课程名
-----------|--------
CS101      | 数据库
MA201      | 高数

-- 3. 成绩表（只保留完全依赖的字段）
成绩表：
学号(FK) | 课程号(FK) | 成绩
---------|------------|------
001      | CS101      | 90
001      | MA201      | 85
002      | CS101      | 88

2NF检查口诀​

“部分依赖要拆分，非主字段靠全键”

判断方法：

  1. 表必须有单列主键 → 自动满足2NF
  2. 如果是复合主键 → 检查每个非主键列是否依赖整个主键

六、第三范式（3NF）：​

任务：“消除传递依赖”

核心思想​

“非主键列之间不能有依赖关系，所有非主键列必须直接依赖于主键”

违反3NF的例子​

-- 学生宿舍表
学生住宿表：
学号(PK) | 姓名   | 宿舍楼 | 宿舍地址       | 收费标准
---------|--------|--------|----------------|----------
001      | 张三   | A栋    | 北京市海淀区   | 1200元
002      | 李四   | A栋    | 北京市海淀区   | 1200元
003      | 王五   | B栋    | 北京市朝阳区   | 1500元

-- 问题分析：
学号 → 宿舍楼 → 宿舍地址  ❌（传递依赖）
学号 → 宿舍楼 → 收费标准  ❌（传递依赖）

传递依赖链​

学号 → 宿舍楼 → 宿舍地址
      （直接）   （间接，通过宿舍楼）
      
应该改为：
学号 → 宿舍楼
宿舍楼 → 宿舍地址  （单独成立）

符合3NF的设计​

-- 1. 学生基本信息表
学生表：
学号(PK) | 姓名   | 宿舍楼(FK)
---------|--------|-----------
001      | 张三   | A栋
002      | 李四   | A栋  
003      | 王五   | B栋

-- 2. 宿舍信息表（消除传递依赖）
宿舍表：
宿舍楼(PK) | 地址           | 收费标准
-----------|----------------|----------
A栋        | 北京市海淀区   | 1200元
B栋        | 北京市朝阳区   | 1500元

3NF检查口诀​

“传递依赖要分离，非主互不相依赖”

判断方法： 对于任意非主键列X和Y： 如果 主键 → X → Y，则违反3NF

七、案例：图书借阅系统​

第1步：创建原始表（可能违反所有范式）​

原始借阅表：
借阅ID | 读者ID | 读者名 | 图书ID | 图书名 | 作者   | 出版社 | 出版社地址 | 借阅日期
-------|--------|--------|--------|--------|--------|--------|------------|----------
B001   | R001   | 张三   | T001   | 数据库 | 李明   | 清华社 | 北京清华园 | 2024-01-01
B002   | R001   | 张三   | T002   | 算法   | 王芳   | 清华社 | 北京清华园 | 2024-01-02
B003   | R002   | 李四   | T001   | 数据库 | 李明   | 清华社 | 北京清华园 | 2024-01-03

第2步：应用1NF（原子化）​

✅ 已经满足（每个格子一个值）

第3步：应用2NF（消除部分依赖）​

问题：

  • 主键是：借阅ID（单列主键）→ 自动满足2NF
  • 但读者名依赖读者ID（属于部分依赖）
  • 图书信息依赖图书ID（属于部分依赖）

拆分：

-- 读者表
读者ID(PK) | 读者名
-----------|-------
R001       | 张三
R002       | 李四

-- 图书表
图书ID(PK) | 图书名 | 作者 | 出版社
-----------|--------|------|--------
T001       | 数据库 | 李明 | 清华社
T002       | 算法   | 王芳 | 清华社

-- 借阅表
借阅ID(PK) | 读者ID(FK) | 图书ID(FK) | 借阅日期
-----------|------------|------------|----------
B001       | R001       | T001       | 2024-01-01
B002       | R001       | T002       | 2024-01-02
B003       | R002       | T001       | 2024-01-03

第4步：应用3NF（消除传递依赖）​

问题：

  • 在图书表中：出版社 → 出版社地址（传递依赖）
  • 图书ID → 出版社 → 出版社地址

进一步拆分：

-- 出版社表
出版社ID(PK) | 出版社名 | 地址
-------------|----------|-----------
P001         | 清华社   | 北京清华园

-- 修改图书表
图书ID(PK) | 图书名 | 作者 | 出版社ID(FK)
-----------|--------|------|------------
T001       | 数据库 | 李明 | P001
T002       | 算法   | 王芳 | P001