数据库习题作业

1、数据库习题作业重点（第2-3-4章）2.6 设有关系R和S：RABCSABC3673 4 5257723723443计算RS，R-S，RS，R×S，3,2（S）,B<5(R),R 2<2 S, R S。解：RSABCRSABCRSABC367367723257257723443443345R×SR.A R.B R.C S.A S.B S.C3，2（S）CB 36 73 4554 36 77 2332 25 73 45 25 77 23 72 33 45 72 37 23 44 33 45 44 37 232<2B<5（R）ABCRSR.A R.B R

2、.C S.A S.B S.C723 72 33 45443RSABC7232.7 设有关系R和S： RABSBCabbccbeadebd B<C计算R S，R S，A=C(R×S)，S R。be a2.8 假设关系U和V分别有m个元组和n个元组，给出下列表达式中可能的最小和最大的元组数量： UV UV UV F(U)×V （F为某个条件） L(U)V（其中L为某属性集）解：操作最小元组数最大元组数 UV0min(m,n) UVmax(m,n)m+n UV0m×nF(U)×V0m×nL(U)V0m2.17 设有三个关系：T（T#,TNMAE

3、,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'（SS

4、CC）T#，TNAME（SEX='M'（SSCCT）1（1=4 25（SC×SC）2（1=S2 4=S4 2=5（SC×SC）C#（C）C#（SNAME='WANG'（SSC）C#，CNAME（C（S#，C#（SC）÷S#（S）S#，C#（SC）÷C#（TNAME='LIU'（CT）2.21 在教学数据库的关系S、SC、C、T中，用户有一查询语句：检索女同学选修课程的课程名和任课教师名。 试写出该查询的关系代数表达式。 画出查询表达式的语法树。 使用启发式优化算法，对语法树进行优化，并画出优化后的语法树。解

5、： 【3.2】设教学数据库中有4个关系：教师关系 T(T#，TNAME，TITLE)课程关系C(C#，CNAME,T#)学生关系S(S#，SNAME,AGE,SEX)选课关系 SC(S#，C#，SCORE)试用SQL查询语句表示下列查询。解：检索年龄小于17岁的女学生的学号和姓名。SELECT S#SNAMEFROM SWHERE AGE<17 AND SEX=F检索男学生所学课程的课程号和课程名。SELECT CC#，CNAME (连接查询方式)FROM S，SC，CWHERE SS#=SC.S# AND SC.C#=C.C# AND SEX=M；检索男学生所学课程的任课老师的工号和姓

6、名。SELECT T.T#, TNAMEFROM S，SC，C，TWHERE 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 YWHERE X.S#=Y.S# AND X.C#!=Y.C#;检索至少有学号为S2和S4的学生选修的课程的课程号。SELECT DISTINCT X.C#FROM SC AS X, SC AS YWHERE X.S#=S2 AND Y.S#=S4 AND X.C#=Y.C#；检索WANG同学不学的课程的课程

7、号。SELECT C#FROM CWHERE NOT EXISTS(SELECT *FROM S, SCWHERE S.S#=SC.S# AND SC.C#=C.C# AND SNAME=WANG)；检索全部学生都选修的课程的课程号与课程名。SELECT C#,CNAMEFROM CWHERE NOT EXISTS(SELECT *FROM SWHERE NOT EXISTS(SELECT *FROM SCWHERE S#=S.S# AND C#=C.C#)；检索选修课程包含LIU老师所授全部课程的学生学号。法一：SELECT DISTINCT S#FROM SC AS XWHERE NOT

8、EXISTS(SELECT *FROM C，TWHERE C.T#=T.T# AND TNAME=LIUAND NOT EXISTS(SELECTFROM SC AS YWHERE Y.S#=X.S# AND Y.C#=C.C#)；法二：SELECT DISTINCT S#FROM SC XWHERE NOT EXISTS(SELECT C# FROM C,TWHERE C.T#=T.T# AND TNAME='LIU)EXCEPT(SELECT C# FROM SC Y WHERE Y.S#=X.S#)；【37】试用SQL查询语句表达下列对第32题中4个基本表T、C、S、SC的查询。

9、统计有学生选修的课程门数。SELECT COUNT(DISTINCT C#) FROM SC；求选修C4课程的女学生的平均年龄。SELECT AVG(AGE)FROM S，SCWHERE SS#=SCS# AND C#=C4 AND SEX=F：求LIU老师所授的每门课程的平均成绩。SELECT CC#AVG(SCORE)FROM SC，C，TWHERE SCC#=CC# AND CT#=TT# AND TNAME=LIUGROUP BY CC#；统计选修每门课程的学生人数(超过l0人的课程才统计)。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，则按课程号升序排列。SELECT C

10、#COUNT(S#)FROM SCGROUP BY C#HAVING COUNT(*)>10ORDER BY 2 DESC，1；检索学号比WANG同学大，而年龄比他小的学生姓名。SELECT SNAMEFROM SWHERE S#>ALL(SELECT S# FROM SWHERE SNAME=WANG)AND AGE<ALL( SELECT AGEFROM SWHERE SNAME=WANG)；在表SC中检索成绩为空值的学生的学号和课程号。SELECT S#, C#FROM SCWHERE SCORE IS NULL；检索姓名以L开头的所有学生的姓名和年龄。SELECT S

11、NAME, AGEFROM SWHERE SNAME LIKE L;求年龄大于女同学平均年龄的男学生的姓名和年龄。SELECT SNAME, AGEFROM SWHERE SEX=MAND AGE>(SELECT AVG(AGE)FROM SWHERE SEX=F)；求年龄大于所有女同学年龄的男学生的姓名和年龄。SELECT SNAME, AGEFROM SWHERE SEX=MAND AGE>ALL(SELECT AGEFROM SWHERE SEX=F)；【312】解：INSERT INTO CVALUES(C8，VC+，T6)；法一：INSERT INTO FACULTY(T

12、NAME)SELECT DISTINCT TNAMEFROM (SELECT TNAME，CC#，AVG(SCORE) FROM T, C, SC WHERE TT#=CT# AND CC#=SCC# GROUP BY TNAME，CC#) AS RESULT(TNAME，C#，AVGSCORE) AS XWHERE 80<=ALL(SELECT AVGSCORE FROM RESULT AS Y WHERE YTNAME=XTNAME)；法二INSERT INTO FACULTY(TNAME)SELECT DISTINCT TNAMEFROM T, C, SC WHERE CC#=SC

13、C# AND TT#=CT#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 SCOR

14、E<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 SCSET SCORE=SCORE * 1.04WHERE C#=C4AND SCORE>70；UPDATE SCSET SCORE=SCORE * 1.05WHERE C#=C4AND SCORE&l

15、t;=70；这两个UPDATE语句的顺序不能颠倒。用一个UPDATE语句实现：UPDATE SCSET SCORE=SCORE * CASEWHEN SCORE>70 THEN 1.04ELSE l.05ENDWHERE C#=C4;UPDATE SC SET SCORE=SCORE * 1.05 WHERE SCORE<(SELECT AVG(SCORE) FROM SC)；【313】设数据库中有3个关系：职工表 EMP(E#，ENAME，AGE，SEX，ECITY)，其属性分别表示职工工号、姓名、年龄、性别和籍贯。工作表 WORKS(E#，C#，SALARY)，其属性分别表示职

16、工工号、工作的公司编号和工资。公司表 COMP(C#，CNAME，CITY)，其属性分别表示公司编号、公司名称和公司所在城市。试用SQL语句写出下列操作：用CREATE TABLE语句创建上述3个表，需指出主键和外键。检索超过50岁的男职工的工号和姓名。假设每个职工只能在一个公司工作，检索工资超过1 000元的男性职工工号和姓名。假设每个职工可在多个公司工作，检索至少在编号为C4和C8的公司兼职的职工工号和姓名。检索在联华公司工作、工资超过1 000元的男性职工的工号和姓名。假设每个职工可在多个公司工作，检索每个职工的兼职公司数目和工资总数，显示(E#，NUM，SUM_SALARY)，分别表示

17、工号、公司数目和工资总数。工号为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)

18、 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#，ENAMEFROM EMPWHERE AGE>50 AND SEX=M；SELECT EMPE#，ENAMEFRO

19、M EMP, WORKSWHERE EMP.E#=WORKSE# AND SALARY>1000 AND SEX=M；SELECT AE#，AENAMEFROM EMP A，WORKS B，WORKS CWHERE AE#=BE# AND BE#=CE#AND BC#=C4 AND CC#=C8；SELECT AE#，AENAMEFROM EMP A，WORKS B，COMP CWHERE AE#=BE# AND BC#=C#AND CNAME=联华公司 AND SALARY>1000 AND SEX='M；SELECT E#，COUNT(C#) AS NUM，SUM(SA

20、LARY) AS SUM_SALARYFROM WORKSGROUP BY E#;SELECT X.E#FROM WORKS XWHERE NOT EXISTS(SELECT *FROM WORKS YWHERE E#=E6AND NOT EXISTS(SELECT *FROM WORKS ZWHERE ZE#=XE# AND ZC#=YC#)；SELECT AE#，AENAMEFROM EMP A，WORKS B，COMP CWHERE AE#=BE# AND BC#=CC# AND CNAME=联华公司AND SALARY<(SELECT AVG(SALARY)FROM WORKSC

21、OMP CWHERE WORK SC#=COMPC# AND CNAME=联华公司)；UPDATE WORKSSET SALARY=SALARY+100WHERE E# IN(SELECT E# FROM EMP WHERE AGE>50)；DELETE FROM WORKSWHERE E# IN (SELECT E# FROM EMP WHERE AGE>60)；DELETE FROM EMPWHERE AGE>60；4.8 设关系模式R（ABCD），F是R上成立的FD集，F= AB，CB ，则相对于F，试写出关系模式R的关键码。并说明理由。解：R的关键码为ACD。因为从已知的F，只能推出ACDABCD。4.9 设关系模式R(ABCDE)上FD集为F，并且F=ABC，CDE，DEB，试判断AB是R的候选键吗？ABD呢？请做出解释。解：从已知的F，可推出ABABC，但不能推出ABABCDE，因此不是。 从已知的F，可推出ABDABCD，ABDABCDE，因此是的。4.13 设关系模式R（ABCD），F是R上成立的FD集，F=AB