Sherfiter's wiki for fun

第 2 章 · 关系模型与关系代数

关系模型敢叫「关系」,是因为它真的建立在数学的集合论上,本章开篇就把这层底牌亮出来。设两个域:学号域 D1 = {S001, S002},课程域 D2 = {C1, C2}。笛卡尔积 D1 × D2 是所有配对:

(S001,C1)  (S001,C2)  (S002,C1)  (S002,C2)     ← 2×2 = 4 种组合

而一张真实的选课表:

┌──────┬──────┐
│ 学号 │ 课程 │
├──────┼──────┤
│ S001 │ C1   │
│ S002 │ C2   │
└──────┴──────┘

只是上述 4 种组合的一个子集(选了 2 种)。所谓关系,就是笛卡尔积的一个子集;所谓表,不过是这个集合的展示形式。这不是文字游戏:子集意味着「不重复、无序」,集合的运算(并、交、差、笛卡尔积)全部适用,于是查询语言可以建立在严格的代数上,优化器才有定理可依。本章把这套语言学到能读会算,SQL 的每种语法在它那里都有原型。

2.1 名词对齐:关系、元组、属性、码

先统一词汇,教材和工程黑话的对应关系值得专门列一遍:

代数术语日常说法例子
关系 Relation表Student
元组 Tuple行(S001, 张三, CS)
属性 Attribute列学号
域 Domain这一列的合法取值集合所有合法学号
关系模式表结构Student(学号, 姓名, 系)

三条集合论带来的性质,SQL 里各有对应物:元组不重复(SQL 靠主键约束逼近)、行无序(SQL 没有「第 5 行」这个概念,想有序必须 ORDER BY)、列无序(靠名字引用,不靠位置)。

码(key) 是本章的重头戏。候选码:能唯一标识元组且无冗余的属性组,「无冗余」三个字最容易被坑:{学号} 是候选码,{学号, 姓名} 也能唯一标识,但扔掉姓名还成立,所以它有冗余,不算。主码:从候选码里挑一个当正式身份,不许为空(没有身份的人没法被引用)。外码:本表的某属性组,引用另一张表的码,值必须来自对方(或者为空)。

Student(学号[主码], 姓名, 系编号)
Dept(系编号[主码], 系名)
SC(学号[外码→Student], 课程号[外码→Course], 成绩)

注意 SC 这张表:它自己没有天然的「身份列」,两个外码组合起来 (学号, 课程号) 才是主码,这种由多个外码拼成的码,是联系表的常态。

2.2 三类完整性:数据库的守门员

约束(constraint)是声明给 DBMS 的规矩,由它强制执行,谁也绕不过(包括你的 bug):

  • 实体完整性:主码非空且唯一。没有身份的行,一切引用和查找都会失效。
  • 参照完整性:外码要么为空,要么必须能在被引用表里找到。选课表里出现一个不存在的课程号,数据就「悬空」了。
  • 用户定义完整性:业务语义,如成绩在 0 到 100、性别取值有限。

用一个真实的 SQLite 会话看约束怎么拦截脏数据:

sqlite> CREATE TABLE sc (学号 TEXT, 课程号 TEXT, 成绩 REAL,
   ...>   PRIMARY KEY (学号, 课程号),
   ...>   FOREIGN KEY (课程号) REFERENCES course(课程号));
sqlite> INSERT INTO sc VALUES ('S001', 'C9', 88);
-- 外码约束开启时报错: FOREIGN KEY constraint failed

工程忠告:约束写在数据库里,永远比写在应用代码里可靠。应用有十个入口(网页、脚本、内部工具),代码校验要写十遍还难免漏,数据库约束只写一遍、对所有人生效。见过不少团队图省事关掉外键(为了导入方便),后来用人工核对数据付出了十倍代价。

2.3 关系代数:SQL 的骨架

关系代数是一组「输入关系、输出关系」的运算符。用两张有数据的表当例子:

Student                      SC
学号   姓名  系              学号   课程   成绩
S001  张三  CS              S001   C1     88
S002  李四  EE              S001   C2     92
                            S002   C1     75

五个常用运算符,每个配一句人话和一个例子:

选择 σ(挑行):按条件留下部分元组。σ‘系=′CS′‘(‘Student‘)\sigma_`{系='CS'}`(\text`{Student}`) 得到张三那一行。对应 SQL 的 WHERE。

投影 π(挑列):留下部分属性,重复元组顺手去掉(集合语义)。π‘姓名‘(‘Student‘)\pi_`{姓名}`(\text`{Student}`) = {张三, 李四}。对应 SELECT 姓名,而 SQL 默认不去重,是多重集语义,要 DISTINCT 才回到集合,这是代数和 SQL 的第一处分叉。

连接 ⋈(拼表):最常用也最贵。Student⋈SCStudent \bowtie SC 按同名属性(学号)相等拼成宽表:

S001 张三 CS S001 C1 88
S001 张三 CS S001 C2 92
S002 李四 EE S002 C1 75

定义上等于「笛卡尔积再按条件选择」,但实现上优化器绝不会真做笛卡尔积(第 9 章讲 hash join、nested loop 就是在这一步做文章)。

并 ∪ / 交 ∩ / 差 −:集合运算,要求两个关系结构相容(同列同域)。SELECT ... UNION 的原型。

除 ÷:最抽象也最有趣的一个,专门回答「对全部……都……」类问题。SC÷π‘课程‘(Course)SC \div \pi_`{课程}`(Course) 找「选了所有课程的学生」。直觉:从 SC 里找那些「与任何课程配对都存在」的学号。SQL 里没有 ÷ 关键字,要用「双重否定」改写(NOT EXISTS (某课程他没选)),这句写法是 SQL 面试的经典题,根源就在这个除法。

2.4 从代数到 SQL:一条换算表

关系代数SQL
σ条件(R)SELECT ... WHERE 条件
π属性(R)SELECT 列 (DISTINCT 才是纯投影)
R ⋈ SFROM R JOIN S ON ...
R ÷ SNOT EXISTS 嵌套(双重否定)
R ∪ S / R − SUNION / EXCEPT

SQL 的执行语义基本就是「代数式的从里到外求值」,所以读复杂 SQL 的正确姿势是把它翻译回代数树:先谁和谁连接、再过滤什么、最后投影什么。第 9 章的查询优化,就是把同一个代数树变换成代价更低的等价树(谓词下推、连接重排),那时你会感谢今天学的这套记号。

常见错误

  • 把 SQL 表当集合用。SQL 表默认允许重复行(多重集),UNION ALL 保留重复,UNION 去重;想要代数语义,记得 DISTINCT。
  • 候选码判断不查「冗余」。{学号, 系编号} 若学号已唯一,它就不是候选码;判断标准是「去掉任何一个属性就不再唯一」。
  • 外码理解成「随便存个值」。外码是约束不是注释,FOREIGN KEY 加上之后,悬空引用在插入时就会被拦截,这正是它存在的意义。

练习

  1. 手算:对 2.3 的两张表,写出并计算 π‘学号‘(σ‘成绩>80‘(SC))\pi_`{学号}`(\sigma_`{成绩>80}`(SC)),说明结果(选了课且至少一门超过 80 的学号,注意去重)。
  2. 手算:用除法回答「选修了 C1 和 C2 两门课的学生」,写出代数式(提示:被除数是 SC,除数是 {C1, C2} 构成的关系)并验证。
  3. 动手:在 SQLite 里建 Student、Course、SC 三张表(带主外码),故意插入一条外码悬空的 SC 行,看报错;再用 PRAGMA foreign_keys=ON 打开约束重试。
  4. 思考:投影会去重,但 SQL 的 SELECT 默认不去重。你觉得哪个语义更「对」?想想「查公司有哪些部门」的期望结果,再想想「查每个部门的员工」为什么必须保留重复。
豫ICP备2026027944号