附录 C：数据库题库

1. 简答题（10 题）

请简述数据库，并说明数据库管理系统的功能
- 数据库 是长期储存在计算机内、有组织、可共享的大量数据的集合。数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享
- 数据库管理系统：是位于用户和操作系统之间的一层数据管理软件
请说明数据库系统的三级模式与数据独立性
- 数据库系统的三级模式结构由外模式、模式和内模式组成
- 数据独立性包括数据的物理独立性和数据的逻辑独立性
- 外模式
  - 也称为 子模式 或 用户模式 它是数据库用户（包括程序员和最终用户）能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示
- 模式
  - 也称为 逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图
- 内模式
  - 也称 存储模式，一个数据库只有一个内模式，它是数据物理结构和存储方式的描述，是数据在数据库内部的组织方式
请简述数据完整性约束，并举例说明
- 三大完整性：
  1. 实体完整性：主码唯一且非空
  2. 参照完整性：外码要么没有，要么只有一个
  3. 用户定义完整性
    - 非空 not null
    - 列值唯一 unique
    - 条件表达式 check in ('男', '女')
- 举例略
请简述函数依赖，并举例说明完全函数依赖，部分函数依赖，传递函数依赖
- 某个属性集决定另一个属性集时，称另一属性集依赖于该属性集
- 举例：
  1. 完全依赖：通过 {学生学号，选修课程名} 可以得到 {该生本门选修课程的成绩}，而通过单独的 {学生学号} 或者单独的 {选修课程名} 都无法得到该成绩，则说明 {该生本门选修课程的成绩} 完全依赖于 {学生学号，选修课程名}
  2. 部分函数依赖：通过 {学生学号，课程号} 可以得到 {该生姓名}，而通过单独的 {学生学号} 已经能够得到 {该生姓名}，则说明 {该生姓名} 部分依赖于 {学生学号，课程号}
  3. 传递函数依赖：在关系 R(学号，宿舍，费用)中，通过 {学号} 可以得到 {宿舍}，通过 {宿舍} 可以得到 {费用}，而反之都不成立，则存在传递依赖 {学号} -> {费用}
请说明 1NF，2NF，3NF，BCNF 概念，并举例说明
- 请参考上面的知识点
请说明视图的作用
- 视图能够简化用户的操作
- 视图使用户能以多种角度看待同一数据
- 视图对重构数据库提供了一定程度的逻辑独立性
- 视图能够对机密数据提供安全保护
- 适度利用视图可以更清晰的表达查询
请说明数据库设计的步骤及任务
1. 需求分析：分析用户的需求，包括数据、功能和性能需求
2. 概念结构设计：主要采用 E-R 模型进行设计，包括画 E-R 图
3. 逻辑结构设计：通过将 E-R 图转换成表，实现从 E-R 模型到关系模型的转换
4. 物理结构设计：主要是为所设计的数据库选择合适的存储结构和存取路径
5. 数据库实施：包括编程、测试和试运行
6. 数据库运行和维护：系统的运行与数据库的日常维护
请说明在数据库设计过程中将 E-R 模型转换为关系数据模型的转换原则
- E-R 图中的每个实体，对应一个关系，该关系具有属性为实体的全部属性，关键字为实体的关键字
请说明针对不同故障恢复的策略
1. 事务故障的恢复就是利用日志文件撤销此事务已对数据库进行的修改
2. 系统故障的恢复就是要撤销故障发生时未完成的事务，重写已完成的事务
3. 介质故障的恢复就是重装数据库，然后重写已完成的事务
请举例说明丢失修改的调度序列，并将其修改为可串行化调度
- 略，参见上面的知识点

2. 计算题

2.1 关系代数计算

$R:$

A	B
1	5
2	6
3	7

$S:$

A	C	D
5	5	1
2	6	2
6	7	3

$\rm \Pi_{A,C}$
$\rm R \Join S$
$\rm \sigma_{A=1}$

2.2 概念模型

学校中有若干系，每个系有若干班级和教研室，每个教研室有若干教员，其中有的教授和副教授每人各带若干研究生，每个班有若干学生，每门课可由若干学生选修。请用 E-R 图画出该学校的概念模型，并转换为关系模型，表明主外码。

3. 应用题

3.1 关系代数和 SQL 编写

有关系模式四个：

供应商关系：S(Sno , Sname, Saddr)
零件关系：P(Pno, Pname, Color, Weight)
工程关系：J(Jno, Jname, Jcity, Balance)
供应关系：SPJ(Sno, Pno, Jno, Price, Qty)

用关系代数（1-3）和 SQL（4-10）完成如下查询：

检索没有发生任何供应关系的零件编号
$\rm \Pi_{Pno}(P) - \Pi_{Pno}(SPJ)$
检索使用了编号为 P3 或 P5 零件的工程编号 Jno
$\rm \Pi_{Jno}(\sigma_{Pno=\text{`P3'}\lor Pno=\text{`P5'}}(SPJ))$
检索使用了红色零件的工程号 Jno 和工程名 Jname
$\rm \Pi_{Jno,\,Jname}(\sigma_{Color=\text{`红色'}}(P \Join SPJ \Join J))$

定义表 SPJ，主码：(Sno, Pno, Jno)，外码 Sno、Pno，Jno

CREATE TABLE SPJ (
    Sno CHAR(4),
    Pno CHAR(4),
    Jno CHAR(8),
    Price DECIMAL(6, 1),
    Qty DECIMAL(8, 2),
    PRIMARY KEY (Sno, Pno, Jno),
    FOREIGN KEY (Sno) REFERENCES S(Sno),
    FOREIGN KEY (Pno) REFERENCES P(Pno),
    FOREIGN KEY (Jno) REFERENCES J(Jno)
);

定义使用红色零件的供应商名称和地址的视图

CREATE VIEW S_1 AS
SELECT Sname, Saddr FROM SPJ, P, S
WHERE SPJ.Sno = S.Sno AND SPJ.Pno = P.Pno AND Color = '红色';

将供应商 S5 提供的零件 P4 的价格提高 6%

UPDATE SPJ SET Price = Price * 1.06 WHERE Sno = 'S5' AND Pno = 'P4';

删除供应商号为 S4 的所有供应商记录

DELETE FROM SPJ WHERE Sno = 'S4';
DELETE FROM S WHERE Sno = 'S4';

检索不使用编号为 P3 零件的工程编号 Jno 和工程名称 Jname

SELECT Jno, Jname FROM J WHERE Jno NOT IN (
    SELECT Jno FROM SPJ WHERE Pno = 'P3'
);

检索至少使用了编号为 P3 和 P5 零件的工程编号 Jno

SELECT Jno FROM SPJ WHERE Pno = 'P3' AND Jno IN (
    SELECT Jno FROM SPJ WHERE Pno = 'P5'
);

检索使用了全部零件的工程名称 Jname

SELECT Jname FROM J WHERE NOT EXISTS (
    SELECT * FROM P WHERE NOT EXISTS (
        SELECT * FROM SPJ WHERE SPJ.Pno = P.Pno AND SPJ.Jno = J.Jno
    )
);

3.2 关系的函数依赖和范式

关系模式：销售（时间，顾客编号，顾客名，商品编号，商品名，数量，单价）。语义：任何时间，对于任何顾客，同一商品的单价相同。

列出函数依赖
请判断该关系模式最高为第几范式，并说明理由
请把上面的关系模式转化为 3NF，说明原因并指明主码

解答：

(1)

\begin{aligned} F &= \{ \\ & \text{顾客编号} \to \text{顾客名}, \\ & \text{商品编号} \to \text{商品名}, \\ & \text{商品编号} \to \text{单价}, \\ & \text{(时间, 顾客编号, 商品编号)} \to \text{数量} \\ \} \end{aligned}

(2)

最高为 1NF，由于上述函数依赖集为：

L 类：时间，顾客编号，商品编号
R 类：顾客名，商品名，单价，数量
N 类： $\{\}$
LR 类： $\{\}$

所以

\begin{aligned} & (\text{时间},\,\text{顾客编号},\,\text{商品编号})_F^+ \\ & = ( \text{时间},\, \text{顾客编号},\, \text{商品编号},\, \text{顾客名},\, \text{商品名},\, \text{单价},\, \text{数量} ) \\ & = U \end{aligned}

那么关系模式的码为：（时间，顾客编号，商品编号），由于非主属性顾客名对码是部分函数依赖，所以不符合 2NF。

(3)

顾客（顾客编号，顾客名）
商品（商品编号，商品名，单价）
销售（时间，顾客编号，商品编号，数量）

非主属性顾客名对码不存在部分函数依赖，也不存在传递函数依赖，所以是 3NF。

4. 其他相关题目

4.1 填空题

数据模型通常由数据结构，数据操作，完整性约束三个要素组成
有了外模式/模式可以保证数据和应用程序之间的逻辑独立性；有了内模式/模式，可以保证数据和应用程序之间的物理独立性
$\rm\sigma_{F_1}(\sigma_{F_2}(E))$ 等价于 $\rm\underline{\sigma_{F_1 \land F_2}(E)}$
关系模式进行投影运算后属性数小于或等于原属性数
SQL 的数据定义语句包括：定义表，定义视图，定义索引等
在 SQL 查询时，如果要去掉查询结果中的重复元组，需使用 distinct
关系完备的系统支持关系数据结构，和所有关系代数操作
事务具有原子性、一致性、隔离性、持续性的特性
在数据库系统中，定义存取权限称为授权
关系数据库规范化要解决的问题是插入异常，删除异常，更新异常

4.2 关系模型

有一课程管理系统：一个系可开设多门课程，学生可选修多门课程，一名教师只教一门课程，但一门课程可有几名教师开设。

试画出 E-R 图并标注属性
转换成关系模型
- Dept(Deptno, Deptname, Dmanger)
- Student(Sno, Sname, Sage, Deptno)
- Teacher(Tno, Tname, Deptno, Cno)
- Course(Cno, Cname, Credit, Deptno)
- SC(Sno, Cno, Grade)

4.3 关系模式

写出3个关系模式分别满足：

是 1NF，不是 2NF
是 2NF，不是 3NF
是 3NF，也是 BCNF

并说明所写的关系模式是前者，不是（或也是）后者。

答：

学生选课（学号，姓名，课程号，成绩）
- 属性不可分，是 1NF；存在非主属性对键码的部分依赖，不是 2NF
学生（学号，姓名，系别，系主任）
- 码为单属性，不存在部分依赖，是 2NF，存在非主属性对键码的传递依赖，不是 3NF
学生（学号，姓名，年龄）；选修（学号，课程号，成绩）
- 非主属性对键码不存在部分依赖和传递依赖，是 3NF，主属性对键码不存在部分依赖和传递依赖，主属性对非主属性不存在函数依赖是 BCNF

4.4 并发和故障恢复

试述系统故障的恢复的策略和方法。

策略：要撤销故障发生时未完成的事务，重做已完成的事务。

步骤：

正向扫描日志文件，找出在故障发生前已经提交的事务记录，将其事务标识记入重做队列。同时找出故障发生时尚未完成的事务，将其事务标识记入撤销队列
对撤销队列中的各个事务进行撤销处理
对重做队列中的各个事务进行重做处理

并发操作可能导致哪几种数据不一致的现象？采用什么协议解决这几种数据不一致现象？

并发操作可能导致

修改丢失
读 “脏” 数据
不可重复读

3 种数据不一致现象。采用一级封锁协议可解决丢失修改的问题，二级封锁协议既可解决丢失修改的问题，也可解决读 “脏” 数据的问题，而三级封锁协议可解决上述 3 种数据不一致现象。

4.5 候选码和最小依赖集

设关系模式 $R(ABCD)$ 上 FD 集为 $F$ ， $F = \{AB \to C,\, A\to B,\,B \to A\}$ 。

试求 $R$ 的候选码
试求 $F_m$

解答：

(1)

L 类： $\{\}$
R 类： $C$
N 类： $D$
LR 类： $A,\,B$

$R$ 的候选码为 $\{(BD),\,(AD)\}$ 。

(2)

F_m = \{ B \to C,\, A \to B\,\, B \to A \}

4.6 事务调度

设 $T_1,\,T_2$ 是如下两个事务，其中 $A$ 、 $B$ 为数据库中某个数据项，设 $A$ 的初值为 $50$ ， $B$ 的初值为 $20$ 。

\begin{aligned} T_1 &: A := B * 10 \\ T_2 &: B := A + 10 \end{aligned}

若允许这二个事务并行执行，试给出：

不可串行化的调度
以及一个遵守两段锁协议的调度

解答：

串行结果：

$T_1 T_2:A=200,\,B=210$
$T_2 T_1:A=600,\,B=60$

（1）不可串行化的调度

$T_1$	$T_2$
$\rm read(B)$
	$\rm read(A)$
$\rm A=B \times 10$
$\rm write(A)$
	$\rm B=A+10$
	$\rm write(B)$

（2）遵守两段锁协议的调度

$T_1$	$T_2$
$\rm Slock(B)$
$\rm read(B)$
$\rm Xlock(A)$
	$\rm Slock(A)$
$\rm A=B \times 10$	$\rm wait$
$\rm write(A)$	$\rm wait$
$\rm commit$	$\rm wait$
$\rm unlock(B)$	$\rm wait$
$\rm unlock(A)$	$\rm wait$
	$\rm read(A)$
	$\rm Xlock(B)$
	$\rm B=A+10$
	$\rm write(B)$
	$\rm commit$
	$\rm unlock(B)$
	$\rm unlock(A)$

（3）遵守两段锁协议但发生死锁的调度

$T_1$	$T_2$
$\rm Slock(B)$
$\rm read(B)$
	$\rm Slock(A)$
	$\rm read(A)$
$\rm Xlock(A)$
$\rm wait$	$\rm Xlock(B)$
	$\rm wait$

# 附录 C：数据库题库

# 1. 简答题（10 题）

# 2. 计算题

# 2.1 关系代数计算

# 2.2 概念模型

# 3. 应用题

# 3.1 关系代数和 SQL 编写

# 3.2 关系的函数依赖和范式

# 4. 其他相关题目

# 4.1 填空题

# 4.2 关系模型

# 4.3 关系模式

# 4.4 并发和故障恢复

# 4.5 候选码和最小依赖集

# 4.6 事务调度