课程 › 第 12 章 › 实验二
第 12 章 · 实验二 · 在第三节之后

分解到第三范式

按课件的两步分解，把 SCD 拆成学生 S、选课 SC、系 D 三张表，用自然连接连回来核对，再故意切错一次，最后看三种异常是否消失。

环　境

MySQL 8.0 和 MySQL Workbench，机房电脑已装好。所有语句在 Workbench 的 SQL 编辑区中执行。

执　行

Ctrl+Enter 执行光标所在的一句，Ctrl+Shift+Enter 执行全部。Mac 上把 Ctrl 换成 ⌘。

注　意

输入法切到英文。表名、字段名不加引号，字符串值才用单引号。

本次实验的顺序　后一个实验用到前一个的结果

  1. 实验 2-1第一步：消除部分依赖
  2. 实验 2-2第二步：消除传递依赖
  3. 实验 2-3连回来核对
  4. 实验 2-4切错一次
  5. 实验 2-5异常消失了
第一步

数据准备（必做）

与实验一相同的脚本，执行后 norm 库中只有 SCD 一张表。

整段复制，全部执行下载 .sql
-- 第 12 章实验数据：课件第 5 页的泛关系 SCD，10 行数据
-- 整段执行一次即可，想从头再做时重新执行。两次实验共用。
DROP DATABASE IF EXISTS norm;
CREATE DATABASE norm DEFAULT CHARACTER SET utf8mb4;
USE norm;

-- SNo 学号，SN 姓名，Age 年龄，Dept 系别，MN 系主任，CNo 课程号，Score 成绩
CREATE TABLE scd (
  SNo CHAR(4) NOT NULL,
  SN VARCHAR(10),
  Age INT,
  Dept VARCHAR(10),
  MN VARCHAR(10),
  CNo CHAR(4) NOT NULL,
  Score INT,
  PRIMARY KEY (SNo, CNo)
);

INSERT INTO scd VALUES
('S1', '赵亦', 17, '计算机', '刘伟', 'C1', 90),
('S1', '赵亦', 17, '计算机', '刘伟', 'C2', 85),
('S2', '钱尔', 18, '信息', '王平', 'C5', 57),
('S2', '钱尔', 18, '信息', '王平', 'C6', 80),
('S2', '钱尔', 18, '信息', '王平', 'C7', NULL),
('S2', '钱尔', 18, '信息', '王平', 'C4', 70),
('S3', '孙珊', 20, '信息', '王平', 'C1', 75),
('S3', '孙珊', 20, '信息', '王平', 'C2', 70),
('S3', '孙珊', 20, '信息', '王平', 'C4', 85),
('S4', '李思', 21, '自动化', '刘伟', 'C1', 93);

SELECT * FROM scd;
2-1

第一步：消除部分依赖

讲义 · 第二范式
目的
把 SCD 分解为选课表 sc 和学生系别表 sd，让每个非主属性都完全依赖于码。
前提
已执行数据准备，当前库是 norm。
  1. 建立 sc(SNo, CNo, Score)，主码 (SNo, CNo)，装入数据
  2. 建立 sd(SNo, SN, Age, Dept, MN)，主码 SNo，装入不重复的数据
  3. 查看 sd

参考答案

可以直接复制到 Workbench 执行
CREATE TABLE sc (
  SNo CHAR(4) NOT NULL,
  CNo CHAR(4) NOT NULL,
  Score INT,
  PRIMARY KEY (SNo, CNo)
);
INSERT INTO sc SELECT SNo, CNo, Score FROM scd;
CREATE TABLE sd (
  SNo CHAR(4) NOT NULL,
  SN VARCHAR(10),
  Age INT,
  Dept VARCHAR(10),
  MN VARCHAR(10),
  PRIMARY KEY (SNo)
);
-- 每个学生只取一行
INSERT INTO sd SELECT DISTINCT SNo, SN, Age, Dept, MN FROM scd;
SELECT * FROM sd;

语句解释

sc只有 Score 一个非主属性，它完全依赖于 (SNo, CNo)
sd主码是单个属性 SNo，不可能有部分依赖，达到第二范式
SELECT DISTINCT同一个学生在 SCD 中有几行，这里只留一行
执行后看：sc 有 10 行，sd 有 4 行，每个学生一行。sd 中信息系和王平还存了 2 次。
2-2

第二步：消除传递依赖

讲义 · 第三范式
目的
把 sd 再分解为学生表 s 和系表 d，切断 SNo → Dept → MN 这条传递依赖。
前提
已完成实验 2-1。
  1. 建立 s(SNo, SN, Age, Dept)，主码 SNo
  2. 建立 d(Dept, MN)，主码 Dept，装入不重复的数据
  3. 查看 d

参考答案

可以直接复制到 Workbench 执行
CREATE TABLE s (
  SNo CHAR(4) NOT NULL,
  SN VARCHAR(10),
  Age INT,
  Dept VARCHAR(10),
  PRIMARY KEY (SNo)
);
INSERT INTO s SELECT SNo, SN, Age, Dept FROM sd;
CREATE TABLE d (
  Dept VARCHAR(10) NOT NULL,
  MN VARCHAR(10),
  PRIMARY KEY (Dept)
);
INSERT INTO d SELECT DISTINCT Dept, MN FROM sd;
SELECT * FROM d;

语句解释

在 Dept 处切开Dept 留在 s 中，同时作为 d 的主码，两张表靠它联系起来
d一个系只存一行，系主任只存一次
第三范式s、sc、d 中都没有非主属性对码的部分依赖和传递依赖
执行后看：d 有 3 行：计算机 刘伟，信息 王平，自动化 刘伟。
2-3

连回来核对

讲义 · 无损连接
目的
用自然连接把 s、sc、d 连起来，核对能否得到原来 SCD 的 10 行。
前提
已完成实验 2-2。
  1. 把三张表自然连接，查看结果
  2. 统计连接结果的行数

参考答案

可以直接复制到 Workbench 执行
SELECT * FROM s NATURAL JOIN sc NATURAL JOIN d;
SELECT COUNT(*) FROM s NATURAL JOIN sc NATURAL JOIN d;

语句解释

NATURAL JOIN自然连接，按同名的列相等来连接，同名的列只保留一个
s 和 sc按 SNo 连接
再连 d按 Dept 连接
无损连接连回来的数据与分解前完全相同，一行不多，一行不少
执行后看：得到 10 行，与 SCD 的内容相同，只是列的顺序变了，同名的列排在前面。
2-4

切错一次

讲义 · 无损连接
目的
把 sd 按系主任 MN 切开，再连回来，看看会发生什么。
前提
已完成实验 2-3。
  1. 用 sd 生成 sd1(SNo, SN, Age, MN) 和 sd2(Dept, MN)
  2. 把两张表自然连接，与 sd 比较

参考答案

可以直接复制到 Workbench 执行
-- CREATE TABLE … AS SELECT 用查询结果直接建表
CREATE TABLE sd1 AS SELECT SNo, SN, Age, MN FROM sd;
CREATE TABLE sd2 AS SELECT DISTINCT Dept, MN FROM sd;
SELECT * FROM sd1 NATURAL JOIN sd2;

语句解释

CREATE TABLE … AS SELECT按查询结果的列建表，同时装入数据
按 MN 连接刘伟同时是两个系的系主任，MN 决定不了 Dept
有损分解连回来多出了原来没有的行，信息被弄乱了
执行后看：连回来有 6 行，比 sd 的 4 行多 2 行：赵亦被连到了自动化系，李思被连到了计算机系。
2-5

异常消失了

讲义 · 规范化的原则和步骤
目的
在分解后的表上重做插入、修改、删除，检查三种异常是否还会出现。
前提
已完成实验 2-4。
  1. 新成立数学系，还没有学生，把它记下来
  2. 信息系的系主任换成张强
  3. 删除李思的选课记录，再查看系表

参考答案

可以直接复制到 Workbench 执行
-- 没有学生的系也能记下来
INSERT INTO d VALUES ('数学', '赵明');
-- 换系主任只改一行
UPDATE d SET MN = '张强' WHERE Dept = '信息';
-- 删除选课记录，系的信息还在
DELETE FROM sc WHERE SNo = 'S4';
SELECT * FROM d;

语句解释

INSERT INTO d系有自己的表和主码，不需要学号和课程号
UPDATE d系主任只存了一次，改一行就完成
DELETE FROM sc删除的只是选课记录，学生和系的信息都还在
执行后看：d 中有 4 个系，数学系的系主任是赵明，信息系的系主任是张强，自动化系仍然存在。
保山学院·人工智能教研室·曹鼎鼎