数据库设计：E-R 图、3NF 范式与 SQL 约束建表

Soulock
2026年07月07日
- 技术

E-R 图基础

以供应商-零件为例：

实体1：供应商（Sno, Sname, City）
实体2：零件（Pno, Pname, Color, Weight）
联系：供应（M:N，含属性 Price）

一个供应商可供应多种零件，一种零件可由多个供应商供应，联系属于 多对多（M:N）。

    Supplier（供应商）           Part（零件）
 ┌─────────────────┐        ┌─────────────────┐
 │ Sno (PK)        │  M   N │ Pno (PK)        │
 │ Sname           ├────────┤ Pname           │
 │ City            │  供应  │ Color           │
 └─────────────────┘ Price  │ Weight          │
                            └─────────────────┘

3NF 关系模式设计

M:N 联系必须单独拆出一张联系表：

Supplier（供应商表）

Supplier(Sno PK, Sname, City)

Part（零件表）

Part(Pno PK, Pname, Color, Weight)

Supply（供应联系表）

Supply(Sno PK FK→Supplier, Pno PK FK→Part, Price)

联合主键 (Sno, Pno) 保证每个供应商对每种零件只有一条记录。三个表均满足 3NF（无传递依赖）。

SQL 建表（含约束）

CREATE TABLE Supplier (
    Sno   CHAR(10)    PRIMARY KEY,
    Sname VARCHAR(50) NOT NULL,
    City  VARCHAR(50)
);

CREATE TABLE Part (
    Pno    CHAR(10)    PRIMARY KEY,
    Pname  VARCHAR(50) NOT NULL,
    Color  VARCHAR(20),
    Weight DECIMAL(8,2)
);

CREATE TABLE Supply (
    Sno   CHAR(10),
    Pno   CHAR(10),
    Price DECIMAL(10,2),

    PRIMARY KEY (Sno, Pno),

    FOREIGN KEY (Sno) REFERENCES Supplier(Sno),
    FOREIGN KEY (Pno) REFERENCES Part(Pno),

    CHECK (Price > 0)
);

PRIMARY KEY：联合主键，值组合唯一且不为 NULL
FOREIGN KEY：引用父表主键，保证参照完整性
CHECK：价格必须大于 0，在插入/更新时自动校验

JOIN 查询

查询”供应红色零件的供应商名称”：

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 = '红色';

等价子查询写法：

SELECT DISTINCT Sname
FROM Supplier
WHERE Sno IN (
    SELECT Sno FROM Supply
    WHERE Pno IN (
        SELECT Pno FROM Part WHERE Color = '红色'
    )
);

JOIN 写法更清晰，子查询写法在没有 JOIN 语法支持时可用。

常见范式对比

范式	要求	典型违反
1NF	每个字段原子值，不含多值	用逗号分隔多个标签存一列
2NF	非主属性完全依赖主键（无部分依赖）	联系表里存冗余属性
3NF	非主属性不传递依赖主键	城市→邮编→省份放同一表

实践中通常达到 3NF 即可，OLAP 场景允许适度冗余换查询性能。