数据库课程有一道经典题目：供应商-零件-供应关系建模。是标准的 M:N 联系带属性，按 E-R → 3NF → SQL 完整走一遍。
题目
某企业采购管理系统：
- 供应商 (Supplier)：编号 Sno、名称 Sname、城市 City
- 零件 (Part)：编号 Pno、名称 Pname、颜色 Color、重量 Weight
- 供应商可以供应多种零件，每种零件可由多个供应商供应
- 每个供应商对每种零件有一个供应价格 Price
要求：
- 画 E-R 图
- 设计满足 3NF 的关系模式
- SQL 建”供应”表，带主键、外键、价格 > 0 约束
- 写”供应红色零件的供应商名称”查询
第一步：E-R 图
两个实体、一个联系：
┌──────────────────┐
│ Supplier │
│──────────────────│
│ Sno (PK) │
│ Sname │
│ City │
└────────┬─────────┘
│ M
│
┌──────┴──────┐
│ Supply │◄── Price（联系属性）
└──────┬──────┘
│ N
│
┌────────┴─────────┐
│ Part │
│──────────────────│
│ Pno (PK) │
│ Pname │
│ Color │
│ Weight │
└──────────────────┘关系类型：Supplier M——(Supply)——N Part，Supply 联系有属性 Price。
第二步：3NF 关系模式
M:N 联系必须独立成一张表，不能合并到任何一端。
Supplier 表：
Supplier (
Sno ← 主键 (PK)
Sname
City
)Part 表：
Part (
Pno ← 主键 (PK)
Pname
Color
Weight
)Supply 表（M:N 联系变实体）：
Supply (
Sno ← 主键的一部分, 外键 → Supplier(Sno)
Pno ← 主键的一部分, 外键 → Part(Pno)
Price
)Supply 的主键是 (Sno, Pno) 联合主键——同一个供应商同一个零件只能有一条供应记录，不同的价格意味着不同的关系需要建模成”报价历史”。
为什么满足 3NF：
- 每个表所有非主属性都完全依赖于主键（无部分依赖 → 2NF）
- 每个表非主属性只依赖主键，不依赖其它非主属性（无传递依赖 → 3NF）
第三步：SQL 建”供应”表
CREATE TABLE Supply (
Sno CHAR(10) NOT NULL,
Pno CHAR(10) NOT NULL,
Price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (Sno, Pno),
FOREIGN KEY (Sno) REFERENCES Supplier(Sno),
FOREIGN KEY (Pno) REFERENCES Part(Pno),
CHECK (Price > 0)
);要点：
- 联合主键：
PRIMARY KEY (Sno, Pno)一起才唯一 - 外键：Sno 必须在 Supplier 里存在、Pno 必须在 Part 里存在
- CHECK 约束：
Price > 0由数据库强制，即使应用忘了校验也保证不了负价 DECIMAL(10,2)而不是FLOAT——金额永远用定点数，浮点会有精度问题
第四步：查询”供应红色零件的供应商名称”
需要连 Supplier / Supply / Part 三张表：
SELECT DISTINCT S.Sname
FROM Supplier S
JOIN Supply SP ON S.Sno = SP.Sno
JOIN Part P ON SP.Pno = P.Pno
WHERE P.Color = '红色';关键点：
DISTINCT— 一个供应商可能供应多种红色零件，去重JOIN— 内连接，只要有匹配就出结果WHERE P.Color = '红色'— 过滤条件
不用 JOIN 也能写（子查询版本）：
SELECT DISTINCT Sname
FROM Supplier
WHERE Sno IN (
SELECT Sno FROM Supply
WHERE Pno IN (
SELECT Pno FROM Part
WHERE Color = '红色'
)
);两种写法结果一样。现代 SQL 优化器基本能把子查询版本优化成 JOIN，但读起来 JOIN 更直观。
一些延伸
要报价历史怎么办？ 加一个 EffectiveDate：
CREATE TABLE SupplyHistory (
Sno CHAR(10),
Pno CHAR(10),
EffectiveDate DATE,
Price DECIMAL(10, 2),
PRIMARY KEY (Sno, Pno, EffectiveDate),
FOREIGN KEY (Sno) REFERENCES Supplier(Sno),
FOREIGN KEY (Pno) REFERENCES Part(Pno),
CHECK (Price > 0)
);查最新价格：
SELECT Sno, Pno, Price
FROM SupplyHistory sh
WHERE EffectiveDate = (
SELECT MAX(EffectiveDate)
FROM SupplyHistory
WHERE Sno = sh.Sno AND Pno = sh.Pno
);现代 SQL 也可以用窗口函数：
SELECT Sno, Pno, Price FROM (
SELECT Sno, Pno, Price,
ROW_NUMBER() OVER (PARTITION BY Sno, Pno ORDER BY EffectiveDate DESC) rn
FROM SupplyHistory
) t WHERE rn = 1;一句话总结
M:N 联系带属性必须独立成表，联合主键 + 两个外键。3NF 的关系模式设计基本是”每个 M:N 拆一张表 + 每张表消除传递依赖”。查询用 JOIN 比多层子查询好读。