数据库系统概念第六版记录 三

外码约束（Foreign Key Constraint）

外码（Foreign Key, FK）是关系数据库中的一个约束，它用于保证表之间的引用完整性。外码的值必须：

要么存在于被引用表的主键列中，要么为空（NULL）。

外码约束的定义
在 SQL 中，可以使用 FOREIGN KEY 语句来定义外码约束。例如：

CREATE TABLE instructor (ID INT PRIMARY KEY,name VARCHAR(50),dept_name VARCHAR(50),salary FLOAT,FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);

在这个例子中：
dept_name 是 instructor 关系中的一个外码，它引用 department 关系的 dept_name。
department.dept_name 必须是 PRIMARY KEY 或者 UNIQUE（UNIQUE：值不能重复，即所有行的 dept_name 都必须是唯一的）这样 instructor 中的 dept_name 才不会引用不存在的部门。

外码约束的影响

外码约束在数据库操作中会影响 插入（INSERT）、删除（DELETE） 和 更新（UPDATE） 操作：

1. 破坏外码约束的 INSERT
   如果 instructor 表中的 dept_name 是 FOREIGN KEY，那么插入时必须确保 department 表中已经存在该 dept_name。

错误示例（如果 Physics 还未存在于 department 表中）：

INSERT INTO instructor (ID, name, dept_name, salary)
VALUES (101, 'Alice', 'Physics', 90000);

如果 department 关系中没有 Physics，则数据库会报错：

ERROR: insert or update on table "instructor" violates foreign key constraint

正确方法（先插入 department 数据）：

INSERT INTO department (dept_name, building, budget)
VALUES ('Physics', 'Science Hall', 500000);INSERT INTO instructor (ID, name, dept_name, salary)
VALUES (101, 'Alice', 'Physics', 90000);

2. 破坏外码约束的 DELETE
   如果一个 dept_name 被 instructor 表引用，则不能直接删除该 department，否则会破坏外码约束。

错误示例：

DELETE FROM department WHERE dept_name = 'Physics';

如果 instructor 表中仍然有 dept_name = ‘Physics’ 的记录，就会报错：

ERROR: update or delete on table "department" violates foreign key constraint

解决方案：

删除 instructor 关联的记录：
DELETE FROM instructor WHERE dept_name = 'Physics';
DELETE FROM department WHERE dept_name = 'Physics';使用 ON DELETE CASCADE 级联删除：
CREATE TABLE instructor (ID INT PRIMARY KEY,name VARCHAR(50),dept_name VARCHAR(50),salary FLOAT,FOREIGN KEY (dept_name) REFERENCES department(dept_name) ON DELETE CASCADE
);
这样，如果 department 中的 dept_name 被删除，所有 instructor 关联的记录也会被自动删除。

3. 破坏外码约束的 UPDATE
   如果更新 department 表中的 dept_name，而 instructor 仍然引用它，则会导致外码约束错误。

错误示例：

UPDATE department SET dept_name = 'Math' WHERE dept_name = 'Physics';

如果 instructor 里仍然有 dept_name = ‘Physics’，则报错：

ERROR: update or delete on table "department" violates foreign key constraint

解决方案：

先更新 instructor 中的相关字段：UPDATE instructor SET dept_name = 'Math' WHERE dept_name = 'Physics';
UPDATE department SET dept_name = 'Math' WHERE dept_name = 'Physics'；使用 ON UPDATE CASCADE 级联更新：
CREATE TABLE instructor (ID INT PRIMARY KEY,name VARCHAR(50),dept_name VARCHAR(50),salary FLOAT,FOREIGN KEY (dept_name) REFERENCES department(dept_name) ON UPDATE CASCADE
);
这样，当 department 里的 dept_name 发生变化时，instructor 里的 dept_name 也会自动更新。
外码约束的选项

选项 作用

ON DELETE CASCADE	删除被引用记录时，自动删除外码关联的记录
ON DELETE SET NULL	删除被引用记录时，外码字段设为 NULL
ON DELETE RESTRICT	阻止删除被引用的记录（默认行为）
ON UPDATE CASCADE	更新被引用记录时，自动更新外码字段
ON UPDATE SET NULL	更新被引用记录时，外码字段设为 NULL

总结

外码（Foreign Key）用于保证数据一致性，它要求：
外码的值必须匹配被引用表的主键，或者为 NULL。
破坏外码约束的操作：
插入（INSERT）：插入的外码值必须存在于主表。
删除（DELETE）：不能删除主表中仍被外码引用的记录，除非使用 CASCADE 或 SET NULL。
更新（UPDATE）：不能直接更新主表中的主键，除非使用 ON UPDATE CASCADE。
如何避免外码约束错误：
先插入主表，再插入外码表。
先删除外码表，再删除主表。
先更新外码表，再更新主表。
使用 CASCADE 级联更新/删除 以自动处理引用问题。

2.第二章习题

1. 考虑图 2-14 所示关系数据库。这些关系上适当的主码是什么?

根据 图 2-14 提供的关系数据库，需要分析每个关系的适当主键：

employee(person-name, street, city)
主键：person-name（假设每个人的名字是唯一的）
works(person-name, company-name, salary)
主键：(person-name, company-name)（一个人可以在多个公司工作，因此需要联合键）
company(company-name, city)
主键：company-name（假设每个公司名称是唯一的）

2.考虑从 instructor 的 dept_name 属性到department 关系的外码约束，给出对这些关系的插入和删除示例，使得它们破坏外码约束。

外码约束：
instructor.dept_name 是外码，引用 department.dept_name。

插入：
若插入 instructor 时，其 dept_name 不存在于 department，则违反外码约束。

INSERT INTO instructor VALUES ('Alice', 'Physics', 90000);

如果 Physics 这个 dept_name 在 department 表中不存在，就会导致错误。

删除：
若删除 department 中的某个 dept_name，但 instructor 仍然引用它，则违反外码约束。
示例：

DELETE FROM department WHERE dept_name = 'Physics';

如果 instructor 里仍然有 Physics 相关的记录，这个删除操作会失败，除非 CASCADE 级联删除。

3.考虑 time_slot 关系。假设一个特定的时间段可以在一周之内出现多次，解释为什么 day 和 start_time 是该关系主码的一部分，而 end_time 却不是。

假设一个特定的时间段可以在一周内出现多次，此时的 (day, start_time) 即可作为唯一标识一个 time_slot，但 end_time 不影响唯一性，相对来说，他是冗余的。

4.在图 2-1 所示 instructor 的实例中，没有两位教师同名。我们是和否可以据此断定 name 可用来作为instructor 的超码(或主码)?

如果 name 唯一，可以用作 instructor 的主键。
如果 name 可能重复，则 ID 才是更合适的主键。

超码（候选主键）：
ID（通常为唯一标识）
name（如果系统保证唯一）
(name, dept_name)（如果教师姓名可能重复但在同一部门不重复）
结论：如果 name 唯一，它可以作为主键；否则，需要 ID 作为主键。

5.先执行 student 和 advisor 的笛卡儿积，然后在结果上执行基于谓词s_id =ID 的选择运算，最后的结果是什么? 〈采用关系代数的符号表示

student 关系与 advisor 关系执行笛卡尔积。
然后根据 s_id = ID 进行筛选（即学生 s_id 必须匹配导师 advisor 关系中的 ID）。
关系代数表达式：
σ s_id=ID (student×advisor)
结果：返回匹配的 (student, advisor) 关系对，表示哪些学生与哪些导师有关联。

6.考虑下面的表达式，哪些使用了关系代数运算的结果来作为另一个运算的输入”对于每个表达式，说明表达式的含义。

分析：
a:
σ_year≥2009 (takes)：从 takes 关系中筛选 year ≥ 2009 的记录。
⋈ student：对筛选后的 takes 结果与 student 关系进行自然连接（通常通过 ID 进行匹配）。
是否使用了关系代数运算的结果作为输入？
✅ 是，因为 takes 先经过选择操作 σ_year≥2009，然后结果作为输入进行连接 ⋈。
表达式含义：
获取2009 年及以后选课的学生信息，包括 student 和 takes 相关的属性。

b：
takes ⋈ student：先进行 takes 和 student 的自然连接。
σ_year≥2009：再从连接后的结果中选择 year ≥ 2009 的记录。
是否使用了关系代数运算的结果作为输入？
✅ 是，因为 takes ⋈ student 先进行连接，然后结果作为输入进行选择 σ_year≥2009。
表达式含义：
获取所有 student 和 takes 相关信息，并且仅保留 year ≥ 2009 的记录。

与 (a) 的区别：

(a) 先筛选 takes 后连接 student，减少连接计算量。
(b) 先连接 takes 和 student，再筛选 year ≥ 2009，可能会增加计算量。

c:
student ⋈ takes：先进行 student 和 takes 的自然连接。
Π_ID, name, course_id：再投影（保留 ID, name, course_id 这几个字段，去除其他字段）。
是否使用了关系代数运算的结果作为输入？
✅ 是，因为 student ⋈ takes 先进行连接，然后结果作为输入进行投影 Π_ID, name, course_id。
表达式含义：
获取所有选课学生的 ID、name 和 course_id，去掉其他无关字段。

7.考虑图 2-14 所示关系数据库。给出关系代数表达式来表示下列每一个查询，

a. 找出居住在“Miami”城市的所有员工姓名。
b. 找出工资在 100 000 美元以上的所有员工姓名。
c. 找出居住在 Miami”并且工资在 100 000 美元以上的所有员工姓名。

a:找出居住在 “Miami” 城市的所有员工姓名表达式：

解析：
σ_city = ‘Miami’ (employee)：先选择 city 为 “Miami” 的员工。
Π_name (…)：再投影 name，仅保留员工姓名。

(b) 找出工资在 100,000 美元以上的所有员工姓名
表达式：

解析：
σ_salary > 100000 (employee)：先选择 salary > 100000 的员工。
Π_name (…)：再投影 name，仅保留员工姓名

© 找出居住在 “Miami” 并且工资在 100,000 美元以上的所有员工姓名
表达式：

解析：
σ_city = ‘Miami’ ∧ salary > 100000 (employee)：先选择 city=‘Miami’ 且 salary > 100000 的员工。
Π_name (…)：再投影 name，仅保留员工姓名。

8.考虑图 2-15 所示银行数据库。对于下列每个查询，给出一个关系代数表达式:

a_ 找出位于” Chicago "的所有支行名字。
b. 找出在支行”Downtown”有贷款的所有贷款人姓名。

(a) 找出位于 “Chicago” 的所有支行名字
涉及的关系：
branch(branch_name, branch_city, assets)

关系代数表达式：

解析：
σ_branch_city = ‘Chicago’ (branch)：筛选 branch_city 为 “Chicago” 的支行。
Π_branch_name (…)：投影 branch_name，只返回支行名称。
(b) 找出在支行 “Downtown” 有贷款的所有贷款人姓名
涉及的关系：
loan(loan_number, branch_name, amount)（存储贷款信息）
borrower(customer_name, loan_number)（存储贷款人与贷款编号的对应关系）
关系代数表达式：

解析：
σ_branch_name = ‘Downtown’ (loan)：筛选 branch_name 为 “Downtown” 的贷款信息。
borrower ⨝ loan：将 loan 与 borrower 进行 自然连接，通过 loan_number 关联贷款人与贷款信息。
Π_customer_name (…)：投影 customer_name，只返回贷款人姓名。

9.考虑图 2-15 所示银行数据库。

a. 适当的主码是什么?

b. 给出你选择的主码，确定适当的外码。

主码解释
branch_name 是 branch 的主码，因为支行名称唯一标识一条记录。
customer_name 是 customer 的主码（假设不同客户的名字唯一）。
loan_number 是 loan 的主码，因为每个贷款号是唯一的。
borrower 需要联合主键 (customer_name, loan_number)，因为多个客户可以有不同的贷款。
account_number 是 account 的主码，每个账户有唯一的编号。
depositor 需要联合主键 (customer_name, account_number)，因为一个客户可以拥有多个账户。

外码解释
loan.branch_name 引用 branch.branch_name，表示每个贷款属于某个支行。
borrower.customer_name 引用 customer.customer_name，表示借款人必须是银行的客户。
borrower.loan_number 引用 loan.loan_number，表示借款人与贷款相关联。
account.branch_name 引用 branch.branch_name，表示每个账户属于某个支行。
depositor.customer_name 引用 customer.customer_name，表示存款人必须是银行的客户。
depositor.account_number 引用 account.account_number，表示存款人与账户相关联。

主码：
单属性主码：branch_name，customer_name，loan_number，account_number
复合主码：(customer_name, loan_number)，(customer_name, account_number)
外码：
branch_name 作为外码出现在 loan 和 account 关系中。
customer_name 作为外码出现在 borrower 和 depositor 关系中。
loan_number 作为外码出现在 borrower 关系中。
account_number 作为外码出现在 depositor 关系中。

10.考虑图 2-8 所示 advisor 关系，advisor 的主码是s_id。假设一个学生可以有多位指导老师。那么，s_id还是 advisor 关系的主码吗? 如果不是，advisor 的主码会是什么呢?

在图2-8所示的大学数据库模式中，如果假设一个学生可以有多位指导老师，那么 s_id 不能单独作为 advisor 关系的主码。因为主码必须唯一标识关系中的每一行，而一个学生对应多个指导老师会导致 s_id 重复。

为了唯一标识 advisor 关系中的每一行，主码应该由 s_id 和指导老师的唯一标识符 i_id共同组成。这样，每个学生和每个指导老师的组合将是唯一的。

因此，advisor 关系的主码应该是 (s_id, i_id)，其中 s_id 是学生的唯一标识符，i_id 是指导老师的唯一标识符。这样可以确保每个学生和指导老师的组合在 advisor 关系中都是唯一的。

11.解释术语关系和关系模式在意义上的区别。

在关系数据库（Relational Database）中，关系（Relation） 和 关系模式（Relation Schema） 是两个相关但不同的概念。

1. 关系（Relation）
   关系是数据的实际存储，它本质上是一个二维表，其中包含了具体的数据（Tuples，元组）。
   一个关系由多行（元组，Tuples）和多列（属性，Attributes）组成。
   关系是动态的，即随着数据的插入、删除和更新，关系的内容会不断变化。
   示例
   假设我们有一个 “customer” 关系，它存储了银行客户的信息：

customer_name	customer_street	customer_city
Alice	123 Main St	New York
Bob	456 Park Ave	Chicago
Charlie	789 Broadway	Miami

这个表 “customer” 是一个 关系（Relation），它存储了具体的客户信息，每一行都是一个 元组（Tuple）。
2. 关系模式（Relation Schema）
关系模式是关系的结构定义，即描述关系中数据的逻辑结构。
它规定了：

关系的名称（Relation Name）
属性的名称（Attributes）
每个属性的数据类型（Domain，属性的取值范围）
主码（Primary Key）及外码（Foreign Key） 约束

示例
对于上面的 customer 关系，它的 关系模式 可以用以下方式表示：

customer
(
customer_name: STRING
,
customer_street: STRING
,
customer_city: STRING
)
customer(customer_name: STRING,customer_street: STRING,customer_city: STRING)

这表示：

customer 是关系的名称。
customer_name，customer_street 和 customer_city 是属性。
这些属性的数据类型都是 字符串（STRING）。
可以进一步定义 customer_name 作为主码（Primary Key），以确保每个客户唯一。

关系模式定义了“表的结构”，而关系则是“表的内容”。关系模式是设计阶段的产物，而关系是运行阶段的数据集合。
也可以类比把关系模式理解为“类（Class）”，关系理解为“对象（Object）”

12.考虑图 2-14 所示关系数据库。给出关系代数表达式来表示下列每一个查询 :

a._ 找出为“First Bank Corporation ”工作的所有员工姓名。
b.，找出为“First Bank Corporation ”工作的所有员工的姓名和居住城市。
c. 找出为“First Bank Corporation "工作且挣钱超过 10 000 美元的所有员工的姓名、街道地址和居住
城市。

a. 找出为“First Bank Corporation”工作的所有员工姓名。
关系代数表达式：
π person-name (σ company-name=’First Bank Corporation’ (works))
解释：
σ company-name=’First Bank Corporation’ (works)：选择在“First Bank Corporation”工作的员工记录。
πperson-name ：投影出这些员工的姓名。

b. 找出为“First Bank Corporation”工作的所有员工的姓名和居住城市。
关系代数表达式：
π person-name, city(σ company-name=’First Bank Corporation’(works⋈employee))
解释：
σ company-name=’First Bank Corporation’(works)：选择在“First Bank Corporation”工作的员工记录。
works⋈employee：将工作记录与员工记录连接，以获取员工的居住城市。
π person-name, city：投影出这些员工的姓名和居住城市。

c. 找出为“First Bank Corporation”工作且挣钱超过10,000美元的所有员工的姓名、街道地址和居住城市。
关系代数表达式：
π person-name, street, city (σ company-name=’First Bank Corporation’∧salary>10000 (works⋈employee))
解释：
σ company-name=’First Bank Corporation’∧salary>10000(works)：选择在“First Bank Corporation”工作且工资超过10,000美元的员工记录。
works⋈employee：将工作记录与员工记录连接，以获取员工的街道地址和居住城市。
π person-name, street, city：投影出这些员工的姓名、街道地址和居住城市。

13.考虑图 2-15 所示银行数据库。对于下列每个查询，给出一个关系代数表达式:

a. 找出贷款额度超过 10 000 美元的所有贷款号。
b. 找出所有这样的存款人姓名，他拥有一个存款额大于 6000 美元的账户。
c. 找出所有这样的存款人姓名，他在“Uptown ”支行拥有一个存款额大于 6000 美元的账户。

a. 找出贷款额度超过10,000美元的所有贷款号。
关系代数表达式：

解释：
σ amount>10000(loan)：选择贷款额度超过10,000美元的贷款记录。
π loan_number：投影出这些贷款的贷款号。

b. 找出所有这样的存款人姓名，他拥有一个存款额大于6,000美元的账户。
关系代数表达式：

解释：σ balance>6000(account)：选择存款额大于6,000美元的账户记录。
account⋈depositor：将账户记录与存款人记录连接，以获取存款人姓名。
π customer_name ：投影出这些存款人的姓名。

c. 找出所有这样的存款人姓名，他在“Uptown”支行拥有一个存款额大于6,000美元的账户。
关系代数表达式：

解释：σ branch_name=’Uptown’∧balance>6000(account)：选择在“Uptown”支行且存款额大于6,000美元的账户记录。
account⋈depositor：将账户记录与存款人记录连接，以获取存款人姓名。
π customer_name：投影出这些存款人的姓名。

14.列出在数据库中引入空值的两个原因。

在数据库中引入空值（NULL） 的主要原因有以下两个：

1. 数据未知或不可用
   有些情况下，数据库中的某个属性值暂时未知，但可能会在未来变得可用。例如：

用户未提供信息：某个新注册用户可能未填写 email，导致 email 字段为空。
数据尚未收集：例如，一个病人的 体温 记录可能在当前时间未测量，因此该字段为空。
未来补充：某个产品的 折扣 可能尚未确定，因此 discount 为空。

2. 某些属性对某些记录不适用
   在某些表中，部分字段可能对某些行不适用，导致这些字段为空。例如：

公司数据库中，某些员工没有奖金，因此 bonus 字段为空。
银行账户表中，某些用户没有贷款，所以 loan_number 字段为空。
顾客可能没有填写备用电话号码，导致 alternate_phone 为空。
这两种情况导致数据库在设计时必须允许NULL 值的存在，以表示缺失或不适用的数据。

15.讨论过程化和非过程化语言的相对优点

过程化语言（Procedural Language）:过程化语言要求用户明确指定操作的执行步骤，即 “如何” 计算结果。这种语言通常包括变量、循环、条件语句等结构，允许开发者逐步构建解决方案。

特点
需要定义控制流（如循环、条件语句）。
代码通常较长，需要更详细地描述执行步骤。
常用于命令式编程（Imperative Programming）

示例

DECLAREtotal_salary NUMBER;
BEGINSELECT SUM(salary) INTO total_salaryFROM employeesWHERE department_id = 10;DBMS_OUTPUT.PUT_LINE('Total Salary: ' || total_salary);
END;

这里的 BEGIN 和 END 块定义了执行步骤，包括变量声明、查询执行和结果输出。

优点
可控性强：开发者可以精确控制计算步骤和执行顺序，适用于复杂计算和逻辑控制。
强大的流程控制：支持循环、条件语句等，使其适用于复杂任务（如事务处理、存储过程）。
更好的优化能力：可针对具体场景进行代码优化，提高执行效率。

缺点
代码冗长，开发效率相对较低。
用户需要了解数据库的具体执行机制，难以优化查询。
不适合复杂查询，如多表关联的查询，可能需要手动优化

非过程化语言（Non-Procedural Language）:非过程化语言只需要用户描述“是什么”（What），而不需要指定“如何”（How）执行查询。系统会自动决定最佳的执行计划。

特点
声明式（Declarative），只描述查询的需求。
高层次，不要求用户关心底层实现。
常用于数据库查询语言（如SQL）

示例

SELECT customer_name
FROM customer
WHERE customer_city = 'Miami';

这里不需要显式描述如何查找数据，数据库管理系统（DBMS）会自动优化执行。

优点
易学易用：用户无需关心底层操作，只需专注于查询目标。
高效执行：数据库系统可以自动优化查询，提高执行效率。
开发效率高：代码简洁，减少了编程工作量，适合数据分析和查询

缺点
控制力较弱：用户无法直接指定查询的执行策略，可能导致子优化问题。
无法处理复杂逻辑：仅适用于数据查询和简单计算，无法实现复杂的事务控制或循环操作。
性能受限：在特定情况下（如递归查询、大规模数据处理），可能需要手动优化。