在 SCD 表上数一数重复存放的数据，做一次更新异常，再用 GROUP BY 和 HAVING 给函数依赖找反例。
MySQL 8.0 和 MySQL Workbench，机房电脑已装好。所有语句在 Workbench 的 SQL 编辑区中执行。
Ctrl+Enter 执行光标所在的一句，Ctrl+Shift+Enter 执行全部。Mac 上把 Ctrl 换成 ⌘。
输入法切到英文。表名、字段名不加引号，字符串值才用单引号。
新建数据库 norm 和课件第 5 页的 SCD 表，装入 10 行数据。C7 的成绩还没有给出，是空值。两次实验共用这份脚本，每次实验前执行一次。
-- 第 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;
SELECT * FROM scd; -- 每个系和它的系主任存了几次 SELECT Dept, MN, COUNT(*) AS 行数 FROM scd GROUP BY Dept, MN; -- 每个学生的学号和姓名存了几次 SELECT SNo, SN, COUNT(*) AS 行数 FROM scd GROUP BY SNo, SN;
| GROUP BY Dept, MN | 系别和系主任都相同的行分成一组 |
| COUNT(*) | 这一对信息在表中存了几次 |
| 数据冗余 | 系主任本来只需要记一次，现在每个学生的每门课都要再记一次 |
UPDATE scd SET MN = '张强' WHERE SNo = 'S2' AND CNo = 'C5'; SELECT Dept, MN, COUNT(*) AS 行数 FROM scd GROUP BY Dept, MN; -- 改回原来的值，后面的实验还要用 UPDATE scd SET MN = '王平' WHERE SNo = 'S2' AND CNo = 'C5';
| WHERE SNo = 'S2' AND CNo = 'C5' | 用主码定位到一行 |
| 更新异常 | 信息系的系主任存了 7 份，只改一份，同一个系就有了两个系主任 |
| Dept → MN | 按语义一个系只有一名系主任，现在表中的数据违反了它 |
-- 同一个学号对应的姓名多于一种，就是 SNo 决定 SN 的反例 SELECT SNo, COUNT(DISTINCT SN), COUNT(DISTINCT Age), COUNT(DISTINCT Dept) FROM scd GROUP BY SNo; -- 只列出有反例的学号 SELECT SNo FROM scd GROUP BY SNo HAVING COUNT(DISTINCT SN) > 1 OR COUNT(DISTINCT Age) > 1 OR COUNT(DISTINCT Dept) > 1;
| COUNT(DISTINCT SN) | 这个学号对应了几种不同的姓名 |
| HAVING … > 1 | 筛选出对应多于一种的组，这些就是反例 |
| 结果为空 | 找不到反例。但函数依赖由语义决定，数据只能否定它，不能证明它 |
-- 一个系对应多名系主任，就是 Dept 决定 MN 的反例 SELECT Dept FROM scd GROUP BY Dept HAVING COUNT(DISTINCT MN) > 1; -- 一名系主任对应多个系，就是 MN 决定 Dept 的反例 SELECT MN, COUNT(DISTINCT Dept) AS 系的个数 FROM scd GROUP BY MN HAVING COUNT(DISTINCT Dept) > 1;
| Dept → MN | 找不到反例，一个系只有一名系主任 |
| MN → Dept | 刘伟同时是计算机系和自动化系的系主任，这就是反例，不成立 |
| 传递依赖 | SNo → Dept，Dept → MN，而 Dept 决定不了 SNo，所以 MN 传递函数依赖于 SNo |
SELECT SNo, COUNT(DISTINCT CNo) AS 课程数 FROM scd GROUP BY SNo HAVING COUNT(DISTINCT CNo) > 1; SELECT CNo, COUNT(DISTINCT SNo) AS 学生数 FROM scd GROUP BY CNo HAVING COUNT(DISTINCT SNo) > 1;
| SNo → CNo | 一个学生选多门课，不成立 |
| CNo → SNo | 一门课有多个学生选，也不成立 |
| 多对多 | 学号与课程号是多对多的联系，两个方向都没有函数依赖 |
-- 只用学号能不能决定成绩 SELECT SNo FROM scd GROUP BY SNo HAVING COUNT(DISTINCT Score) > 1; -- 只用课程号能不能决定成绩 SELECT CNo FROM scd GROUP BY CNo HAVING COUNT(DISTINCT Score) > 1; -- 只用学号能不能决定姓名 SELECT SNo FROM scd GROUP BY SNo HAVING COUNT(DISTINCT SN) > 1;
| (SNo, CNo) → Score | 主码能决定成绩。去掉任何一个属性后都有反例，所以是完全函数依赖 |
| (SNo, CNo) → SN | 主码能决定姓名，但只用 SNo 也能决定，所以是部分函数依赖 |
| 部分依赖 | 下一节会讲，第二范式就是要消除非主属性对码的部分依赖 |