习题课1(2013)

1.试述数据库的三级模式结构及其带来的好处。数据库的三级模式包括外模式、逻辑模式和内模式，以及外模式/逻辑模式映像和逻辑模式/内模式映像。外模式：是用户观念下局部数据结构的逻辑描述,是数据库用户(包括应用程序员和最终用户)能够看见和使用的局部数据用逻辑数据模型对用户用到的数据的描述。逻辑模式：是数据库中全体数据的逻辑结构和特征的描述,是所有用户的公共数据视图,外模式是逻辑模式的一个逻辑子集。内模式：是对数据库中数据的物理结构和存储方式的描述，是数据在数据库内部的表现形式，一个数据库只有一个内部模式。数据库系统习题课一第一章习题参考答案：第一章带来的好处：1.数据独立性三级模式是对数据的三个抽象级别，它把数据的具体组织留给DBMS管理，使用户能逻辑地抽象地处理数据，而不必关心数据在计算机中的具体表示形式与存储方式，提高数据的独立性。2.方便了数据库的设计和实现数据库系统习题课一第一章习题P179——5.13数据库系统习题课一第一章习题设某商业集团数据库中有三个实体集。一是“商店”实体集，属性有商店编号、商店名、地址等；二是“商品”实体集，属性有商品编号、商品名、规格、单价等；三是“职工”实体集，属性有职工编号、姓名、性别、业绩等。商店与商品间存在“销售”联系，每个商店可销售多种商品，每种商品也可放在多个商店销售，每个商店销售一种商品，有月销售量；商店与职工间存在着“聘用”联系，每个商店有许多职工，每个职工只能在一个商店工作，商店聘用职工有聘期和月薪。试画出ER图，并在图上标明属性、联系的类型。商店商品职工聘用销售mn1m商店编号商店名地址职工编号姓名业绩性别规格商品编号商品名单价聘期月薪月销售量数据库系统习题课一第一章习题参考答案：关系模型的参照完整性约束要求不能引用不存在的实体，通过外键实现。当外键不是本关系主键的一部分（主属性），允许外键为空；否则，不允许为空。数据库系统习题课一第二章习题2.4．外键值何时允许为空？何时不允许为空？参考答案：第二章2.6.设有关系R和S，如下：RABC367257723443SABC345723ABC367257723443345R∪SABC367257443R－SABC723R∩S数据库系统习题课一第二章习题参考答案：RABC367257723443SABC345723R.AR.BR.CS.AS.BS.C367345367723257345257723723345723723443345443723R×S3,2(S)CB5432数据库系统习题课一第二章习题RABC367257723443SABC345723σB‘5’(R)ABC723443R.AR.BR.CS.AS.BS.C723345RS22ABC723RS数据库系统习题课一第二章习题数据库系统习题课一第二章习题评注：◆关系代数的运算次序:()、一元、二元◆公共属性上的连接是自然连接，要消除结果中的冗余属性◆R中每个元组与S中每个元组比较、连接用关系代数表达式表下列查询语句：1)检索年龄小于17岁的女学生的学号和姓名2)检索男学生所学课程的课程号和课程名πS#,SNAME(σAGE‘17’∧sex=‘女’(S))或π1,2(σ3‘17’∧4=‘女’(S))T(T#,TNAME,TITLE)C(C#,CNAME,T#)S(S#,SNAME,AGE,SEX)SC(S#,C#,SCORE)2.17设有三个关系：数据库系统习题课一第二章习题参考答案：πC#,CNAME(σsex=‘男’(SSCC))))3)检索男学生所学课程的任课老师的工号和姓名4)检索至少选修两门课的学生学号5)检索至少有学号为S2和S4学生选修的课程的课程号6)检索WANG同学不学的课程的课程号π1(σ1=4∧2!=5(SC×SC))πC#(C)－πC#(σSNAME=‘WANG’(SSC))数据库系统习题课一第二章习题π2(σ2=5∧1=‘S2’∧4=‘S4’(SC×SC))πT#,TNAME(σSEX=‘男’(SSCCT))πC#,CNAME(C()学生选课情况表示为：πS#,C#(SC)全部学生表示为：πS#(S)全部学生都选修的课程可用除法操作表示为：πS#,C#(SC)÷πS#(S)数据库系统习题课一第二章习题7)检索全部学生都选修的课程的课程号与课程名8)检索选修课程包含LIU老师所授全部课程的学生学号学生选课情况表示为：πS#,C#(SC)LIU老师所授全部课程为：πC#(σTNAME=LIU’(CT))所学课程包含LIU老师所授全部课程的学生学号πS#,C#(SC)÷πC#(σTNAME=‘LIU’(CT))试用SQL的查询语句表达下列查询：1)检索年龄小于17岁的女同学的学号和姓名2)检索男同学所学课程的课程号和课程名SELECTS#,SNAMEFROMSWHEREAGE＜17ANDSEX=‘F’;S(S#,SNAME,AGE,SEX)SC(S#,C#,SCORE)C(C#,CNAME,T#)T（T#，TNAME，TITLE）3.2对于教学数据库的四个基本表：数据库系统习题课一第三章习题参考答案：SELECTC.C#,CNAMEFROMS,SC,CWHERES.S#=SC.S#ANDSC.C#=C.C#ANDSEX=‘M’;第三章3)检索男同学所学课程的任课老师的工号和姓名4)检索至少选修两门课的学生学号SELECTT.T#,TNAMEFROMS,SC,C,TWHERES.S#=SC.S#ANDSC.C#=C.C#ANDC.T#=T.T#ANDSEX=‘M’;数据库系统习题课一第三章习题SELECTDISTINCTX.S#FROMSCASX，SCASYWHEREX.S#=Y.S#ANDX.C#!=Y.C#;SELECTS#FROMSCGROUPBYS#HAVINGCOUNT(*)1;5)检索至少有学号为S2和S4的学生选修的课程的课程号SELECTDISTINCTX.C#FROMSCASX，SCASYWHEREX.C#=Y.C#ANDX.S#=‘S2’ANDY.S#=‘S4’;数据库系统习题课一第三章习题7)检索全部学生都选修的课程的课程号与课程名SELECTC#,CNAMEFROMCWHERENOTEXISTS(SELECT*FROMSWHERENOTEXISTS(SELECT*FROMSCWHEREC.C#=SC.C#ANDSC.S#=S.S#));6)检索WANG同学不学的课程的课程号SELECTC#FROMCWHEREC#NOTIN(SELECTC#FROMS,SCWHERES.S#=SC.S#ANDSNAME=‘WANG’);数据库系统习题课一第三章习题7)检索全部学生都选修的课程的课程号与课程名SELECTC.C#,CNAMEFROMSC,CWHERESC.C#=C.C#GROUPBYC.C#,CNAMEHAVINGCOUNT(*)=(SELECTCOUNT(S#)FROMS)解法二：8)检索选修课程包含LIU老师所授全部课程的学生学号数据库系统习题课一第三章习题SELECTDISTINCTS#FROMSCASXWHERENOTEXISTS(SELECT*FROMC,TWHEREC.T#=T.T#ANDTNAME=‘LIU’ANDNOTEXISTS(SELECT*FROMSCASYWHEREY.S#=X.S#ANDY.C#=C.C#));1)统计有学生选修的课程门数2)求选修C4课程的女同学的平均年龄SELECTCOUNT(DISTINCTC#)FROMSC；SELECTAVG(AGE)FROMSWHERESEX=‘女’ANDS#IN(SELECTS#FROMSCWHEREC#=‘C4’);S(S#,SNAME,AGE,SEX)SC(S#,C#,SCORE)C(C#,CNAME,T#)T(T#,TNAME,TITLE）3.7试用SQL查询语句表达下列对3.2题的教学数据库中四个基本表S、SC、C、T的查询：数据库系统习题课一第三章习题参考答案：3)求LIU老师所授课程的每门课程的平均成绩4)统计每门课程的学生选修人数（超过10人的课程才统计）。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。SELECTC.C#,AVG(SCORE)FROMSC,C,TWHERESC.C#=C.C#ANDC.T#=T.T#ANDTNAME=‘LIU’GROUPBYC.C#;数据库系统习题课一第三章习题SELECTC#,COUNT(S#)FROMSCGROUPBYC#HAVINGCOUNT(S#)10ORDEYBY2DESC,1ASC;5)检索学号比WANG同学大，而年龄比他小的学生姓名6)在表SC中检索成绩为空值的学生学号和课程号。SELECTSNAMEFROMSWHERES#ALL(SELECTS#FROMSWHERESNAME=‘WANG’)ANDAGEALL(SELECTAGEFROMSWHERESNAME=‘WANG’);数据库系统习题课一第三章习题SELECTS#,C#FROMSCWHERESCOREISNULL;数据库系统习题课一第三章习题7)检索姓名以L打头的所有学生的姓名和年龄SELECTSNAME,AGEFROMSWHERESNAMELIKE‘L%’;8)检索年龄大于女同学平均年龄的男同学姓名和年龄SELECTSNAME,AGEFROMSWHERESEX=‘M’ANDAGE(SELECTAVG(AGE)FROMSWHERESEX=‘F’);9)检索年龄大于所有女同学年龄的男同学姓名和年龄SELECTSNAME,AGEFROMSWHERESEX=‘M’ANDAGE(SELECTMAX(AGE)FROMSWHERESEX=‘F’);1)往关系C中插一个课程元组(‘C8’,’VC++’,’T6’)2)检索所授每门课程平均成绩均大于80分的教师姓名，并把检索到的值送往另一个已存在的表FACULTY(TNAME)INSERTINTOCVALUES(‘C8’,’VC++’,’T6’)；3.12试用SQL更新语句表达下列对3.2题的教学数据库中关系S、SC、C、T的更新操作：数据库系统习题课一第三章习题参考答案：数据库系统习题课一第三章习题2)检索所授每门课程平均成绩均大于80分的教师姓名，并把检索到的值送往另一个已存在的表FACULTY(TNAME)INSERTINTOFACULTYSELECTDISTINCTTNAMEFROMC,T,SCWHEREC.T#=T.T#ANDC.C#=SC.C#ANDT#NOTIN(SELECTT#FROMCWHEREC#IN(SELECTC#FROMSCGROUPBYC#HAVINGAVG(SCORE)=80));解法一：数据库系统习题课一第三章习题2)检索所授每门课程平均成绩均大于80分的教师姓名，并把检索到的值送往另一个已存在的表FACULTY(TNAME)解法二：INSERTINTOFACULTYSELECTDISTINCTTNAMEFROM(SELECTTNAME,C.C#,AVG(SCORE)FROMT,C,SCWHERET.T#=C.T#ANDC.C#=SC.C#GROUPBYTNAME,C.C#）ASRESULE(TNAME,C#,AVG_SCORE)ASXWHERE80=ALL(SELECTAVG_SCOREFROMRESULTASYWHEREY.TNAME=X.TNAME);3)在SC中删除尚无成绩的选课元组4)把选修LIU老师课程的女同学选课元组全部删去。DELETEFROMSCWHERESCOREISNULL;数据库系统习题课一第三章习题DELETEFROMSCWHEREC#IN(SELECTC#FROMC,TWHEREC.T#=T.T#ANDTNAME=‘LIU’)ANDS#IN(SELECTS#FROMSWHERESEX=‘F’)；5)把MATHS课不及格的成绩全改为60分。UPDATESCSETSCORE=60WHERESC