先比较系别作为属性和作为实体的两种表，再把采购部、销售部两张局部图得到的商品表，依次消除属性冲突、命名冲突和结构冲突，合成一张商品表。
MySQL 8.0 和 MySQL Workbench，机房电脑已装好。所有语句在 Workbench 的 SQL 编辑区中执行。
Ctrl+Enter 执行光标所在的一句，Ctrl+Shift+Enter 执行全部。Mac 上把 Ctrl 换成 ⌘。
输入法切到英文。表名、字段名不加引号，字符串值才用单引号。
新建 er13 库：学生表 stu_a 把系名、系主任、办公地点都当作学生的属性。另有两个局部 E-R 图各自得到的商品表 purchase_goods 和 sale_goods。
-- 第 13 章实验一数据：一张把系别信息当作属性的学生表，以及两个局部 E-R 图各自得到的商品表 -- 整段执行一次即可，想从头再做时重新执行。 DROP DATABASE IF EXISTS er13; CREATE DATABASE er13 DEFAULT CHARACTER SET utf8mb4; USE er13; -- 系别作为学生的属性：系名、系主任、办公地点都写在学生表中 CREATE TABLE stu_a ( sno CHAR(4) NOT NULL, sname VARCHAR(10), dept VARCHAR(10), mn VARCHAR(10), office VARCHAR(20), PRIMARY KEY (sno) ); INSERT INTO stu_a VALUES ('s1', '王彤', '计算机', '刘伟', '信息楼 301'), ('s2', '苏乐', '信息', '王平', '信息楼 205'), ('s3', '林欣怡', '信息', '王平', '信息楼 205'), ('s4', '陶然', '计算机', '刘伟', '信息楼 301'); -- 采购部的局部 E-R 图得到的商品表 CREATE TABLE purchase_goods ( gno CHAR(11) NOT NULL, gname VARCHAR(40), maker VARCHAR(40), intime DATETIME, PRIMARY KEY (gno) ); INSERT INTO purchase_goods VALUES ('11000000112', '数据库原理及应用教程', '人民邮电出版社', '2020-02-11 11:15:25'), ('21200000109', '联想笔记本电脑 E14', '联想集团', '2020-03-02 09:30:00'); -- 销售部的局部 E-R 图得到的商品表，编号的名称和类型都与采购部不同 CREATE TABLE sale_goods ( goods_id BIGINT NOT NULL, gname VARCHAR(40), price DECIMAL(9,2), PRIMARY KEY (goods_id) ); INSERT INTO sale_goods VALUES (11000000112, '数据库原理及应用教程', 45.50), (21200000109, '联想笔记本电脑 E14', 5588.60); SHOW TABLES;
SELECT * FROM stu_a; SELECT dept, mn, office, COUNT(*) AS 行数 FROM stu_a GROUP BY dept, mn, office;
| dept、mn、office | 系名、系主任、办公地点，都写在学生这一行中 |
| GROUP BY | 系的信息相同的学生分成一组 |
| 问题 | 系有了自己的描述信息，却跟着每个学生重复存放 |
CREATE TABLE dept ( dept VARCHAR(10) NOT NULL, mn VARCHAR(10), office VARCHAR(20), PRIMARY KEY (dept) ); INSERT INTO dept SELECT DISTINCT dept, mn, office FROM stu_a; CREATE TABLE student ( sno CHAR(4) NOT NULL, sname VARCHAR(10), dept VARCHAR(10), PRIMARY KEY (sno), FOREIGN KEY (dept) REFERENCES dept (dept) ); INSERT INTO student SELECT sno, sname, dept FROM stu_a; SELECT s.sno, s.sname, d.dept, d.mn, d.office FROM student s JOIN dept d ON s.dept = d.dept;
| 实体有描述信息 | 系有系主任、办公地点等描述信息，所以系别应当是实体 |
| dept 表 | 每个系只存一行 |
| FOREIGN KEY (dept) | 学生与系是 n:1 的联系，在学生表中用外码表示 |
DESC purchase_goods; DESC sale_goods; -- 统一为字符型，编号不参与计算 ALTER TABLE sale_goods MODIFY goods_id CHAR(11) NOT NULL; DESC sale_goods; SELECT * FROM sale_goods;
| 属性冲突 | 同一个属性在两张局部图中的类型、取值范围或单位不同 |
| MODIFY | 修改字段的类型，已有的数据按新类型转换 |
| 用 CHAR | 编号只用来区分商品，不做加减运算，用字符型 |
ALTER TABLE sale_goods RENAME COLUMN goods_id TO gno; DESC sale_goods;
| 命名冲突 | 异名同义：名称不同，指的是同一个属性。反过来同名异义也是命名冲突 |
| RENAME COLUMN | MySQL 8.0 中修改字段名的写法 |
| 案例中 | 采购商品与销售商品也是异名同义，合并后统一叫商品 |
CREATE TABLE goods ( gno CHAR(11) NOT NULL, gname VARCHAR(40), maker VARCHAR(40), intime DATETIME, price DECIMAL(9,2), PRIMARY KEY (gno) ); -- 采购部的属性加上销售部的单价 INSERT INTO goods SELECT p.gno, p.gname, p.maker, p.intime, s.price FROM purchase_goods p LEFT JOIN sale_goods s ON p.gno = s.gno; SELECT * FROM goods;
| 结构冲突 | 同一个实体在两张局部图中的属性组成不同 |
| 取并集 | 合并后的实体包含两张图中的全部属性 |
| LEFT JOIN | 以采购部的商品为准，按编号找到销售部的单价 |