{T}

范式设计

数据库范式是关系型数据库设计的规范，用于消除数据冗余和更新异常。本章从 1NF 到 BCNF、4NF、5NF 系统讲解各范式的定义、依赖关系与实战判断。

数据库范式概述

什么是范式

范式（Normal Form）是关系型数据库设计的规范等级，用于衡量数据表的规范化程度。

范式等级

code
1NF → 2NF → 3NF → BCNF → 4NF → 5NF

低 ←──────────────────────────────→ 高
冗余度高                            冗余度低

实际应用：通常满足 3NF 即可，有时为了性能需要进行反范式设计。

数据库中的键

理解范式需要先了解数据库中的键的概念。

键类型说明示例
超键能唯一标识元组的属性集(学号)、(学号, 姓名)、(身份证号)
候选键最小的超键，不含多余属性(学号)、(身份证号)
主键从候选键中选一个作为主键(学号)
外键其他表的主键(班级编号)
主属性包含在候选键中的属性学号、身份证号
非主属性不包含在候选键中的属性姓名、年龄

示例：球员表（player）

code
字段：球员编号、姓名、身份证号、年龄、球队编号

超键：(球员编号)、(球员编号, 姓名)、(身份证号)、(身份证号, 年龄)...
候选键：(球员编号)、(身份证号)
主键：(球员编号)
外键：(球队编号)
主属性：球员编号、身份证号
非主属性：姓名、年龄、球队编号

第一范式（1NF）

定义

第一范式要求每个属性都是原子的，不可再分。

示例

sql
-- 违反 1NF：地址字段可以再分
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    address VARCHAR(200)  -- 包含省、市、区，可再分
);

-- 符合 1NF：地址拆分为多个字段
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    province VARCHAR(50),
    city VARCHAR(50),
    district VARCHAR(50)
);

1NF 要点

  • 每个字段只包含单一值
  • 字段不能再分解为更小的单位
  • 所有 DBMS 默认满足 1NF

第二范式（2NF）

定义

第二范式在 1NF 基础上，要求非主属性完全依赖于候选键，不能只依赖候选键的一部分。

违反 2NF 的示例

sql
-- 球员比赛表
CREATE TABLE player_game (
    player_id INT,        -- 球员编号
    game_id INT,          -- 比赛编号
    player_name VARCHAR(50),  -- 球员姓名
    player_age INT,       -- 球员年龄
    game_time DATETIME,   -- 比赛时间
    game_venue VARCHAR(100),  -- 比赛场地
    score INT,            -- 得分
    PRIMARY KEY (player_id, game_id)  -- 联合主键
);

问题分析：

code
完全依赖：
(player_id, game_id) → score  ✓ 得分完全依赖联合主键

部分依赖：
player_id → player_name, player_age  ✗ 姓名和年龄只依赖球员编号
game_id → game_time, game_venue      ✗ 时间和场地只依赖比赛编号

违反 2NF 的问题

问题说明
数据冗余球员信息重复存储，比赛信息重复存储
插入异常无法单独插入新比赛（没有球员参与）
删除异常删除球员会同时删除比赛信息
更新异常修改比赛时间需要更新多条记录

符合 2NF 的设计

sql
-- 球员表
CREATE TABLE player (
    player_id INT PRIMARY KEY,
    player_name VARCHAR(50),
    player_age INT
);

-- 比赛表
CREATE TABLE game (
    game_id INT PRIMARY KEY,
    game_time DATETIME,
    game_venue VARCHAR(100)
);

-- 球员比赛关系表
CREATE TABLE player_game (
    player_id INT,
    game_id INT,
    score INT,
    PRIMARY KEY (player_id, game_id),
    FOREIGN KEY (player_id) REFERENCES player(player_id),
    FOREIGN KEY (game_id) REFERENCES game(game_id)
);

第三范式（3NF）

定义

第三范式在 2NF 基础上，要求非主属性不传递依赖于候选键。

违反 3NF 的示例

sql
-- 球员表
CREATE TABLE player (
    player_id INT PRIMARY KEY,
    player_name VARCHAR(50),
    team_name VARCHAR(50),     -- 球队名称
    team_coach VARCHAR(50)     -- 球队主教练
);

问题分析：

code
传递依赖：
player_id → team_name → team_coach

球员编号决定球队名称，球队名称决定主教练
存在传递依赖：player_id → team_coach

违反 3NF 的问题

问题说明
数据冗余同一球队的主教练重复存储
更新异常修改球队主教练需要更新多条记录
插入异常无法单独插入球队信息
删除异常删除球员可能丢失球队信息

符合 3NF 的设计

sql
-- 球员表
CREATE TABLE player (
    player_id INT PRIMARY KEY,
    player_name VARCHAR(50),
    team_id INT,
    FOREIGN KEY (team_id) REFERENCES team(team_id)
);

-- 球队表
CREATE TABLE team (
    team_id INT PRIMARY KEY,
    team_name VARCHAR(50),
    team_coach VARCHAR(50)
);

BC范式（BCNF）

定义

BCNF（Boyce-Codd 范式）在 3NF 基础上要求：每一个决定因素（函数依赖左侧）都是候选键。

与 3NF 的区别

3NF 只要求"非主属性不传递依赖"，但可能仍存在主属性之间的传递依赖。BCNF 把约束扩大到所有属性（含主属性）。

违反 BCNF 的示例

sql
-- 一个"学生选课"表，假设一门课程只有一个老师，一位老师只教一门课
CREATE TABLE student_course (
    student_id INT,
    course_id  INT,
    teacher    VARCHAR(50),
    PRIMARY KEY (student_id, course_id)
);

函数依赖：

code
(student_id, course_id) → teacher   -- 联合主键决定老师
course_id → teacher                  -- 课程决定老师（决定因素 course_id 不是候选键！）
teacher → course_id                  -- 老师决定课程（决定因素 teacher 也不是候选键！）

问题：course_id → teacher 成立，但 course_id 不是候选键。虽然满足 3NF（非主属性 teacher 完全依赖候选键），但仍存在冗余——同一课程/老师的组合在每行重复存储。

符合 BCNF 的设计

把 course_id → teacher 单独拆成一张表：

sql
-- 课程表（主键 course_id 是候选键，满足 BCNF）
CREATE TABLE course (
    course_id INT PRIMARY KEY,
    teacher   VARCHAR(50)
);

-- 选课表
CREATE TABLE student_course (
    student_id INT,
    course_id  INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (course_id) REFERENCES course(course_id)
);

判断口诀：凡是"非候选键列决定了别的列"（X → Y 且 X 不是候选键），就违反 BCNF。日常 3NF 基本够用，BCNF 能进一步消除主属性间的依赖，但实际项目很少严格追求。

第四范式（4NF）

定义

4NF 处理多值依赖（MVD，Multi-Valued Dependency）：一个属性组可以独立地决定多个属性值，且不依赖其他属性。4NF 要求：消除非平凡的多值依赖（主属性间的多值依赖）。

多值依赖示例

sql
-- 一个学生可以选多门课，也可以有多个爱好（两个独立的"多值"）
CREATE TABLE student_info (
    student_id INT,
    course     VARCHAR(50),
    hobby      VARCHAR(50)
);

问题：student_id 同时决定多个 course 和多个 hobby，两者相互独立（学生选课与爱好无关）。若用一张表存储，会产生笛卡尔积式冗余——一个学生有 3 门课、2 个爱好，就要存 3×2=6 行。

符合 4NF 的设计

把两个独立的多值属性拆成两张表：

sql
-- 选课表（学生↔课程）
CREATE TABLE student_course (
    student_id INT,
    course     VARCHAR(50),
    PRIMARY KEY (student_id, course)
);

-- 爱好表（学生↔爱好）
CREATE TABLE student_hobby (
    student_id INT,
    hobby      VARCHAR(50),
    PRIMARY KEY (student_id, hobby)
);

4NF 消除"一个实体的多个独立多值属性"造成的笛卡尔积冗余，拆成"一对多"关系表。

第五范式（5NF）

定义

5NF（Project-Join 范式，投影-连接范式）处理连接依赖（JD，Join Dependency）：一个关系能否无损分解为多个关系，且可无损连接还原。5NF 要求：消除非平凡的连接依赖。

核心思想

5NF 是最高的范式，关注"分解后能否无损还原"。实际中很少出现仅违反 5NF、不违反 4NF 的情况。

sql
-- 供应商、零件、项目 三者的三元关系
CREATE TABLE spj (
    supplier_id INT,
    part_id     INT,
    project_id  INT
);

问题：若此表存在"三向唯一约束"（某供应商供应某零件给某项目），但拆成三个两两关系后能通过连接无损还原，则可能需要 5NF 处理；若不能无损连接还原，则拆分会丢失语义。

实践结论

  • 5NF 理论价值高、实践极少用到：几乎不会遇到"满足 4NF 却不满足 5NF"的真实业务；
  • 一般设计做到 3NF/BCNF 即可，冗余优化到 4NF 已很充分。

范式选择：规范 vs 反规范

范式解决代价
1NF属性原子性-
2NF部分依赖表变多，需 JOIN
3NF传递依赖更多 JOIN
BCNF主属性间依赖拆分更细
4NF多值依赖独立实体拆表
5NF连接依赖极少用

实际取舍：

  • OLTP 在线交易：3NF 为主，保证一致性与更新效率；
  • 适度反范式：高频查询若 JOIN 过多，冗余热点列（见《反范式设计》）；
  • OLAP 分析：以宽表/反范式为主，牺牲写入一致性换查询性能；
  • 不要为了范式而范式：范式是手段不是目的，以业务查询模式为准。

范式设计总结

范式递进关系

code
1NF：属性原子性
  ↓
2NF：消除部分依赖（非主属性完全依赖候选键）
  ↓
3NF：消除传递依赖（非主属性直接依赖候选键）

各范式要点

范式核心要求解决的问题
1NF属性不可再分字段原子性
2NF消除部分依赖减少冗余、避免更新异常
3NF消除传递依赖进一步减少冗余

范式设计的优点

  1. 减少数据冗余：节省存储空间
  2. 避免更新异常：数据一致性更好
  3. 结构清晰：表职责单一
  4. 易于维护：修改影响范围小

范式设计的缺点

  1. 查询效率低：需要多表连接
  2. 索引效率低：索引分散在多个表
  3. 复杂查询：需要编写复杂的 JOIN 语句

范式设计实践

设计步骤

code
1. 分析业务需求，确定实体和关系
2. 设计初始表结构
3. 检查是否满足 1NF
4. 检查是否满足 2NF（消除部分依赖）
5. 检查是否满足 3NF（消除传递依赖）
6. 根据性能需求考虑是否反范式

实践建议

  1. 默认遵循 3NF：保证数据一致性
  2. 适当反范式：提高查询性能
  3. 考虑业务场景：根据实际需求调整
  4. 平衡冗余和性能：适度冗余换取性能

总结

范式对比

范式要求关键词解决
1NF属性原子性不可再分字段冗余
2NF消除部分依赖完全依赖候选键冗余、更新异常
3NF消除传递依赖非主属性直接依赖冗余、更新异常
BCNF每个决定因素都是候选键消除主属性间依赖主属性间冗余
4NF消除多值依赖独立多值拆表笛卡尔积冗余
5NF消除连接依赖无损分解还原极少用到

范式递进关系（完整版）

code
1NF：属性原子性
  ↓
2NF：消除部分依赖（非主属性完全依赖候选键）
  ↓
3NF：消除传递依赖（非主属性直接依赖候选键）
  ↓
BCNF：每个决定因素都是候选键（约束扩展到主属性）
  ↓
4NF：消除多值依赖（独立多值属性拆表）
  ↓
5NF：消除连接依赖（无损分解还原，几乎不用）

最佳实践

  1. 理解业务：先理解业务再设计表结构
  2. 遵循 3NF：默认满足第三范式（绝大多数业务足够）
  3. 按需进阶：主属性依赖明显时考虑 BCNF，多个独立多值属性时考虑 4NF
  4. 适度冗余：性能要求高时考虑反范式
  5. 持续优化：根据实际使用情况调整，不为范式而范式