第 13 章 · 实验二 · 在第四节之后

把 E-R 图转换为表

按转换规则把 1:n、1:1、m:n、三个实体之间、同一实体内部的联系逐一建成表，最后按案例做一次模式合并和水平分解。

环　境

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

执　行

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

注　意

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

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

实验 2-1一对多：学生属于系
实验 2-2一对一：系主任领导系
实验 2-3多对多：学生选修课程
实验 2-4三个实体：供应
实验 2-5同一实体内部：职工领导职工
实验 2-6合并主码相同的模式
实验 2-7水平分解

第一步

数据准备（必做）

新建一个空的 er13 库。实验一建的表会被删除，本次实验的表都在实验中建立。

整段复制，全部执行下载 .sql

-- 第 13 章实验二数据：新建一个空的 er13 库，表都在实验中建立
-- 整段执行一次即可，想从头再做时重新执行。
DROP DATABASE IF EXISTS er13;
CREATE DATABASE er13 DEFAULT CHARACTER SET utf8mb4;
USE er13;

2-1

一对多：学生属于系

讲义 · 转换规则

目的: 学生与系是 n:1 的联系。实体各转换为一张表，联系并入 n 端的学生表，用外码表示。
前提: 已执行数据准备，当前库是 er13。

建立系表 dept 和学生表 student，student 中的 dno 参照 dept
插入 2 个系和 3 名学生
插入一名系号不存在的学生

参考答案

可以直接复制到 Workbench 执行

CREATE TABLE dept (
  dno CHAR(2) NOT NULL,
  dname VARCHAR(20),
  PRIMARY KEY (dno)
);
-- 联系并入 n 端，加上 1 端的码作为外码
CREATE TABLE student (
  sno CHAR(4) NOT NULL,
  sname VARCHAR(10),
  dno CHAR(2),
  PRIMARY KEY (sno),
  FOREIGN KEY (dno) REFERENCES dept (dno)
);
INSERT INTO dept VALUES ('d1', '计算机系'), ('d2', '信息系');
INSERT INTO student VALUES ('s1', '王彤', 'd1'), ('s2', '苏乐', 'd2'), ('s3', '林欣怡', 'd2');
-- 系号 d9 不存在，报错 1452
INSERT INTO student VALUES ('s4', '陶然', 'd9');

语句解释

1:n 联系	转换出的关系模式主码与 n 端相同，所以直接并入 n 端
FOREIGN KEY (dno)	学生所在的系必须是系表中存在的系
报错 1452	外码的值在被参照的表中找不到

执行后看：前面的语句都成功。最后一句报错 1452，s4 没有插入。

2-2

一对一：系主任领导系

讲义 · 转换规则

目的: 系主任与系是 1:1 的联系。把系主任的工号并入系表，再加上唯一约束，保证一名系主任只领导一个系。
前提: 已完成实验 2-1。

建立系主任表 director，插入两名系主任
在 dept 中增加 tno，既是唯一的，又参照 director
让 t1 领导计算机系，再让 t1 领导信息系

参考答案

可以直接复制到 Workbench 执行

CREATE TABLE director (
  tno CHAR(4) NOT NULL,
  tname VARCHAR(10),
  PRIMARY KEY (tno)
);
INSERT INTO director VALUES ('t1', '刘伟'), ('t2', '王平');
-- 并入一端，UNIQUE 保证一对一
ALTER TABLE dept
  ADD tno CHAR(4) UNIQUE,
  ADD FOREIGN KEY (tno) REFERENCES director (tno);
UPDATE dept SET tno = 't1' WHERE dno = 'd1';
SELECT * FROM dept;
-- t1 已经领导计算机系，报错 1062
UPDATE dept SET tno = 't1' WHERE dno = 'd2';

语句解释

1:1 联系	可以并入任意一端，每个实体的码都是候选码
UNIQUE	没有这个约束，一名系主任就能领导几个系，变成了 1:n
报错 1062	tno 的值 t1 已经存在，违反唯一约束

执行后看：计算机系的 tno 为 t1。最后一句报错 1062，信息系的 tno 仍为空。

2-3

多对多：学生选修课程

讲义 · 转换规则讲义 · 学生选课的 E-R 图

目的: 学生与课程是 m:n 的联系，单独转换为选修表 sc，主码是两个实体码的组合，成绩是联系的属性。
前提: 已完成实验 2-2。

建立课程表 course
建立选修表 sc，主码 (sno, cno)，两个字段分别参照 student 和 course
插入选课记录，查询学生姓名、课程名和成绩

参考答案

可以直接复制到 Workbench 执行

CREATE TABLE course (
  cno CHAR(4) NOT NULL,
  cname VARCHAR(20),
  PRIMARY KEY (cno)
);
CREATE TABLE sc (
  sno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  score INT,
  PRIMARY KEY (sno, cno),
  FOREIGN KEY (sno) REFERENCES student (sno),
  FOREIGN KEY (cno) REFERENCES course (cno)
);
INSERT INTO course VALUES ('c1', '数据库'), ('c2', '高等数学');
INSERT INTO sc VALUES ('s1', 'c1', 90), ('s1', 'c2', 85), ('s2', 'c1', 78);
SELECT s.sname, c.cname, sc.score
FROM sc
JOIN student s ON sc.sno = s.sno
JOIN course c ON sc.cno = c.cno;

语句解释

m:n 联系	必须单独成表，不能并入任何一端
PRIMARY KEY (sno, cno)	码是两个实体码的组合
score	成绩是选修这个联系的属性，放在 sc 中

执行后看：查询得到 3 行：王彤的数据库 90、高等数学 85，苏乐的数据库 78。

2-4

三个实体：供应

讲义 · 多个实体的联系

目的: 供应商、项目、零件三个实体之间的供应联系，转换为一张表，主码由三个实体的码组成。
前提: 已完成实验 2-3。

建立供应商、项目、零件三张表
建立供应表 supply，主码 (spno, pjno, ptno)，数量是联系的属性
插入数据并查询

参考答案

可以直接复制到 Workbench 执行

CREATE TABLE supplier (spno CHAR(4) NOT NULL, spname VARCHAR(20), PRIMARY KEY (spno));
CREATE TABLE project (pjno CHAR(4) NOT NULL, pjname VARCHAR(20), PRIMARY KEY (pjno));
CREATE TABLE part (ptno CHAR(4) NOT NULL, ptname VARCHAR(20), PRIMARY KEY (ptno));
CREATE TABLE supply (
  spno CHAR(4) NOT NULL,
  pjno CHAR(4) NOT NULL,
  ptno CHAR(4) NOT NULL,
  qty INT,
  PRIMARY KEY (spno, pjno, ptno),
  FOREIGN KEY (spno) REFERENCES supplier (spno),
  FOREIGN KEY (pjno) REFERENCES project (pjno),
  FOREIGN KEY (ptno) REFERENCES part (ptno)
);
INSERT INTO supplier VALUES ('sp1', '华信公司');
INSERT INTO project VALUES ('pj1', '教学楼'), ('pj2', '图书馆');
INSERT INTO part VALUES ('pt1', '螺栓');
INSERT INTO supply VALUES ('sp1', 'pj1', 'pt1', 200), ('sp1', 'pj2', 'pt1', 500);
SELECT * FROM supply;

语句解释

三个实体的联系	转换为一个关系模式，码是三个实体码的组合
qty	供应数量是联系的属性
另设主码	码太长时，也可以另加一个自增的编号做主码

执行后看：supply 有 2 行，华信公司给两个项目各供应了一批螺栓。

2-5

同一实体内部：职工领导职工

讲义 · 多个实体的联系

目的: 职工之间的领导联系是 1:n 的，在职工表中增加 leader 字段，参照职工表自己。
前提: 已完成实验 2-4。

建立职工表 worker，leader 参照 worker 的 wno
插入一名领导和两名职工
用自身连接查出每名职工和他的领导

参考答案

可以直接复制到 Workbench 执行

CREATE TABLE worker (
  wno CHAR(4) NOT NULL,
  wname VARCHAR(10),
  leader CHAR(4),
  PRIMARY KEY (wno),
  FOREIGN KEY (leader) REFERENCES worker (wno)
);
-- 赵明没有领导，leader 为空
INSERT INTO worker VALUES ('w1', '赵明', NULL), ('w2', '钱红', 'w1'), ('w3', '孙磊', 'w1');
SELECT w.wname AS 职工, l.wname AS 领导
FROM worker w LEFT JOIN worker l ON w.leader = l.wno;

语句解释

leader	领导的工号，外码参照同一张表的主码
自身连接	把 worker 当作两张表 w 和 l，按 leader = wno 连接
LEFT JOIN	没有领导的职工也显示出来

执行后看：查询得到 3 行：赵明的领导为空，钱红和孙磊的领导都是赵明。

2-6

合并主码相同的模式

讲义 · 合并与分解

目的: 案例中拥有联系转换为拥有(地址编号, 用户编号)，与地址的主码相同。把用户编号并入地址，删去拥有。
前提: 已完成实验 2-5。

建立 address 和 owns 两张表并插入数据
在 address 中增加 uid，从 owns 中填入
删除 owns，查看 address

参考答案

可以直接复制到 Workbench 执行

CREATE TABLE address (
  aid CHAR(10) NOT NULL,
  contact VARCHAR(10),
  phone CHAR(11),
  PRIMARY KEY (aid)
);
-- 拥有联系单独转换出的模式，主码也是地址编号
CREATE TABLE owns (
  aid CHAR(10) NOT NULL,
  uid INT,
  PRIMARY KEY (aid)
);
INSERT INTO address VALUES ('A000000001', '李娜', '13800002876'), ('A000000002', '李娜', '13800002876');
INSERT INTO owns VALUES ('A000000001', 1), ('A000000002', 1);
ALTER TABLE address ADD uid INT;
UPDATE address a JOIN owns o ON a.aid = o.aid
SET a.uid = o.uid
WHERE a.aid <> '';
DROP TABLE owns;
SELECT * FROM address;

语句解释

合并的条件	两个模式的主码相同，一行对一行
UPDATE … JOIN	按地址编号把 owns 中的用户编号写到 address 中
WHERE a.aid <> ''	Workbench 的安全更新模式要求 WHERE 中用到主码，这个条件对所有行都成立

执行后看：address 有 2 行，多了 uid 一列，值都是 1。owns 已经删除。

2-7

水平分解

讲义 · 合并与分解

目的: 订单包含商品的记录增长很快，按年份把它分到两张结构相同的表中，需要全部数据时再合起来。
前提: 已完成实验 2-6。

建立 contain_2020，再用 LIKE 建立结构相同的 contain_2021
各插入数据
用 UNION ALL 查出全部记录

参考答案

可以直接复制到 Workbench 执行

CREATE TABLE contain_2020 (
  ono CHAR(17) NOT NULL,
  gno CHAR(11) NOT NULL,
  qty INT,
  PRIMARY KEY (ono, gno)
);
CREATE TABLE contain_2021 LIKE contain_2020;
INSERT INTO contain_2020 VALUES ('20200410140000001', '11000000112', 5);
INSERT INTO contain_2021 VALUES ('20210105090000001', '11000000112', 2),
                                ('20210105090000001', '21200000109', 1);
SELECT * FROM contain_2020
UNION ALL
SELECT * FROM contain_2021;

语句解释

水平分解	按行切开，每张表的结构相同，只是存放不同时期的数据
CREATE TABLE … LIKE	按已有的表建立结构相同的空表
UNION ALL	把几张表的行合在一起，不去掉重复行

执行后看：合起来得到 3 行，与分解前一行不多、一行不少。

上一节第四节　逻辑结构设计下一章第 14 章　数据库物理结构设计、实施和运行维护

保山学院·人工智能教研室·曹鼎鼎

把 E-R 图转换为表

环 境

执 行

注 意

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

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

环　境

执　行

注　意

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