数据库系统大题

①一个学生可选修多门课，一门课有若干学生选修；②一个教师可讲授多门课，一门课只有一个教师讲授；③一个学生选修一门课，仅有一个成绩。要求：根据上述语义画出ER图，要求在图中画出实体的属性并注明联系的类型；解答：成绩教师编号教师姓名学生选修m课程教师讲授nn1学号姓名课程号课程名1、设有如下所示的关系S(S#,SNAME,AGE,SEX)、C(C#,CNAME,TEACHER)和SC(S#,C#,GRADE)，试用关系代数表达式表示下列查询语句：(1)检索“程军”老师所授课程的课程号(C#)和课程名(CNAME)。(2)检索年龄大于21的男学生学号(S#)和姓名(SNAME)。(3)检索至少选修“程军”老师所授全部课程的学生姓名(SNAME)。(4)检索”李强”同学不学课程的课程号(C#)。(5)检索至少选修两门课程的学生学号(S#)。(6)检索全部学生都选修的课程的课程号(C#)和课程名(CNAME)。(7)检索选修课程包含“程军”老师所授课程之一的学生学号(S#)。(8)检索选修课程号为k1和k5的学生学号(S#)。(9)检索选修全部课程的学生姓名(SNAME)。(10)检索选修课程包含学号为2的学生所修课程的学生学号(S#)。(11)检索选修课程名为“C语言”的学生学号(S#)和姓名(SNAME)。解：本题各个查询语句对应的关系代数表达式表示如下：(1).∏C#,CNAME(σTEACHER=‘程军’(C))(2).∏S#,SNAME(σAGE21∧SEX=”男”(C))(3).∏SNAME{s[∏S#,C#（sc）÷∏C#(σTEACHER=‘程军’(C))]}(4).∏C#(C)-∏C#(σSNAME=‘李强’(S)SC)(5).∏S#(σ[1]=[4]∧[2]≠[5](SC×SC))(6).∏C#,CNAME(C(∏S#,C#(sc)÷∏S#(S)))(7).∏S#(SC∏C#(σTEACHER=‘程军’(C)))(8).∏S#,C#（sc）÷∏C#(σC#=’k1’∨C#=’k5’(C))(9).∏SNAME{s[∏S#,C#（sc）÷∏C#(C)]}(10).∏S#,C#（sc）÷∏C#(σS#=’2’(SC))(11).∏S#,SNAME{s[∏S#(SCσCNAME=‘C语言’(C))]}2、关系R和S如下图所示，试计算R÷S。RABCDabcdabefabhkbdefbddlckcdckef1、设有如下关系表R：R(No，NAME，SEX，AGE，CLASS)主关键字是NO其中NO为学号，NAME为姓名，SEX为性别，AGE为年龄，CLASS为班号。写出实现下列功能的SQL语句。①插入一个记录(25，“李明”，“男”，21，“95031”)；②插入“95031”班学号为30、姓名为“郑和”的学生记录；③将学号为10的学生姓名改为“王华”；④将所有“95101”班号改为“95091”；⑤删除学号为20的学生记录；⑥删除姓“王”的学生记录；答案：①INSERTINTORVALUES(25，“李明”，“男”，21，“95031”)②INSERTINTOR(NO，NAME，CLASS)VALUES(30，“郑和”，“95031”)③UPDATERSETNAME=“王华”WHERENO＝10④UPDATERSETCLASS＝“95091”WHERECLASS＝“95101”⑤DELETEFROMRWHERENO=20⑥DELETEFROMRWHERENAMELIKE“王％”2、设学生课程数据库中有三个关系：学生关系S（S#，SNAME，AGE，SEX）学习关系SC（S#，C#，GRADE）课程关系C（C#，CNAME）其中S#、C#、SNAME、AGE、SEX、GRADE、CNAME分别表示学号、课程号、姓名、年龄、性别、成绩和课程名。SCDcdef用SQL语句表达下列操作（1）检索选修课程名称为“MATHS”的学生的学号与姓名（2）检索至少学习了课程号为“C1”和“C2”的学生的学号（3）检索年龄在18到20之间（含18和20）的女生的学号、姓名和年龄（4）将一个新学生元组（学号：200215128；姓名：王丽；年龄：19；性别：女）插入到S表中（5）将选修课程号为“C1”的同学成绩加5分（6）找出每个学生超过他选修课程平均成绩的课程号（7）查询查询选修“C2”号课程且成绩在90分以上的所有学生。（8）查询所有姓刘的学生的姓名、学号和性别（9）检索平均成绩超过80分的学生学号和平均成绩（10）检索选修了全部课程的学生姓名（11）检索选修了三门课以上的学生的姓名（12）创建视图stu_info，包括选修“C1”号课程所有学生的学号、姓名、性别、年龄、选修课程号、课程名、成绩答案：（1）SELECTSNAME,AGEFROMS,SC,CWHERES.S#=SC.S#ANDC.C#=SC.C#ANDCNAME=’MATHS’(2)SELECTS#FROMSCWHERECNO=’C1’ANDS#IN(SELECTS#FROMSCWHERECNO=’C2’)(3)SELECTS#,SNAME,AGEFROMSWHEREAGEBETWEEN18AND20(4)INSERTINTOS(S#,SNAME,AGE,SEX)VALUES(“200215128”,”王丽”,19,”女”)；(5)UPDATESCSETGRADE=GRADE+5WHEREC#=‘C1’;(6)SELECTS#,C#FROMSXWHEREGRADE=(SELECTAVG(GRADE)FROMCYWHEREY.S#=X.S#);(7)SELECTS.S#,SNAMEFROMS,SCWHERES.S#=SC.S#ANDSC.C#=’C2’ANDSC.GRADE90;(8)SELECTSNAME,S#,SEXFROMSWHERELIKE‘刘%’;(9)SELECTS#,AVG(GRADE)‘平均成绩’FROMSCGROUPBYS#HAVINGAVG(GRADE)80(10)SELECTSNAMEFROMSWHERENOTEXISTS(SELECT*FROMCWHERENOTEXISTS(SELECT*FROMSCWHERES#=S.S#ANDC#=C.C#))(11)SELECTSNAMEFROMS,SCWHERES.S#=SC.S#GROUPBYSNAMEHAVINGCOUNT(*)3(12)CREATEVIEWSTU_INFOASSELECTS.SNO,SNAME,SEX，AGE，C.CNO，CNAME，GRADEFROMS，SC，CWHERES.SNO=SC.SNOANDC.CNO=SC.CNOANDC.CNO=‘C1’3、设学生-课程数据库中包括三个表：学生表：Student（Sno，Sname，Sex，Sage，Sdept）课程表：Course（Cno，Cname，Ccredit）学生选课表：SC（Sno，Cno，Grade）其中Sno、Sname、Sex、Sage、Sdept、Cno、Cname、Ccredit、Grade分别表示学号、姓名、性别、年龄、所在系名、课程号、课程名、学分和成绩。试用SQL语言完成下列项操作：（1）查询选修课程包括“1042”号学生所学的课程的学生学号（2）创建一个计科系学生信息视图S_CS_VIEW，包括Sno学号、Sname姓名、Sex性别;（3）通过上面第2题创建的视图修改数据，把王平的名字改为王慧平（4）创建一选修数据库课程信息的视图，视图名称为datascore_view，包含学号、姓名、成绩。答案：(1)SELECTDISTINCTSNOFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.SNO='1042'ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.SNO=SCX.SNOANDSCZ.CNO=SCY.CNO))；(2)CREATEVIEWS_CS_VIEWASSELECTSNO,SNAME,SEXFROMSTUDENTWHERESDEPT=’CS’(3)UPDATES_CS_VIEWSETSNAME=’王慧平’WHERESNAME=’王平’(4)CREATEVIEWDATASCORE_VIEWASSELECTSNO学号、SNAME姓名、GRADE成绩FROMSTUDENT,SC,COURSEWHERESTUDENT.SNO=SC.SNOANDCOURSE.CNO=SC.CNOANDCNAME=’数据库’1、设有关系模式S-L-C(Sno,Sdept,Sloc,Cno,Grade),其中属性分别表示：学号、所在系、学生住处、课程号、成绩。假定每个系的学生住在同一个地方，每个学生可选修多门课程，每门课程可被多个学生选修，请根据语义写出该关系模式的主码及基本函数依赖，并通过模式分解，使其分解为与之等价的3NF模式集。答：主码为(Sno，Cno)。基本函数依赖：(Sno，Cno)GradeSnoSdept，(Sno，Cno)SdeptSnoSloc，(Sno，Cno)Sloc，SdeptSloc分解结果(3NF)：SC(Sno，Cn，Grade)S-D(Sno，Sdept)D-L(Sdept，Sloc)2、已知学生关系模式S(Sno，Sname，SD，Sdname，Course，Grade)其中：Sno学号、Sname姓名、SD系名、Sdname系主任名、Course课程、Grade成绩。(1)写出关系模式S的基本函数依赖和主码。(2)原关系模式S为几范式？为什么？分解成高一级范式，并说明为什么?(3)将关系模式分解成3NF，并说明为什么?(1)写出关系模式S的基本函数依赖和主码。答:关系模式S的基本函数依赖如下：Sno→Sname，SD→Sdname，Sno→SD，(Sno，Course)→Grade关系模式S的码为：（Sno，Course）。(2)原关系模式S为几范式？为什么？分解成高一级范式，并说明为什么?答:原关系模式S是属于1NF的，码为(Sno，Course)，非主属性中的成绩完全依赖于码，而其它非主属性对码的函数依赖为部分函数依赖，所以不属于2NF。消除非主属性对码的函数依赖为部分函数依赖，将关系模式分解成2NF如下：S1(Sno，Sname，SD，Sdname)S2(Sno，Course，Grade)(3)将关系模式分解成3NF，并说明为什么?答:将上述关系模式分解成3NF如下：关系模式S1中存在Sno→SD，SD→Sdname，即非主属性Sdname传递依赖于Sno，所以S1不是3NF。进一步分如下：S11(Sno，Sname,SD)S12(SD，Sdname)分解后的关系模式S11、S12满足3NF。对关系模式S2不存在非主属性对码的传递依赖，故属于3NF。所以，原模式S(Sno，Sname，SD，Sdname，Course，Grade)按如下分解满足3NF。S11(Sno，Sname，SD)S12(SD，Sdname)S2(Sno，Course，Grade)PP（1）．一个学生可选修多门课程，一门课程可为多个学生选修；（2）．一个教师可讲授多门课程，一门课程可为多个教师讲授；（3）．一个单位可有多个教师，一个教师只能属于一个单位。试完成如下工作：（1）．分别设计学生选课和教师任课两个局部信息的结构E-R图。（2）．将上述设计完成的E-R图合并成一个全局E-R图。（3）．将该全局E-R图转换为等价的关系模型表示的数据库逻辑结构。解：（1）．学生选课、教师任课局部E-R图如下所示。（2）．合并后的全局E-R图如下所示。开课单位课程选修学生拥有姓名性别学号课程名教师号编号单位名年龄11mmnm学生选课局部E-R图教师课程讲授单位属于单位名电话编号姓名性别教师号年龄mnm1教师授课局部E-R图属于单位课程选修学生拥有教师开课讲授111mmmnnmm全局E-R图3、请设计一个图书馆数据库，此数据库中对每个