S05 · 数据建模与数据库

系统分析师

发布日期: 2026-09-06

更新日期: 2026-09-06

文章字数: 13.6k

第 5 周｜主打科目：案例分析 + 综合知识｜对应知识域：数据库系统（9–12%）、系统分析与设计｜主打基本问题：Q3（什么时候守规范，什么时候为性能打破规范？）｜本阶段产出：E-R 图 + 规范化到 BCNF 的关系模式集 + 反范式点及代价清单

项目宪法见 00-课程总纲.md；数据库方向在案例分析里是「套路最固定、最能练到稳拿」的一类，见 02-考点地图.md。

一、情境开场（Hook）

S04 那张领域类图评审完的第二天，质量部王工提了个要求：做一个”批次追溯查询”页面，输入批次号，3 秒内出完整链条——工单、报工、检验值、设备参数、原材料供应商，一层都不能少。

开发组长当场算了笔账：按现在这套关系模式，一份追溯报告要 JOIN 11 张表，链条深度 5 层时，本地测试库实测 4.7 秒。王工说 3 秒是硬指标，”客户审核时当着面查不出来，这一单就黄了”。

DBA 老李给了个方案：把 物料名称、供应商名称、批次当前状态 这几个字段冗余写进追溯明细表，”JOIN 从 11 张降到 3 张，能压到 1 秒以内”。

架构师皱眉：”冗余了，供应商改名怎么办？”老李说：”半夜跑批同步。”

你夹在中间。S04 刚把 批次 — 物料 — 供应商 这条链画得干干净净，现在有人要你亲手把冗余塞回去。问题是：规范化那条路明明是对的，为什么还要往回走？ 而更现实的问题是——如果塞了冗余，谁为”供应商已经改名、追溯报告上还印着旧名字”这件事负责？

本周不给你一个”两边都对”的和稀泥答案，只给你一套能把代价算清楚的工具：什么情况下冗余是划算的，什么情况下是给自己埋雷，以及埋了之后怎么把雷管住。

二、为什么学这一周（Where & Why）

三级模式两级映像：综合知识每年 1–2 题，纯记忆送分。案例偶有”更换存储设备要不要改应用程序”的问法，答错就是白丢。
E-R 模型与向关系模式的转换规则：综合知识 2–3 题；案例”补画 E-R 图 / 补关系模式”约 6–10 分，是第 1 题建模题的常见形态之一（与 DFD、用例图轮换出现）。
关系代数：综合知识 1–2 题，最爱考”下列哪个表达式等价于给定 SQL”以及自然连接与等值连接的区别。
规范化判定与分解：综合知识 2–4 题，案例几乎年年有一问，8–15 分。这是本周的绝对核心——不会就是硬丢分，且无法用别的模块补。
反规范化：案例”说明反规范化的手段、代价与补偿措施”约 6–8 分；同时是论文”数据库建模及应用”方向唯一能写出深度的素材。
触发器 / 事务并发 / 封锁协议：综合知识 2–4 题（三级封锁协议排序年年考）；案例”触发器 vs 应用层实现”约 6–8 分。
SQL 补全：案例填空，每空 1–2 分，是性价比最高的”手到擒来”题型，但空位一多就容易漏 GROUP BY 或写错 HAVING。

三、核心讲义（Equip）

3.1 三级模式两级映像与数据库设计阶段

定义：数据库系统（Database System, DBS）采用三级模式结构——外模式、模式、内模式，中间由两级映像连接。

模式	别名	数量	内容
外模式（External Schema）	子模式 / 用户模式	一个数据库可有多个	某个用户或应用能看到和使用的局部数据的逻辑结构与特征描述，是用户的数据视图
模式（Schema）	逻辑模式 / 概念模式	一个数据库只有一个	全体数据的整体逻辑结构与特征描述，是所有用户的公共数据视图
内模式（Internal Schema）	存储模式 / 物理模式	一个数据库只有一个	数据物理结构与存储方式的描述（记录格式、索引、压缩、加密、是否聚簇）

两级映像 → 两种数据独立性（本题年年考，问法固定）：

映像	存在位置	保证的独立性	含义
外模式 / 模式映像	每一个外模式都有一个	逻辑独立性	模式改变（增加新表、新属性、改变联系）时，只需由 DBA 修改映像，外模式可保持不变，应用程序不必修改
模式 / 内模式映像	唯一	物理独立性	存储结构改变（更换存储设备、改变索引、改变文件组织）时，只需修改映像，模式可保持不变，应用程序不必修改

真题判定：”更换存储设备 / 增加索引 / 改变文件组织 → 应用程序是否需要修改？”→ 答不需要，因为物理独立性。

数据库设计六阶段（规范设计法 / 新奥尔良方法）：

阶段	产出	独立性
① 需求分析	数据字典、数据流图、需求说明书	与具体 DBMS 无关
② 概念结构设计	E-R 图（独立于任何 DBMS）	独立于机器与 DBMS
③ 逻辑结构设计	关系模式集（E-R → 关系模型，再规范化）	与 DBMS 有关、与物理存储无关
④ 物理结构设计	存储结构、存取路径、索引、分区	与具体 DBMS 高度相关
⑤ 数据库实施	建库、装载数据、试运行	—
⑥ 运行与维护	备份恢复、性能调优、重组重构	—

3.2 概念结构设计：E-R 模型

定义：实体-联系模型（Entity-Relationship Model, E-R 模型）是概念结构设计的主流工具，用实体、属性、联系三要素描述现实世界。

要素	图形	本项目例子
实体（Entity）	矩形	批次、工单、设备、物料、供应商、检验记录
弱实体（Weak Entity）	双线矩形，其标识性联系用双线菱形	`设备参数快照`——脱离”设备”无法被唯一标识，其码部分来自设备编号
属性（Attribute）	椭圆	批次号、生产日期、库存数量
联系（Relationship）	菱形	属于、加工于、检验于、供应

属性的四种分类（高频辨析）：

分类	定义	本项目例子
简单属性	不可再分	批次号
复合属性	可再分解为子属性	`地址` → 省 / 市 / 区 / 详细地址；`尺寸` → 长 / 宽 / 高
多值属性	一个实体在该属性上有多个取值	`员工的联系电话`（手机 + 座机）；`设备的通信协议`（Modbus + OPC-UA）
派生属性（Derived）	可由其他属性计算得到，一般不实际存储	`年龄` 由 `出生日期` 算出；`合格数` 由 `总数 − 不合格数` 算出；`库存金额` 由 `数量 × 单价` 算出

转换硬规则：复合属性要拆成多个简单属性（或保留为组件属性）；多值属性必须单独建一个关系模式（否则违反 1NF）；派生属性通常不存储，改为应用层计算或加派生列（见 3.5 反规范化）。

联系的基数（Cardinality）与转换直觉：

基数	含义	本项目例子
1 : 1	两端各一个	`工单 1 —— 1 工艺路线版本`
1 : n	一端一个，多端多个	`工单 1 —— n 批次`；`设备 1 —— n 报工记录`
m : n	两端都多个	`物料 m —— n 供应商`（一种料可有多家供应商，一家供应商可供应多种料）

参与约束：完全参与（双线连接，实体集中每个实体都参与该联系，如”检验记录必属于某个批次”）；部分参与（单线，如”设备可以尚未产生任何报工”）。

3.3 逻辑结构设计：转换规则与关系代数

E-R 图向关系模式转换的六条规则（必须逐条背死，案例直接考）：

#	情形	转换方法
1	一个实体型	转换为一个关系模式：实体的属性 = 关系的属性，实体的码 = 关系的码
2	1 : 1 联系	① 转为独立关系模式：相连各实体的码 + 联系本身的属性 → 关系的属性，各实体的码均为该关系的候选码；② 或与任意一端的关系模式合并（在该端加入另一端实体的码与联系属性）。实践中通常选②，少一张表
3	1 : n 联系	① 转为独立关系模式：属性的码 + 联系属性，关系的码取 n 端实体的码；② 或与 n 端关系模式合并（在 n 端加入 1 端实体的码与联系属性）。实践中通常选②
4	m : n 联系	必须转为一个独立的关系模式：相连各实体的码 + 联系本身的属性 → 关系的属性，各实体码的组合为该关系的码
5	多元联系（三个及以上实体）	转为一个独立的关系模式，各实体码的组合为关系的码，联系属性一并纳入
6	具有相同码的关系模式	可以合并（减少连接次数）

最大易错点：1:n 联系与 n 端合并（不是与 1 端合并）；m:n 必须独立建表（不能合并到任何一端，否则会产生多值属性、违反 1NF）。

本项目一组转换示例（照此核对你的交付物）：

E-R 联系	基数	结果关系模式
`工单 1 —— n 批次`	1:n	与 n 端合并：`批次(批次号, 工单号, 产品编码, 计划数量, 状态, ……)`
`物料 m —— n 供应商`	m:n	独立建表：`供货关系(物料编码, 供应商编码, 供货价格, 供货周期, 是否主供应商)`，码 = `(物料编码, 供应商编码)`
`批次 1 —— n 检验记录`	1:n	与 n 端合并：`检验记录(检验记录号, 批次号, 检验项, 检验值, 判定结果, 检验员, 检验时间)`
`批次 — 设备 — 班次`（三元：某批次在某设备上由某班次加工）	三元	独立建表：`生产实绩(批次号, 设备编号, 班次编号, 合格数, 不合格数)`，码 = 三者组合

关系代数（Relational Algebra）——SQL 的数学基础，考题常让”写出等价的关系代数表达式”：

类别	运算	说明
传统集合运算	并 ∪	R ∪ S：并相容（属性个数相同、对应属性域相同）的元组合并并去重
	差 −	R − S：属于 R 但不属于 S 的元组
	交 ∩	R ∩ S：既属于 R 又属于 S 的元组（可由 `R − (R − S)` 导出）
	笛卡尔积 ×	R × S：属性列拼接（m+k 列），元组两两组合（n×p 行）
专门关系运算	选择 σ	`σ_条件(R)`：水平方向，选出满足条件的行
	投影 π	`π_属性列(R)`：垂直方向，选出指定的列，并去掉重复行
	连接 ⋈	在笛卡尔积上按条件筛选
	除 ÷	`R ÷ S`：用于”全部 / 所有 / 至少包含“类查询（如”查询采用了某批次全部检验项的批次”）

四种连接的辨析（高频）：

连接类型	定义	结果列
θ 连接	在 R × S 上选取满足 `A θ B`（θ 可为 >、<、≥ 等）的元组	保留两侧全部列
等值连接	θ 为 `=` 的 θ 连接	保留两侧全部列（比较列会重复出现）
自然连接	特殊的等值连接：比较的分量必须是同名属性组，且结果中自动去掉重复的属性列	同名属性列只出现一次
外连接	保留悬浮元组（无匹配的元组），缺失属性填 NULL	分左外连接（保左）、右外连接（保右）、全外连接（保两边）

判定口诀：**”等值连接只等不去重，自然连接既等又去重”**。考题问”两个关系有同名属性，做等值连接与自然连接，结果列数是否相同”→ 不同，自然连接少列。

3.4 规范化：判定步骤与 BCNF 分解演算

定义：范式（Normal Form, NF）是关系模式满足的规范化程度。规范化的本质是消除数据冗余与更新异常（插入异常、删除异常、修改异常/数据不一致）——注意：冗余不是病，冗余导致的更新异常才是病。

前置概念：

概念	定义
函数依赖（Functional Dependency, FD）	`X → Y`：对关系模式的任意关系实例，不存在两个元组在 X 上取值相等而在 Y 上取值不等。X 叫决定因素
完全函数依赖	`X → Y`，且对 X 的任何真子集 X′ 都有 `X′ ↛ Y`
部分函数依赖	`X → Y`，但存在 X 的真子集 X′ 使 `X′ → Y`
传递函数依赖	`X → Y`，`Y ⊄ X`，`Y ↛ X`，`Y → Z`，则称 Z 传递函数依赖于 X
候选码（Candidate Key）	能唯一标识元组、且其任何真子集都不能唯一标识的属性组（可有多个）
主属性	包含在任一候选码中的属性；非主属性 = 不包含在任何候选码中的属性

四个范式（定义必须背原文级别准确）：

范式	定义	消除什么
1NF	关系模式的每个分量都是不可再分的数据项（不允许”表中有表”、不允许重复组、不允许一列存多个值）	消除重复组
2NF	R ∈ 1NF，且每个非主属性都完全函数依赖于候选码	消除非主属性对码的部分函数依赖
3NF	R ∈ 2NF，且每个非主属性都不传递依赖于候选码	消除非主属性对码的传递函数依赖
BCNF	R ∈ 1NF，且每一个非平凡函数依赖 X → Y 中，X 必含有候选码（等价说法：每个决定因素都是候选码；也等价于消除了主属性对码的部分与传递依赖）	消除主属性对码的部分 / 传递依赖

注意 2NF/3NF 都只管非主属性，所以”全是主属性的关系模式自动满足 3NF”——这正是下面第二个演算的陷阱所在。

判定步骤（四步，考场上按此流水线走）：

求候选码：① 只看出现在 FD 左部（L 类）与左右部都不出现（N 类）的属性——L 类 ∪ N 类必包含在任何候选码中；② 只出现在右部（R 类）的属性必不在任何候选码中；③ 左右部都出现（LR 类）的属性，与①的属性组一起求闭包，闭包覆盖全部属性的最小属性组即为候选码。
划分主属性 / 非主属性。
逐级判范式：先查 1NF（分量是否可再分）→ 查非主属性对码是否有部分依赖（判 2NF）→ 查非主属性对码是否有传递依赖（判 3NF）→ 查每个决定因素是否都含码（判 BCNF）。
分解：把违规依赖的决定因素及其决定的属性抽出去单独成表；分解后必须检查无损连接性与是否保持函数依赖。

无损连接判定定理（二元分解）：将 R 分解为 R1、R2，若 R1 ∩ R2 → R1 − R2 或 R1 ∩ R2 → R2 − R1 在 F 中成立，则该分解是无损连接分解。

【演算一】从 1NF 一路分解到 BCNF（制造企业仓储）

给定 库存(物料编码, 物料名称, 规格型号, 供应商编码, 供应商名称, 供应商联系人, 仓库编号, 库位编号, 库存数量, 盘点日期)

函数依赖集 F：

① 物料编码 → 物料名称, 规格型号, 供应商编码
② 供应商编码 → 供应商名称, 供应商联系人
③ (物料编码, 仓库编号, 库位编号) → 库存数量, 盘点日期

步骤 1：求候选码。L 类（只在左部）= 物料编码、仓库编号、库位编号；R 类（只在右部）= 其余全部；LR 类 = 无。
L 类属性组 {物料编码, 仓库编号, 库位编号} 的闭包：由①得物料名称、规格型号、供应商编码，由②（传递）得供应商名称、供应商联系人，由③得库存数量、盘点日期 → 闭包 = 全部属性。
∴ **候选码唯一：(物料编码, 仓库编号, 库位编号)**。

步骤 2：划分属性。主属性 = 物料编码、仓库编号、库位编号；非主属性 = 物料名称、规格型号、供应商编码、供应商名称、供应商联系人、库存数量、盘点日期。

步骤 3：判范式。

1NF：满足（每个分量都是原子值）。
2NF：不满足。因为 物料编码 → 物料名称，而 物料编码 是候选码的真子集，即非主属性（物料名称、规格型号、供应商编码）对码存在部分函数依赖。
∴ 该关系模式只满足 1NF。

步骤 4：分解。

消除部分依赖（→ 2NF）：把①的左部与所决定属性抽出去：
- R1(物料编码, 物料名称, 规格型号, 供应商编码)　码 = 物料编码
- R2(物料编码, 仓库编号, 库位编号, 库存数量, 盘点日期)　码 = (物料编码, 仓库编号, 库位编号)
- 无损性：R1 ∩ R2 = {物料编码}，R1 − R2 = {物料名称, 规格型号, 供应商编码}，而 物料编码 → 物料名称, 规格型号, 供应商编码 成立 → 无损。
判 R2：非主属性（库存数量、盘点日期）完全依赖于码，且无传递依赖，决定因素只有码 → R2 ∈ BCNF ✔
判 R1：码 = 物料编码，非主属性 = 物料名称、规格型号、供应商编码、供应商名称、供应商联系人。
- 物料编码 → 供应商编码 → 供应商名称（由①、②）→ 存在传递函数依赖 → R1 只满足 2NF，不满足 3NF。
消除传递依赖（→ 3NF）：
- R11(物料编码, 物料名称, 规格型号, 供应商编码)　码 = 物料编码
- R12(供应商编码, 供应商名称, 供应商联系人)　码 = 供应商编码
- 无损性：R11 ∩ R12 = {供应商编码}，R12 − R11 = {供应商名称, 供应商联系人}，供应商编码 → 供应商名称, 供应商联系人 成立 → 无损。
最终判定：R11 的决定因素只有 物料编码（含码）→ BCNF ✔；R12 的决定因素只有 供应商编码（含码）→ BCNF ✔

最终结果（3 个 BCNF 关系模式）：

关系模式	码	满足范式
`物料(物料编码, 物料名称, 规格型号, 供应商编码)`	物料编码	BCNF
`供应商(供应商编码, 供应商名称, 供应商联系人)`	供应商编码	BCNF
`库存(物料编码, 仓库编号, 库位编号, 库存数量, 盘点日期)`	(物料编码, 仓库编号, 库位编号)	BCNF

【演算二】”满足 3NF 却不满足 BCNF”的经典陷阱

检验标准分配(产品型号, 检验项, 检验标准编号)，语义：某产品型号在某检验项上采用唯一一个检验标准；每个检验标准只适用于一个检验项。

F = { ①(产品型号, 检验项) → 检验标准编号；②检验标准编号 → 检验项 }

候选码：L 类 = 产品型号；LR 类 = 检验项、检验标准编号。
{产品型号, 检验项}⁺ = 全部 ✔ 是候选码；{产品型号, 检验标准编号}⁺：由②得检验项 → 闭包 = 全部 ✔ 也是候选码。
∴ 两个候选码：(产品型号, 检验项)、(产品型号, 检验标准编号)。
主属性 = 产品型号、检验项、检验标准编号（全部都是主属性，无非主属性）。
判 3NF：非主属性为空，不存在非主属性的部分/传递依赖 → 满足 3NF（这是最容易误判的一步，很多人看到”满足 3NF”就停笔）。
判 BCNF：FD②检验标准编号 → 检验项 的决定因素”检验标准编号”不含任何候选码 → 不满足 BCNF。
分解：R_a(检验标准编号, 检验项)（码 = 检验标准编号）+ R_b(产品型号, 检验标准编号)（码 = 候选码之一）。
- 无损性：R_a ∩ R_b = {检验标准编号}，R_a − R_b = {检验项}，检验标准编号 → 检验项 成立 → 无损 ✔
代价：原 FD①(产品型号, 检验项) → 检验标准编号 在分解后不再能被直接保持——这正是 BCNF 分解可能损失函数依赖保持性的经典案例。工程上的补偿：对 R_b 加唯一约束（同一产品型号对同一标准只能绑定一次）+ 在应用层用”先查标准对应的检验项、再查绑定”的两步校验保证语义。

取舍原则：若要求”无损 + 保持依赖”，3NF 是能保证的上限；若要求”无损 + BCNF”，可能不得不放弃部分依赖的保持性。 案例题若问”分解后是否需要保持函数依赖”，这就是得分点。

3.5 反规范化、索引与物理/分布式设计

定义：反规范化（Denormalization）指在规范化之后，有意地引入受控冗余，用可控的更新异常风险去换取查询性能。

手段	做法	本项目例子
增加冗余列	在多个表中保存同一列，避免连接	在 `追溯明细` 表中冗余 `物料名称`、`供应商名称`、`批次状态`
增加派生列	存储可由其他列计算出的结果	在 `批次` 表增加 `不合格品数`、`合格率`；在 `库存` 表增加 `库存金额`
重组表 / 重新组表	把频繁一起查询的表合并成一张	把 `批次` 与 `批次扩展属性` 合并，减少一次 JOIN
水平分割表	按行拆分（按时间、厂区、状态分区）	`检验记录` 按年份 + 厂区分区；历史表与当期表分离
垂直分割表	按列拆分（高频列与低频列分开）	`设备` 表的常用列（编号/名称/状态）与低频列（采购合同号/保修条款）分表

代价（必背，案例 6–8 分就在这儿）：

更新异常：插入异常、删除异常、修改异常（数据不一致）——供应商改名后追溯报告上仍是旧名，这是本项目最典型的一例。
存储开销增大；3. 维护复杂度上升（写入路径变多，容易漏改一处）；4. 约束变弱（无法完全依赖数据库约束保证一致性）。

补偿手段（与代价一一对应）：

补偿手段	适用	注意
触发器	同源同库的实时同步	隐式、难调试、批量操作放大开销（见 3.6）
应用层同一事务内更新	写路径集中、可控	必须保证所有写入路径都走同一处，否则漏改
批处理校验 / 对账作业	允许分钟级至天级延迟	须有差异告警与自动修复，且要能追溯到具体行
物化视图（Materialized View）	复杂聚合查询的预计算	刷新策略：完全刷新 vs 增量刷新；刷新窗口内数据不一致
定期重算 / 重建	派生列	需记录重算时间点，报告中标注”数据截至时间”

索引（Index）——作用：加速查询 / 加速连接与排序分组 / 保证唯一性；代价：占存储空间、降低增删改速度（索引需同步维护）、过多索引会拖慢优化器。
适合建索引：主键与外键、频繁出现在 WHERE / JOIN / ORDER BY / GROUP BY 中的列、选择度高的列。
不适合：小表、频繁更新的列、低选择度列（如”是否删除”这种布尔列）、极少被查询的列。

分布式数据库（Distributed Database, DDB）与 CAP / BASE（点到为止，S11 深化）：

透明性（由高到低）：分片透明 > 位置透明 > 局部数据模型透明（逻辑透明）。
CAP：一致性（Consistency）、可用性（Availability）、分区容错性（Partition tolerance）——三者最多同时满足两个；分布式系统必须容忍 P，故实际是在 C 与 A 之间权衡（CP 或 AP）。
BASE：基本可用（Basically Available）、软状态（Soft state）、最终一致性（Eventual consistency）——对 CAP 中”放弃强一致”的工程化处理。
本项目落点：3 厂区边缘侧采集允许”本地先存、断网续传”，接受秒级最终一致；跨厂区的追溯查询走中心库，要求强一致。

3.6 SQL 补全、触发器与事务并发控制

SQL 补全题套路（按顺序逐格检查，不要跳）：
SELECT 列/聚合 → FROM 表 → [JOIN 表 ON 关联条件] → WHERE 行过滤 + 表关联 → GROUP BY 分组列 → HAVING 分组后过滤（可用聚合函数） → ORDER BY。

高频空位	要点
`WHERE` vs `HAVING`	`WHERE` 作用于分组前的行，不能用聚合函数；`HAVING` 作用于分组后的组，可以用聚合函数
`ON` vs `WHERE`（外连接时）	外连接的过滤条件写在 `ON` 与写在 `WHERE` 结果不同：写在 `WHERE` 会把因无匹配而补 NULL 的行一并过滤掉，外连接退化成内连接
`EXISTS` vs `IN`	`EXISTS` 对外层逐行代入子查询、命中即可返回、走索引；`IN` 先执行子查询生成结果集再匹配。子查询结果集大 → 用 `EXISTS`；外层表小、内层表大 → 亦倾向 `EXISTS`
NULL 判断	必须写 `IS NULL / IS NOT NULL`，`= NULL` 永远为假
聚合函数与 NULL	聚合函数（`SUM/AVG/COUNT(列)/MAX/MIN`）忽略 NULL；*`COUNT()` 统计所有行，不忽略 NULL**
相关子查询	子查询引用了外层表的列，外层每取一行就执行一次

本项目一道典型补全（记住骨架）：

查询”不合格数大于 0、且至少经过 3 道工序的批次号、产品编码与不合格总数”：
SELECT b.批次号, b.产品编码, SUM(r.不合格数) FROM 批次 b JOIN 报工记录 r ON b.批次号 = r.批次号 WHERE b.状态 = '已完工' GROUP BY b.批次号, b.产品编码 HAVING SUM(r.不合格数) > 0 AND COUNT(DISTINCT r.工序编号) >= 3 ORDER BY SUM(r.不合格数) DESC;

触发器（Trigger）：由事件驱动自动执行的一段过程，四要素 = 触发事件（INSERT / UPDATE / DELETE）× 触发时机（BEFORE / AFTER）× 触发表 × 触发动作，共 3 × 2 = 6 种组合。新旧值：NEW（新值，INSERT/UPDATE 有）、OLD（旧值，DELETE/UPDATE 有）。

优点	缺点
自动执行，不必依赖应用方记得调用	隐式执行：应用不可见，出问题难追踪、难调试
能实现 CHECK/外键等约束无法表达的跨表复杂约束	级联风险：一次更新触发连锁反应，易形成循环触发
便于实现审计日志（谁在何时改了什么）	性能：行级触发器在大批量导入时开销被成倍放大
集中实现业务规则，多应用共享	迁移与运维复杂，逻辑散落在库与应用两处

替代方案（案例高频问：”是否应使用触发器？”）：① 应用层在同一事务内显式更新（最推荐，逻辑可见、可测试）；② 约束（外键 / CHECK / UNIQUE / NOT NULL）能表达的就用约束；③ 存储过程；④ 物化视图；⑤ 定时批处理对账；⑥ 事件驱动 + 消息队列（跨库、跨系统时）。

本项目立场：追溯明细表中的冗余列（物料名称、供应商名称），不用触发器，改用应用层事务 + 每日对账作业 + 差异告警。理由：这两个字段的修改源（物料主数据维护、供应商主数据维护）都在系统内且写路径集中，应用层可控；用触发器会让主数据保存路径的性能与可调试性变差。

事务（Transaction）与 ACID：原子性（Atomicity，要么全做要么全不做）、一致性（Consistency，事务执行前后数据库都处于一致状态）、隔离性（Isolation，并发事务互不干扰）、持久性（Durability，提交后结果永久保存）。

四类并发问题（必须能举出各自的例子）：

问题	现象
丢失修改	T1、T2 同时读同一数据并各自修改，后提交的覆盖掉先提交的，先提交的那次修改丢失
读”脏”数据（脏读）	T1 修改某数据并写回，T2 读到该中间值，随后 T1 回滚，T2 读到的就是不存在的数据
不可重复读	T1 读某数据，T2 修改并提交，T1 再次读同一行得到不同结果
幻读	T1 按某条件统计/查询记录数，T2 插入或删除了符合条件的记录并提交，T1 再次按同一条件查询，记录数变了

封锁（Locking）：排他锁 X（写锁）——加锁者可读可改，其他事务不能再加任何锁；共享锁 S（读锁）——加锁者只能读，其他事务可再加 S 锁、不能加 X 锁。相容性：S-S 相容，S-X 不相容，X-X 不相容。

三级封锁协议（排序题必考，逐级增强）：

协议	规定	解决
一级	事务在修改数据前必须先加 X 锁，直到事务结束才释放	防止丢失修改（不能保证不读脏、不能保证可重复读）
二级	一级 + 事务在读取数据前必须先加 S 锁，读完即可释放 S 锁	防止丢失修改 + 防止读脏数据
三级	一级 + 事务在读取数据前必须先加 S 锁，直到事务结束才释放	防止丢失修改 + 读脏数据 + 不可重复读

记忆法：三级协议的区别只有一件事——S 锁什么时候放。不加 → 会丢修改；读完就放 → 不读脏；事务结束才放 → 可重复读。

隔离级别（由低到高）：读未提交（Read Uncommitted）→ 读已提交（Read Committed）→ 可重复读（Repeatable Read）→ 可串行化（Serializable）。

级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读已提交	不会	可能	可能
可重复读	不会	不会	可能
可串行化	不会	不会	不会

四、典型考法

4.1 综合知识怎么考

题 1：数据库系统中，通过修改”模式/内模式映像”可以实现（　　）。　A. 数据的物理独立性　B. 数据的逻辑独立性　C. 数据的安全性　D. 数据的完整性
答案：A。解析：模式/内模式映像保证物理独立性（存储结构变了，模式可不变，程序不改）；外模式/模式映像保证逻辑独立性。

题 2：E-R 图中，一个 1:n 联系转换为关系模式时，通常采用的转换方法是（　　）。　A. 独立建一个关系模式，码取 1 端实体的码　B. 与 n 端实体的关系模式合并，加入 1 端实体的码与联系属性　C. 与 1 端实体的关系模式合并　D. 必须独立建一个关系模式，码取两端实体码的组合
答案：B。解析：1:n 联系与 n 端合并（在 n 端加入 1 端的码），可减少一个关系；若独立建表，其码取 n 端的码（不是 1 端）；m:n 才必须独立建表且码为两端码的组合。

题 3：关于自然连接与等值连接，下列说法正确的是（　　）。　A. 二者语义完全相同　B. 自然连接要求比较的分量是同名属性组，且结果中去掉重复列　C. 等值连接会去掉重复列　D. 自然连接属于外连接的一种
答案：B。解析：等值连接只要求”相等”，保留两侧全部列（比较列重复出现）；自然连接要求同名属性组，且自动去掉重复列。外连接是另一类（保留悬浮元组，缺值填 NULL）。

题 4：关系模式 R(A, B, C, D)，F = {AB → CD, C → D}，则 R 满足的范式是（　　）。　A. 1NF　B. 2NF　C. 3NF　D. BCNF
答案：B。解析：L 类 = A、B，R 类 = D，LR 类 = C。{A,B}⁺ = {A,B,C,D} → 候选码 (A,B)，主属性 A、B，非主属性 C、D。非主属性对码完全依赖（无真子集能决定它们）→ 满足 2NF；但 AB → C → D 构成传递依赖 → 不满足 3NF。

题 5：事务 T1 在读取数据 R 之前必须先加 S 锁，直到事务结束才释放。该规定属于（　　），能防止（　　）。　A. 一级封锁协议 / 丢失修改　B. 二级封锁协议 / 读脏数据　C. 三级封锁协议 / 不可重复读　D. 三级封锁协议 / 丢失修改
答案：C。解析：S 锁保持到事务结束是三级封锁协议的特征（二级是读完即放），三级协议可同时防止丢失修改、读脏数据、不可重复读；”防止不可重复读”是三级相对二级新增的能力。

4.2 案例分析怎么考

【案例题】（共 25 分）

承接本项目。系统分析师正在设计”原材料仓储与质量追溯”部分的数据模型。目前已经得到一个关系模式：
库存(物料编码, 物料名称, 规格型号, 供应商编码, 供应商名称, 供应商联系人, 仓库编号, 库位编号, 库存数量, 盘点日期)
经分析，该函数依赖集为：
① 物料编码 → 物料名称, 规格型号, 供应商编码
② 供应商编码 → 供应商名称, 供应商联系人
③ (物料编码, 仓库编号, 库位编号) → 库存数量, 盘点日期

【问题 1】（10 分） 求出 库存 关系模式的候选码，指出主属性与非主属性，判定该关系模式满足第几范式，并详细说明理由。
【问题 2】（8 分） 将该关系模式分解为满足 BCNF 的关系模式集合，写出分解过程，并说明分解是否具有无损连接性。
【问题 3】（7 分） 系统上线后，”批次追溯查询”需要关联 11 张表、响应时间 4.7 秒，不满足 3 秒指标。DBA 建议将 物料名称、供应商名称 冗余写入 追溯明细 表。请说明：该手段属于什么技术？会带来什么问题？有哪些补偿措施？你作为系统分析师的立场是什么？

采分点拆解：

问题 1（10 分）：候选码 2 分；主属性/非主属性划分 2 分；范式结论 2 分；理由 4 分——必须明确指出”物料编码是候选码的真子集，非主属性对其存在部分函数依赖”，只写结论不写理由最多得 4 分。
问题 2（8 分）：第一步消除部分依赖 3 分；第二步消除传递依赖 3 分；无损连接性判定 2 分（写出判定定理并代入）。未说明无损性的扣 2 分。
问题 3（7 分）：技术名称（反规范化 / 增加冗余列）1 分；代价 3 分（更新异常/数据不一致 + 存储开销 + 维护复杂度）；补偿措施 2 分（≥2 条）；立场与理由 1 分。

标准作答范例

【问题 1】

候选码：(物料编码, 仓库编号, 库位编号)。求解过程：物料编码、仓库编号、库位编号 只出现在函数依赖左部（L 类），必包含在任何候选码中；三者的闭包 = {物料编码, 仓库编号, 库位编号, 物料名称, 规格型号, 供应商编码, 供应商名称, 供应商联系人, 库存数量, 盘点日期} = 全部属性，且三者任一真子集均不能覆盖全部属性，故为唯一候选码。
主属性：物料编码、仓库编号、库位编号。非主属性：物料名称、规格型号、供应商编码、供应商名称、供应商联系人、库存数量、盘点日期。
范式结论：该关系模式只满足第一范式（1NF）。
理由：① 满足 1NF——所有分量均为不可再分的原子值，无”表中表”与重复组。② 不满足 2NF——存在函数依赖 物料编码 → 物料名称, 规格型号, 供应商编码，而 物料编码 是候选码 (物料编码, 仓库编号, 库位编号) 的真子集，即非主属性对码存在部分函数依赖（物料名称只由物料编码决定，与该物料存放在哪个仓库库位无关），违反了 2NF 的定义。③ 既然不满足 2NF，自然不满足 3NF 与 BCNF。

【问题 2】

第一步：消除非主属性对码的部分函数依赖，使其达到 2NF。 将①的左部及其决定的属性抽出：

R1(物料编码, 物料名称, 规格型号, 供应商编码)，码 = 物料编码
R2(物料编码, 仓库编号, 库位编号, 库存数量, 盘点日期)，码 = (物料编码, 仓库编号, 库位编号)

第二步：判 R2——非主属性（库存数量、盘点日期）完全依赖于码，且不存在传递依赖，唯一决定因素即为码 → R2 ∈ BCNF。

第三步：判 R1 并消除传递依赖。 R1 中 物料编码 → 供应商编码（由①）、供应商编码 → 供应商名称, 供应商联系人（由②），故存在传递函数依赖 物料编码 → 供应商编码 → 供应商名称，R1 仅满足 2NF。继续分解：

R11(物料编码, 物料名称, 规格型号, 供应商编码)，码 = 物料编码，决定因素只有码 → BCNF
R12(供应商编码, 供应商名称, 供应商联系人)，码 = 供应商编码，决定因素只有码 → BCNF

最终结果：物料(物料编码, 物料名称, 规格型号, 供应商编码)、供应商(供应商编码, 供应商名称, 供应商联系人)、库存(物料编码, 仓库编号, 库位编号, 库存数量, 盘点日期) —— 均为 BCNF。

无损连接性判定：根据二元分解的无损判定定理，若 R1 ∩ R2 → R1 − R2 或 R1 ∩ R2 → R2 − R1 成立，则分解无损。

第一次分解：R1 ∩ R2 = {物料编码}，R1 − R2 = {物料名称, 规格型号, 供应商编码}，而 物料编码 → 物料名称, 规格型号, 供应商编码 在 F 中成立 → 无损。
第二次分解：R11 ∩ R12 = {供应商编码}，R12 − R11 = {供应商名称, 供应商联系人}，而 供应商编码 → 供应商名称, 供应商联系人 成立 → 无损。
两次分解均无损，且保持了原有的函数依赖，故最终分解是无损且保持依赖的 BCNF 分解。

【问题 3】

① 技术名称：属于反规范化（Denormalization）中的增加冗余列手段。其动机是以可控的冗余换取查询性能，把原本需要 JOIN 的 11 张表压缩为 3 张。

② 带来的问题：

数据不一致 / 修改异常：这是最主要的风险。物料名称、供应商名称 在主数据中被修改后，若 追溯明细 中的冗余列未同步，追溯报告将显示旧名称——而追溯报告是要出具给客户与监管方的凭证，名称不一致会被质疑报告的可信度，属于业务上不可接受的后果。
更新异常的其他形态：插入异常、删除异常。
存储开销增大，追溯明细是本系统数据量最大的表（按年千万行级），冗余列的存储成本不可忽略。
维护复杂度上升：所有写入 追溯明细 的路径都必须维护这两个字段，漏一处即不一致。
约束能力下降：无法再单靠数据库约束保证一致性，人为因素成为一致性的薄弱环节。

③ 补偿措施：

应用层在同一事务中显式更新：主数据变更时，在同一数据库事务内同步更新冗余列，保证原子性。
批处理校验 / 对账作业：每日低峰期执行差异比对，把主数据与冗余列不一致的行查出，自动生成差异清单并告警，支持自动修复。
物化视图：对追溯链条做预计算并定期刷新，刷新窗口需避开业务高峰，并在报告中标注”数据截至时间”。
保留可回退能力：冗余列设计为可重算——任何时候发现不一致，都能从权威源（物料主数据、供应商主数据）重建，这是引入冗余的前提条件。

④ 我的立场：有条件地接受，但不使用触发器实现同步。
理由是：这两个字段的修改源唯一且写路径集中（物料主数据维护、供应商主数据维护两个功能入口），应用层完全可控，因此用应用层事务 + 每日对账 + 差异告警 + 可重算的组合足以把不一致窗口控制在 24 小时内；而触发器是隐式执行的，会让主数据保存路径的性能与可调试性下降，且追溯明细表数据量巨大，触发器的行级开销在大批量导入时会被成倍放大。
同时设立触发重新评估的量化信号：若对账作业连续 3 天出现差异，或冗余列带来的性能收益低于 30%，则回退为不冗余方案，改用物化视图 + 读写分离来达成指标。反规范化的前提永远是”冗余可被重算、代价可观测、异常可回滚”。

五、易错点（Rethink）

错误认知	为什么错	正确理解
“物理独立性靠外模式/模式映像”	映像记反	模式/内模式映像 → 物理独立性；外模式/模式映像 → 逻辑独立性。记忆：映像的名字里带”内”的管物理
“1:n 联系与 1 端合并”	方向记反	1:n 与 n 端合并，在 n 端加入 1 端的码；独立建表时其码也取 n 端的码
“m:n 联系可以合并到某一端”	会产生多值属性、违反 1NF	m:n 必须独立建一个关系模式，码为两端实体码的组合
“自然连接与等值连接只是写法不同”	忽略了去重	等值连接保留两侧全部列（比较列重复）；自然连接要求同名属性组且去掉重复列
“全是主属性的关系模式一定满足 BCNF”	忽略了主属性之间的依赖	无非主属性时自动满足 3NF，但仍可能因”决定因素不含码”而不满足 BCNF（见演算二）
“分解到 3NF 就够了，不必看 BCNF”	考题常要求判到 BCNF	判完 3NF 后必须再看每一个决定因素是否都含候选码；且要说明 BCNF 分解可能不保持函数依赖
“反规范化就是设计没做好”	混淆”无意冗余”与”受控冗余”	反规范化是主动、有代价评估、有补偿措施的性能手段；错在只加冗余不管理代价
“用触发器同步冗余最省事”	触发器隐式执行	触发器隐式、难调试、级联风险、批量操作开销放大；写路径集中时优先应用层事务 + 对账
“二级封锁协议就能防止不可重复读”	混淆 S 锁释放时机	二级 = 读完即放 S 锁（防丢修改 + 防脏读）；三级 = S 锁保持到事务结束（再加防不可重复读）
“不可重复读和幻读是一回事”	对象不同	不可重复读针对某一行的值被改；幻读针对按条件查询的记录数变化（被插入/删除）
“WHERE 里可以用 COUNT/SUM 过滤”	执行顺序不同	WHERE 在分组前对行过滤，不能用聚合函数；分组后的过滤必须用 HAVING
“投影 π 不会改变行数”	忽略去重	投影是垂直取列，且会去掉结果中的重复行

六、GRASPS 推进（Experience）

本周五前，往交付物「数据模型」里加三块内容，总计 1200–1500 字 + 两张图 + 一张表：

E-R 图一张：不少于 8 个实体（批次、工单、设备、物料、供应商、检验记录、报工记录、仓库库位），至少包含 1 个多值属性的正确处理（如 设备的通信协议 单独建表）与 1 个派生属性的显式标注（如 不合格数 标注为派生，不落库）；至少 1 组 m:n 联系（物料—供应商）1 组 1:n 联系（工单—批次），图下逐条写出对应的转换结果。
关系模式集一张表：由 E-R 图导出 不少于 12 个关系模式，每行列明关系名、属性、主键、外键、满足第几范式。其中至少 3 个关系模式要写出完整的规范化演算过程（求候选码 → 划主属性 → 判范式 → 分解 → 判无损），字数不少于 500 字。
反规范化清单：列出你在本项目中主动引入的冗余（不少于 3 处），逐处写清：冗余了什么字段、为什么（性能目标与当前实测数据）、代价是什么、补偿手段是什么、不一致窗口多久、什么条件下回退。

验收标准：任挑一个关系模式，你能在 90 秒内说出”候选码是什么、满足第几范式、为什么”；每一处冗余你都能说出”如果它不一致了，会有什么业务后果、谁会发现、多久能修”；把反规范化清单给质量部王工看，他能在上面指出至少一处”这个字段我们其实每月都会改”——能被业务方纠正的清单，才说明你把代价问对了地方。

七、自测

数据库三级模式中，描述”全体数据整体逻辑结构、是所有用户公共数据视图”的是（　　）。　A. 外模式　B. 模式　C. 内模式　D. 存储模式
数据库中，保证数据逻辑独立性的是（　　）。　A. 外模式/模式映像　B. 模式/内模式映像　C. 索引　D. 事务日志
E-R 模型中，一个实体的某属性取值可能有多个（如多个联系电话），该属性称为（　　）。　A. 复合属性　B. 多值属性　C. 派生属性　D. 简单属性
E-R 图中一个 m:n 联系转换为关系模式时，该关系模式的码是（　　）。　A. 任一端实体的码　B. 两端实体码的组合　C. n 端实体的码　D. 新建的代理主键即可，与原实体码无关
关系 R 与 S 做自然连接，与做等值连接相比，主要区别是（　　）。　A. 自然连接速度更快　B. 自然连接要求比较分量是同名属性组且结果去掉重复列　C. 等值连接会去掉重复列　D. 二者完全相同
关系模式 R 满足 2NF 的充分必要条件是（　　）。　A. 每个属性都不可再分　B. 每个非主属性都完全函数依赖于候选码　C. 每个非主属性都不传递依赖于候选码　D. 每个决定因素都包含候选码
关系模式 R(A,B,C)，F = {A→B, B→C}，其候选码为 A，则 R 最高满足（　　）。　A. 1NF　B. 2NF　C. 3NF　D. BCNF
下列不属于反规范化手段的是（　　）。　A. 增加冗余列　B. 增加派生列　C. 水平分割表　D. 消除传递函数依赖
三级封锁协议中，规定”读数据前加 S 锁、读完即可释放”的是（　　），它能防止（　　）。　A. 一级 / 丢失修改　B. 二级 / 读脏数据　C. 三级 / 不可重复读　D. 二级 / 丢失修改
下列隔离级别中，隔离程度最高的是（　　）。　A. 读未提交　B. 读已提交　C. 可重复读　D. 可串行化

题号	1	2	3	4	5
答案	B	A	B	B	B
一句话解析	模式是全体数据的整体逻辑结构，一个库只有一个；外模式是局部视图，内模式是存储结构	外模式/模式映像 → 逻辑独立性；模式/内模式映像 → 物理独立性	多值属性必须单独建关系模式；复合属性可再分；派生属性可由其他属性算出，不落库	m:n 必须独立建表，码 = 两端实体码的组合；1:n 才与 n 端合并	自然连接 = 同名属性组 + 自动去重；等值连接保留全部列，比较列会重复出现

题号	6	7	8	9	10
答案	B	B	D	B	D
一句话解析	2NF = 消除非主属性对码的部分依赖；A 是 1NF，C 是 3NF，D 是 BCNF	候选码 A，非主属性 B、C；`A→B→C` 为传递依赖 → 满足 2NF，不满足 3NF	D 是规范化（消除传递依赖）；冗余列、派生列、水平/垂直分割、重组表才是反规范化	二级 = 读完即放 S 锁，防丢失修改 + 防读脏；三级 = 事务结束才放，再加防不可重复读	由低到高：读未提交 < 读已提交 < 可重复读 < 可串行化（全部并发问题均可避免）

八、分档任务（Tailor）

保底 45：背熟 3.1 两级映像与两种独立性、3.3 六条转换规则（尤其 1:n 与 n 端合并、m:n 独立建表）、3.4 四范式定义与判定四步骤、3.6 三级封锁协议表；完成 4.1 五题与自测十题；案例 4.2 的【问题 1】能写出候选码与范式结论。
冲 60：完成 GRASPS 三件产出；案例 4.2 三问全部手写完整作答并对照采分点自评；把演算一、演算二在不看答案的情况下各重做一遍，要求 15 分钟内完成一题；额外练 3 道 SQL 补全题，重点检查 GROUP BY 与 HAVING。
冲 70：把本项目换成医疗场景（患者 / 就诊记录 / 检验项 / 医生 / 科室）重做一套 E-R 与规范化演算，要求出现一处”3NF 但非 BCNF”的模式并说明分解后是否保持函数依赖；针对”追溯查询 4.7 秒”写一份 400 字的性能优化方案，必须同时给出”反规范化”与”不反规范化”两条路径及各自代价；自命题一道”范式判定 + 反规范化代价”的案例题并写出采分点拆解。

九、本阶段回答基本问题

Q3（第一次正式回答）：什么时候该严格遵守规范，什么时候该为性能主动打破规范？谁来承担代价？

第一，规范化的对象不是”冗余”，是”更新异常”。 1NF→2NF→3NF→BCNF 追问的从来不是”有没有重复数据”，而是”哪个键决定哪个事实“。库存表里 物料名称 重复一万次本身不是问题，问题是它只由 物料编码 决定、却被塞进了以 (物料编码, 仓库编号, 库位编号) 为码的表里——于是改一次物料名要改一万行，漏一处就自相矛盾。规范化消除的是”一个事实被存放在了不该存放它的地方”。

第二，反规范化不是规范的失败，而是规范的延伸——前提是你必须能回答三个问题。 我给本项目立的门槛是：冗余是否可重算、代价是否可观测、异常是否可回滚。可重算意味着任何时候都能从权威源重建，冗余只是缓存不是真相；可观测意味着对账作业能报出不一致的具体行数；可回滚意味着性能收益低于预期时，能干净地退回去。答不出这三个问题的冗余，不叫反规范化，叫埋雷。

第三，代价必须落到人身上，不能落在”系统”身上。 “数据不一致”是技术术语，”追溯报告上印着三年前就该改掉的供应商旧名、客户据此质疑报告可信度”才是业务代价。谁发现、多久能修、修之前业务怎么兜底——这三件事写不清楚，就不该引入冗余。所以我选了应用层事务 + 每日对账 + 差异告警而不是触发器：触发器的代价是隐性的（性能、可调试性、级联风险），而应用层同步的代价是显性的、可测试的、可问责的。

第四，什么时候守、什么时候破，取决于”写多读少”还是”读多写少”，更取决于”谁会为不一致买单”。 追溯明细表是典型读多写少（写入一次、查询成千上万次），且冗余字段的修改源唯一、写路径集中——这是可以破的。反过来，检验记录表是写多、且每条记录都有法定存档效力，一次都不能破。规范化的边界不在技术，在业务的容错成本。

第五，”满足 3NF”不等于”设计对了”，”满足 BCNF”也不等于”该分解”。 演算二里那个”三个属性全是主属性、满足 3NF 却不满足 BCNF”的模式提醒我们：范式是必要条件不是充分条件，分解还要同时检查无损连接与依赖保持——BCNF 分解甚至可能损失函数依赖的保持性。会判定是及格线，知道判定之后还要权衡才是水平线。

下一周 S06 会提出一个更根本的问题：数据模型告诉你”世界由什么构成”，但**怎么证明这套架构是”好的”**——这是 Q2。而在 S11，当数据被拆分到 3 个厂区、甚至跨云时，CAP 会让”一致性”这个词本身变得需要重新定义，Q3 会被第二次追问。