Sherfiter's wiki for fun

第 5 章 · 关系数据库设计理论

先看一张「什么都往里塞」的表,这是我大二数据库课设的真实作品,期末验收时被老师当场点名:

选课(学号, 课程号, 姓名, 系, 系主任, 成绩)
S001  C1   张三  CS   王大力  88
S001  C2   张三  CS   王大力  92
S002  C1   李四  CS   王大力  75

这张表能跑,但藏着三种病,每种都在某天真的咬了我一口:

  • 修改异常:计算机系换了系主任,要改几千行(每个 CS 学生每条选课都带着系主任),漏改一行,同一个系两个主任,数据自相矛盾。
  • 插入异常:新系刚成立还没学生,「系 + 系主任」插不进来(主码里的学号不许为空)。
  • 删除异常:某系最后一个学生退学,他选课行一删,这个系的存在证据也跟着消失。

根子是表里混进了好几个主题(选课、学生、系),一份数据(系主任)被复印了 N 份存在 N 行里。本章给出诊断工具(函数依赖)和处方(范式):按依赖把表拆到每个主题一份,冗余归零、异常消失。同时也要学会另一面:不是拆得越细越好,拆过头查询全是连接,工程上要会在两端之间选位置。

5.1 函数依赖:数据内部的因果

函数依赖(FD)写作 X→YX \to Y:X 的值一旦确定,Y 的值唯一确定。「学号 → 姓名」(一个学号对应一个人名)、「(学号,课程号) → 成绩」(一对选课关系只有一个分数)、「系 → 系主任」。它必须是业务规则上恒成立的决定关系,不是「这个数据集里碰巧没重复」:如果恰巧今年没重名学生就认为「姓名 → 学号」,明年一个重名就翻车。

依赖分三种脾气,后面范式全靠它们分类:

  • 完全依赖:YY 要靠 X 的全部属性才能决定。成绩要「学号 + 课程号」一起给,缺一不可。
  • 部分依赖:Y 只靠 X 的一部分就够了。上面那张表里,「姓名」只要学号就能定,课程号是陪跑的。部分依赖是冗余的直接来源。
  • 传递依赖:X 决定 Y,Y 决定 Z,则 X 传递地决定 Z。学号 → 系,系 → 系主任,于是学号 → 系主任;系主任实际依赖的是「系」这个中间主题,却被迫挂在学号身上。

5.2 闭包:判断码的机械办法

「哪些属性组是候选码」可以不靠灵光一现,用闭包算法机械地算。X+X^+ 表示「从 X 出发,用已知 FD 能推出的一切属性」。算法就是不断扩张:

X⁺ = X
反复:找到某条 Y→Z,其中 Y ⊆ X⁺,把 Z 并入 X⁺
直到 X⁺ 不再变大

拿上面那表练一遍,已知 FD:学号→姓名、学号→系、系→系主任、(学号,课程号)→成绩:

(学号,课程号)⁺
  = {学号, 课程号}
  ∪ {姓名} (学号→姓名)     → {学号,课程号,姓名}
  ∪ {系}   (学号→系)       → +系
  ∪ {系主任}(系→系主任)     → +系主任
  ∪ {成绩} ((学号,课程号)→成绩) → 全部属性

闭包覆盖全表所有属性,所以 (学号,课程号) 是码;再检查最小性:去掉课程号,学号⁺ 推不出成绩,不行;去掉学号更不行。结论:唯一候选码是 (学号,课程号)。闭包还能验证「某依赖是否被蕴含」:问「学号 → 系主任 成立吗」,算学号⁺ 看是否包含系主任即可。考试两大题型(求码、验证依赖)全用这一个算法。

5.3 范式:逐级拆表

范式是「拆到什么程度」的判据,一档比一档严格:

1NF:属性原子,一个格子一个值。「爱好」列里塞「篮球,游泳」就是典型违规,该拆成单独的爱好表(每行一个爱好)。

2NF:消除非主属性对码的部分依赖。把开头的表按闭包结论诊断:姓名、系、系主任只依赖学号(部分依赖),留在「学号+课程号」表里就是冗余。拆:

学生(学号, 姓名, 系, 系主任)     ← 系主任仍在传递依赖里
选课(学号, 课程号, 成绩)        ← 完全依赖,干净

3NF:再消除传递依赖。学生表里系主任经由「系」传递而来,再拆一刀:

学生(学号, 姓名, 系)
系(系, 系主任)

现在系主任全库只存一份,改主任改一行,三种异常全部消失。绝大多数业务表设计到 3NF 就是合理落点,这也是工程默认。

BCNF:更严一档,要求每个决定因素都是候选码(不只非主属性)。教科书经典反例:「学生选课由老师教」 ‘(学生,课程)‘→‘老师‘\text`{(学生,课程)}` \to \text`{老师}` 且 ‘老师‘→‘课程‘\text`{老师}` \to \text`{课程}`,拆到 3NF 仍有主属性冗余,BCNF 才干净。BCNF 偶尔与查询友好性冲突(拆掉某些连接),所以工程上「3NF 是默认,BCNF 是洁癖」,面试聊到这就够体现深度了。

5.4 拆是手段,不是信仰

范式拆表的无损性有数学保证(拆开的表用自然连接能精确还原),但这不等于「拆就完事」。每次拆细,查询就多一次连接:查「张三的系主任」从原来的一张表变成三表连接。读写比例决定了你在天平上的位置:

  • 写多读少、数据一致性要命(订单、账户、库存):坚定 3NF,让冗余为零,修改永远不会自相矛盾。
  • 读密集、求快(报表、商品页、排行榜):故意反规范化,把「商品名」冗余进订单行,查询少一次连接。代价回归开头:同一份数据两处存,要靠应用层同步、触发器、或定时对账防漂移。

数据仓库甚至把反规范化推到极致(大宽表、星型模型),和分析型查询的访问模式配套。所以这门「设计理论」的正确打开方式:范式是体检指标,告诉你现在有多少冗余和异常风险;拆不拆、拆多少,最后是读写账本说了算。

常见错误

  • 把「列的值恰好没重复」当成函数依赖。FD 是业务恒成立规则,建议连同业务方一起确认,别从抽样数据里猜。
  • 一上来就 BCNF,把查询拖进连接地狱。OLTP 表 3NF 起步,发现真实的连接瓶颈再做局部反规范化,顺序别反。
  • 以为范式检查只看主码。2NF、3NF 的对象是非主属性,BCNF 才管主属性;四档各管各的,考试爱混着出。

练习

  1. 手算:给定 FD 集 R(A,B,C,D),F = {A→B, B→C, A→D}。求 A⁺;判断 A 是不是候选码;B→D 是否被 F 蕴含?
  2. 手算:表 销售(日期, 门店, 商品, 门店地址, 售价),FD:门店→门店地址、(日期,门店,商品)→售价。指出所有部分依赖和传递依赖,拆到 3NF。
  3. 手算:上题结果里,「门店地址」是不是全库只存一份了?若每天有 10 万条销售行、100 家门店,反规范化前后的存储量差多少倍?
  4. 思考:微博这样的系统,「关注关系」表和「用户主页」查询,你会把「粉丝数」存进用户表(反规范化)还是实时 COUNT 关注表?从写入频率、一致性容忍度、读 QPS 三个角度推演。
豫ICP备2026027944号