《数据库系统原理》作业题与参考答案

ByXUZhuo-ming,HohaiUniversity许卓明,河海大学Page1of12《数据库系统原理》作业题与参考答案作业题PARTⅠ基础篇CHAPTER1数据库系统引论1.{教材Page10习题3}CHAPTER2数据模型1.｛教材Page54习题7(1)－(4),(6)｝CHAPTER3SQL语言1.｛教材Page88习题2－5｝（See:参考答案1）PartⅡ系统篇CHAPTER4DBMS引论1.名词解释：(1)事务；(2)数据目录；(3)多线程DBMS；(4)集中式DB与分布式DBCHAPTER5数据库的存储结构1.试解释关系DB系统中基表数据的三种典型存取路径：(1)索引；(2)簇集；(3)散列。（即基表的四种典型存储机制：(1)表；(2)索引的表；(3)索引簇表；(4)散列簇表）。CHAPTER6查询处理与优化1.用语法树表示教材Page66例3-4查询的代数优化过程。（See:参考答案2）CHAPTER7事务管理1.｛教材Page172习题6｝ByXUZhuo-ming,HohaiUniversity许卓明,河海大学Page2of122.｛教材Page172习题7｝CHAPTER8数据库的安全与完整性约束1.｛教材Page185习题1｝2.｛教材Page185习题2｝CHAPTER9触发器与主动数据库系统1.职员（emp）基表定义见课件。试用SQL:1999/SQL3语法定义一个名为empBandh的触发器来实现：一旦在emp表中删除一个员工的数据，只要此员工的工种不是“bandh”，就在emp表中恢复（插入）此员工的数据，将其工种（job）置为“bandh”，月薪（sal）置为2000.0，佣金（comm）置为NULL，其余属性不变。（See:参考答案3）PARTⅢ应用篇CHAPTER10数据依赖与关系模式的规范化1.｛教材Page225习题8｝（See:参考答案4）CHAPTER11数据库设计1.试对四个实体：学生、班级、课程、教师，及它们间的四个联系：组成、选课、任课、班主任进行E/R建模，并把E/R数据模式转换成关系数据库模式，并写出创建基表的SQLDDL语句，同时定义PK与FK、以及你认为必要的其他完整性约束。（See:参考答案5）CHAPTER12数据库管理1.｛教材Page225习题1｝ByXUZhuo-ming,HohaiUniversity许卓明,河海大学Page3of12参考答案参考答案1基表（详细定义见教材）：student(sno,sname,sex,bdate,height)course(cno,lhour,credit,semester)sc(sno,cno,grade)(1)查询身高大于1.80米的男生的学号和姓名。SELECTsno,snameFROMstudentWHEREheight1.8ANDsex=‘男’;(2)查询计算机系秋季所开课程的课程号和学分数。SELECTcno,creditFROMcourseWHEREcnoLIKE‘cs%’ANDsemester=‘秋’；(3)查询选修计算机系秋季所开课程的男生姓名、课程号、学分数、成绩。SELECTsc.cno,credit,sname,gradeFROMsc,course,studentWHEREcourse.cno=sc.cnoANDstudent.sno=sc.snoANDsc.cnoLIKE‘cs%’ANDsemester=‘秋’ANDsex=‘男’ORDERBYsc.cno;(4)查询至少选修一门电机系课程的女生的姓名。SELECTsnameFROMstudentWHEREsex=‘女’ANDEXISTS(SELECT*//相关子查询FROMscWHEREsc.sno=student.snoANDByXUZhuo-ming,HohaiUniversity许卓明,河海大学Page4of12sc.cnoLIKE‘ee%’);或者：SELECTsnameFROMstudentWHEREsex=‘女’ANDsnoIN(SELECTDISTINCTsno//不相关子查询FROMscWHEREcnoLIKE‘ee%’);或者用“连接”（不好！）：SELECTDISTINCTsnameFROMstudent,scWHEREstudent.sno=sc.snoANDsex=‘女’ANDcnoLIKE‘ee%’;注：此处的DISTINCT，若加，则会丢失其他同姓名而又均选修了电机系课程的学生；若不加，则选多门电机系课程的某学生的姓名重复出现。因此，用“连接”的方法不好！(5)查询每位学生已修课程的门数和总平均成绩。SELECTsc.sno,sname,COUNT(sc.cno),AVG(sc.grade)FROMsc,studentWHEREstudent.sno=sc.snoGROUPBYsc.sno;注：标准SQL（见斯坦福书Page275）或Oracle7中SELECT列表仅可包含下列表达式类型：(1)常数；(2)组函数；(3)与GROUPBY子句中相同的列表达式；(4)由GROUPBY子句中的列表达式所组成的表达式。因此，上述查询中不能再连接student表而获得sname。(6)查询每门课程选修的学生人数、最高成绩、最低成绩和平均成绩。SELECTcno,COUNT(sno),MAX(grade),MIN(grade),AVG(grade)FROMscByXUZhuo-ming,HohaiUniversity许卓明,河海大学Page5of12GROUPBYcno;(7)查询所有课程的成绩都在80分以上的学生的姓名、学号，并按学号升序排列。SELECTsname,snoFROMstudentWHERE80ALL(SELECTgrade//相关子查询FROMscWHEREsc.sno=student.snoANDgradeISNOTNULL)ORDERBYsno;注：具体的RDBMS对NULL值的处理可能不同。(8)查询缺成绩的学生姓名、缺成绩的课程号及其学分数。SELECTsname,sc.cno,creditFROMstudent,sc,courseWHEREgradeISNULLANDstudent.sno=sc.snoANDcourse.cno=sc.cno;(9)查询有（含）一门以上三学分以上课程的成绩低于70分的学生的姓名。SELECTsnameFROMstudentWHEREsnoIN(SELECTDISTINCTsno//不相关子查询FROMscWHEREgrade70ANDcnoIN(SELECTcno//不相关子查询FROMcourseWHEREcredit=3));或者：SELECTsnameFROMstudentByXUZhuo-ming,HohaiUniversity许卓明,河海大学Page6of12WHEREEXISTS(SELECT*//相关子查询FROMscWHEREsc.sno=student.snoANDgrade70ANDcnoIN(SELECTcno//不相关子查询FROMcourseWHEREcredit=3));或者用“连接”（不好！）：SELECTDISTINCTsnameFROMstudent,sc,courseWHEREgrade70ANDsc.sno=student.snoANDcourse.cno=sc.cno;注：此处的DISTINCT同题(4)，因此，用“连接”的方法不好！(10)查询1984－1986年出生的姓名、总平均成绩及已修学分数。SELECTsno,AVG(grade),SUM(credit)FROMsc,courseWHEREcourse.cno=sc.cnoANDsnoIN(SELECTsnoFROMstudentWHEREYEAR(bdate)BETWEEN1974AND1976)GROUPBYsno;注：若考虑有同姓名学生，只能用ORDERBYsno，而不能用ORDERBYname。但此时在SELECT子句中就不能出现name，见题(5)注。因此，此题不好！或者（也不好！）：SELECTsname,student.sno,AVG(grade),SUM(credit)FROMsc,course,studentWHEREcourse.cno=sc.cnoANDsc.sno=student.snoANDYEAR(bdate)BETWEEN1974AND1976GROUPBYsname,student.sno;ByXUZhuo-ming,HohaiUniversity许卓明,河海大学Page7of12（Return:CHAPTER3作业题）参考答案2基表：s(sno,sname,sex,bdate,height)c(cno,lhour,credit,seme)sc(sno,cno,grade)查询：SELECTsname,c.cno,gradeFROMs,c,scWHEREs.sno=sc.snoANDsc.cno=c.cnoANDseme=‘秋’;查询可表示成关系代数操作表达式：∏sname,c.cno,grade(δt((s×sc)×c))其中：t=s.sno=sc.snoANDsc.cno=c.cnoANDseme=‘秋’查询可表示成（原始）语法树；其代数优化（语法树等价变换）过程为：ByXUZhuo-ming,HohaiUniversity许卓明,河海大学Page8of12优化后的查询可表示成关系代数操作表达式：∏sname,c.cno,grade((∏sno,snames)s.sno=sc.sno(∏cno(δseme=‘秋’c)c.cno=sc.cnosc)))（Return:CHAPTER6作业题）参考答案3(职员（emp）基表定义：emp(empno,ename,job,mgr,hiredate,sal,comm,deptno))CREATETRIGGERempBandh先做δ，后做×∏sname,c.cno,gradeδt×cssc×∏sname,c.cno,gradeδs.sno=sc.snoANDsc.cno=c.cno×δseme=‘秋’ssc×c合并×与δ，成为∏sname,c.cno,gradesc.cno=c.cnos.sno=sc.snoδseme=‘秋’sscc先做小，后做大设|δseme=‘秋’c||s||sc|∏sname,c.cno,gradesc.cno=c.cnos.sno=sc.snoδseme=‘秋’sscc加必要的投影，消除无用属性∏sname,c.cno,gradesc.cno=c.cnos.sno=sc.snoδseme=‘秋’sscc∏sno,sname∏cno(优化后语法树)ByXUZhuo-ming,HohaiUniversity许卓明,河海大学Page9of12AFTERDELETEONempREFERENCINGOLDROWASoldrow,FOREACHROWWHENoldrow.job”bandh”BEGININSERTINTOempVALUES(oldrow.empno,oldrow.ename,“bandh”,oldrow.mgr,oldrow.hiredate,2000.0,NULL,oldrow.deptno)END;（Return:CHAPTER9作业题）参考答案4判别关系模式最高需第几范式？并解释原因。(1)R(ABCD),F={BD,ABC};解：KEY={A,B}=AB非平凡函数依赖BD中：决定子B不是超键，所以R不属于BCNF；被决定子D不是主属性，所以R不属于3NF；又，D部分依赖于KEY，所以R不属于2NF；结论：R属于1NF。(2)R(ABCDE),F={ABCE,EAB,CD};解：KEY=AB或E非平凡函数依赖CD中：ByXUZhuo-ming,HohaiUniversity许卓明,河海大学Page10