数据库原理及其应用

主题

ER 图与范式设计 ​

第五讲：数据库"设计"的方法论。给定一堆业务描述，如何画出 ER 图、如何把 ER 图转成表、以及如何用范式判定"这张表设计得对不对"——期末大题的标准出题区。

先记住一句话：范式不是越高级越好，而是"消除冗余到够用"——每一级范式都在消灭一种"修改起来会自相矛盾"的坏结构。

一、ER 模型：实体-联系方法 ​

三个基本要素：

要素图形例子
实体 entity矩形学生、课程
属性 attribute椭圆（连在实体上）学号、姓名
联系 relationship菱形（连接两个实体）选修（学生—课程）

联系的三种类型（由"一端一个实体对应另一端几个实体"决定）：

  • 1:1：一个班配一位班主任
  • 1:n：一个系有多名学生（"多"端要放入"一"端的主键作外键）
  • m:n：学生选课——必须单独建一张联系表，主键为两端主键的组合

二、ER 图转关系模式的规则 ​

口诀：实体各成表，1:n 外键放 n 端，m:n 单独建表。

以"学生—(m:n 选修)—课程"为例，转出三张表：

sql
student(sno PRIMARY KEY, sname, ssex, sage)
course (cno PRIMARY KEY, cname, credit)
sc     (sno, cno, grade, PRIMARY KEY(sno, cno),
        FOREIGN KEY (sno) REFERENCES student(sno),
        FOREIGN KEY (cno) REFERENCES course(cno))

1:1 联系可以并入任一端；1:n 联系并入 n 端——这两条决定了你少建几张表。

三、函数依赖：范式判定的语言 ​

设 R(U)，X、Y ⊆ U。X → Y 表示"X 的值一旦确定，Y 的值就唯一确定"（Y 函数依赖于 X）。例：学号 → 姓名（学号定了姓名就定了）；（学号，课程号）→ 成绩。

三个推理规则（Armstrong 公理），判定题全靠它们：

  • 自反：X ⊇ Y，则 X → Y（部分函数依赖的判定基础）
  • 增广：X → Y 则 XZ → YZ
  • 传递：X → Y，Y → Z，则 X → Z（传递函数依赖的判定基础）

两个必考术语：

  • 部分函数依赖：(sno, cno) → sname，但其实 sno → sname 就够了——sname 部分依赖于候选键（sno, cno）
  • 传递函数依赖：sno → sdept，sdept → sloc（系地址），于是 sno → sloc 是传递来的

四、范式逐级判定：一张"坏表"的进化 ​

用一个经典例子完整走一遍。原始表：

text
SC(sno, sname, sdept, sloc, cno, cname, grade)
主键：(sno, cno)
语义：一个学生属于一个系、住一个地方；一门课一个名字、一个学分

这张表有三个毛病，逐级治：

第一范式（1NF）：属性不可再分 —— 每列都是原子值。"联系方式"列里塞"电话,QQ"就违反 1NF，拆成两列。本表已满足。

第二范式（2NF）：消除非主属性对候选键的【部分】函数依赖 —— 非主属性 sname、sdept、sloc 只依赖于 sno，cname 只依赖于 cno，都"部分依赖"于 (sno, cno)。拆表：

text
STU(sno, sname, sdept, sloc)      主键 sno
COU(cno, cname, credit)           主键 cno
SC (sno, cno, grade)              主键 (sno, cno)

第三范式（3NF）：消除非主属性对候选键的【传递】函数依赖 —— STU 里 sno → sdept → sloc，sloc 传递依赖于 sno。再拆：

text
STU(sno, sname, sdept)            主键 sno
DEPT(sdept, sloc)                 主键 sdept

BCNF：每个函数依赖 X → Y 的决定因素 X 都必须包含候选键 —— 比 3NF 更严：3NF 只管非主属性，BCNF 连主属性之间的依赖也管。判定口诀：所有依赖的左边都是（超）候选键，才是 BCNF。

🧠 记忆锚点：1NF 原子，2NF 去部分（对候选键），3NF 去传递（非主属性），BCNF 决定因素全是键。 口诀串："原子-部分-传递-全键"。

五、为什么要消除冗余：不拆的代价 ​

回到原始 SC 表，三个具体事故场景：

  • 修改异常：张三转系，要改他选过的每一行课的 sdept——漏一行数据就自相矛盾
  • 插入异常：新系刚成立还没开课，系名和系地址插不进去（主键 cno 为 NULL，违反实体完整性）
  • 删除异常：某门课最后一 个学生退课，删掉这行——这门课的信息也跟着消失了

拆成 3NF 后，每个事实只存一处，三类异常全部消失。范式的本质是"一个事实只存一次"。

六、反范式：设计不是越规范越好 ​

规范化带来一致性的同时带来连接成本：查"张三的系地址"要从 STU 连 DEPT 两张表。在读多写少、追求速度的场景（报表、大屏、日志宽表），故意保留冗余换取少一次 JOIN，这叫反范式化。

考试的正确答法：先按 3NF/BCNF 设计，再说明在查询性能压力下可以对哪些读多写少的属性做受控冗余——既得分又工程正确。

小结 ​

  • ER 三要素：实体（矩形）、属性（椭圆）、联系（菱形）；联系分 1:1、1:n、m:n
  • 转表口诀：实体各成表，1:n 外键放 n 端，m:n 单独建表
  • 判定顺序：写出全部函数依赖 → 找候选键 → 逐级检查部分依赖（2NF）、传递依赖（3NF）、决定因素全键（BCNF）
  • 三类异常：修改异常、插入异常、删除异常——范式消灭的是"一个事实存多处"
  • 工程上：3NF 起步，读性能瓶颈处受控反范式

最后一讲，解决"多人同时改数据怎么办"：事务与并发控制——事务与并发控制。