数据库五大范式（1NF～4NF+BCNF）

官方术语 + 性质 + MySQL示例完整版

0. 先统一基础官方术语

这些是理解范式的前提，必须先明确。

1. 函数依赖（Functional Dependency, FD）

设 R(U) 是属性集 U 上的关系模式。
若对于 R 中任意两个元组：

• 如果 X 属性值相等，则 Y 属性值一定相等
  称：Y 函数依赖于 X，记作

X \rightarrow Y

• X 称为决定因素
• Y 称为被决定因素

2. 完全函数依赖

在 X \rightarrow Y 中，
若去掉 X 中任意一个属性，依赖就不成立，则 Y 完全函数依赖于 X。
记：

X \stackrel{f}{\longrightarrow} Y

例：(学号, 课程号) → 成绩
只给学号 → 不知道成绩
只给课程号 → 不知道成绩
→ 成绩完全依赖于联合主键

3. 部分函数依赖

若 X \rightarrow Y，且存在 X 的真子集 X’，使得 X’→Y，
则 Y 部分函数依赖于 X。
记：

X \stackrel{p}{\longrightarrow} Y

例：(学号, 课程号) → 姓名
但 学号 → 姓名 已经成立
→ 姓名部分依赖于联合主键

4. 传递函数依赖

若：

X \rightarrow Y,\quad Y \rightarrow Z,\quad Y \nrightarrow X

则：

X \stackrel{传递}{\longrightarrow} Z

例：学号 → 班级，班级 → 班主任
→ 学号 传递依赖 班主任

5. 候选键 / 主键

能唯一决定整条记录的最小属性集，叫候选键。
选中一个做主键。

6. 非主属性

不包含在任何候选键中的属性。

1. 第一范式（1NF）

官方定义

关系模式 R 中所有属性都是原子的、不可再分的基本数据项。

核心性质

• 列不能是集合、数组、逗号分隔串
• 消除复合属性、多值属性
• 是关系型数据库的最低要求

违反示例

-- 违反1NF：hobby 是多值
CREATE TABLE student_bad (
    id INT,
    name VARCHAR(20),
    hobby VARCHAR(50)  -- '篮球,足球,游戏'
);

满足1NF（拆成行）

CREATE TABLE student (
    id INT,
    name VARCHAR(20),
    hobby VARCHAR(20)
);

INSERT INTO student VALUES
(1,'张三','篮球'),
(1,'张三','足球'),
(2,'李四','游戏');

2. 第二范式（2NF）

官方定义

1. 满足 1NF
2. 所有非主属性完全函数依赖于任何一个候选键
   （消除部分函数依赖）

核心性质

• 主要针对联合主键
• 不允许：字段只依赖主键的一部分

违反2NF示例（经典）

CREATE TABLE score_bad (
    student_id INT,
    course_id INT,
    student_name VARCHAR(20),  -- 只依赖 student_id
    course_name VARCHAR(20),   -- 只依赖 course_id
    score INT,                 -- 依赖整个主键
    PRIMARY KEY (student_id, course_id)
);

依赖关系：

• (student_id,course_id) → student_name
  但 student_id → student_name
  → 部分函数依赖
• 同理 course_name 也是部分依赖

满足2NF：拆表

-- 学生表
CREATE TABLE student (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(20)
);

-- 课程表
CREATE TABLE course (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(20)
);

-- 成绩表（完全依赖联合主键）
CREATE TABLE score (
    student_id INT,
    course_id INT,
    score INT,
    PRIMARY KEY (student_id, course_id)
);

3. 第三范式（3NF）

官方定义

1. 满足 2NF
2. 所有非主属性不传递函数依赖于候选键
   （消除传递函数依赖）

核心性质

• 非主属性之间不能互相决定
• 非主属性只能直接依赖主键，不能依赖其他非主属性

违反3NF示例

CREATE TABLE student_class (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(20),
    class_name VARCHAR(20),
    teacher_name VARCHAR(20)  -- 班级 → 老师
);

依赖链：
student_id → class_name → teacher_name
→ 传递依赖，违反3NF

满足3NF：拆表

CREATE TABLE student (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(20),
    class_id INT
);

CREATE TABLE class (
    class_id INT PRIMARY KEY,
    class_name VARCHAR(20),
    teacher_name VARCHAR(20)
);

4. 巴斯范式（BCNF）

官方定义

1. 满足 3NF
2. 对于每个非平凡函数依赖 X \rightarrow Y，X 必为候选键
   （所有决定因素都是键）

核心性质

• 消除主键内部的隐藏依赖
• 3NF 可能还存在：主键之外的字段也能决定其他字段

经典BCNF反例：仓库管理

CREATE TABLE warehouse (
    store_id INT,      -- 仓库
    goods_id INT,      -- 商品
    manager_id INT     -- 管理员
);

依赖规则：

• 一个仓库只有一个管理员
• 一个管理员只在一个仓库
• (store_id, goods_id) 是主键

函数依赖：

1. (store_id, goods_id) → manager_id
2. manager_id → store_id

问题：
manager_id 是决定因素，但不是候选键 → 违反 BCNF

满足BCNF

-- 管理员 ↔ 仓库（一对一）
CREATE TABLE manager_store (
    manager_id INT PRIMARY KEY,
    store_id INT
);

-- 仓库 ↔ 商品（一对多）
CREATE TABLE store_goods (
    store_id INT,
    goods_id INT,
    PRIMARY KEY (store_id, goods_id)
);

5. 第四范式（4NF）

官方定义

1. 满足 BCNF
2. 不允许非平凡多值依赖
   （消除多值依赖）

多值依赖（MVD）官方定义

对于关系 R(U)，X、Y、Z 是 U 的子集，Z=U−X−Y
若对于一个 X 值，有一组 Y 值与之对应，且与 Z 无关，
称 Y 多值依赖于 X，记：

X \twoheadrightarrow Y

违反4NF示例

学生、课程、爱好三者互相独立

CREATE TABLE student_course_hobby (
    s_id INT,
    course VARCHAR(20),
    hobby VARCHAR(20)
);

数据会爆炸：

1 数学 篮球
1 数学 音乐
1 语文 篮球
1 语文 音乐

多值依赖：

• s_id →→ course
• s_id →→ hobby

课程与爱好互相独立、无关联 → 违反4NF

满足4NF

CREATE TABLE student_course (s_id INT, course VARCHAR(20));
CREATE TABLE student_hobby (s_id INT, hobby VARCHAR(20));

五大范式一句话总结（官方+通俗）

1. 1NF：列原子，不可再分
2. 2NF：消除部分函数依赖
3. 3NF：消除传递函数依赖
4. BCNF：所有决定因素必须是候选键
5. 4NF：消除多值依赖

异常问题（范式要解决的目标）

1. 数据冗余

重复存储大量相同信息

2. 更新异常

改一个数据要改 N 行，容易不一致

3. 插入异常

想插入的数据必须依赖其他数据才能插

4. 删除异常

删一条记录，连带删掉其他有用信息

范式越高，异常越少，但表越多，JOIN 越多。

最终完整可运行 MySQL 示例（整合版）

-- 1NF 示例
CREATE TABLE student_1nf (
    s_id INT PRIMARY KEY,
    name VARCHAR(20)
);

CREATE TABLE student_hobby_1nf (
    s_id INT, hobby VARCHAR(20), PRIMARY KEY(s_id,hobby)
);

-- 2NF 示例
CREATE TABLE student_2nf (s_id INT PRIMARY KEY, name VARCHAR(20));
CREATE TABLE course_2nf (c_id INT PRIMARY KEY, c_name VARCHAR(20));
CREATE TABLE score_2nf (s_id INT, c_id INT, score INT, PRIMARY KEY(s_id,c_id));

-- 3NF 示例
CREATE TABLE class_3nf (c_id INT PRIMARY KEY, c_name VARCHAR(20), teacher VARCHAR(20));
CREATE TABLE student_3nf (s_id INT PRIMARY KEY, name VARCHAR(20), c_id INT);

-- BCNF 示例
CREATE TABLE manager_store (m_id INT PRIMARY KEY, s_id INT);
CREATE TABLE store_goods (s_id INT, g_id INT, PRIMARY KEY(s_id,g_id));

-- 4NF 示例
CREATE TABLE s_c_4nf (s_id INT, c VARCHAR(20));
CREATE TABLE s_h_4nf (s_id INT, h VARCHAR(20));