• Soulock
• 2026年07月07日
• • 技术

供应关系 E-R 建模 + 3NF + SQL：一道数据库题的标准答案

数据库课程有一道经典题目：供应商-零件-供应关系建模。是标准的 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 比多层子查询好读。