数据库习题作业

数据库习题作业重点（第2-3-4章）

7 2 3

4 4 3

计算R ∪S ，R-S ，R ∩S ，R ×S ，π3,2（S ）,σB<’5’ S, 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 π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

σB<’5’（R ）

A B C R ?S

7 2 3

7

2 3

3 4

5

4 4 3

R ?S A B C

2<2

7 2 3

设有关系R和S：

R A B S B C

a b b c

c b e a

d e b d

计算R ? S，R ? S，σA=C(R×S)，S ? R。

假设关系U和V分别有m个元组和n个元组，给出下列表达式中可能的最小和最大的元组数量：

⑴ U∩V ⑵ U∪V

⑶ U?V ⑷σ

F

(U)×V （F为某个条件）

⑸π

L (U)－V （其中L为某属性集）

B

e a

b

解：

操作最小元组数最大元组数

⑴ U∩V0min(m,n)

⑵ U∪V max(m,n)m+n

⑶ U?V0m×n

⑷σF(U)×V0m×n

⑸πL(U)－V0m

设有三个关系：

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））

在教学数据库的关系S、SC、C、T中，用户有一查询语句：检索女同学选修课程的课程名和任课教师名。

①试写出该查询的关系代数表达式。

②画出查询表达式的语法树。

③使用启发式优化算法，对语法树进行优化，并画出优化后的语法树。

解：

【】设教学数据库中有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 A GE<17 AND SEX=’F’

②检索男学生所学课程的课程号和课程名。SELECT C．C#，CNAME (连接查询方式)

FROM S，SC，C

WHERE S．S#=# AND #=# AND SEX=’M’；

③检索男学生所学课程的任课老师的工号和姓名。SELECT #, TNAME

FROM S，SC，C，T

WHERE #=# AND #=# AND #=# AND SEX=’M’;

④检索至少选修两门课程的学生学号。

SELECT DISTINCT #

FROM SC AS X, SC AS Y

WHERE #=# AND #!=#;

⑤检索至少有学号为S2和S4的学生选修的课程的课程号。SELECT DISTINCT #

FROM SC AS X, SC AS Y

WHERE #=’S2’AND #=’S4’ AND #=#；

⑥检索WANG同学不学的课程的课程号。

SELECT C#

FROM C

WHERE NOT EXISTS

(SELECT *

FROM S, SC

WHERE #=# AND #=# AND SNAME=’WANG’)；

⑦检索全部学生都选修的课程的课程号与课程名。SELECT C#,CNAME

FROM C

WHERE NOT EXISTS

(SELECT *

FROM S

WHERE NOT EXISTS

(SELECT *

FROM SC

WHERE S#=# AND C#=#))；

⑧检索选修课程包含LIU老师所授全部课程的学生学号。

法一：

SELECT DISTINCT S#

FROM SC AS X

WHERE NOT EXISTS

(SELECT *

FROM C，T

WHERE #=# AND TNAME=’LIU’

AND NOT EXISTS

(SELECT{

FROM SC AS Y

WHERE #=# AND #=#))；

法二：

SELECT DISTINCT S#

FROM SC X

WHERE NOT EXISTS

((SELECT C# FROM C,T

WHERE #=# AND TNAME='LIU’)

EXCEPT

(SELECT C# FROM SC Y WHERE #=#))；

【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 AGE

FROM S

WHER E SNAME=’WANG)；

⑥在表SC中检索成绩为空值的学生的学号和课程号。

SELECT S#, C#

FROM SC

WHERE SCORE IS NULL；

⑦检索姓名以L开头的所有学生的姓名和年龄。

SELECT SNAME, AGE

FROM S

WHERE SNAME LIKE ’L％’;

⑧求年龄大于女同学平均年龄的男学生的姓名和年龄。SELECT SNAME, 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 #=# 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 *

WHERE S # IN(SELECT S# FROM S WHERE SEX='F’)

AND SCORE<(SELECT AVG(SCORE) FROM SC)；

⑦用两个UPDATE语句实现：

UPDATE SC

SET SCORE=SCORE *

WHERE C#=’C4’AND SCORE>70；

UPDATE SC

SET SCORE=SCORE *

WHERE C#=’C4’AND SCORE<=70；

这两个UPDATE语句的顺序不能颠倒。

用一个UPDATE语句实现：

UPDATE SC

SET SCORE=SCORE * CASE

WHEN SCORE>70 THEN

ELSE

END

WHERE C#=’C4’;

⑧UPDATE SC

SET SCORE=SCORE *

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 #=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 #

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；

设关系模式R（ABCD），F是R上成立的FD集，F=｛ A→B，C→B ｝，则相对于

F，试写出关系模式R的关键码。并说明理由。

解：R的关键码为ACD。因为从已知的F，只能推出ACD→ABCD。

设关系模式R(ABCDE)上FD集为F，并且F=｛AB→C，CD→E，DE→B｝，试判断AB是R的候选键吗ABD呢请做出解释。

解：①从已知的F，可推出AB→ABC，但不能推出AB→ABCDE，因此不是。

②从已知的F，可推出ABD→ABCD，ABD→ABCDE，因此是的。

设关系模式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。

设关系模式R(ABCDE)上FD集为F，并且F=｛A→BC，CD→E，B→D，E→A｝。

①试求R的候选键。

②试求B+的值。

解：① R的候选键有四个：A、E、CD和BC。

② B+=BD。

设关系模式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模式集。

设关系模式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章：

作业：P37

, ，, ， , ，

第5章

作业：

，，，，，，

第7章

作业：

，，，，，

复习范围

、、、

、、、

、、、

、、

、、、

、、、、