DB2602 - 关系代数的应用

设有一个如下表所示的学生选课数据库，其中：带下划线属性是码；同一门课同一个学生只能有一条选课记录；成绩采用百分制，课程成绩大于或等于60分为‘通过’。

题目隐含条件别遗漏

关系	属性集	关系模式
学生	学号, 学生姓名, 就读院系, 年级	S ( sno, sname, dept, grade )
课程	课程号, 课程名, 开课院系, 课程学分	C ( cno, cname, dept, credit )
教师	教师编号, 教师姓名, 工作院系	T ( tno, tname, dept )
选课	学号, 课程号, 授课教师编号, 成绩, 授课年份	L ( sno, cno, tno, score, year )

请用关系代数写出下述查询。

1.满足下述条件的学生的学号和姓名：选修了课程名为“数据管理基础”的课程；

$\pi_{S.sno,S.sname}(\pi_{cno}(\sigma_{C.cname='数据管理基础'}(C))\bowtie L \bowtie S)$

参考答案：

\pi_{S.sno,S.sname}((\pi_{cno}(\sigma_{C.cname='数据管理基础'}(C))\bowtie L) \bowtie S)

多了一层括号，没有连用自然连接

2.满足下述条件的教师的姓名和工作院系：只讲授过自己工作院系开设的课程；

$\pi_{T.tname,T.dept}((\pi_{T.tno}(T \bowtie L) - \pi_{T.tno}(\sigma_{C.dept \neq T.dept \wedge C.cno=L.cno \wedge T.tno=L.tno}(C \times T \times L)))\bowtie T)$

参考答案 1：
$\pi_{tname,dept}(T \bowtie (\pi_{tno}(L) - \pi_{T.tno}(\sigma_{C.dept \neq T.dept \wedge C.cno=L.cno \wedge T.tno=L.tno}(C \times T \times L))))$
与原提交的答案相比

最外层自然连接 $T$ 前置更醒目

内层被减数简化

最外层投影直接使用属性名

参考答案 2：
$\pi_{tname,dept}(\pi_{T.tno,tname,dept}(T \bowtie L) - \pi_{T.tno,tname,T.dept}(\sigma_{T.tno=L.tno \wedge C.dept \neq T.dept}(T \times (C \bowtie L))))$
自然连接时若有同名属性需格外注意

课件要点：

在使用 ‘差’ 运算时，在减数和被减数关系模式中需要包含查询目标的码（教师编号 $tno$）

3.满足下述条件的学生的学号和姓名：选修通过了‘智软’学院开设的所有课程；

使用除法：
$\pi_{S.sno,S.sname}((\pi_{sno,cno}(\sigma_{L.score \geq 60}(L)) \div \pi_{cno}(\sigma_{C.dept='智软'}(C)))\bowtie S)$
参考答案 1：
$\pi_{S.sno,S.sname}(S \bowtie (\pi_{sno,cno}(\sigma_{L.score \geq 60}(L)) \div \pi_{cno}(\sigma_{C.dept='智软'}(C))))$
参考答案 2：
$\pi_{sno, sname, cno}(S \bowtie \sigma_{score \geq 60}(L)) \div \pi_{cno}(\sigma_{dept='智软'}(C))$

不使用除法：
$\pi_{S.sno,S.sname}((\pi_{sno}(L) - \pi_{sno}(\pi_{S.sno,C.cno}(\sigma_{C.dept='智软'}(S\times C)) - \pi_{sno,cno}(\sigma_{score \geq 60}(L)))) \bowtie S)$
参考答案 3：
$\pi_{sno, sname}(S \bowtie (\pi_{sno}(S) - \pi_{sno}((\pi_{sno}(S) \times \pi_{cno}(\sigma_{dept='智软'}(C))) - \pi_{sno, cno}(\sigma_{score \geq 60}(L)))))$
参考答案 4：
$\pi_{sno, sname}(S) - \pi_{sno, sname}((\pi_{sno, sname}(S) \times \pi_{cno}(\sigma_{dept='智软'}(C))) - \pi_{sno, sname, cno}(\sigma_{score \geq 60}(L) \bowtie S))$

4.满足下述条件的教师的姓名和工作院系：讲授过自己工作院系开设的所有课程；

$\pi_{T.tname,T.dept}(T \bowtie (\pi_{T.tno}(L)-\pi_{T.tno}(\pi_{T.tno,C.cno}(T \bowtie C) - \pi_{tno,cno}(L))))$
现在看来 $\pi_{T.tno}(L)$ 这一项明显有误

参考答案 1：
$\pi_{tname,dept}(T \bowtie (\pi_{tno}(T)-\pi_{tno}(\pi_{tno,cno}(T \bowtie C) - \pi_{tno,cno}(L))))$

至少存在一门自己工作院系开设的课程还没有讲授过的教师（总 - 实际讲授）：

$R1 = \pi_{tno}(\pi_{tno,cno}(T \bowtie C) - \pi_{tno,cno}(L))$

符合题意的教师：

$R2 = \pi_{tno}(T) - R1$

再与教师关系连接获取姓名和院系（略）

注意：最外层被减数 $\pi_{tno}(T)$

AI 提示：在处理关系代数的除法等价转换时，最外层的被减数应当始终使用实体集本身的投影

参考答案 2：
$\pi_{tname, dept}(\pi_{tno, tname, dept}(T) - \pi_{tno, tname, dept}(\pi_{tno, tname, dept, cno}(T \bowtie C) - \pi_{tno, tname, dept, cno}(L \bowtie T)))$

但不能错误表示为（减法没 “码”）：

$\pi_{\text{tname}, \text{dept}}(T) - \pi_{\text{tname}, \text{dept}}(\pi_{\text{tname}, \text{dept}, \text{cno}}(T \bowtie C) - \pi_{\text{tname}, \text{dept}, \text{cno}}(L \bowtie T))$

5.查询每一门课程的历史最高成绩和最低成绩，结果返回课程号、课程历史最高成绩和最低成绩。

令 $A := L$
$B = \pi_{cno,score}(L) - \pi_{L.cno,L.score}(L\mathop{\bowtie}\limits_{L.score < A.score \wedge L.cno = A.cno} A)$ $C = \pi_{cno,score}(L) - \pi_{L.cno,L.score}(L\mathop{\bowtie}\limits_{L.score > A.score \wedge L.cno = A.cno} A)$
查询结果即 $B \mathop{\bowtie}\limits_{B.cno = C.cno} C$

参考答案：令 $M := L, N := L$

① 在每一门课程中，查询不是历史最高成绩的成绩
$R1(cno, score) := \pi_{M.cno, M.score}(\sigma_{M.cno = N.cno \land M.score < N.score}(M \times N))$
② 查询每一门课程的历史最高成绩：
$\text{highest}(cno, max\_score) := \pi_{cno, score}(L) - R1$
③ 在每一门课程中，查询不是历史最低成绩的成绩
$R2(cno, score) := \pi_{N.cno, N.score}(\sigma_{M.cno = N.cno \land M.score > N.score}(M \times N))$
④ 查询每一门课程的历史最低成绩：
$\text{lowest}(cno, min\_score) := \pi_{cno, score}(L) - R2$
⑤ 查询每一门课程的课程号、课程历史最高成绩和最低成绩：
$\text{highest} \bowtie \text{lowest}$
使用新关系 $R(A_1,A_2)$ 的方式重命名属性