数据库原理答案.docx

资源描述

数据库原理答案.docx

《数据库原理答案.docx》由会员分享，可在线阅读，更多相关《数据库原理答案.docx（46页珍藏版）》请在冰点文库上搜索。

数据库原理答案.docx

数据库原理答案

第二章

2.6设有关系R和S：

RABCSABC

367345

257723

723

443

计算R∪S，R-S，R∩S，R×S，π3,2（S）,σB<’5’（R）,R2<2S,RS。

解：

R∪SABCR－SABCR∩SABC

367367723

257257

723443

443

345

R×SR.AR.BR.CS.AS.BS.Cπ3，2（S）CB

36734554

36772332

257345

257723

723345

723723

443345

443723

σB<’5’（R）ABCR⋈SR.AR.BR.CS.AS.BS.C

723723345

443

R⋈SABC

723

2.7设有关系R和S：

RABSBC

abbc

cbea

debd

计算R⋈S，R⋈S，σA=C（R×S），S⋉R。

解：

R⋈SABCR⋈SR.AR.BS.BC

abcabbc

abdabbd

cbccbbc

cbdcbbd

dea

S⋉RBC

2.17设有4个关系：

S（S#，SNAME，AGE，SEX）

SC（S#，C#，SCORE）

C（C#，CNAME，T#）

T（T#,TNAME,TITLE）

试用关系代数表达式表示下列查询语句：

①检索年龄小于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中，用户有一查询语句：

检索女同学选修课程的课程名和任课教师名。

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

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

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

解：

①关系代数表达式为：

πCNAME，TEACHER（σSEX=’F’（S⋈SC⋈C⋈T））

上述的关系代数表达式为：

πCNAME，TEACHER（σSEX=’F’（πL（σS.S#=SC.S#∧SC.C#=C.C#（（S×SC）×C））））

此处L为S、SC、C中全部属性（公共属性只取一次）。

②上述关系代数表达式的语法树如图2.2所示。

图2.2

③上述的关系代数表达式为：

优化后的语法树如图2.3所示。

图2.3

第三章

3.2对于教学数据库的4个基本表

S（S#，SNAME，AGE，SEX）

SC（S#，C#，SCORE）

C（C#，CNAME，T#）

T（T#,TNAME,TITLE）

试用SQL的查询语句表达下列查询：

试用关系代数表达式表示下列查询语句：

①检索年龄小于17岁的女学生的学号和姓名。

②检索年男学生所学课程的课程号和课程名。

③检索男学生所学课程的任课教师的工号和姓名。

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

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

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

⑦检索全部学生都选修的课程的课程号与课程名。

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

解：

①SELECTS#,SNAME

FROMS

WHEREAGE<17ANDSEX=’F’;

2SELECTC.C#,CNAME

FROMS,SC,C

WHERES.S#=SC.S#ANDSC.C#=C.C#ANDSEX=’M’;

③SELECTT.T#，TNAME

FROMS,SC,C,T

WHERES.S#=SC.S#ANDSC.C#=C.C#ANDC.T#=T.T#ANDSEX=’M’;

④SELECTDISTINCTX.S#

FROMSCASX,SCASY

WHEREX.S#=Y.S#ANDX.C#!

=Y.C#

⑤SELECTDISTINCTX.C#

FROMSCASX,SCASY

WHEREX.S#=’S2’ANDY.S#=’S4’ANDX.C#=Y.C#;

嵌套写法：

SELECTC#

FROMSC

WHERES#=’S2’ANDC#IN

（SELECTC#

FROMSC

WHERESC.S#=’S4’）;

SELECTC#

FROMC

WHEREC#NOTIN（SELECTC#

FROMS,SC

WHERES.S#=SC.S#ANDSNAME=’WANG’）;

SELECTC#

FROMC

WHERENOTEXISTS

（SELECT*

FROMS,SC

WHERES.S#=SC.S#ANDSC.C#=C.C#

ANDSNAME=’WANG’）;

6SELECTC#,CNAME

FROMC

WHERENOTEXISTS

（SELECT*

FROMS

WHERENOTEXISTS

（SELECT*

FROMSC

WHERES#=S.S#ANDC#=C.C#））;

7SELECTDISTINCTS#

FROMSCASX

WHERENOTEXISTS

（SELECT*

FROMC,T

WHEREC.T#=T.T#ANDTNAME=’LIU’

ANDNOTEXISTS

（SELECT*

FROMSCASY

WHEREY.S#=X.S#ANDY.C#=C.C#））;

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

统计有学生选修的课程门数。

求选修C4课程的女学生的平均年龄。

求LIU老师所授课程的每门课程的平均成绩。

统计每门课程的学生选修人数（超过10人的学生才统计）。

要求显示课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

检索学号比WANG同学大，而年龄比他小的学生姓名。

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

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

求年龄大于女同学平均年龄的男学生姓名和年龄。

求年龄大于所有女同学年龄的男学生姓名和年龄。

解：

SELECTs#,COUNT（DISTINCTC#）

FROMSC

GROUPBYs#；

SELECTAVG（AGE）

FROMS,SC

WHERES.S#=SC.S#ANDC#=’C4’ANDSEX=’F’；

SELECTC.C#，AVG（SCORE）

FROMSC，C,T

WHERESC.C#=C.C#ANDSC.T#=T.T#ANDTNAME=‘LIU’

GROUPBYC.C#；

SELECTC#,COUNT（S#）

FROMSC

GROUPBYC#

HAVINGCOUNT（*）>10

ORDERBY2DESC,1；

SELECTSNAME

FROMS

WHERES#>ALL（SELECTS#

FROMS

WHERESNAME=’WANG’）

ANDAGE

FROMS

WHERESNAME=’WANG’）；

SELECTY.SNAME

FROMSX,SY

WHEREX.SNAME=’WANG’ANDY.S#>X.S#ANDY.AGE

SELECTS#,C#

FROMSC

WHERESCOREISNULL；

SELECTSNAME,AGE

FROMS

WHERESNAMELIKE’L%’；

SELECTSNAME,AGE

FROMS

WHERESEX=’M’

ANDAGE>（SELECTAVG（AGE）

FROMS

WHERESEX=’F’）；

SELECTSNAME,AGE

FROMS

WHERESEX=’M’

ANDAGE>ALL（SELECTAGE

FROMS

WHERESEX=’F’）；

3.8对于下面的关系R和S，试求出下列各种联接操作的执行结果：

①RNATURALINNERJOINS

②RNATURALRIGHTOUTERJOINS

③RRIGHTOUTERJOINSUSING（C）

④RINNERJOINS

⑤RFULLOUTERJOINSONfalse

解：

①

②

③

R.B

S.B

null

④

R.B

R.C

S.B

S.C

⑤

R.B

R.C

S.B

S.C

null

3.12试用SQL更新语句表达对3.2题教学数据库中关系S、SC、C、T的更新操作：

往关系C中插一个课程元组（'C8'，'VC++'，'T6'）。

检索所授每门课程平均成绩均大于80分的教师姓名，并把检索到的值送往另一个已存在的表FACULTY（TNAME）。

在SC中删除尚无成绩的选课元组。

把选修LIU老师课程的女同学选课元组全部删去。

把MATHS课不及格的成绩全改为60分。

把低于所有课程总平均成绩的女同学成绩提高5％。

在表SC中修改C4课程的成绩，若成绩小于等于70分时提高5％，若成绩大于70分时提高4％（用两种方法实现，一种方法是用两个UPDATE语句实现，另一种方法是用带CASE操作的一个UPDATE语句实现）。

⑧在表SC中，当某个成绩低于全部课程的平均成绩时，提高5%。

解：

①INSERTINTOC

VALUES（'C8'，'VC++'，'T6'）；

②INSERTINTOFACULTY（TNAME）

SELECTDISTINCTTNAME

FROMTX

WHERENOTEXIST

（SELECT*

FROMSC,C,TY

WHERESC.C#=C.C#ANDC.T#=T.#ANDX.T#=Y.T#

GROUPBYT#,C.C#HAVINGAVG（SECOR）<=80））;

；

③DELETEFROMSC

WHERESCOREISNULL；

④DELETEFROMSC

WHERES#IN（SELECTS#FROMSWHERESEX='F'）

ANDC#IN（SELECTC#FROMC,TWHEREC.T#=T.#ANDTNAME='LIU'）；

⑤UPDATESC

SETSCORE=60

WHERESCORE<60

ANDC#IN（SELECTC#FROMCWHERECNAME='MATHS'）；

⑥UPDATESC

SETSCORE=SCORE*1.05

WHERES#IN（SELECTS#FROMSWHERESEX='F'）

ANDSCORE<（SELECTAVG（SCORE）FROMSC）；

⑦用两个UPDATE语句实现：

UPDATESC

SETSCORE=SCORE*1.04

WHEREC#='C4'ANDSCORE>70；

UPDATESC

SETSCORE=SCORE*1.05

WHEREC#='C4'ANDSCORE<=70；

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

）

用一个UPDATE语句实现：

UPDATESC

SETSCORE=SCORE*CASE

WHENSCORE>70THEN1.04

ELSE1.05

END

WHEREC#='C4'；

⑧UPDATESC

SETSCORE=SCORE*1.05

WHERESCORE<（SELECTAVG（SCORE）

FROMSC）；

3.13设数据库中有三个关系：

职工表EMP（E#，ENAME，AGE，SEX，ECITY），

其属性分别表示职工工号、姓名、年龄、性别和籍贯。

工作表WORKS（E#，C#，SALARY），

其属性分别表示职工工号、工作的公司编号和工资。

公司表COMP（C#，CNAME，CITY），

其属性分别表示公司编号、公司名称和公司所在城市。

试用SQL语句写出下列操作：

①用CREATETABLE语句创建上述三个表，需指出主键和外键。

②检索超过50岁的男职工的工号和姓名。

③假设每个职工只能在一个公司工作，检索工资超过1000元的男性职工工号和姓名。

④假设每个职工可在多个公司工作，检索在编号为C4和C8公司兼职的职工工号和姓名。

⑤检索在“联华公司”工作、工资超过1000元的男性职工的工号和姓名。

⑥假设每个职工可在多个公司工作,检索每个职工的兼职公司数目和工资总数.显示（E#，NUM，SUM_SALARY），分别表示工号、公司数目和工资总数。

⑦工号为E6的职工在多个公司工作，试检索至少在E6职工兼职的所有公司工作的职工工号。

⑧检索联华公司中低于本公司平均工资的职工工号和姓名。

⑨在每一公司中为50岁以上职工加薪100元（若职工为多个公司工作，可重复加）。

⑩在EMP表和WORKS表中删除年龄大于60岁的职工有关元组。

解：

①CREATETABLEEMP

（E#CHAR（4）NOTNULL,

ENAMECHAR（8）NOTNULL,

AGESMALLINT,

SEXCHAR

（1）,

ECITYCHAR（20）,

PRIMARYKEY（E#））；

CREATETABLECOMP

（C#CHAR（4）NOTNULL,

CNAMECHAR（20）NOTNULL,

CITYCHAR（20）,

PRIMARYKEY（C#））；

CREATETABLEWORKS

（E#CHAR（4）NOTNULL,

C#CHAR（4）NOTNULL,

SALARYSMALLINT,

PRIMARYKEY（E#,C#）,

FOREIGNKEY（E#）REFERENCESEMP（E#）,

FOREIGNKEY（C#）REFERENCESCOMP（C#））；

②SELECTE#,ENAME

FROMEMP

WHEREAGE>50ANDSEX='M'；

③SELECTEMP.E#,ENAME

FROMEMP,WORKS

WHEREEMP.E#=WORKS.E#ANDSALARY>1000；

④SELECTA.E#,A.ENAME

FROMEMPA,WORKSB,WORKSC

WHEREA.E#=B.E#ANDB.E#=C.E#

ANDB.C#='C4'ANDC.C#='C8'；

⑤SELECTA.E#,A.ENAME

FROMEMPA,WORKSB,COMPC

WHEREA.E#=B.E#ANDB.C#=C.C#

ANDCNAME='联华公司'ANDSALARY>1000

ANDSEX='M'；

⑥SELECTE#,COUNT（C#）ASNUM,SUM（SALARY）ASSUM_SALARY

FROMWORKS

GROUPBYE#；

⑦SELECTX.E#

FROMWORKSX

WHERENOTEXISTS

（SELECT*

FROMWORKSY

WHEREE#='E6'

ANDNOTEXISTS

（SELECT*

FROMWORKSZ

WHEREZ.E#=X.E#

ANDZ.C#=Y.C#））；

⑧SELECTA.E#,A.ENAME

FROMEMPA,WORKSB,COMPC

WHEREA.E#=B.E#ANDB.C#=C.C#

ANDCNAME='联华公司'

ANDSALARY<（SELECTAVG（SALARY）

FROMWORKS,COMP

WHEREWORKS.C#=COMP.C#

ANDCNAME='联华公司'）；

⑨UPDATEWORKS

SETSALARY=SALARY+100

WHEREE#IN（SELECTE#FROMEMPWHEREAGE>50）；

⑩DELETEFROMWORKS

WHEREE#IN（SELECTE#FROMEMPWHEREAGE>60）；

DELETEFROMEMP

WHEREAGE>60；

3.14对第3.13题中的关系建立一个有关女职工信息的视图EMP_WOMAN，属性包括（E#，ENAME，C#，CNAME，SALARY）。

然后对视图EMP_WOMAN操作，检索每一位女职工的工资总数。

（假设每个职工可在多个公司兼职）

解：

CREATEVIEWEMP_WOMAN

ASSELECTA.E#,A.ENAME,C.C#,CNAME,SALARY

FROMEMPA,WORKSB,COMPC

WHEREA.E#=B.E#ANDB.C#=C.C#

ANDSEX='F'；

SELECTE#，SUM（SALARY）

FROMEMP_WOMAN

GROUPBYE#；

3.15对于3.2题的教学数据库中基本表SC，建立一个视图：

CREATEVIEWS_SCORE（S#，C_NUM，AVG_SCORE）

ASSELECTS#，COUNT（C#），AVG（SCORE）

FROMSC

GROUPBYS#；

试判断下列查询和更新操作是否允许执行。

如允许，写出转换到基本表SC上的相应操作。

①SELECT*

FROMS_SCORE；

②SELECTS#，C_NUM

FROMS_SCORE

WHEREAVG_SCORE>80；

③SELECTS#，AVG_SCORE

FROMS_SCORE

WHEREC_NUM>（SELECTC_NUM

FROMS_SCORE

WHERES#='S4'）；

④UPDATES_SCORE

SETS#='S3'

WHERES#='S4'；

⑤DELETEFROMS_SCORE

WHEREC_NUM>4；

答：

①允许查询。

相应的操作如下：

SELECTS#，COUNT（C#）ASC_NUM，AVG（SCORE）ASAVG_SCORE

FROMSC

GROUPBYS#；

②允许查询。

相应的操作如下

展开阅读全文