数据库系统基础

这是一份按原课程顺序整理的数据库学习长笔记。从基本概念和关系模型开始，依次梳理 SQL、约束、范式、设计、查询优化、恢复与并发控制。可以从头阅读，也可以通过章节目录直接定位。

第一章 · 绪论

1.1 数据库基本概念

四个核心术语（由小到大）

🔑 重点： 四者的关系：数据库保存数据，DBMS 管理数据库，数据库系统还包括应用、人员和运行环境；它们不是简单的集合包含关系

DBMS的主要功能

• 数据定义（Data Definition）：定义数据库结构
• 数据操纵（Data Manipulation）：增删改查
• 数据库运行管理：完整性检查、安全性控制、并发控制、故障恢复
• 数据库的建立和维护：备份、恢复、性能监控

1.2 数据管理技术的发展

三个阶段

人工管理阶段 → 文件系统阶段 → 数据库系统阶段
(无共享)        (共享差)        (高共享、低冗余)

🔑 重点： 数据库系统相比文件系统的核心飞跃是 数据结构化——数据不再面向单一应用，而是面向整个系统。

数据库系统的四大特点

1. 数据结构化 — 面向全组织，不只是单一应用
2. 数据共享性高，冗余度低 — 减少重复存储
3. 数据独立性高 — 物理独立性 + 逻辑独立性
4. 由DBMS统一管理和控制 — 安全性、完整性、并发、恢复

💡 注意： 物理独立性 = 存储结构变了，程序不用改；逻辑独立性 = 逻辑结构变了，程序不用改。这是通过三级模式+两级映像实现的。

1.3 数据模型

数据模型的三要素

两大类数据模型

1. 概念模型（信息模型）

• 按用户观点建模 → 用于数据库设计阶段
• 代表：E-R模型（实体-联系模型）
• 基本元素：
  • 实体：客观存在且可区分的事物（如学生张三）
  • 属性：实体的特征（如学号、姓名、年龄）
  • 实体型：实体名(属性1, 属性2, …)，如 学生(学号, 姓名, 年龄)
  • 实体集：同类实体的集合
  • 键：能唯一标识一个实体的属性
  • 联系：实体之间的关系（1:1 / 1:N / M:N）

2. 逻辑模型 + 物理模型

• 按计算机观点建模 → 用于DBMS实现
• 三大经典逻辑模型：

1.4 数据库系统的三级模式结构

三级模式

       外模式A    外模式B    外模式C     ← 用户视图（可以有多个）
            \       |       /
             模式（逻辑模式）              ← 全局逻辑视图（唯一）
                 |
             内模式（存储模式）            ← 物理存储视图（唯一）

两级映射（保证数据独立性）

🔑 重点： 三级模式+两级映像是数据库系统的核心架构，是实现数据独立性的关键。

1.5 本章知识结构图

数据库系统概述
├── 四个核心概念：数据 → DB → DBMS → DBS
├── 数据管理三阶段：人工 → 文件 → 数据库系统
├── 数据模型
│   ├── 三要素：数据结构 + 数据操作 + 完整性约束
│   ├── 概念模型：E-R模型（实体、属性、联系）
│   └── 逻辑模型：层次 → 网状 → 关系（主流）
└── 三级模式结构
    ├── 外模式（用户视图，多个）
    ├── 模式（逻辑视图，唯一）
    ├── 内模式（物理视图，唯一）
    └── 两级映射 → 数据独立性

第二章 · 关系数据库

2.1 关系数据结构

关系 = 二维表

关系数据库的核心思想：用二维表来表示数据及数据之间的联系。

🔑 创始人： E.F.Codd 于1970年发表论文《A Relational Model of Data for Large Shared Data Banks》，奠定了关系数据库理论基础，

关系 vs 表的术语对照

🔑 重点： 关系中每个分量必须为原子值（不可再分）——这是第一范式（1NF）的要求。

关系的三种类型

• 基本表：实际存在，有物理存储
• 查询表：查询结果对应的临时表
• 视图表：由基本表导出的虚表，不存数据只存定义

2.2 关系操作

核心特点：集合操作

关系模型的操作对象和结果都是集合（一组元组），而非单条记录——这叫"一次一集合"。

基本关系操作分类

💡 过程化 vs 非过程化： 关系代数是过程化语言（你要说"怎么做"），SQL是非过程化语言（你只说要"什么"）。优化器常用关系代数及其扩展表示和变换查询；SQL 默认保留重复行，与纯集合模型有区别。

2.3 关系完整性约束

三大完整性

1. 实体完整性（Entity Integrity）

• 主码的值不能为空（NOT NULL） 且必须唯一
• 原因：主码是元组的唯一标识符

2. 参照完整性（Referential Integrity） 🔑

• 外键非空值须匹配被引用的主键或其他满足要求的唯一键；空值与复合外键规则需结合约束声明。
• 原因：外码引用必须真实存在
• 违反参照完整性时DBMS的处理方式：
  • 拒绝执行（NO ACTION）
  • 级联更新/删除（CASCADE）
  • 置空（SET NULL）

3. 用户定义完整性（User-defined Integrity）

• 用户根据业务规则自定义的约束
• 示例：成绩 BETWEEN 0 AND 100、性别 IN ('男','女')

🔑 重点： 主键、外键等约束需在模式中声明，DBMS 才会按相应规则检查；并非所有表都会自动拥有这些约束。

2.4 关系代数

传统集合运算

专门关系运算

连接详解

🔑 重点： 自然连接 = 等值连接 + 自动去重列。它是日常开发中最常用的JOIN操作。

关系代数表达式示例

查询"选修了数据库课程的学生姓名"：

Π_Sname (σ_Cname='数据库' (Student ⋈ SC ⋈ Course))

💡 解读：先将三张表自然连接得到完整信息 → 从中选出课程名为"数据库"的行 → 只保留学生姓名这一列。从内向外读，从下向上执行。

2.5 本章知识结构图

关系数据库
├── 关系数据结构
│   ├── 关系（表）、元组（行）、属性（列）、域
│   ├── 候选码、主码、外码
│   └── 分量必须为原子值（1NF）
├── 关系操作
│   ├── 特点：一次一集合
│   └── 查询 + 更新两大类
├── 关系完整性约束
│   ├── 实体完整性（主码非空唯一）
│   ├── 参照完整性（外码引用真实）← 重中之重
│   └── 用户定义完整性
└── 关系代数
    ├── 传统集合运算：∪ ∩ − ×
    └── 专门关系运算：σ(选择) Π(投影) ⋈(连接) ÷(除)

第三章 · SQL语言

本文查询示例采用常见 SQL 写法，以 PostgreSQL 的行为作核对。不同数据库在类型、授权、触发器和空值处理上可能有差异。尖括号或方括号语法表示教学模板，不应原样执行。

3.1 SQL概述

SQL是什么？

SQL（Structured Query Language，结构化查询语言）是关系数据库的标准语言。1970年代由IBM开发，1986年成为ANSI标准，1987年成为ISO标准。

SQL的四大功能模块

SQL的主要特点

• 高度非过程化：说"要什么"，不说"怎么做"
• 面向集合：操作对象和结果都是元组集合
• 一体化：DDL/DML/DQL/DCL统一语法风格
• 两种使用方式：交互式（命令行直接敲） + 嵌入式（嵌入Java/Python等程序）

3.2 数据定义（DDL）

创建表 CREATE TABLE

下面三个基本表供本章示例使用。此处为演示模型，约定一名学生对同一课程最多一条选课记录；示例姓名均为虚构。

CREATE TABLE Student (
    Sno CHAR(9) PRIMARY KEY,
    Sname VARCHAR(20) NOT NULL,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept VARCHAR(20)
);

CREATE TABLE Course (
    Cno CHAR(4) PRIMARY KEY,
    Cname VARCHAR(40) NOT NULL,
    Credit SMALLINT
);

CREATE TABLE SC (
    Sno CHAR(9) REFERENCES Student(Sno),
    Cno CHAR(4) REFERENCES Course(Cno),
    Grade SMALLINT CHECK (Grade BETWEEN 0 AND 100),
    PRIMARY KEY (Sno, Cno)
);

常用数据类型

🔑 重点： CHAR vs VARCHAR — CHAR(10)存"Hi"也占10字符；VARCHAR(10)存"Hi"只占2字符+长度开销。日常开发中变长字符串优先用VARCHAR。

修改和删除表

ALTER TABLE Student ADD Sbirth DATE;           -- 新增列
ALTER TABLE Student ALTER COLUMN Sage INT;      -- 修改列类型
DROP TABLE Student;                              -- 删除整张表

3.3 数据查询（DQL — SELECT）🔑

SELECT基本语法

SELECT [DISTINCT] 目标列
FROM 表名
[WHERE 条件表达式]
[GROUP BY 列名 [HAVING 条件]]
[ORDER BY 列名 [ASC|DESC]];

💡 实际执行顺序： FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。理解这个顺序是写对复杂查询的前提！

WHERE子句常用运算符

🔑 重点： NULL ≠ 0 ≠ ‘’。判断NULL必须用 IS NULL 或 IS NOT NULL，不能用 = NULL！

聚合函数

分组查询 GROUP BY + HAVING

-- 查询每个专业的平均年龄（只显示平均年龄<21的专业）
SELECT Sdept, AVG(Sage) AS avg_age
FROM Student
GROUP BY Sdept
HAVING AVG(Sage) < 21;

🔑 重点：WHERE vs HAVING — WHERE 在分组前过滤行，不能直接引用同一查询层级的聚合结果；HAVING 过滤组，可使用聚合表达式。子查询有各自的查询层级。

连接查询（JOIN）

-- 查每个学生的选课信息
SELECT Student.Sno, Sname, Cno, Grade
FROM Student JOIN SC ON Student.Sno = SC.Sno;

子查询（嵌套查询）

-- 查询选修了"数据库"的学生姓名
SELECT Sname FROM Student
WHERE Sno IN (
    SELECT Sno FROM SC
    WHERE Cno IN (
        SELECT Cno FROM Course WHERE Cname='数据库'
    )
);

• IN：属于子查询结果集
• EXISTS：子查询结果非空即为真（相关子查询）
• > ALL / > ANY：比子查询结果中的所有/任意一个值大

集合查询

SELECT 默认保留重复行；需要去重时使用 DISTINCT。查询结果可以用UNION(并)、INTERSECT(交)、EXCEPT(差)。前提：列数相同且对应列类型兼容。UNION 默认去重，UNION ALL 保留重复行。

3.4 数据更新（DML）

-- 插入
INSERT INTO Student VALUES('2024001', '张三', '男', 19, 'CS');
INSERT INTO Student(Sno, Sname) VALUES('2024002', '李四');  -- 指定列插入

-- 修改
UPDATE Student SET Sage = 20 WHERE Sno = '2024001';

-- 删除
DELETE FROM Student WHERE Sno = '2024001';

⚠️ 致命警告： UPDATE/DELETE不写WHERE子句会操作全部行！请一定再三确认WHERE条件。

3.5 视图（View）

定义

普通视图是由查询定义的虚表，主要保存定义；物化视图另行保存查询结果，需要刷新。像一扇"数据窗口"。

CREATE VIEW CS_Student AS
SELECT Sno, Sname, Sage
FROM Student
WHERE Sdept = 'CS';

视图的作用

• 简化操作：把复杂查询封装成视图
• 安全性：隐藏敏感列（如工资）
• 逻辑独立性：基本表结构变了，只改视图定义

💡 注意：不是所有视图都能更新。涉及聚合函数、多表连接的视图通常是只读的。

3.6 索引（Index）

索引就像书的目录，帮助数据库快速定位数据，避免全表扫描。

CREATE INDEX idx_student_dept ON Student(Sdept);

🔑 索引不是越多越好——加速查询的同时，通常会增加写操作的维护成本（索引也需要同步更新），并占用额外存储空间。

3.7 本章知识结构图

SQL语言
├── DDL（数据定义）：CREATE/ALTER/DROP TABLE
│   ├── 数据类型：CHAR/VARCHAR/INT/NUMERIC/DATE...
│   └── CHAR vs VARCHAR（定长 vs 变长）
├── DQL（数据查询）← 重中之重
│   ├── 单表查询：SELECT...WHERE...ORDER BY
│   ├── 聚合与分组：GROUP BY + HAVING
│   ├── 连接查询：JOIN（内/外/左外/右外）
│   ├── 嵌套查询：IN / EXISTS / ALL / ANY
│   └── 集合查询：UNION / INTERSECT / EXCEPT
├── DML（数据更新）：INSERT / UPDATE / DELETE
│   └── ⚠️ WHERE不能丢！
├── 视图（View）：虚表，安全窗口
└── 索引（Index）：加速查询，拖慢写操作

第四章 · 数据库安全性

4.1 安全性概述

安全性 vs 完整性

🔑 重点： 两者互补——安全性关注访问与操作权限，完整性关注数据规则是否成立；内外部主体都可能涉及这两类问题。缺一不可。

安全标准（TCSEC/TDI）

4.2 存取控制

两步防线

1. 用户标识与鉴别：你是谁？（用户名+密码）
2. 存取控制：你能做什么？（根据权限规则）

两种存取控制方法

💡 DAC vs MAC： MAC中每个数据标密级（绝密/机密/秘密/公开），每个用户标许可级别。用户只能读 ≤ 自己级别的数据（向下读原则）。

4.3 用户授权（GRANT / REVOKE）

GRANT 授权

GRANT 权限名 [, 权限名...]
ON 对象名
TO 用户名 [, 用户名...]
[WITH GRANT OPTION];  -- 允许被授权者继续授权给他人

REVOKE 收回权限

REVOKE 权限名 [, 权限名...]
ON 对象名
FROM 用户名 [, 用户名...] [CASCADE | RESTRICT];

常用权限

示例

-- 授权：把Student表的查询权给U1，U1可再授权
GRANT SELECT ON Student TO U1 WITH GRANT OPTION;

-- 授权：SC表的插入和修改权给U2和U3
GRANT INSERT, UPDATE ON SC TO U2, U3;

-- 收回：级联收回（连U1授权出去的一起收回）
REVOKE SELECT ON Student FROM U1 CASCADE;

🔑 重点： CASCADE（级联收回）会连带收回被授权者再授出的权限；RESTRICT则拒绝收回（如果权限已被再授出）。

4.4 角色（Role）

角色是权限的集合——将一组权限打包命名，整体授予用户，简化管理。

CREATE ROLE Teacher;                                    -- 创建角色
GRANT SELECT, INSERT, UPDATE ON Course TO Teacher;      -- 给角色授权
GRANT Teacher TO 王老师, 李老师;                         -- 把角色授予用户

💡 好处：权限变更时只改角色权限，所有拥有该角色的用户自动生效——权限与角色分离。

4.5 审计与加密

审计（Audit）

下方 AUDIT / NOAUDIT 是原课程的方言示意，不是 PostgreSQL 通用命令；实际审计需依据所用数据库配置。 按审计策略记录选定事件，用于追踪操作；是否覆盖全部操作取决于配置。

AUDIT UPDATE, DELETE ON Student;    -- 审计修改操作
NOAUDIT UPDATE, DELETE ON Student;  -- 关闭审计

💡 审计消耗系统资源，通常只对敏感操作开启。

数据加密

将敏感数据以密文形式存储。

• 对称加密：如 AES，加解密使用共享密钥；DES 仅作为历史算法背景。
• 非对称加密：RSA（公钥加密，私钥解密）
• 哈希与密码存储：通用哈希和专用密码哈希要区分；密码验证应采用带盐、可调成本的密码哈希方案，不能直接把 MD5 或快速 SHA 摘要当作密码存储方案。

🔑 密码存储应用哈希而非加密——加密可解密，哈希单向不可逆。安全性仍受密码强度、成本参数与实现影响，不能保证泄露后无法被猜测。

4.6 本章知识结构图

数据库安全性
├── 安全 vs 完整：防外部攻击 vs 保内部正确
├── 存取控制
│   ├── 两步：用户标识 → 存取控制
│   ├── DAC（自主）：灵活，商用主流
│   └── MAC（强制）：严格，军事/政府
├── GRANT / REVOKE
│   ├── WITH GRANT OPTION（允许再授权）
│   └── CASCADE（级联收回） vs RESTRICT
├── 角色（Role）：权限打包，简化管理
├── 审计（Audit）：记录操作，事后追查
└── 加密：对称/非对称/哈希（密码用哈希+加盐）

第五章 · 数据库完整性

5.1 完整性概述

完整性 = 正确性 + 相容性

• 正确性：数据值在合理范围内（年龄≥0，性别∈{男，女}）
• 相容性：不同数据之间不矛盾（选课记录引用的学号在学生表中存在）

DBMS维护完整性的三部曲

1. 用户定义完整性约束条件
2. DBMS在每次数据更新时自动检查
3. 违反规则 → 按约束时机与动作处理：拒绝、延迟检查或执行声明的级联动作

🔑 重点： 完整性约束条件应作为数据库模式的一部分定义，由DBMS自动强制执行——不是运行时才检查的"后补规则"。

5.2 实体完整性

定义

每个关系的主码值必须唯一且不能为空（NOT NULL）。

CREATE TABLE Student (
    Sno CHAR(9) PRIMARY KEY,  -- PRIMARY KEY 同时要求 UNIQUE 和 NOT NULL
    ...
);

为什么？

• 不能为空：主码是每行的"身份证号"，NULL意味着"不知道是谁"
• 必须唯一：两个不同元组不能有相同的标识符

🔑 重点： PRIMARY KEY 同时要求 UNIQUE 和 NOT NULL。一个表只有一个主码。插入/更新违反实体完整性时，DBMS直接拒绝。

5.3 参照完整性 🔑

定义

对单列外键，非空值必须匹配被引用键；被引用键不一定是主键，也可以是满足相应唯一性要求的键。能否为空由 NOT NULL 等约束决定，复合外键还受 MATCH 规则影响。

CREATE TABLE SC (
    Sno CHAR(9),
    Cno CHAR(4),
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

违反参照完整性时的处理策略

FOREIGN KEY (Sno) REFERENCES Student(Sno)
    ON DELETE CASCADE   -- 删学生时级联删除选课
    ON UPDATE CASCADE;  -- 更新学号时级联更新

🔑 重点： 参照完整性保证表间引用不会出现"悬空引用"——外码引用的一定是真实存在的。

5.4 用户定义完整性

常见约束类型

CREATE TABLE Student (
    Ssex  CHAR(2) CHECK(Ssex IN ('男','女')),     -- 性别枚举
    Sage  SMALLINT CHECK(Sage BETWEEN 0 AND 120),   -- 年龄范围
    Grade SMALLINT CHECK(Grade BETWEEN 0 AND 100)   -- 成绩范围
);

CHECK 遇到空值可能得到未知值，不能替代 NOT NULL；是否允许缺失值需单独声明。约束也可能声明为延迟检查，不能把所有检查都理解成单行更新后的立即拒绝。

5.5 触发器（Trigger）

定义

触发器是事件驱动的特殊存储过程——当指定事件发生时自动执行。

三要素

示例：成绩修改幅度不能超过20%

以下为教学伪代码，展示事件—条件—动作结构，不是可直接在所有 DBMS 执行的 SQL。

CREATE TRIGGER Check_Grade_Change
AFTER UPDATE OF Grade ON SC
REFERENCING OLD ROW AS OldTuple, NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.Grade > OldTuple.Grade * 1.2)
BEGIN
    RAISE APPLICATION_ERROR(...);  -- 报错拒绝
END;

💡 触发器功能强大但应谨慎使用——过多触发器会降低性能且难以调试。能用CHECK解决的就用CHECK。

5.6 本章知识结构图

数据库完整性
├── 实体完整性
│   └── PRIMARY KEY 同时要求 UNIQUE 和 NOT NULL
├── 参照完整性 ← 重中之重
│   ├── FOREIGN KEY ... REFERENCES
│   └── 违例处理：NO ACTION / CASCADE / SET NULL
├── 用户定义完整性
│   ├── NOT NULL
│   ├── UNIQUE
│   ├── CHECK（条件表达式）
│   └── DEFAULT（默认值）
└── 触发器（Trigger）
    ├── 事件 + 条件 + 动作
    └── 最灵活但需谨慎使用

第六章 · 关系数据理论（规范化理论）

6.1 问题的提出——为什么需要规范化？

“坏"设计的四大问题

🔑 核心思想： 好的表设计应该一事一地（one fact in one place）——每种信息只存一次，通过外码关联。

6.2 函数依赖（Functional Dependency）🔑

定义

X → Y（Y函数依赖于X）：给定X的值，就能唯一确定Y的值。

三种函数依赖

候选码的函数依赖定义

• 候选码：K完全函数依赖地确定整个属性集U
• 主属性：任何一个候选码中的属性
• 非主属性：不在任何候选码中的属性

🔑 关键区分： 部分依赖 vs 完全依赖——这是判断2NF的核心。

6.3 范式（Normal Form）

范式阶梯

1NF → 2NF → 3NF → BCNF → 4NF → 5NF
(关系数据库)  (日常开发通常到3NF)

各范式要求

🔑 记忆口诀： 1NF原子值 → 2NF去部分 → 3NF去传递 → BCNF决定因素是码

具体案例分析

关系模式：Student(Sno, Sname, Sdept, Dhead, Cno, Grade)

函数依赖：

• Sno → Sname
• Sno → Sdept
• Sdept → Dhead
• (Sno, Cno) → Grade

→ 2NF 分解（去除非主属性对码的部分依赖）：

• Student(Sno, Sname, Sdept, Dhead) + SC(Sno, Cno, Grade)

→ 3NF 分解（去除非主属性对码的传递依赖）：

• Student(Sno, Sname, Sdept) + Dept(Sdept, Dhead) + SC(Sno, Cno, Grade)

💡 规范化常通过满足无损连接等要求的模式分解消除不合适的依赖；不是任意拆表——把一个有很多属性的大表拆成多个小表，每个小表只描述一个主题。

6.4 规范化 vs 反规范化

💡 不是范式越高越好！ 更高范式不必然使查询变慢。设计需兼顾一致性、查询模式与维护成本，反规范化也不只是合表；是否保留冗余应有测量依据。

6.5 本章知识结构图

关系数据理论（规范化理论）
├── 问题：数据冗余、更新/插入/删除异常
├── 函数依赖（数学基础）
│   ├── 完全函数依赖
│   ├── 部分函数依赖 ← 产生2NF问题
│   └── 传递函数依赖 ← 产生3NF问题
├── 范式阶梯
│   ├── 1NF：属性原子化
│   ├── 2NF：去除非主属性对码的部分依赖
│   ├── 3NF：去除非主属性对码的传递依赖 ← 日常设计目标
│   └── BCNF：每个决定因素都包含候选码
└── 规范化的本质：拆表（一事一地）

分解的两个检查点

无损连接要求分解后的表能够按约定连接回原关系；保持依赖要求原来的约束仍能有效检查。达到更高范式与同时保持全部依赖并不总是同一件事。

第七章 · 数据库设计

7.1 数据库设计六阶段

🔑 重点： 前两个阶段与具体DBMS无关，从阶段③开始才选择具体数据库软件。

7.2 需求分析

输出物

💡 数据字典是设计文档，不是数据库的元数据表。

7.3 概念结构设计（E-R模型）🔑

E-R图基本元素

💡 实体标识属性可用下划线作为标记。

联系的三种类型

E-R图示例

    [学生] —M— (选课) —N— [课程]
   (学号, 姓名, 年龄)           (课程号, 课程名, 学分)
                   (成绩)  ← 联系的属性

7.4 逻辑结构设计

E-R图 → 关系模式转换规则

🔑 重点： M:N联系不能合并，必须单独建表！在此示例中，中间表用两端外码的组合做主码；实际也可用代理键，并另设业务唯一约束。

示例转换

学生(M:N)课程 → 三张表：

• Student(Sno, Sname, Sage) — 主码：Sno
• Course(Cno, Cname, Credit) — 主码：Cno
• SC(Sno, Cno, Grade) — 主码：(Sno, Cno)，两列都是外码

7.5 物理设计与实施

物理设计关注

• 索引设计：哪些列建索引？聚簇还是非聚簇？
• 数据存放：哪些数据放高速存储？
• 系统配置：缓冲区大小、连接数等参数

实施步骤

1. 写DDL建表、建索引、建视图
2. 编写应用程序
3. 加载测试数据 → 测试 → 上线

7.6 本章知识结构图

数据库设计
├── ① 需求分析 → 数据字典 + DFD
├── ② 概念设计 → E-R图
│   ├── 实体（矩形）、属性（椭圆）、联系（菱形）
│   └── 联系类型：1:1 / 1:N / M:N
├── ③ 逻辑设计 → 关系模式（表结构）
│   ├── 实体→表；1:N→N端加外码；M:N→中间表
│   └── 规范化检查（至少3NF）
├── ④ 物理设计 → 索引、存储方案
├── ⑤ 实施 → DDL建表+编程+测试
└── ⑥ 维护 → 监控+备份+调整

第八章 · 查询处理与优化

8.1 查询处理的四个阶段

一条SQL从输入到结果的旅程：

🔑 重点： 同一条SQL可能有上百种执行方式，执行时间可相差几个数量级。查询优化器在搜索空间和代价估计范围内选择方案，并不保证全局最优。

8.2 查询分析

• 词法分析：拆分Token
  • SELECT Sname FROM Student → [SELECT][Sname][FROM][Student]
• 语法分析：检查Token排列是否符合SQL语法 → 生成语法树

8.3 查询检查（语义检查）

检查内容：

1. 表是否存在？
2. 列是否在对应表中存在？
3. 用户是否有权限？
4. 数据类型是否匹配？

通过后生成查询树（关系代数表达式树）。

8.4 查询优化概述

为什么需要优化？

SQL是非过程化的——你只说"要什么”，DBMS决定"怎么做"。

策略A（先连接再选择）：3表全连接 → 选中"数据库" → 投影 = 慢！
策略B（先选择再连接）：筛选"数据库"课程 → 只连接相关行 → 投影 = 快！

两个层面

8.5 代数优化——等价变换🔑

核心原则（按优先级排序）

🔑 最最重要： “选择尽量做早”——大幅减少中间结果的数据量。

常用等价变换规则

• 选择串接律：σ_F1(σ_F2(E)) ≡ σ_{F1∧F2}(E)（多个条件合并一次筛选）
• 选择下推连接：σ_F(R⋈S) ≡ σ_F(R)⋈S（当F只涉及R时）
• 投影下推连接：各输入必须保留输出所需属性及连接条件所需属性，连接完成后再投影到 A；不能提前丢掉连接键。

8.6 物理优化

物理优化决定

• 每个表用什么访问方法？（全表扫描 vs 索引扫描）
• 连接用什么算法？（嵌套循环 / Hash Join / Sort-Merge Join）
• 中间结果怎么传递？（物化 vs 流水线）

两种优化策略

💡 统计信息（表大小、值分布）是CBO准确估算的前提——需定期更新统计信息。

8.7 本章知识结构图

查询处理与优化
├── 四阶段：分析 → 检查 → 优化 → 执行
├── 代数优化（逻辑层）
│   ├── 等价变换规则
│   └── 核心原则：选择尽早做！投影尽早做！
├── 物理优化（物理层）
│   ├── 访问路径选择（全表 vs 索引）
│   ├── 连接算法选择
│   └── CBO（主流） vs RBO
└── 目标：最小化总代价

第九章 · 数据库恢复技术

9.1 事务（Transaction）

定义

事务是一个不可分割的数据库操作序列——要么全做，要么全不做。

经典例子：银行转账

-- 转账示意：在同一事务中执行两次更新
BEGIN;
UPDATE 账户 SET 余额 = 余额 - 100 WHERE 账号 = 'A';
UPDATE 账户 SET 余额 = 余额 + 100 WHERE 账号 = 'B';
COMMIT;

🔑 如果第一条执行后系统崩溃，A的100元就消失了！事务保证这两步同生共死。

事务的两种结束方式

9.2 事务的ACID特性 🔑

🔑 各自由不同机制保证： 原子性→恢复技术，一致性→完整性约束，隔离性→并发控制，持久性→日志。

9.3 故障的种类

🔑 系统故障（内存丢但磁盘完好）vs 介质故障（磁盘也坏了，需备份恢复）。

9.4 恢复策略

恢复的基本原理：冗余

恢复不能无中生有，需提前保存冗余数据：

1. 日志文件（Log） —— 记录每次修改的流水账

• 事务标识 + 数据对象 + 旧值(UNDO) + 新值(REDO)

2. 数据备份（Backup） —— 数据库的定期快照

WAL原则（Write-Ahead Logging）

🔑 先写日志，再写数据库。 相关日志必须先持久化，才能将对应的数据页写入持久存储；这不等于每次修改内存页前都必须先刷盘。这样即使修改中崩溃，日志已记录了恢复信息。

系统故障恢复（UNDO + REDO）

介质故障恢复

1. 从最近的完整备份恢复数据库
2. 用备份之后的日志重做（REDO）所有已提交操作

💡 备份+日志缺一不可——备份确定起点，日志补齐后续修改。

9.5 本章知识结构图

数据库恢复技术
├── 事务（Transaction）
│   ├── 不可分割的操作序列
│   └── COMMIT（提交） / ROLLBACK（回滚）
├── ACID特性
│   ├── 原子性 ← 恢复技术
│   ├── 一致性 ← 完整性约束
│   ├── 隔离性 ← 并发控制
│   └── 持久性 ← 日志
├── 四种故障
│   ├── 事务内部故障 → 软故障(系统) → 硬故障(介质) → 病毒
│   └── 严重度递增
└── 恢复手段
    ├── 冗余：日志 + 备份
    ├── WAL原则：先写日志后写数据库
    └── UNDO（撤销未提交） + REDO（重做已提交）

第十章 · 并发控制

10.1 并发操作带来的三大问题

① 丢失修改（Lost Update）

两个事务同时读同一数据并分别修改，后一次写入覆盖前一次写入，造成更新丢失；具体是否发生取决于数据库的隔离级别与操作方式。

T1: 读500 → +100 → 写600
T2: 读500 → +200 → 写700  ← T1的修改被覆盖！
实际结果700，正确结果应为800

② 不可重复读（Non-Repeatable Read） 🟠

一个事务内两次读同一数据结果不同（中间被其他事务改了）。

③ 读"脏"数据（Dirty Read） 🟡

读到其他事务未提交的修改，后来那个事务回滚了。

10.2 封锁（Lock）

两种基本锁

锁相容矩阵

🔑 记忆口诀： S锁可与S锁共存（大家一起读），X锁与任何锁都不兼容（写的时候谁也不许动）。

10.3 三级封锁协议

🔑 二级 vs 三级的关键区别： S锁的释放时机。二级读完即放（中间可被改→不可重复读），三级事务结束才放（数据全程不变）。

10.4 死锁与活锁

等待饥饿与活锁

某个事务一直等待、其他事务不断插队，通常称为饥饿；活锁指事务持续采取动作或重试，却没有取得进展。 → 解决： 先来先服务（FCFS）排队。

死锁（Deadlock）

T1持有A锁等B锁，T2持有B锁等A锁——互相等待。

死锁的解决

💡 实际系统中检测+解除更常用——预防法太保守，影响并发度。

10.5 可串行化调度

定义

并发执行的结果 = 某种顺序串行执行的结果 → 并发调度是正确的。

两段锁协议（2PL）——保证可串行化

🔑 遵守两段锁协议 → 调度可串行化 → 并发执行正确。但2PL仍可能死锁。

10.6 本章知识结构图

并发控制
├── 三种并发问题
│   ├── 丢失修改
│   ├── 不可重复读
│   └── 读脏数据
├── 封锁（Lock）
│   ├── S锁（共享锁/读锁）：可共存
│   └── X锁（排他锁/写锁）：排斥一切
├── 三级封锁协议
│   ├── 一级：防丢失修改
│   ├── 二级：+防脏读
│   └── 三级：+防不可重复读（S锁事务结束释放）
├── 死锁与活锁
│   ├── 预防：一次封锁 / 顺序封锁
│   └── 检测+解除：等待图检测 → 回滚
└── 可串行化 + 两段锁协议（2PL）

补充：简记中的依赖与约束

候选码、主码与空值

候选码是能唯一确定整个元组的最小属性集，主码是选定的候选码。SQL 中单独声明 UNIQUE 与理论候选码不完全等价：还需明确空值、非空要求和数据库的具体规则。复合主键整体唯一，各组成列都非空。

Armstrong 公理与属性闭包

• 自反律：若 $Y\subseteq X$，则 $X\to Y$。
• 增广律：若 $X\to Y$，则 $XZ\to YZ$。
• 传递律：若 $X\to Y$ 且 $Y\to Z$，则 $X\to Z$。

计算属性集 X 的闭包时，从 X 出发，反复将左部已被包含的依赖的右部加入，直到不再增长。若闭包覆盖全部属性，X 是超码；再检查是否存在仍为超码的真子集，才能判断是否为候选码。

多值依赖与 4NF

函数依赖描述唯一决定，多值依赖则刻画在固定 X 时，Y 的可能值与剩余属性的可能值之间的独立组合关系，不能仅解释成“X 对应多个 Y”。

4NF 要求每个非平凡多值依赖 $X\twoheadrightarrow Y$ 的决定因素 X 都是超码。平凡情形包括 $Y\subseteq X$ 或 $X\cup Y$ 已覆盖整个关系属性集。5NF 涉及连接依赖，此处不作未完成的展开。

事务状态与恢复的边界

活动、部分提交、失败、回滚后终止、成功提交是理解事务生命周期的常见状态。应用业务规则、约束、并发控制与恢复共同维护一致性，不能把 ACID 每一项机械地交给单一机制。

写前日志约束的是日志与数据页持久化的先后关系。具体数据库是否使用物理或逻辑日志、如何实现撤销与重做，需要结合实现；不能把课程中的统一模型当作所有产品内部结构。

参考与整理范围

本文合并数据库系统基础的十章文字笔记，吸收《数据库简记》中的补充内容并去重。原目录中的教材题目截图与依赖原题的答案合集不随正文公开；正文保留可独立理解的概念、示例和推导。

• PostgreSQL：约束
• PostgreSQL：查询与去重
• PostgreSQL：物化视图
• PostgreSQL：事务隔离
• PostgreSQL：写前日志
• OWASP：密码存储