翻译该文件内容形成一个新的文件

### COMP9120 第五周：数据库完整性约束 第二学期 2024 教授 Athman Bouguettaya 计算机科学学院
#### 内容与 Dr Lijun Chang 共同开发
##### 致谢词
我想向澳大利亚的传统所有者表示感谢，并承认他们对土地、水和文化的持续联系。目前我所在的地方是 Eora 国家 Gadigal 人民的土地上，我对他们的长老们表示敬意。

#### 版权声明
##### 大纲
- 完整性约束概览
- 静态完整性约束
- 域约束
- 主键/引用完整性约束
- 语义完整性约束
- 断言
- 动态完整性约束
- 触发器

#### 完整性约束（IC）
- 定义：必须适用于每个数据库实例的条件。
- 初始设计阶段的一部分以保证数据库一致性和一致性。
- 可在任何时候添加或更新（通过 `ALTER TABLE` 命令）。
- 当执行这样的命令时，系统会首先确认关系满足指定的约束。如果满足，则加入关系；如果不满足，则拒绝命令。
- 合法的关系实例是指满足所有规定约束的关系。
- 不一定总是如此。

#### 捕获完整性约束的原因
为什么需要捕获完整性约束？
为了确保数据存储符合应用程序域的实际意义（语义），避免数据输入错误并保持数据的一致性，使应用开发更简单且可维护性强。

#### 数据库中的完整性约束
如前所述，完整性约束是在数据库模式设计过程中指定的。
检测过程可以自动化但可能带来不可接受的开销。
当提到的部分数据库被修改时检查完整性约束。
可以在某些情况下指定何时应检查完整性约束，例如，在 SQL 语句之后或者在一个事务结束时。

#### 完整性的可能反应方式
- 拒绝数据库操作——不执行它。
- 中止整个事务——回滚作为事务一部分的所有操作。
- 执行“维护”操作来恢复数据库合法性。

#### 完整性约束的分类
- 静态完整性约束描述了每个合法数据库实例必须满足的状态独立条件。
- 插入/删除/更新违反 IC 将被禁止。
- 四种类型的静态完整性约束：域约束、主键约束及引用完整性、语义完整性约束、断言。
- 动态完整性约束是对数据库状态变化上的谓词捕捉两个或多个状态间的条件。因此动态完整性约束是状态依赖的。
- 触发器

#### 域约束
字段必须属于正确的数据域。
使用 SQL DDL 还可以通过以下子句进一步限制属性的数据域：
- 默认值 (`DEFAULT`)：为属性提供默认值。
- 不允许空值 (`NOT NULL`)：属性不允许成为 NULL。
- 允许空值 (`NULL`)：属性的值可以是 NULL，默认为空值。

#### 示例：域约束

CREATE TABLE Student (
    sid INTEGER NOT NULL,
    name VARCHAR(20) NOT NULL,
    semester INTEGER DEFAULT 1,
    birthday DATE NULL,
    country VARCHAR(20)
);

示例插入：

INSERT INTO Student(sid, name) VALUES(123, 'Peter');

域检查约束
限制属性允许的值通过指定额外条件使用内联检查约束：

att-name sql-data-type CHECK(condition)

例子：
- 成绩只能是 'F'，'P'，'C'，'D' 或 'H'
- 年龄必须是正数

#### 用户定义的域
可以从现有数据域创建新域，带有自己的默认值和限制：

CREATE DOMAIN domain-name sql-data-type ...

示例：

CREATE DOMAIN Grade CHAR(1) DEFAULT 'P' CHECK(VALUE IN ('F', 'P', 'C', 'D', 'H'));

#### 主键约束
在 SQL 中我们使用 `PRIMARY KEY` 和 `UNIQUE` 子句指定主键约束：
一个关系可以有多个候选键，但是只有一个主键。

#### 外键和引用完整性
外键是一组引用关系中用来引用父表元组的属性集。
对于每个外键值α，必须有一个父表的对应属性值也是α。
如果强制实施所有外键约束，则实现了引用完整性。

#### 在 SQL 中的外键

CREATE TABLE Enrolled (
    sid INTEGER,
    uos CHAR(8),
    grade VARCHAR(2),
    PRIMARY KEY(sid, uos),
    FOREIGN KEY(sid) REFERENCES Student,
    FOREIGN KEY(uos) REFERENCES Unitofstudy
);

#### 强制引用完整性的 SQL 支持选项
支持删除和更新父关系上的选项：
- 默认 NO ACTION（拒绝删除/更新）
- CASCADE（也删除/更新所有指向已删除/更新元组的元组）
- SET NULL/ SET DEFAULT（设置所有指向已删除/更新元组的元组为 NULL/DEFAULT）

#### 表约束：语义完整性约束
例子：“总分介于 0 和 100 之间”，“只有课程讲师才能给出分数。”
使用 SQL CHECK 约束进行内联或作为单独命名的约束。

#### 命名完整性约束
用于命名任何完整性约束。

CONSTRAINT constraint-name constraint-definition

#### 约束检查的时间点
任何约束都可声明为：
- NOT DEFERRABLE（默认）。每次发生数据库修改时立即检查约束。
- DEFERRABLE 给予等待直到事务完成再检查约束的选择。
- INITIALLY DEFERRED 等待事务结束，然而可以在事务后期动态改变。
- INITIALLY IMMEDIATE 立即检查，然而可以在事务后期动态改变。

#### 添加/修改/移除完整性约束
使用 `ALTER TABLE` 语句添加、修改（仅适用于域约束）、移除现有的架构约束。

ALTER TABLE table-name constraint-modification

#### 断言
一些约束无法只用域约束或引用完整性约束表达；
需要更通用的完整性约束，跨多个表适用：断言。

CREATE ASSERTION assertion-name CHECK(condition)

#### 断言示例
假设要定义一个小帆船俱乐部的要求。

CREATE ASSERTION smallclub CHECK(
    (SELECT COUNT(s.sid) FROM Sailors s) +
    (SELECT COUNT(b.bid) FROM Boats b) < 10));

#### 使用一般断言
写出选择违反条件的元组查询，然后使用 `NOT EXISTS` 确保断言返回真。
结果集为空时断言才成立。

#### 一般断言示例
假设四个关系：贷款、借款人、存款人、账户。
定义断言说明每个贷款至少有一名借款人维持账户余额最低1000美元。

CREATE ASSERTION balance_constraint CHECK(
    NOT EXISTS (
        SELECT * FROM Loan 
        WHERE NOT EXISTS (
            SELECT Borrower.loan_number, Borrower.balance 
            FROM Borrower JOIN Depositor ON Borrower.customer_name = Depositor.customer_name 
            JOIN Account ON Depositor.account_number = Account.account_number
            WHERE Loan.loan_number = Borrower.loan_number AND Account.balance >= 1000
        )
    )
);

#### 触发器
触发器是一种代码段，当数据库发生特定修改并且特定条件为真时自动执行。
触发器规范由三部分组成：
- 事件（激活触发器的操作）
- 条件（确定是否执行动作）
- 动作（若条件为真时发生的操作）

#### 为何使用触发器
断言不能修改数据，需要更强大的机制来检查条件并修改数据库中的数据。
触发器可用于维护外键和语义约束，通常与 `ON DELETE` 和 `ON UPDATE` 结合使用。
还可以编码动态业务规则，监测传感器读数等。

#### 触发器示例
如果某个学生的当前评估成绩之和大于等于50，则录入等级 "P"。

CREATE TRIGGER gradeEntry AFTER INSERT OR UPDATE ON Assessment
BEGIN
    UPDATE Enrolled E SET grade='P' 
    WHERE (SELECT SUM(mark) FROM Assessment A WHERE A.sid=E.sid AND A.uos=E.uosCode) >= 50;
END;

#### 触发事件和动作
触发事件可以是 `INSERT`, `DELETE` 或 `UPDATE`。
触发器可以针对具体属性更新。
可以引用更新前后的属性值。

#### 触发粒度
- 行级触发器：每行修改后触发一次。
- 语句级触发器：针对每次触发事件触发一次，无论有多少行被修改。

#### 行级触发器示例
假设出于审计目的，定义了一个触发器以确保每次员工表中插入或更新一行时，记录修改人的用户名和时间戳，并检查员工姓名和薪水不得为空且薪水非负。

CREATE FUNCTION emp_stamp() RETURNS trigger AS $$
...
END; $emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

#### 语句级触发器示例
假设平均工资在每次插入新员工或更新员工记录时被时间戳到专用关系中。

CREATE FUNCTION Salary_Average() RETURNS trigger AS $$
...
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER RecordNewAverage AFTER UPDATE OF Salary OR INSERT ON Employee FOR EACH STATEMENT EXECUTE PROCEDURE Salary_Average();

#### 何时使用行级或语句级触发器
考虑因素包括更新成本以及触发器的执行频率。

#### 触发器级别对比
- 行级触发器：每次更新/插入/删除时执行一次，主要用于数据审核。
- 语句级触发器：每次单个 SQL 语句执行一次，用于批量修改。

#### 触发器的一些提示
- 使用 `BEFORE` 触发器——通常用于检查完整性约束。
- 能够在 SQL 架构中捕获完整性约束。
- 能够利用断言制定复杂的语义约束。
- 知道何时使用断言和 CHECK 约束。
- 能够制定简单的触发器。
- 知道行级与语句级触发器的区别。

#### 下周预告
- 高级 SQL：嵌套查询、聚合与分组。
- NULL 值。