数据库系统概论第二次作业及参考答案1、一个电影资料数据库假定有三个基本表：电影表Movie、演员表Actor和电影主演表Acts。
Movie中的属性包括电影名、制作年份、长度等，Actor中的属性包括姓名、地址、性别，出生日期等，Acts反映的是哪部电影是哪位演员主演。
用SQL的建表语句建立这三个基本表，要注意合理地选择属性的类型。
答：CREATE TABLE Movie(CHAR(30),TitleYearINT,INT);LengthActor(CREATETABLECHAR(20),NameV ARCHAR(30),AddressCHAR(1),GenderBirthdateDATE);Acts(CREATECHAR(30),MovieTitleMovieYearINT,CHAR(20));ActorName2、在习题1的基础上，用SQL实现如下查询或更新操作：（1）统计1999年制作电影的平均长度；（2）在1999年主演过电影的演员姓名；（3）所有同名电影各自的电影名和制作年份；（4）往演员关系Actor中插入一个演员记录，具体的分量由你指定；（5）把电影“我的世界”的长度改为109分钟；（6）删除1940年以前制作的所有电影记录以及电影主演记录。
答：（1）SELECT A VG(Length), MIN(Length) FROM Movie WHERE Year=1999;（2）SELECT ActorName FROM Acts WHERE MovieYear=1999;（3）SELECT M1.Title,M1.Year,M2.YearFROM Movie AS M1,Movie AS M2WHERE M1.Title=M2.Title AND M1.Year<M2.Year;（4）INSERT INTO Actor(Name,Gender) V ALUES(‘秀兰·邓波儿’,’F’);（5）UPDATE Movie SET Length=109 WHERE Title=’我的世界’;（6）DELETE FROM Movie WHERE Year<1940;DELETE FROM Acts WHERE MovieYear<1940;3、在习题1建立的表的基础上，要求为Movie关系增加一个属性MovieDirector（电影导演），用SQL实现这种模式的改变。
答：ALTER TABLE Movie ADD MovieDirector CHAR(20)；4、假设学生-课程数据库关系模式如下：Student (Sno,Sname ,Sage,Ssex ) Course (Cno,Cname,Teacher) SC(Sno,Cno,Grade)用SQL语句表达下列查询：（1）找出刘老师所授课程的课程号和课程名。
（2）找出年龄小于22岁女生的学号和姓名。
（3）找出至少选修刘老师讲的一门课的学生姓名。
（4）找出“程序设计”课成绩在90分以上的学生姓名。
（5）找出不学C3课的学生姓名。
（6）找出至少选修C1课和C2课的学生学号。
答：（1）SELECT Cno,Cname FROM Course WHERE Teacher LIKE ‘刘%’;（2）SELECT Sno,Sname FROM Student WHERE Sage<22 AND Ssex=’F’；（3）SELECT Sname FROM Student,SC,CourseWHERE Student.Sno=SC.Sno AND o=o AND Teacher LIKE ‘刘%’;（4）SELECT Sname FROM Student,SC,CourseWHERE Student.Sno=SC.Sno AND o=oAND Cname=’程序设计’ AND Grade>90；（5）SELECt Sname FROM Student WHERE NOT EXISTS(SELECT * FROM SCWHERE Sno=Student.Sno AND Cno=’C3’);（6）SELECT X.Sno FROM SC AS X, SC AS YWHERE X.Sno=Y.Sno AND o=’C1’ AND o=’C2’;5、按照习题4的数据库模式写出下列查询：（1）求孙老师讲的每门课的学生平均成绩。
（2）统计选修各门课的学生人数，输出课程号和人数。
查询结果按人数降序排列，若人数相同按课程号升序排列。
答：（1）SELECT Cno,A VG(Grade) FROM SC,CourseWHERE o=o AND Teacher LIKE ‘孙%’GROUP BY Cno;（2）SELECT Cno,COUNT(*) FROM SCGROUP BY CnoORDER BY COUNT(*) DESC,Cno;6、用SQL的更新语句表达对学生-选课数据库的下列更新操作：（1）往学生关系Student中插入一个学生元组（990012，梅力松，20，女）。
（2）从学生选课关系SC中删除夏春秋同学的所有元组。
（3）从学生选课关系SC中，把英语课的成绩提高10％。
答：（1）INSERT INTO Student V ALUES(990012,’梅立松’,20,’女’);（2）DELETE FROM SC WHERE Sno IN(SELECT Sno FROM Student WHERE Sname=’夏春秋’);（3）UPDATE SC SET Grade=1.1*Grade WHERE Cno IN(SELECT Cno FROM Course WHERE Cname=’英语’);7、从学生-课程数据库中查询选修“数据库原理”课并且成绩在90分以上的学生名单，SQL 语句序列如下：SELECT Sname FROM Student, SC, CourseWHERE Student.Sno=SC.Sno AND o=o ANDame=’数据库原理’ AND SC.Score>90（1）画出这个查询的关系代数语法树；（2）对该语法树进行优化；（3）画出原始的和优化的语法树。
答：（1）该查询的关系代数语法树如下：πSname（2①>90SC.Score>90②Score>90（3优化语法树如下（Student简写为S，Course简写为C）：πSNameσS.SNo=SC.SNo╳πS.SNo,SName πSC.SNoS σo=o╳πSC.SNo,o πoσScore>90 σCName=’数据库原理’SC C8、参照上题的学生-课程数据库查询郑小华同学选修课程的课程名和任课老师姓名。
（1）写出该查询的关系代数表达式；（2）写出表达式的等价变换过程；答：（1）查询的关系代数表达式如下：πCname,Teachar((σ（2）为说明等价变换过程，以笛卡尔积为基础表达查询（Student简化为S，Course简化为C）：πCname,Teacher(σSname=’郑小华’(σS.Sno=SC.Sno AND o=o(S ╳ SC ╳ C)))①利用选择的交换律σSname=’郑小华’和σS.Sno=SC.Sno AND o=o交换，得到σSname=’郑小华’(S ╳ SC ╳ C)②利用选择对笛卡尔的分配律得到σSname=’郑小华’(S )╳ SC ╳ C③利用选择的串接律σS.Sno=SC.Sno AND o=o分解为σS.Sno=SC.Sno和σo=o④利用选择对笛卡尔积的分配律，得到σo=o（σS.Sno=SC.Sno（σSname=’郑小华’(S) ╳ SC ）╳ C）⑤利用选择与投影的串接律πCname,Teacher（σo=o（…））⇒πCname,Teacher（σo=o（πCname,Teacher,o,o(…)））a) 把πCname,Teacher,o,o分解为πCname,Teacher,o和πob) 与e,f类似，对πo（σS.Sno=SC.Sno（…））做相应变换，并把πo,S.Sno,SC.Sno分解为πS.Sno和πSC.Sno,oc) 对πS.Sno(σSname=’郑小华’(S))作相应变换，最终结果如下：πCname,Teacher(σo=o(πo(σS.Sno=SC.Sno(πS.Sno(σSname=’郑小华’(πS.Sno,Sname(S))) ╳πSC.Sno,SC,Cno(SC))) ╳ C))可用自然连接表示如下：πCname,Teacher（πo（πS.Sno（σSname=’郑小华’(πS.Sno,Sname(S))）SC.Sno,o（SC））C）9、假设关系模式为R(A,B,C,D)，函数依赖为AÆ B，BÆC和BÆD（1）求蕴含于给定函数依赖的所有非平凡函数依赖。
（2）求R的所有键码和所有超键码。
答：（1）先求各种属性组合的封闭集，再从中找出新的函数依赖。
①单属性的封闭集有：A+=ABCD B+=BCD C+=C D+=D新的函数依赖：AÆC, AÆD ……（2个）②双属性的封闭集有：A B+=ABCD A C+=ABCD A D+=ABCD BC+=BCD BD+=BCD CD+=CD新的函数依赖：ABÆC ABÆD ACÆB ACÆD ADÆBADÆC BCÆD BDÆC （8个）③三属性的封闭集有：A B C+=ABCD A B D+=ABCD BCD+=BCD新的函数依赖：ABCÆD ABDÆC （2个）④四属性的封闭集有：A B C D+=ABCD蕴含于给定函数依赖的非平凡函数依赖共12个。
（2）A为键码。
AB,AC,AD,ABC,ABD,ABCD为超键码。
10、设关系模式为R(A,B,C,D,E)，函数依赖为AB Æ C，CÆ D和D ÆA（1）找出所有违背BCNF的函数依赖。
（2）把关系模式R分解成属于BCNF的关系模式的集合。
答：（1）共有14个非平凡函数依赖（包括已知的和导出的）：CÆA,CÆD,DÆAABÆC,ABÆD,ACÆD,BCÆA,BCÆD,BDÆA,BDÆC,CDÆAABCÆD,ABDÆC,BCDÆA共有3个键码：AB, BC, BD其决定因素不包含键码的函数依赖即为BC范式的违例，如下所示：CÆA,CÆD,DÆA,ACÆD,CDÆA（2）以违例CÆD为基础进行分解：R1(C,D)R2(A,B,C)R1属于BC范式。