数据库理论与设计教学

关系就是一张二维表，但有三条规范化要求：列同质、列可交换、行不重复。 关系代数用一组运算描述查询，是 SQL 的数学基础。 五种基本运算是并、差、笛卡尔积、选择、投影，其余（交、连接、除）都可由它们导出。

-- 关系代数符号与含义
σ（选择）：按条件挑行      π（投影）：挑指定的列
∪ 并   − 差   ∩ 交   × 笛卡尔积   ⋈ 连接   ÷ 除

-- 例：查询选修了 2 号课程的学生的姓名
π Sname (σ Cno='2' (SC ⋈ Student))

-- 例：查询选修了全部课程的学生学号（除运算）
π Sno,Cno (SC) ÷ π Cno (Course)

-- 对应的 SQL 写法（EXISTS 表达形式）
SELECT Sno FROM Student
WHERE NOT EXISTS (
  SELECT * FROM Course WHERE NOT EXISTS (
    SELECT * FROM SC WHERE SC.Sno = Student.Sno AND SC.Cno = Course.Cno));

安全性关注防止非法用户与非法操作，手段包括身份鉴别、存取控制、视图、审计、加密； 完整性关注数据本身是否正确、有效、相容，手段是各类约束与触发器。 二者目标不同：即使一个用户完全合法，也可能输入违反语义的数据——这正是完整性要管的。

-- 自主存取控制
GRANT SELECT ON TABLE Student TO U1 WITH GRANT OPTION;   -- 可再授权
GRANT UPDATE(Sage) ON TABLE Student TO U2;               -- 列级权限
REVOKE UPDATE(Sage) ON TABLE Student FROM U2;
CREATE ROLE R1;  GRANT SELECT ON TABLE Student TO R1;  GRANT R1 TO 王平;

-- 完整性约束
CREATE TABLE SC (
  Sno   CHAR(9)  NOT NULL,
  Cno   CHAR(4)  NOT NULL,
  Grade SMALLINT CHECK (Grade BETWEEN 0 AND 100),   -- 用户定义完整性
  PRIMARY KEY (Sno, Cno),                            -- 实体完整性
  FOREIGN KEY (Sno) REFERENCES Student(Sno)          -- 参照完整性
    ON DELETE CASCADE ON UPDATE NO ACTION
);

-- 命名约束后便于修改
ALTER TABLE SC DROP CONSTRAINT C1;

设计不好的关系模式会带来冗余与三类异常（插入、删除、更新）。 规范化通过分解关系模式逐步消除不合适的函数依赖： 1NF（属性不可再分）→ 2NF（消除非主属性对码的部分依赖）→ 3NF（消除传递依赖）→ BCNF（所有决定因素都含码）→ 4NF（消除多值依赖）。 求候选码的标准方法是属性闭包。

-- 例：关系模式 SLC(Sno, Sdept, Sloc, Cno, Grade)，语义为一个学生只在一个系
-- 函数依赖：Sno → Sdept，Sdept → Sloc，(Sno,Cno) → Grade
-- 码：(Sno, Cno)
-- 问题：Sdept 与 Sloc 只依赖于 Sno（部分依赖）→ 不是 2NF，存在冗余与更新异常
-- 分解：SL(Sno, Sdept, Sloc) 与 SC(Sno, Cno, Grade)
--       SL 中 Sno → Sdept → Sloc 是传递依赖 → 再分解为 SD(Sno, Sdept) 与 DL(Sdept, Sloc)

-- 最后得到三个 3NF 关系：SD(Sno,Sdept)、DL(Sdept,Sloc)、SC(Sno,Cno,Grade)

数据库设计分六个阶段：需求分析 → 概念结构设计（E-R 图）→ 逻辑结构设计（转成关系模式）→ 物理结构设计 → 实施 → 运行维护。 其中最关键的是 E-R 图向关系模式的转换，转换规则按联系的基数（1:1、1:n、m:n）而不同。

-- 转换规则要点
-- ① 一个实体型 → 一个关系模式，实体的属性即关系的属性，实体的码即关系的码
-- ② 1:1 联系：可并入任意一端（在该端加另一端的码），也可单独建关系
-- ③ 1:n 联系：并入 n 端（在 n 端关系中加 1 端的码），若联系有属性则一并加入
-- ④ m:n 联系：必须单独建立一个关系，属性为两端实体的码 + 联系自身的属性，码为两端码的组合
-- ⑤ 三个及以上实体的多元联系：单独建关系，码为各实体码的组合

-- 例：学生与课程是 m:n 联系"选修"，带属性"成绩"
-- Student(Sno, Sname, ...)      Course(Cno, Cname, ...)
-- SC(Sno, Cno, Grade)           主码 (Sno, Cno)，两个外码分别参照 Student 与 Course

多个事务并发执行会产生三类数据不一致：丢失修改、不可重复读、读脏数据。 并发控制的目标是让并发调度的结果等价于某种串行调度（可串行化）。 最常用的机制是封锁：X 锁（写锁、排它）、S 锁（读锁、共享），配合不同级别的封锁协议解决问题。

-- 事务
BEGIN TRANSACTION;
  SELECT ...;  UPDATE ...;
COMMIT;              -- 提交；出错则 ROLLBACK 回滚

-- 封锁与协议
-- 一级封锁协议：修改前加 X 锁直到事务结束   → 防止丢失修改
-- 二级封锁协议：一级 + 读取前加 S 锁读完即放 → 防止读脏数据
-- 三级封锁协议：一级 + 读取前加 S 锁直到结束 → 防止不可重复读

-- 两段锁协议：加锁阶段与解锁阶段严格分开 → 保证并发调度可串行化
-- 多粒度封锁：意向锁 IS / IX / SIX 提高加锁检查效率

恢复的两大支柱是数据转储（备份副本）与日志文件（记录每次更新）。 核心原则是先写日志：必须先把日志记录写到稳定存储，再写数据库，否则故障后无法判断该重做还是撤销。 恢复时，对未完成事务做 UNDO（撤销），对已提交但未落盘的事务做 REDO（重做）。

-- 日志记录的典型内容
--  事务开始标记：<Ti BEGIN>
--  更新记录：<Ti, 数据对象, 旧值, 新值>
--  提交标记：<Ti COMMIT>

-- 恢复策略
-- 事务故障：反向扫描日志，对该事务的更新执行 UNDO，直到 BEGIN
-- 系统故障：正向扫描找已提交事务 → REDO；未完成事务 → UNDO
-- 介质故障：装入最近的后备副本 + 重做此后所有已提交事务

-- 检查点技术：定期写检查点记录，恢复时只需处理检查点之后的事务，缩短扫描范围