数据库系统过程性考核

(1) 关系代数表达式：

$\pi_{sname}(\sigma_{obtain\_date='2025-02-25'}(SC) \bowtie Staff)$

(2) SQL 语句：

-- SQL1（笛卡尔积 + 选择）
SELECT sname
FROM SC, Staff
WHERE SC.sno = Staff.sno AND obtain_date = '2025-02-25';

-- SQL2（子查询 IN）
SELECT sname
FROM Staff
WHERE sno IN (
    SELECT sno
    FROM SC
    WHERE obtain_date = '2025-02-25'
);

-- SQL3（EXISTS）
SELECT S.sname
FROM Staff S
WHERE EXISTS (
    SELECT *
    FROM SC R
    WHERE R.obtain_date = '2025-02-25' AND R.sno = S.sno
);

(1) 关系代数表达式：

$\pi_{sname}(\sigma_{cissuer='\text{省教育厅}'}(Certificate) \bowtie SC \bowtie Staff)$

(2) SQL 语句：

-- SQL1（等值连接）
SELECT sname
FROM Certificate, Staff, SC
WHERE SC.sno = Staff.sno AND Certificate.cno = SC.cno AND cissuer = '省教育厅';

-- SQL2（嵌套子查询 IN）
SELECT S.sname
FROM Staff S
WHERE S.sno IN (
    SELECT R.sno
    FROM SC R
    WHERE R.cno IN (
        SELECT C.cno
        FROM Certificate C
        WHERE C.cissuer = '省教育厅'
    )
);

关系代数表达式：

$\pi_{obtain\_date}(\sigma_{slevel='Senior'}(Staff) \bowtie SC \bowtie Certificate)$

SQL 语句：

SELECT obtain_date
FROM Staff, SC, Certificate
WHERE SC.sno = Staff.sno AND Certificate.cno = SC.cno AND slevel = 'Senior';

(1) 关系代数表达式：

代数1：

$\pi_{sname}(Staff \bowtie SC)$

代数2：

$\pi_{sname}(\sigma_{sno \in (\pi_{sno}(SC))}(Staff))$

(2) SQL 语句：

-- SQL1（DISTINCT）
SELECT DISTINCT sname
FROM Staff, SC
WHERE Staff.sno = SC.sno;

-- SQL2（JOIN）
SELECT DISTINCT sname
FROM Staff
JOIN SC ON Staff.sno = SC.sno;

(1) 关系代数表达式：

$\pi_{sname}((\sigma_{cname='\text{教师证}'}(Staff \bowtie SC \bowtie Certificate)) \cup (\sigma_{cname='\text{会计证}'}(Staff \bowtie SC \bowtie Certificate)))$

(2) SQL 语句：

-- SQL1（OR 条件）
SELECT sname
FROM SC, Certificate, Staff
WHERE SC.sno = Staff.sno AND SC.cno = Certificate.cno
  AND (cname = '教师证' OR cname = '会计证');

-- SQL2（UNION）
SELECT sname
FROM Staff, SC, Certificate
WHERE SC.sno = Staff.sno AND Certificate.cno = SC.cno AND cname = '教师证'
UNION
SELECT sname
FROM Staff, SC, Certificate
WHERE SC.sno = Staff.sno AND Certificate.cno = SC.cno AND cname = '会计证';

(1) 关系代数表达式：

代数1：除法思想的图示表示

代数2：除法思想的图示表示

(2) SQL 语句：

-- SQL1（双重 IN）
SELECT DISTINCT sname
FROM Staff
WHERE sno IN (
    SELECT sno FROM sc
    JOIN Certificate ON sc.cno = Certificate.cno
    WHERE Certificate.cname = '教师证'
)
AND sno IN (
    SELECT sno FROM sc
    JOIN Certificate ON sc.cno = Certificate.cno
    WHERE Certificate.cname = '会计证'
);

-- SQL2（INTERSECT）
SELECT DISTINCT sname
FROM (
    SELECT Staff.sno, sname, cname
    FROM Staff
    JOIN sc ON Staff.sno = sc.sno
    JOIN Certificate ON sc.cno = Certificate.cno
) AS Temp
WHERE cname = '教师证'
INTERSECT
SELECT DISTINCT sname
FROM (
    SELECT Staff.sno, sname, cname
    FROM Staff
    JOIN sc ON Staff.sno = sc.sno
    JOIN Certificate ON sc.cno = Certificate.cno
) AS Temp
WHERE cname = '会计证';

-- SQL3（GROUP BY + HAVING）
SELECT sname
FROM Staff
JOIN sc ON Staff.sno = sc.sno
JOIN Certificate ON sc.cno = Certificate.cno
WHERE cname IN ('教师证', '会计证')
GROUP BY sname
HAVING COUNT(DISTINCT cname) = 2;

-- SQL4（双重 EXISTS）
SELECT sname
FROM Staff S
WHERE EXISTS (
    SELECT 1
    FROM sc SC
    JOIN Certificate C ON SC.cno = C.cno
    WHERE SC.sno = S.sno AND C.cname = '教师证'
)
AND EXISTS (
    SELECT 1
    FROM sc SC
    JOIN Certificate C ON SC.cno = C.cno
    WHERE SC.sno = S.sno AND C.cname = '会计证'
);

(1) 关系代数表达式：拿到教师证的员工 − 拿到会计证的员工

(2) SQL 语句：

-- SQL1（EXCEPT）
SELECT S.sname
FROM Staff S
WHERE S.sno IN (
    SELECT R1.sno
    FROM SC R1, Certificate C1
    WHERE R1.cno = C1.cno AND C1.cname = '教师证'
    EXCEPT
    SELECT R2.sno
    FROM SC R2, Certificate C2
    WHERE R2.cno = C2.cno AND C2.cname = '会计证'
);

-- SQL2（NOT IN）
SELECT DISTINCT sname
FROM Staff, SC, Certificate
WHERE Staff.sno = SC.sno
  AND SC.cno = Certificate.cno
  AND Certificate.cname = '教师证'
  AND Staff.sno NOT IN (
    SELECT SC.sno
    FROM SC, Certificate
    WHERE SC.cno = Certificate.cno
      AND Certificate.cname = '会计证'
);

SELECT sname
FROM Staff, SC
WHERE Staff.sno = SC.sno
GROUP BY sname
HAVING COUNT(DISTINCT SC.cno) >= 2;

解析：第一步先前三行利用两个表的自然连接，选出拿过证书的员工；第二步在查询结果按照 sname 分组，且约束条件是在 SC 表出现的证书是不重复出现 2 个。

关系代数表达式：

$\pi_{sname}(\sigma_{sage > 40}(Staff) - (\sigma_{sage > 40}(Staff) \bowtie SC))$

SQL 语句：

SELECT sname
FROM Staff
WHERE sage > 40
  AND sno NOT IN (SELECT sno FROM SC);

SELECT sno, AVG(score)
FROM SC, Certificate
WHERE SC.cno = Certificate.cno
  AND score BETWEEN 60 AND 90
  AND cissuer LIKE '省%'
GROUP BY sno;

(1) 关系代数表达式：除法运算思想图示

(2) SQL 语句：

-- SQL1（NOT EXISTS 双重否定）
SELECT sname
FROM Staff
WHERE NOT EXISTS (
    SELECT * FROM Certificate
    WHERE NOT EXISTS (
        SELECT * FROM SC
        WHERE SC.sno = Staff.sno AND SC.cno = Certificate.cno
    )
);

-- SQL2（GROUP BY 计数）
SELECT sname
FROM Staff
WHERE sno IN (
    SELECT sno FROM SC
    GROUP BY sno
    HAVING COUNT(DISTINCT cno) = (SELECT COUNT(*) FROM Certificate)
);

(1) 关系代数表达式：除法思路

• 先从 Certificate 中提取教师证的元组，再投影到 cno 上
• 再从 SC 中投影到 sno 和 cno，通过除运算获得拿到教师证的员工号
• 最后和 Staff 做连接，因为题干求的是姓名，所以投影到姓名 sname

(2) SQL 语句：

SELECT sname
FROM Staff
WHERE EXISTS (
    SELECT *
    FROM SC
    WHERE SC.sno = Staff.sno AND SC.cno = C.cno
      AND Certificate.cname = '教师证'
);

SELECT sname, sage
FROM Staff first
WHERE first.sage = (
    SELECT MAX(sage)
    FROM Staff second
    WHERE first.sage = second.sage
);

解析：通过 Staff 表的自我连接把这张表复制一遍，连接后的表共 10 列，连接过程中让共同属性 sage 的值相同的元组才能连接，通过父子查询关联第一张表的 sage 值和第二张表的最大值。最后按照题干要求，“的” 右侧求的是姓名和年龄，所以 SELECT 后方写 sname 和 sage。

SELECT s1.sname
FROM Staff s1
WHERE s1.sage > (
    SELECT MAX(s2.sage)
    FROM Staff s2
    WHERE s2.slevel = 'senior'
);

类似课本实例 3.57。

SELECT slevel, MIN(sage)
FROM Staff
WHERE sage >= 18
GROUP BY slevel
HAVING COUNT(sno) >= 5;

（1）违反，因为引用的课程表中没有 ‘C203’ 这门课。

（2）违反，因为插入元组和已有元组的主码重复。

（3）不违反，虽然插入元组的后三个属性和已有元组的后三个属性对应值一样，但这三个属性没有唯一性的约束。

（4）违反，因为插入的值 ‘3.5’ 违反了学分是整数型的用户定义完整性约束。

INSERT，DELETE，UPDATE。

整个第五章都是针对数据库的增删改三大操作进行的，参考课本 169 中间偏下触发器的章节，第（4）点触发事件可以在 INSERT、DELETE 或 UPDATE 进行时发生。

3NF。

思路：题目中是全码 all-key 的情形，如 185 例 6.9 所示，而 2NF 和 3NF 分别指的是非主属性不存在部分依赖和传递依赖，连非主属性都没有所以肯定满足 2NF 和 3NF；

然而，是否是 BCNF？按照 184 页 BCNF 的定义第二条「所有主属性对每一个不包含它的码也是完全函数依赖」，这句话言外之意主属性内部之间不能有部分依赖，所以 BCNF 是否满足未知。

综上，满足 3NF。

CE。

思路1（最笨）：

课本 181 中 6.2.2 码的定义里，候选码是属性集合的一个子集，它可以唯一地标识关系中的每一行（或称为元组）。一个关系模式可能有多个候选码，但每个候选码都必须满足两个条件：

• 唯一性：候选码必须能够唯一地标识关系中的每个元组。
• 最小性：候选码是不可约的，即移除候选码中的任何一个属性，它就不再能唯一地标识每个元组。

综上，候选码是超码（超码能够唯一确定每个元组的属性集合）的最小集合，即在保持唯一性的同时，没有冗余的属性。

所以从属性组合从个数从小到大地挨个尝试：

能否候选码只有一个属性的话通过依赖蕴含 F 推导出全部的 U？依次尝试 A，B，C，D，E，P

• 只已知 A，依据 F 中的 A → B，可以推导出 B，但依赖蕴含 F 中再无其他由 A 和 B 出来的箭头，所以已知 A 只能推导出 B，而 U 的其他属性列 {CDEP} 都推导不出来，候选码只有 A 不符合；
• 同理，只已知 B，U 的其他属性列 {ACDEP} 都推导不出来，候选码只有 B 不符合；
• 同理，只已知 C，根据 C → P，所以只能推导出 P，U 的其他属性列 {ADE} 都推导不出来，候选码只有 C 不符合；
• 同理，只已知 D，U 的其他属性列 {ABCEP} 都推导不出来，候选码只有 D 不符合；
• 同理，只已知 E，根据 E → A，所以只能推导出 A，U 的其他属性列 {BCDP} 都推导不出来，候选码只有 E 不符合；
• 同理，只已知 P，U 的其他属性列 {ABCDE} 都推导不出来，候选码只有 P 不符合；

综上候选码只有一个属性列都不符合要求。

能否候选码有 2 个属性的话通过依赖蕴含 F 推导出全部的 U？

CE 完全可以，因为 C 可以推导出 P，而 E 可以推导出 A，CE 可以推导出 D，这是第一轮迭代（依据 191 页末尾算法 6.1）；（CE）+（PAD）= ACDEP，只缺少 B 了；

第二轮迭代，上轮迭代中 A 经过推导已经变成了已知，根据 A → B，本轮迭代（ACDEP）+（B）= ABCDEP，此时 U 全部属性列已全部推导出来了，所以 CE 是候选码满足条件。

候选码有两个属性列 CE 足以推导出全部的 U，所以三个及三个以上属性列的组合不再尝试，因为候选码要求最小集合且没有冗余属性列。

因为候选码可能有其他组合，考虑其他任意两个属性组合是否可以通过依赖蕴含 F 推导出全部的 U？

• 已知 AB，无法推导出 CDEP；
• 已知 AC，只能推导出 BP，无法推导出 DE；
• 已知 AD，只能推导出 B，无法推导出 CEP；
• 已知 AE，只能推导出 B；
• 已知 AP，只能推导出 B；
• 已知 BC，只能推导出 P；
• 已知 BD，什么都推导不出来；
• 已知 BE，只能推导出 A，而 CDP 无法推导出来；
• 已知 BP，什么都推导不出来；
• 已知 CD，只能推导出 P，而 ABE 推导不出来；
• 已知 CE，可全部推导，符合候选码要求；
• 已知 CP，什么都推导不出来；
• 已知 DE，可推导出 A，再由 A 可以推导出 B，而 CD 不可以；
• 已知 DP，什么都推导不出来；
• 已知 EP，可推导出 A，再由 A 可以推导出 B，而 CD 不可以。

上述是所有已知 2 个属性列，只有 CE 符合候选码推导出全部 U 的要求。

思路2（最快）

观察依赖集合 F = {A → B, C → P, E → A, CE → D} 的箭头左侧有哪些属性列记作 L，箭头右侧有哪些属性列记作 R，箭头左右侧都出现过的属性列记作 LR，因此有

• L：CE
• R：BP
• LR：A

候选码不允许在箭头右侧出现过，所以只能从 L 中选，所以候选码是 CE。

（1）A 的闭包是 ABCDE，B 的闭包是 BCD，C 的闭包是 CD。

（2）A 可以推导出全部属性，所以 A 是候选码。

（3）包含候选码的属性组合都是超码：

• 单个属性：A；
• 两个属性：AB、AC、AD、AE；
• 三个属性：ABC、ABD、ABE、ACD、ACE、ADE；
• 四个属性：ABCD、ABCE、ABDE、ACDE；
• 五个属性：ABCDE。

（1）候选码：（A，E）

（A，E）是候选码，因为它们可以决定所有属性，即 (A, E)⁺ ⊇ R。

思路 1：候选码要求可推导出全部 R，且属性列个数最少。

同理第 1 大题的第 9 小题，先考虑单个属性列 ABCDE 中只知道一个属性列是否可以推导出全部 R？都做不到，因为 F 中的 CE → D 已经说明必须同时知道 CE 才能推导出 D；接下来考虑两个属性列的组合，CE 同时知道才能推导出 D 但推导不出 A，所以同时知道 CE 无法推导出全体 R，A 可以推导出 C，所以知道 AE 可以推导出全部 R，AE 符合候选码条件；ACE 虽然符合可以推导出全部 R 的要求，但比 AE 多了冗余列 C，按照候选码选最少属性列个数的原则，ACE 不如 AE。

思路 2：观察函数依赖集合 F = {A → B，B → C，CE → D}，箭头左侧有哪些属性列记作 L，箭头右侧有哪些属性列记作 R，箭头左右侧都出现过的属性列记作 LR，因此有：

• L：AE
• R：CD
• LR：B

所以候选码是 AE，因为候选码只允许在箭头左侧出现。

（2）第一范式（1NF）

凡是要求判断第几范式的题目均默认 1NF 满足，所以满足第一范式；

因为第（1）小题已证明 AE 是候选码，而候选码可以推导出一切，所以 AE → B 成立；但 F 中居然有 A → B，第二范式不允许有非主属性的部分函数依赖，所以不符合 2NF 要求。

综上，此关系属于第一范式（1NF），因为存在非主属性的部分函数依赖。如 A，E 决定 B，又 A 决定 B，所以 A，E 部分函数确定 B。

（3）3NF 分解

R 拆分成 R1(A, B, C) 和 R2(C, D, E)：消除了部分函数依赖关系。

R1 继续拆分成 R11(A, B) 和 R12(B, C)：消除了 R1 中传递函数依赖关系。

思路：只要题目中要求分解关系 R = (ABCDE)，说明本质要拆的是函数依赖集 F = {A → B，B → C，CE → D}；

一个关系表可能有多个候选码，主码在候选码里选，但本题在第（1）问得出候选码只有 AE，所以 AE 是主码，AE 可以推导出 R 关系中的一切其他属性列；

第（2）问中已经得出不满足 2NF，是因为既有 AE → B 这个箭头又有 A → B 这个箭头，为消除这个部分依赖，需要考虑如何将 F 分解且 EB 两个属性列不能在一起，生成两个子关系表：

• R1：A → B
• R2：CE → D

那剩下 F 中的 B → C 归 R1 还是 R2 呢？

假设归 R2，则有

• R1：A → B
• R2：CE → D，B → C

但是在这种情况下，候选码仅仅是 AE 就不够了，R2 还需要将 B 纳入候选码，也就是 ABE。

所以 B → C 归 R1 合适，则有

• R1：A → B，B → C // 已知 A 就可以推导出 BC
• R2：CE → D // 已知 E 再加上 R1 推导出的 C，就可以推出 D

所以将 R 拆分成 R1(ABC) 和 R2(CDE)。

R1(ABC) 将 A → B，B → C 拆开，来消除非主属性 B 的传递依赖，此时 A → B 组合成 R11(A, B) 而 B → C 组合成 R12(B, C)。

（1）不属于 3NF

因为存在传递依赖，职工号 → 单位号，单位号 → 单位名，职工号 → 单位名。

（2）属于 2NF，不属于 3NF

是 2NF，不存在非主属性对码的部分函数依赖。

思考：

• 第一范式（1NF）：每个字段都是原子的，没有重复的组，所以 R 满足 1NF。
• 第二范式（2NF）：没有字段依赖于候选码的一部分，所以 R 满足 2NF。
• 第三范式（3NF）：由于存在传递依赖，R 不满足 3NF。

因此，关系 R 属于 2NF，但不满足 3NF。

（3）模式分解为 3NF

分解为两个关系：

• R1(职工号，职工名，年龄，性别，单位号)
• R2(单位号，单位名)

然后将原关系 R 中的记录与这两个新关系关联：

• 对于职工表，保留「职工号」、「职工名」、「年龄」、「性别」、「单位号」。
• 对于单位表，保留「单位号」和「单位名」。

当需要引用单位信息时，通过「单位号」在职工表和单位表之间建立联系。

通过这种方式，消除了「单位名」对「职工号」的传递依赖，从而将关系 R 规范化为 3NF。

（1）ER 图

依据课本 217 页中间，五个实体所以有五个矩形，六个联系所以有六个菱形，先将这两类连接起来，最后用椭圆形表示的属性附着到各实体和各联系上；班长的本质是学生，所以要用 217 页图 7.8 单个实体的一对多联系，系主任本质是教师，所以要用同样的单个实体一对多联系；负责是包含三个实体的多对多联系，参考 217 页图 7.7（b），所以三个连线上要有 n、m 和 p。

扣分标准（自 2009 年计算机考研全国统考开始，2009 年至 2023 年各大双一流大学考研复试阅卷标准）：漏一个矩形/菱形/椭圆形则分数全扣，写错一个联系类别（例如 1 对 1 写成 1 对 n，包括 n、m 和 p 写成 n、n 和 n）则分数全扣。

（2）关系模型（码用下划线标出）

扣分标准：忘记写码则分数全扣，上一小题 ER 图错误则本小题肯定不对所以分数全扣。

答案 1（最不易出错）

学生（学号，姓名，年龄，出生地，班长的学号）

班级（班级号，班级名，自习室楼号，宿舍地址）

课程（课程号，课程名，上课地点）

教师（教师号，教师名，系名，系主任的教师号）

教材（教材号，ISBN 号，教材名，出版社名，出版日期）

组织（学号，班长的学号）

属于（学号，班级号，成立时间）

代表（班长的学号，班级号）或 代表（班长的学号，班级号）

选课（学号，课程号）

负责（课程号，教师号，教材号，授课时间）

督导（教师号，系主任的教师号）

思路：

依据 232 页首段的最后一句「一个实体转换为一个关系表，则关系的属性就是实体的属性，关系的码就是实体的码」，所以五个实体无脑连同属性转成关系表，题干中第一个属性被标为码，注意同一实体下属性的顺序不要换，字不要写错。

难点在于后六个联系。

第一种 1:1 联系要获取两端实体的码，本题中是「代表」，而「代表」连接的是班长（本质是学生实体）和班级实体，这两个实体的码分别是班长的学号和班级号，所以有：代表（班长的学号，班级号）。

1:1 关系中谁当码？按照 232 页第三段的第三行中间「每个实体的码均是该关系的候选码」所以谁当码均可，所以码任选其一。

第二种 1:n 联系同样获取两端实体的码，本题中是「组织」「属于」「督导」，获取两端实体的码，所以有：

• 组织（学号，班长的学号）
• 属于（学号，班级号，成立时间）← 勿忘此处题干中「属于」有自己的属性「成立时间」
• 督导（教师号，系主任的教师号）

依据 232 页第四段末尾句「关系的码唯 n 端实体的码」，所以 1:n 联系两端中 n 标记在哪个实体上，则 1:n 关系的码是该实体的码。

第三种 n:m 联系同样获取两端实体的码，本题中是「选课」，所以将「学生」实体的码「学号」和「课程」实体的码「课程号」组合起来：选课（学号，课程号）。

按照 232 页第五段末尾句「各实体的码组成关系的码」，意味着两者共同组成码，所以选课（学号，课程号）。注意要跟本页倒数第四行的例子「参加」一样下划线需包含这个逗号。

第四种 n:m:p 包含三种实体的联系，本题中是「负责」，所以将「教师」实体的码「教师号」、「课程」实体的码「课程号」、「教材」实体的码「教材号」组合起来：负责（课程号，教师号，教材号，授课时间）← 勿忘属性「授课时间」。

依据 232 页第六段末尾句「各实体的码组成关系的码」，因此三个实体的码共同组合成该联系的码：负责（课程号，教师号，教材号，授课时间）。注意要跟本页倒数第二行的例子「参加」一样下划线需包含这 2 个逗号。

答案 2（关系模型个数最少，因为这样维护成本低，是大厂面试期望看到的答案）

课程（课程号，课程名，上课地点）

教材（教材号，ISBN 号，教材名，出版社名，出版日期）

选课（学号，课程号）

负责（课程号，教师号，教材号，授课时间）

学生（学号，姓名，年龄，出生地，班长的学号，班级号，成立时间）

班级（班级号，班级名，自习室楼号，宿舍地址，班长的学号）

教师（教师号，教师名，系名，系主任的教师号）

思路：

第一，m:n 的联系和三个实体以上的 m:n:p 的联系和答案 1 的一样，因为按照 232 页的第五段首句和第六段首句，这两种只能「转换为一个关系模式」所以

• 选课（学号，课程号）
• 负责（课程号，教师号，教材号，授课时间）

第二，连在这两种联系（m:n 和 m:n:p）上的实体也和答案 1 的一样，所以

• 课程（课程号，课程名，上课地点）
• 教材（教材号，ISBN 号，教材名，出版社名，出版日期）

难点在于第三，按照 232 页第三段首句末尾「也可以与任意一端对应的关系模式合并」和第四段首句末尾「也可以与 n 端对应的关系模式合并」，说明剩余四个联系

• 组织（学号，班长的学号）
• 属于（学号，班级号，成立时间）
• 代表（班长的学号，班级号）
• 督导（教师号，系主任的教师号）

要合并到各自所连接的 n 端实体中，若是 1:1 则合并到任意一端的实体中去。

合并后：

• 学生（学号，姓名，年龄，出生地，班长的学号，班级号，成立时间）

因为「属于」的 n 端是「学生」实体，黄色标志的属性来自于关系「属于」，所以「属于」不再单独设立一个表了。蓝色标志的属性来自于关系「组织」，所以「组织」也不再单独设立一个表了。

• 班级（班级号，班级名，自习室楼号，宿舍地址，班长的学号）

黄色标志的属性来自于关系「代表」，所以「代表」不再单独设立一个表了。

• 教师（教师号，教师名，系名，系主任的教师号）

黄色标志的属性来自于关系「督导」，所以「督导」也不再单独设立一个表了。