数据库模拟题作业.doc

数据库习题作业重点（第2-3-4章）
2.6 设有关系R和S：
R A B C S A B C
3 6 7 3 4 5
2 5 7 7 2 3
7 2 3
4 4 3
计算R∪S，R-S，R∩S，R×S，π3,2（S）,σB<’5’(R),R 2<2 S, R S。
解： R∪S A B C R－S A B C R∩S A B C
3 6 7 3 6 7 7 2 3
2 5 7 2 5 7
7 2 3 4 4 3
4 4 3
3 4 5
R×S R.A R.B R.C S.A S.B S.C π3，2（S） C B
3 6 7 3 4 5 5 4
3 6 7 7 2 3 3 2
2 5 7 3 4 5
2 5 7 7 2 3
7 2 3 3 4 5
7 2 3 7 2 3
4 4 3 3 4 5
4 4 3 7 2 3
2<2
σB<’5’（R） A B C R⋈S R.A R.B R.C S.A S.B S.C
7 2 3 7 2 3 3 4 5
4 4 3
R⋈S A B C
7 2 3

2.7 设有关系R和S：
R A B S B C
a b b c
c b e a
d e b d
BF(U)×V （F为某个条件）
⑸ πL(U)－V （其中L为某属性集）
解：
操作
最小元组数
最大元组数
⑴ U∩V
0
min(m,n)
⑵ U∪V
max(m,n)
m+n
⑶ U⋈V
0
m×n
⑷σF(U)×V
0
m×n
⑸πL(U)－V
0
m

2.17 设有三个关系：
T（T#,TNMAE,TITLE）
C（C#，CNAME，T#）
S（S#，SNAME，AGE，SEX)
SC（S#，C#，SCORE）
试用关系代数表达式表示下列查询语句：
① 检索年龄小于17岁的女学生的学号和姓名。
② 检索男学生所学课程的课程号和课程名。
③ 检索男学生所学课程的任课教师的工号和姓名。
④ 检索至少选修两门课的学生学号。
⑤ 检索至少有学号为S2和S4学生选修的课程的课程号。
⑥ 检索WANG同学不学的课程的课程号。
⑦ 检索全部学生都选修的课程的课程号与课程名。
⑧ 检索选修课程包含LIU老师所授全部课程的学生学号。
解：⑴ πS#，SNAME（σAGE<’17’ ∧SEX=’F’（S））
⑵ Πc#，CNAME（σSEX='M'（S⋈SC⋈C））
⑶ ΠT#，TNAME（σSEX='M'（S⋈SC⋈C⋈T））
⑷ π1（σ1=4 ∧ 2≠5（SC×SC））
⑸ π2（σ1=’S2’ ∧4=’S4’∧ 2=5（SC×SC））
⑹ πC#（C）－πC#（σSNAME='WANG'（S⋈SC））
⑺ πC#，CNAME（C⋈（πS#，C#（SC）÷πS#（S）））
⑻ πS#，C#（SC）÷πC#（σTNAME='LIU'（C⋈T））

2.21 在教学数据库的关系S、SC、C、T中，用户有一查询语句：检索女同学选修课程的课程名和任课教师名。
① 试写出该查询的关系代数表达式。
② 画出查询表达式的语法树。
③ 使用启发式优化算法，对语法树进行优化，并画出优化后的语法树。
解：

【3.2】设教学数据库中有4个关系：
教师关系 T(T#，TNAME，TITLE)
课程关系C(C#，CNAME,T#)
学生关系S(S#，SNAME,AGE,SEX)
选课关系 SC(S#，C#，SCORE)
试用SQL查询语句表示下列查询。
解：
①检索年龄小于17岁的女学生的学号和姓名。
SELECT S#．SNAME
FROM S
WHERE AGE<17 AND SEX=’F’
②检索男学生所学课程的课程号和课程名。
SELECT C．C#，CNAME (连接查询方式)
FROM S，SC，C
WHERE S．S#=SC.S# AND SC.C#=C.C# AND SEX=’M’；
③检索男学生所学课程的任课老师的工号和姓名。
SELECT T.T#, TNAME
FROM S，SC，C，T
WHERE S.S#=SC.S# AND SC.C#=C.C# AND C.T#=T.T# AND SEX=’M’;
④检索至少选修两门课程的学生学号。
SELECT DISTINCT X.S#
FROM SC AS X, SC AS Y
WHERE X.S#=Y.S# AND X.C#!=Y.C#;
⑤检索至少有学号为S2和S4的学生选修的课程的课程号。
SELECT DISTINCT X.C#
FROM SC AS X, SC AS Y
WHERE X.S#’S2’ AND Y.S#=’S4’ AND X.C#=Y.C#；
⑥检索WANG同学不学的课程的课程号。
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 C#,CNAME
FROM C
WHERE NOT EXISTS
(SELECT *
FROM S
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE S#=S.S# AND C#=C.C#))；
⑧检索选修课程包含LIU老师所授全部课程的学生学号。
法一：
SELECT DISTINCT S#
FROM SC AS X
WHERE NOT EXISTS
(SELECT *
FROM C，T
WHERE C.T#=T.T# AND TNAME=’LIU’
AND NOT EXISTS
(SELECT{
FROM SC AS Y
WHERE Y.S#=X.S# AND Y.C#=C.C#))；
法二：
SELECT DISTINCT S#
FROM SC X
WHERE NOT EXISTS
((SELECT C# FROM C,T
WHERE C.T#=T.T# AND TNAME='LIU’)
EXCEPT
(SELECT C# FROM SC Y WHERE Y.S#=X.S#))；

【3．7】试用SQL查询语句表达下列对第3．2题中4个基本表T、C、S、SC的查询。
①统计有学生选修的课程门数。
SELECT COUNT(DISTINCT C#) FROM SC；
②求选修C4课程的女学生的平均年龄。
SELECT AVG(AGE)
FROM S，SC
WHERE S．S#=SC．S# AND C#=’C4’ AND SEX=’F’：
③求LIU老师所授的每门课程的平均成绩。
SELECT C．C#．AVG(SCORE)
FROM SC，C，T
WHERE SC．C#=C．C# AND C．T#=T．T# AND TNAME=’LIU’
GROUP BY C．C#；
④统计选修每门课程的学生人数(超过l0人的课程才统计)。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，则按课程号升序排列。
SELECT C#．COUNT(S#)
FROM SC
GROUP BY C#
HAVING COUNT(*)>10
ORDER BY 2 DESC，1；
⑤检索学号比WANG同学大，而年龄比他小的学生姓名。
SELECT SNAME
FROM S
WHERE S#>ALL(SELECT S# FROM S
WHERE SNAME=’WANG’)
AND AGEE, AGE
FROM S
WHERE SEX=’M’
AND AGE>(SELECT AVG(AGE)
FROM S
WHERE SEX=’F’)；
⑨求年龄大于所有女同学年龄的男学生的姓名和年龄。
SELECT SNAME, AGE
FROM S
WHERE SEX=‘M’
AND AGE>ALL(SELECT AGE
FROM S
WHERE SEX=’F’)；

【3．12】
解：
①INSERT INTO C
VALUES(‘C8’，’VC++’，’T6’)；
②
法一：
INSERT INTO FACULTY(TNAME)
SELECT DISTINCT TNAME
FROM (SELECT TNAME，C．C#，AVG(SCORE)
FROM T, C, SC
WHERE T．T#=C．T# AND C．C#=SC．C#
GROUP BY TNAME，C．C#)
AS RESULT(TNAME，C#，AVG—SCORE) AS X
WHERE 80<=ALL(SELECT AVG—SCORE
FROM RESULT AS Y
WHERE Y．TNAME=X．TNAME)；
法二
INSERT INTO FACULTY(TNAME)
SELECT DISTINCT TNAME
FROM T, C, SC
WHERE C．C#=SC．C# AND T．T#=C．T#
AND T# NOT IN (SELECT T# FROM C
WHERE C# IN
(SELECT C# FROM SC
GROUP BY C# HAVING AVG(SCORE)<=80)
)

③DELETE FROM SC WHERE SCORE IS NULL；
④DELETE FROM SC
WHERE S# IN(SELECT S# FROM S WHERE SEX=’F’)
AND C# IN(SELECT C# FROM C, T WHERE C.T#=T.T# AND TNAME=’LIU’);
⑤UPDATE SC
SET SCORE=60
WHERE SCORE<60
AND C# IN(SELECT C#FROM C WHERE CNAME=’MATHS’)；
⑥UPDATE SC
SET SCORE=SCORE * 1.05
WHERE S # IN(SELECT S# FROM S WHERE SEX='F’)
AND SCORE<(SELECT AVG(SCORE) FROM SC)；
⑦用两个UPDATE语句实现：
UPDATE SC
SET SCORE=SCORE * 1.04
WHERE C#=’C4’AND SCORE>70；

UPDATE SC
SET SCORE=SCORE * 1.05
WHERE C#=’C4’AND SCORE<=70；
这两个UPDATE语句的顺序不能颠倒。
用一个UPDATE语句实现：
UPDATE SC
SET SCORE=SCORE * CASE
WHEN SCORE>70 THEN 1.04
ELSE l.05
END
WHERE C#=’C4’;
⑧UPDATE SC
SET SCORE=SCORE * 1.05
WHERE SCORE<(SELECT AVG(SCORE)
FROM SC)；

【3．13】设数据库中有3个关系：
职工表 EMP(E#，ENAME，AGE，SEX，ECITY)，其属性分别表示职工工号、姓名、年龄、性别和籍贯。
工作表 WORKS(E#，C#，SALARY)，其属性分别表示职工工号、工作的公司编号和工资。
公司表 COMP(C#，CNAME，CITY)，其属性分别表示公司编号、公司名称和公司所在城市。
试用SQL语句写出下列操作：
①用CREATE TABLE语句创建上述3个表，需指出主键和外键。
②检索超过50岁的男职工的工号和姓名。
③假设每个职工只能在一个公司工作，检索工资超过1 000元的男性职工工号和姓名。
④假设每个职工可在多个公司工作，检索至少在编号为C4和C8的公司兼职的职工工号
和姓名。
⑤检索在联华公司工作、工资超过1 000元的男性职工的工号和姓名。
⑥假设每个职工可在多个公司工作，检索每个职工的兼职公司数目和工资总数，显示
(E#，NUM，SUM_SALARY)，分别表示工号、公司数目和工资总数。
⑦工号为E6的职工在多个公司工作，试检索至少在E6职工兼职的所有公司工作的职工
工号。
⑧检索联华公司中低于本公司平均工资的职工工号和姓名。
⑨在每一公司中为50岁以上的职工加薪100元(若职工为多个公司工作，可重复加)。
⑩在EMP表和WORKS表中删除年龄大于60岁的职工有关元组。
解：
①CREATE TABLE EMP
(E# CHAR(4) NOT NULL，
ENAME CHAR(8) NOT NULL，
AGE SMALLINT,
SEX CHAR(1)，
ECITY CHAR(20)，
PRIMARY KEY(E#))；

CREATE TABLE COMP
(C# CHAR(4) NOT NULL，
CNAME CHAR(20) NOT NULL，
CITY CHAR(20)
PRIMARY KEY(C#))；

CREATE TABLE WORKS
(E# CHAR(4) NOT NULL，
C# CHAR(4) NOT NULL，
SALARY SMALLINT，
PRIMARY KEY(E#，C#)，
FOREIGN KEY(E#) REFERENCES EMP(E#)，
FOREIGN KEY(C#) REFERENCES COMP(C#))；

②(SELECT E#，ENAME
FROM EMP
WHERE AGE>50 AND SEX=’M’；
③SELECT EMP．E#，ENAME
FROM EMP, WORKS

WHERE EMP.E#=WORKS．E# AND SALARY>1000 AND SEX=’M’；
④SELECT A．E#，A．ENAME
FROM EMP A，WORKS B，WORKS C
WHERE A．E#=B．E# AND B．E#=C．E#
AND B．C#=’C4’ AND C．C#=’C8’；
⑤SELECT A．E#，A．ENAME
FROM EMP A，WORKS B，COMP C
WHERE A．E#=B．E# AND B．C#=C．#
AND CNAME=’联华公司’ AND SALARY>1000 AND SEX='M’；
⑥SELECT E#COUNT(C#) AS NUM，SUM(SALARY) AS SUM_SALARY
FROM WORKS
GROUP BY E#;
⑦SELECT X.E#
FROM WORKS X
WHERE NOT EXISTS
(SELECT *
FROM WORKS Y
WHERE E#=’E6’
AND NOT EXISTS
(SELECT *
FROM WORKS Z
WHERE Z．E#=X．E# AND Z．C#=Y．C#))；
⑧SELECT A．E#，A．ENAME
FROM EMP A，WORKS B，COMP C
WHERE A．E#=B．E# AND B．C#=C．C# AND CNAME=’联华公司’
AND SALARY<(SELECT AVG(SALARY)
FROM WORKS．COMP C
WHERE WORK S．C#=COMP．C# AND CNAME=’联华公司’)；
⑨UPDATE WORKS
SET SALARY=SALARY+100
WHERE E# IN(SELECT E# FROM EMP WHERE AGE>50)；
⑩DELETE FROM WORKS
WHERE E# IN (SELECT E# FROM EMP WHERE AGE>60)；
DELETE FROM EMP
WHERE AGE>60；

4.8 设关系模式R（ABCD），F是R上成立的FD集，F=｛ A→B，C→B ｝，则相对于F，试写出关系模式R的关键码。并说明理由。
解：R的关键码为ACD。因为从已知的F，只能推出ACD→ABCD。

4.9 设关系模式R(ABCDE)上FD集为F，并且F=｛AB→C，CD→E，DE→B｝，试判断AB是R的候选键吗？ABD呢？请做出解释。
解：①从已知的F，可推出AB→ABC，但不能推出AB→ABCDE，因此不是。
② 从已知的F，可推出ABD→ABCD，ABD→ABCDE，因此是的。

4.13 设关系模式R（ABCD），F是R上成立的FD集，F=｛A→B，B→C｝，
① 试写出属性集BD的闭包(BD)+。
② 试写出所有左部是B的函数依赖（即形为“B→？”）。
解：①从已知的F，可推出BD→BCD，所以(BD)+=BCD。
②由于B+=BC，因此左部是B的FD有四个：
B→φ，B→B，B→C，B→BC。

4.14 设关系模式R(ABCDE)上FD集为F，并且F=｛A→BC，CD→E，B→D，E→A｝。
① 试求R的候选键。
② 试求B+的值。
解：① R的候选键有四个：A、E、CD和BC。
② B+=BD。

4.26 设关系模式R（ABCD），F是R上成立的FD集，F=｛ AB→CD，A→D ｝。
① 试说明R不是2NF模式的理由。
② 试把R分解成2NF模式集。
答：①从已知FD集F，可知R的候选键是AB。
另外，AB→D是一个局部依赖，因此R不是2NF模式。
②此时R应分解成ρ={ AD，ABC }，ρ是2NF模式集。

4.27 设关系模式R（ABC），F是R上成立的FD集，F=｛ C→B，B→A ｝。
① 试说明R不是3NF模式的理由。
② 试把R分解成3NF模式集。
答：①从已知FD集F，可知R的候选键是C。
从C→B和B→A，可知C→A是一个传递依赖，因此R不是3NF模式。
②此时R应分解成ρ={ CB，BA }，ρ是3NF模式集。

第1章：
o 作业：P37
o 1.3, 1.4，1.5, 1.6， 1.12, 1.17 ，1.27
第5章
o 作业：
o 5.2， 5.5，5.6，5.8， 5.13， 5.14， 5.15
第7章
n 作业：
n 7.2，7.3， 7.4，7.8，7.16，7.17

复习范围
1.2.2、1.2.3、1.3-1.6、1.7
2.1、2.2、2.4、2.6
3.2、3.3、3.5、3.8
4.2、4.4、4.6
5.1、5.2、5.3、5.4
7.1、7.2、7.3、7.5、7.6

12 / 12