数据库期中复习笔记

《数据库》期中复习

只有两个重点：查询表的方法和设计表的模式。

第二章关系数据模型

数据模型是用于描述数据或信息的标记，由数据结构、数据操作和数据上的约束条件组成。

一个关系的列被称为属性 attribute，关系名和其属性的集合称为关系的模式 schema，关系的每一行称为元组 tuple，元组的每个分量所属的类型称为域 domain，一个给定关系中的一个元组的集合叫做关系的一个实例 instance。

键 key是关系的一组属性组成的集合，通过键的定义，可以保证一个关系实例中在这组属性上的元组是唯一的。

(a) 考虑三个元组的全排列为 \(3!\) 三个属性的全排列为 \(3!\) 答案为 \(3!*3!=36\)

(b) 略

(c) 容易证明答案 \(n!m!\) 如果考虑元组出现重复情况，每种元组的个数为 \(a_1, a_2, ..., a_k\) 答案应该是 \(\frac{n!m!}{a_1!a_2!...a_k!}\)

SQL 支持的数据类型包括：

可变长度字符串 VARCHAR(n)，表示最多可有 n 个字符的字符串；固定长度字符串 CHAR(n)，表示恒定有 n 个字符的字符串。
位串 BIT(n)，表示恒定有 n 个比特的串；可变位串 BIT VARYING(n)，表示最多有 n 个比特的串。
逻辑类型 BOOLEAN，可取值为 TRUE、FALSE 或 UNKNOWN。
整数类型 INT，SHORTINT。
浮点类型 FLOAT，DOUBLE，表示浮点数；高精度的浮点类型 DOUBLE PRECISION；指定小数点后位数的 DECIMAL(n, d)，表示有 n 位有效数字，小数点在右数第 d 位。
日期和时间类型 DATE，TIME；为了区分日期和字符串类型，往往需要额外添加 DATE 关键字，比如 DATE '2020-10-19' 表示一个日期。

简单的表定义如下：

CREATE TABLE <relation name>
(
    <attribute name> <data type>,
    ...
    <attribute name> <data type>
);

删除某个关系：

DROP TABLE <relation name>;

修改关系中的某个属性（增加新的属性或者删除原有属性）：

ALTER TABLE <relation name> 
ADD <attribute name> <data type>;

ALTER TABLE <relation name> 
DROP COLUMN <attribute name>; -- MySQL 中使用 DROP COLUMN

为了避免用 NULL 填充默认值，可以在创建表或者增加新的属性的时候指定默认值：

CREATE TABLE <relation name>
(
    <attribute name> <data type> DEFAULT <default value>,
    ...
    <attribute name> <data type> DEFAULT <default value>
);

ALTER TABLE <relation name> 
ADD <attribute name> <data type> DEFAULT <default value>;

有两种方式为关系指定键：PRIMARY KEY 和 UNIQUE。PRIMARY KEY 指定的属性 S 不能有 NULL 值，但是 UNIQUE 可以允许 NULL 值。

CREATE TABLE <relation name>
(
    <attribute name> <data type> PRIMARY KEY,
    ...
    <attribute name> <data type> UNIQUE
);

(d) 答案如下：

CREATE TABLE Printer(
    model VARCHAR(10) PRIMARY KEY,
    color BOOLEAN,
    type VARCHAR(10),
    price DECIMAL(10, 2)
)

(e) 答案如下：

ALTER TABLE Printer
DROP COLUMN color;

(f) 答案如下：

ALTER TABLE Laptop
ADD od VARCHAR(10) DEFAULT 'none';

在关系上定义并、交、补运算：R 和 S 是具有同样属性集合的表，同时 R 和 S 的各个属性的域也必须匹配且有相同的顺序。

并运算：\(R \cup S\)，包含 R 和 S 中的所有元组。
交运算：\(R \cap S\)，包含 R 和 S 中共有的元组。
差运算：\(R - S\)，包含 R 中但不在 S 中的元组。

投影操作用来从关系 R 生成一个新的关系，这个关系只包含 R 中的一部分属性。投影操作的结果是一个关系，其模式是 R 的属性的一个子集。

\[\pi_{A_1, A_2, ..., A_n}(R) \]

选择操作用来从关系 R 中选择出满足某个条件的元组。选择操作的结果是一个关系（元组的集合），其模式是 R 的模式。

\[\sigma_{condition}(R) \]

笛卡尔积接受两个关系作为输入，生成一个新的关系，其模式是输入关系的元组的集合的笛卡尔积。

\[R \times S \]

自然连接是一种特殊的连接操作，它是在两个关系的笛卡尔积上应用选择操作，选择那些在两个关系的公共属性上取值相等的元组。如果一个元组不能和另一个关系中的任何元组匹配，那么这个元组被称为悬浮元组 dangling tuple。

\[R \bowtie S \]

\(\theta\) 连接是一种连接操作，它是在两个关系的笛卡尔积上应用选择操作，选择那些满足某个条件的元组。当条件是在两个关系的公共属性上取值相等时，\(\theta\) 连接和自然连接是等价的。

\[R \bowtie_{condition} S \]

有一个很有意思的等价关系可以连接笛卡尔积和自然连接：

定义条件 \(c\) 为 \(R.A = S.A \land R.B = S.B \land ...\)，\(L\) 是在关系 \(R\) 和 \(S\) 中不在 \(R\) 和 \(S\) 的属性上的属性的集合，那么：

\[R \bowtie S = \pi_L (\sigma_c(R \times S)) \]

重命名操作是为关系的属性或者关系本身指定一个新的名字。重命名操作的结果是一个新的关系，其模式是原来的模式，但是属性名或者关系名被替换为新的名字。

\[\rho_{newname}(attrname1, attrname2, ...)(R) \]

(a)

\(\pi_{model}(\sigma_{speed >= 3.00}(PC))\)

(b)

\(\pi_{maker}(\sigma_{hd >= 100}(Product \bowtie Laptop))\)

(f)
\(\pi_{hd}(\sigma_{PC1.model <> PC2.model \text{ AND } PC1.hd = PC2.hd}(\rho_{PC1}(PC) \bowtie \rho_{PC2}(PC)))\)

已经讨论了数据结构和数据操作，接下来讨论数据约束。数据约束是关系数据库中的一种重要机制，用来保证数据的完整性和一致性。

用关系代数表示约束有两种风格：

“ R 的值必须为空”的约束，与“R中没有元组”等价，用 \(R = \emptyset\) 表示。
“R 的值必须是 S 的子集”的约束，用 \(R \subseteq S\) 表示。

这两种形式是等价的，因为 \(R \subseteq S\) 显然等价于 \(R - S = \emptyset\)。

引用完整性约束 referential integrity constraint 是一种常见的约束，如果关系 R 中的某个属性（A）的值 v，人们希望这个值也是关系 S 中的某个属性（B）的值，那么这个约束就是引用完整性约束。

\[\pi_{A}(R) \subseteq \pi_{B}(S) \]

键约束 key constraint 是一种约束，要求关系中的某个属性集合是一个键，这一约束同样可以用关系代数表示。

以上面的练习为例，可以有

\[\sigma_{PC1.model = PC2.model \text{ AND } PC1.hd <> PC2.hd}(\rho_{PC1}(PC) \bowtie \rho_{PC2}(PC)) = \emptyset \]

此时，PC 的 model 构成了一个键。

通过上述的方式，我们就找到了一种彻彻底底形式化描述数据库的方法。

\[\pi_{A1, A2, ..., An}(R) - \pi_{A1, A2, ..., An}(S) = \emptyset \]

等价于

\[\pi_{A1, A2, ..., An}(R) \subseteq \pi_{A1, A2, ..., An}(S) \]

第三章关系数据库设计理论

目前来说，一个关系模式可以设计的非常奔放，但是这显然不对，如果设计的太过冗余会导致物理资源的浪费，如果设计的太过简单会导致数据的不完整性。因此，我们需要在各个关系之间建立约束来描述它们之间的关系。

函数依赖 functional dependency 是一种约束，它描述了一个属性集合的值决定了另一个属性集合的值。如果一个属性集合 X 的值决定了另一个属性集合 Y 的值，即如果两个元组在 X 上值相同，在 Y 上也必定相同，那么我们称 X 函数依赖于 Y，记作 \(X \rightarrow Y\)。

我们在前面提到了键 key的概念，在这里我们会在函数依赖的语境下给出一个更加详细的定义：认为属性集 X 是关系 R 的一个键，当且仅当：

X 决定 R 的所有其他属性
X 的任意一个真子集不能决定 R 的所有其他属性

有时一个关系可能有多个键，我们指定其中一个为主键 primary key

一个包含键的属性集合称为超键 superkey

函数依赖 FD 可以由一系列规则推导：

传递规则：如果 \(X \rightarrow Y\) 且 \(Y \rightarrow Z\)，那么 \(X \rightarrow Z\)
分解规则：如果 \(X \rightarrow YZ\)，那么 \(X \rightarrow Y\) 和 \(X \rightarrow Z\)
结合规则：如果 \(X \rightarrow Y\) 和 \(X \rightarrow Z\)，那么 \(X \rightarrow YZ\)，是分解规则的对偶
平凡函数依赖：如果 Y 是 X 的子集，那么 \(X \rightarrow Y\)；同时，也可以在右边删除那些在左边已经出现的属性，即如果 \(Z \subseteq X\)，那么\(X \rightarrow YZ\) 等价于 \(X \rightarrow Y\)
增广规则：如果 \(X \rightarrow Y\)，那么 \(XZ \rightarrow YZ\)，需要消除两边相同的属性

假设 X 是属性集合，F 是函数依赖集合，X 的闭包 \(X^+\) 使得每一个满足 F 的关系 R 也满足 \(X \rightarrow X^+\)。注意，这里讨论的闭包是“属性的闭包”，而不是关系的闭包。

用人话说，属性的闭包是一个属性集合，它保证函数依赖集合中的所有函数依赖都成立，或者形象地说，任何一个依赖都被限制在这个闭包中————就像加法之于正整数。另外，属性的闭包依赖于函数依赖集合，而不是关系实例。

属性的闭包算法：容易推导

满足下面三个条件的 FD 集合 B 被称为关系的最小化基本集 minimal basis：

B 中所有的 FD 都是不可分解的
从 B 中删除任何一个 FD 都会导致 B 不再是基本集
对于 B 中的所有 FD，如果从左边或者右边删除一个属性，那么这个 B 不再是基本集

函数依赖的投影是一个函数依赖集合的子集，它只包含某些属性的函数依赖。比如，令 \(X_1 = \pi(X)\)，那么在 X 上成立的函数依赖只有部分在 \(X_1\) 上成立，因为有些属性在 \(X_1\) 上并没有定义。

函数依赖集的投影算法：

设 T 为最终的投影集，初始化为空
对于 \(R_1\) 中的每一个子集 X，计算 X 的闭包 \(X^+\)；对于所有在闭包中也在 \(R_1\) 中的属性 Y，把所有非平凡的 FD \(X \rightarrow Y\) 添加到 T 中
构造 T 的最小化基本集

为什么算法中要计算闭包？因为通过闭包的计算可以自然建立 \(X \rightarrow Y\) 的关系，而且可以避免原 FD 集合并不是最大化（或许可以定义成函数依赖集的闭包）的情况。比如对于 \(R(A, B, C)\) 投影出 \(R_1(A, C)\)，如果原来的 FD 集合是 \(A \rightarrow B, B \rightarrow C\)，那么显然 \(A \rightarrow C\) 也是成立的，但是如果不计算闭包只从原 FD 集合中取出某个 FD，那么就会遗漏这个函数依赖。

这个方法也可以推广到计算所有的函数依赖（也就是函数依赖集的闭包），不过是这个算法的一种特例罢了。

因为任意一个属性都能决定其他两个属性，问题可以实际上转化成构造尽可能少的边使得这个有向图变成强连通图（最小树形图）。

当试图在一个关系中包含过多的信息时，产生的问题称为异常 anmoaly：

冗余：信息没有必要重复存储
更新异常：如果信息重复存储，那么更新一个信息可能会导致信息不一致
删除异常：删除一个信息可能会导致不可挽回的其他信息的丢失

Boyce-Codd范式 BCNF：关系 R 属于 BCNF 当且仅当对于 R 的每一个非平凡函数依赖 \(X \rightarrow Y\)，X 都是 R 的一个超键。换言之，每个非平凡函数依赖的左边都必须包含键。

如果一个关系不满足 BCNF，那么我们可以通过分解来消除这种异常。分解的目标是将一个关系分解成多个关系，这些关系都满足 BCNF。

BCNF分解算法：

检查 R 是否满足 BCNF，如果满足则结束
如果存在 BCNF 违例，假设为 \(X \rightarrow Y\)，计算 \(X^+\)，选择 \(R_1 = X^+\)，\(R_2 = (R - X^+) ∪ X\)
计算 \(R_1\) 和 \(R_2\) 上的投影后的函数依赖集
递归地对 \(R_1\) 和 \(R_2\) 进行检查

(a)

首先计算全部的函数依赖集

子集 \({A}\) 的闭包是 \({A}^+ = {A}\)；子集 \({B}\) 的闭包是 \({B}^+ = {B}\)；子集 \({C}\) 的闭包是 \({A, C, D}\)，那么\(C \rightarrow A\) 被加入到 FD 中；子集 \({D}\) 的闭包是 \({D}\)

子集 \({A, B}\) 的闭包是 \({A, B, C, D}\)，那么 \(A B \rightarrow D\) 被加入到 FD 中；子集 \({A, C}\) 的闭包是 \({A, C, D}\)，那么 \(A C \rightarrow D\) 被加入到 FD 中；子集 \({A, D}\) 的闭包是 \({A, D}\)，那么 \(A D \rightarrow C\) 被加入到 FD 中；子集 \({B, C}\) 的闭包是 \({A, B, C, D}\)，那么 \(B C \rightarrow D, B C \rightarrow A\) 被加入到 FD 中；子集 \({B, D}\) 的闭包是 \({A, B, C, D}\)，那么 \(B D \rightarrow A, B D \rightarrow C\) 被加入到 FD 中；子集 \({C, D}\) 的闭包是 \({A, C, D}\)，那么 \(C D \rightarrow A\) 被加入到 FD 中

子集 \({A, B, C}\) 的闭包为 \({A, B, C, D}\)，那么 \(A B C \rightarrow D\) 被加入到 FD 中；子集 \({A, B, D}\) 的闭包为 \({A, B, C, D}\)，那么 \(A B D \rightarrow C\) 被加入到 FD 中；子集 \({A, C, D}\) 的闭包为 \({A, C, D}\)；子集 \({B, C, D}\) 的闭包为 \({A, B, C, D}\)，那么 \(B C D \rightarrow A\) 被加入到 FD 中

综上所述，所有的 FD 已经求出

关系的键是 \({A, B}, {B, C}, {B, D}\)

违反 BCNF 的 FD：\({C \rightarrow D, D \rightarrow A, C \rightarrow A, CD \rightarrow A}\)

先根据 \(C \rightarrow D\) 分解，得到 \(R_1 = {A, C, D}, R_2 = {B, C}\)

\(R_1\) 的键为 \({C}\), \(D \rightarrow A\) 违反 BCNF，分解得到 \(R_3 = {A, D}, R_4 = {C, D}\)

答案为 \(R_2, R_3, R_4\)

~~打字太麻烦，剩下的题不写了~~

一个好的分解，应该具有如下的三个性质：

消除异常
信息的可恢复，如果可以通过分解的关系恢复原来的关系，那么这个分解含有无损连接 lossless join
依赖的保持，如果分解后的函数依赖集合和原来的函数依赖集合一致，那么这个分解含有保持连接性

chase检验是一种检验一个分解是否含有无损连接的方法。核心思想是利用的悬浮元组的思想，尝试从若干子关系连接起来后产生的元组恢复成原来的关系中的对应元组。

容易证明 chase 检验的有效性

无损连接和依赖保持往往不可兼得，不得不在二者之间做出选择。

(d) 对每个子关系计算原 FD 最小基本集的投影

关系 R 属于第三范式 3NF：只要 R 中的每一个非平凡函数依赖 \(X \rightarrow Y\)，或者 X 是 R 的一个超键，或者每个属于 Y 但是不属于 X 的属性都是 R 的某个键的成员（通常被称为主属性 prime）

3NF分解算法：

找出函数依赖集 F 的一个最小基本集 G
对于G中的每一个 FD \(X \rightarrow Y\)，把 \(X Y\) 作为分解出的某个关系的模式
如果第 2 步分解出的关系的模式均不包含 R 的超键，则增加一个关系，模式为 R 的任意一个键

为什么 3NF 分解算法可以保证函数依赖的保持？容易证明，最小基本集中每个 FD 都对应着分解后的某个关系模式，没有哪个 FD 被“拆分”了。

比如对于关系 \(R(A, B, C)\) \(FD: {A \rightarrow C, B \rightarrow C}\)，键为 \({A, B}\)，对于 \(A \rightarrow C\) 分解得到 \(R_1(A, C), R_2(A, B)\)，显然 \(B \rightarrow C\) 没有得到保持；这个例题也很好展示出了 BCNF 和 3NF 的区别

多值依赖 multivalued dependency MVD：\(A \rightarrow \rightarrow B\) 对于 R 中每个在所有 A 属性上的元组对 t 和 u，能在 R 中找到满足如下条件的元组 v：

v 和 t 在 A 上的值相同
v 和 u 在 B 上的值相同
v 在 R 中不属于 A 或 B 的属性上的值与 u 相同

多值依赖的推导有如下规则：

传递规则：如果 \(A \rightarrow \rightarrow B\) 且 \(B \rightarrow \rightarrow C\)，那么 \(A \rightarrow \rightarrow C\)
结合规则：如果 \(A \rightarrow \rightarrow B\) 和 \(A \rightarrow \rightarrow C\)，那么 \(A \rightarrow \rightarrow BC\)；注意多值依赖没有分解规则
FD升级规则：如果 \(A \rightarrow B\)，那么 \(A \rightarrow \rightarrow B\)
互补规则：如果 \(A \rightarrow \rightarrow B\)，那么 \(A \rightarrow \rightarrow (R - B)\)；这可以导出附加平凡多值依赖 more trivial MVD，即 \(R = {A, B}\)，那么 \(A \rightarrow \rightarrow B\) 是平凡的