数据库系统教程-课后答案(施伯乐)(第三版). 下载本文

FROM S

WHERE AGE>23 AND SEX=’M’; ③ SELECT CNAME,TEACHER

FROM SC, C

WHERE SC.C#=C.C# AND S#=’S3’; ④ SELECT SNAME (联接查询方式)

FROM S, SC, C

WHERE S.S#=SC.S# AND SC.C#=C.C#

AND SEX=’F’ AND TNAME=’LIU’; 或:SELECT SNAME (嵌套查询方式)

FROM S

WHERE SEX=’F’

AND S# IN (SELECT S#

FROM SC

WHERE C# IN (SELECT C#

FROM C

WHERE TNAME=’LIU’));

或:SELECT SNAME (存在量词方式)

FROM S

WHERE SEX=’F’

AND EXISTS (SELECT *

FROM SC

WHERE SC.S#=S.S#

AND EXISTS (SELECT * FROM C WHERE C.C#=SC.C# AND TNAME=’LIU’));

⑤ SELECT C# FROM C

WHERE NOT EXISTS (SELECT * FROM S, SC WHERE S.S#=SC.S# AND SC.C#=C.C# AND SNAME=’WANG’); ⑥ SELECT DISTINCT X.S#

FROM SC AS X, SC AS Y

WHERE X.S#=Y.S# AND X.C#!=Y.C#; ⑦ SELECT C#, CNAME FROM C

WHERE NOT EXISTS

(SELECT * FROM S

WHERE NOT EXISTS

(SELECT * FROM SC WHERE S#=S.S# AND C#=C.C#));

在1974年的SYSTEM R系统中,曾使用过“集合包含”的语法,即 (集合1)CONTAINS(集合2) 用这种语法也能写出本题的SELECT语句,即: SELECT C#,CNAME FROM C WHERE (SELECT S# FROM SC WHERE C#=C.C#) CONTAINS (SELECT S# FROM S); 由于判断“(集合1)CONTAINS(集合2)”与“NOT EXISTS((集合2)EXCEPT(集合1))”是等价的,因此本题的SELECT语句也能这样写:

SELECT C#,CNAME FROM C WHERE NOT EXISTS

((SELECT S# FROM S)

EXCEPT

(SELECT S# FROM SC WHERE C#=C.C#));

⑧ SELECT DISTINCT S# FROM SC AS X WHERE NOT EXISTS (SELECT * FROM C WHERE TNAME=’LIU’ AND NOT EXISTS (SELECT * FROM SC AS Y WHERE Y.S#=X.S# AND Y.C#=C.C#)); 与⑦类似,本题的SELECT语句也能这样写: SELECT DISTINCT S#

FROM SC X WHERE NOT EXISTS

((SELECT C# FROM C WHERE TEACHER=’LIU’)

EXCEPT

(SELECT C# FROM SC Y WHERE Y.S#=X.S#));

3.3 对于第3.2题中的8个查询语句,试给出SELECT语句的图示形式。

解:为了说明问题,这里先用高级语言的算法形式表示其执行过程,再给出图示形式。 下面给出④、⑤、⑦、⑧的算法及图示形式。

④ 如果把三个关系S、SC、C看成三个文件,那么可以看出这个查询语句的SELECT语句实际上是一个三重循环。从而可得这个查询的算法形式如下:

for关系S的每个元组do {which:=false; if S.SEX=’F’ then for 关系SC的每个元组,且NOT which do if SC.S#=S.S# then for 关系C的每个元组,且NOT which do if C.C#=SC.C#,且TEACHER=’LIU’ then {print(S.SNAME); which:=true; }

};

这个算法可以用图3.1表示。 S S# SNAME AGE SEX SC S# C# GRADE C C# CNAME TEACHER _X P. F _X _Y _Y LIU 图3.1 ⑤for 关系S的每个元组 do

{if S.SNAME=’WANG’ then

for 关系C的每个元组 do {which:=false;

for 关系SC的每个元组,且NOT which do if SC.S# =S.S# ,且SC.C#=C.C# then which := true;

if NOT which then print(S.SNAME);

};

这个算法可以用图3.2表示。图中“┐”表示“NOT EXISTS”,即“不存在满足此条件的元组” S S# SNAME AGE SEX C C# CNAME TEACHER SC S# C# GRADE _X WANG P._Y ┐ _X _Y 图3.2 ⑦ for 关系C的每个元组 do {which1 := false;

for 关系S的每个元组,且NOT which1 do

{ which2 := false;

for 关系SC的每个元组,且NOT which2 do

if SC.S# =S.S#, 且SC.C# =C.C# then which2 := true; if NOT which2 then which1:=true; };

if NOT which1 then print(C.C#,C.CNAME);

};

这个算法可以用图3.3表示。

C C# CNAME TEACHER S S# SNAME AGE SEX SC S# C# GRADE P._X P. ┐ _Y ┐ _Y _X 图3.3 ⑧ for 关系SC的每个元组x do {which1 := false;

for 关系C的每个元组y,且NOT which1 do

{ if y.TEACHER=’LIU’then { which2 := false;

for 关系SC的每个元组z,且NOT which2 do

if z.S# =x.S#,且z.C# =y.C# then which2 := true; if NOT which2 then which1:=true; };

if NOT which1 then print(x.S#); } };

这个算法可以用图3.4表示。

SC S# C# GRADE C C# CNAME TEACHER SC S# C# GRADE P._X P. ┐ _Y LIU ┐ _X _Y 图3.4

3.4 设有两个基本表R(A,B,C)和S(A,B,C),试用SQL查询语句表达下列关系代数表

达式:

① R∪S ② R∩S ③ R-S ④ R×S ⑤πA,B(R) πB,C(S) ⑥ π1,6(σ3=4(R×S) ⑦π1,2,3( 3=3 R S) ⑧ R÷πC(S) 解:① (SELECT * FROM R) UNION

(SELECT * FROM S);

② (SELECT * FROM R) INTERSECT

(SELECT * FROM S); ③ (SELECT * FROM R) MINUS

(SELECT * FROM S); ④ SELECT *

FROM R, S;

⑤ SELECT R.A, R.B, S.C

FROM R, S

WHERE R.B=S.B; ⑥ SELECT R.A, S.C

FROM R, S

WHERE R.C=S.A;

⑦ SELECT R.* (R.*表示R中全部属性)

FROM R, S

WHERE R.C=S.C;

⑧ R÷πC(S)的元组表达式如下:

{ t |(?u)(?v)(?w)(R(u)∧ S(v)∧ R(w)∧ w[1]=u[1] ∧ w[2]=u[2]

∧ w[3]=v[3] ∧ t[1]=u[1] ∧ t[2]=u[2])}

据此,可写出SELECT语句: SELECT A, B

FROM R RX

WHERE NOT EXISTS

( SELECT * FROM S

WHERE NOT EXISTS

( SELECT * FROM R RY

WHERE RY.A=RX.A AND RY.B=RX.B AND RY.C=S.C));

3.5 设有两个关系R(A,B)和S(A,C),试用SQL查询语句表示下列域表达式:

① { a |(?b)(R(ab)∧ b=‘17’)} ② { abc | R(ab)∧ S(ac)}

③ { a |(?c)(?b1)(?b2)(S(ac)∧R(ab1)∧ R(cb2)∧ b1>b2)} 解:① SELECT A FROM R

WHERE B=17;

② SELECT R.A, R.B, S.C FROM R, S

WHERE R.A=S.A; ③ SELECT S.A

FROM S, R RX, R RY

WHERE S.A=RX.A AND RX.B>RY.B;

3.6 试叙述SQL语言的关系代数特点和元组演算特点。 答:SQL的关系代数特点如下:

① 有关系代数运算的并、交、差、自然联接等运算符;

② FROM子句体现了笛卡尔积操作,WHERE子句体现了选择操作,SELECT子句体现

了投影操作。

SQL的元组演算特点如下:

① FROM子句中的基本表名应视为“元组变量”,属性名应视为“元组分量”; ② 有存在量词EXISTS符号。

3.7 试用SQL查询语句表达下列对3.2题中三个基本表S、SC、C的查询:

① 在表C中统计开设课程的教师人数。 ② 求选修C4课程的女学生的平均年龄。

③ 求LIU老师所授课程的每门课程的平均成绩。 ④ 统计每个学生选修课程的门数(超过5门的学生才统计)。要求输出学生学号和选修门

数,查询结果按门数降序排列,若门数相同,按学号升序排列。 ⑤ 检索学号比WANG同学大,而年龄比他小的学生姓名。 ⑥ 在表SC中检索成绩为空值的学生学号和课程号。 ⑦ 检索姓名以L打头的所有学生的姓名和年龄。 ⑧ 求年龄大于女同学平均年龄的男学生姓名和年龄。 ⑨ 求年龄大于所有女同学年龄的男学生姓名和年龄。 解:① SELECT COUNT(DISTINCT TEACHER) FROM C;

② SELECT AVG(AGE) FROM S, SC

WHERE S.S#=SC.S# AND C#=’C4’ AND SEX=’F’; ③ SELECT C.C#,AVG(GRADE) FROM SC,C

WHERE SC.C#=C.C# AND TEACHER=‘LIU’ GROUP BY C.C#;

④ SELECT S#, COUNT(C#) FROM SC GROUP BY S#

HAVING COUNT(*)>5 ORDER BY 2 DESC, 1; ⑤ SELECT SNAME FROM S

WHERE S#>ALL(SELECT S#