南京邮电大学数据库系统课后习题答案1.ppt

,1. 试述数据库的三级模式结构及其带来的好处。,数据库的三级模式包括外模式、逻辑模式和内模式，以及外模式/逻辑模式映像和逻辑模式/内模式映像。 外模式：是用户观念下局部数据结构的逻辑描述,是数据库用户 (包括应用程序员和最终用户)能够看见和使用的局部数据用逻辑 数据模型对用户用到的数据的描述。 逻辑模式(概念模式、模式),是数据库中全体数据的逻辑结构和 特征的描述,是所有用户的公共数据视图,外模式是逻辑模式的一 个逻辑子集。 内模式：是对数据库中数据的物理结构和存储方式的描述，是 数据在数据库内部的表现形式，一个数据库只有一个内部模式。,数据库系统 习题课一 第一章习题,参考答案：,第一章,带来的好处： 1. 数据独立性 三级模式是对数据的三个抽象级别，它把数据的具体组织留给DBMS管理，使用户能逻辑地抽象地处理数据，而不必关心数据在计算机中的具体表示形式与存储方式，提高数据的独立性。 2. 方便了数据库的设计和实现,数据库系统 习题课一 第一章习题,P1795.13,数据库系统 习题课一 第一章习题,设某商业集团数据库中有三个实体集。一是“商店”实体集，属性有商店编号、商店名、地址等；二是“商品”实体集，属性有商品编号、商品名、规格、单价等；三是“ 职工”实体集，属性有职工编号、姓名、性别、业绩等。 商店与商品间存在“销售”联系，每个商店可销售多种商品，每种商品也可放在多个商店销售，每个商店销售一种商品，有月销售量；商店与职工间存在着“聘用”联系，每个商店有许多职工，每个职工只能在一个商店工作，商店聘用职工有聘期和月薪。 试画出ER图，并在图上标明属性、联系的类型。,数据库系统 习题课一 第一章习题,参考答案：,某商业集团有三个实体集。一个是“公司”实体集，属性有公司编号、公司名、地址等。二是“仓库”实体集，属性有仓库编号、仓库名、地址等。三是“职工”实体集，属性有职工编号、姓名、性别等。公司与仓库间存在“隶属”关系，每个公司管辖若干个仓库，每个仓库只属于一个公司管辖；仓库与间存在“聘用”联系，每个仓库可聘用多个职工，每个职工只能在一个仓库工作，仓库聘用职工有聘期和工资。画出ER图，并在图上注明属性、联系的类型。转换成关系模式集，并指出每个关系模式的主键和外键。,数据库系统 习题课一 第一章习题,P1805.14,数据库系统 习题课一 第一章习题,参考答案：,关系模式集 公司（公司编号，公司名，地址） 仓库（仓库编号，仓库名，地址，公司编号） 职工（职工编号，姓名，性别，仓库编号，聘期，工资）,数数据库系统 习题课一 第一章习题,关系模型的参照完整性约束要求不能引用不存在的实体，通过外键实现。 当外键不是本关系主键的一部分（主属性），允许外键为空；否则，不允许为空。,数据库系统 习题课一 第二章习题,2.4外键值何时允许为空？何时不允许为空？,参考答案：,第二章,2.6. 设有关系R和S，如下：,数据库系统 习题课一 第二章习题,参考答案：,数据库系统 习题课一 第二章习题,数据库系统 习题课一 第二章习题,数据库系统 习题课一 第二章习题,评注：,关系代数的运算次序: ( )、一元、二元 公共属性上的连接是自然连接，要消除结果中的 冗余属性 R中每个元组与S中每个元组比较、连接,用关系代数表达式表下列查询语句：,1)检索年龄小于17岁的女学生的学号和姓名,2)检索男学生所学课程的课程号和课程名,S#,SNAME(AGE17 sex=女(S) 或 1,2(317 4=女(S),T(T#,TNAME,TITLE) C(C#,CNAME,T#) S(S#,SNAME,AGE,SEX) SC(S#,C#,SCORE),2.17 设有三个关系：,数据库系统 习题课一 第二章习题,参考答案：,1(1=4 2!=5(SCSC),数据库系统 习题课一 第二章习题,2(2=5 1=S24 =S4(SCSC),学生选课情况表示为：s#,c# (SC) 全部学生表示为： S#(S) 全部学生都选修的课程可用除法操作表示为： SC) S#(S),数据库系统 习题课一 第二章习题,7)检索全部学生都选修的课程的课程号与课程名,参考答案：, 初始的关系代数表达式： CNAME, TNAME(SEX=女 S.S#=SC.S# SC.C#=C.C# C.T#=T.T# (S SC C T),数据库系统 习题课一 第二章习题,注意书写规范,评注：,2.21 在教学数据库的关系S、C、SC、T中，用户有 一查询语句：检索女同学选修课程的课程名和 任课教师名。 试写出该查询的关系代数表达式 画出查询表达式的语法树 使用启发式优化算法，对语法树进行优化， 并画出优化后的语法树。,参考答案：, 原始的查询树：,数据库系统 习题课一 第二章习题,数据库系统 习题课一 第二章习题,代数优化的主要规则与步骤 ： 操作向叶结点下压(单表的、多表的) ,操作先对小关系，后对大关系 可组合成连接 在叶结点附加 ，但要保留上级所需的属性,优化：,参考答案：, 对原始的查询树优化：,S,SC,C, S.S# = SC.S# SC.C# = C.C# C.T# = T.T# SEX=女, CNAME, TNAME,数据库系统 习题课一 第二章习题,T, 下压, ,参考答案：, 对原始的查询树优化：,S,SC, CNAME, TNAME,数据库系统 习题课一 第二章习题,SEX=女,S.S#=SC.S#,C,SC.C#=C.C#,T,C.T#=T.T#, 下压, 优化的查询树：,数据库系统 习题课一 第二章习题, 语法树应与关系代数表达式一致,评注：,优化的关系代数表达式： CNAME, TAME ( ( ( CNAME,T# ( C#(S#(SEX=女(S) S#,C#(SC) C) ) T#,TNAME(T) ),CREATE TABLE WORKS (E# CHAR(7) NOT NULL, C# CHAR(6) NOT NULL, SALARY SMALLINT, PRIMARY KEY (E#,C#), FOREIGN KEY (E#) REFERENCES EMP(E#) ON DELETE CASCADE, FOREIGN KEY (C#) REFERENCES COMP(C#) ON DELETE RESTRICT);,3.13 创建工作表，指出主键和外键,数据库系统 习题课一 第三章习题,参考答案：,试用SQL的查询语句表达下列查询：,1)检索年龄小于17岁的女同学的学号和姓名,2)检索男同学所学课程的课程号和课程名,SELECT S#, SNAME FROM S WHERE AGE17 AND SEX=F;,S(S#,SNAME,AGE,SEX) SC(S#,C#,SCORE) C(C#,CNAME,T#) T（T#，TNAME，TITLE）,3.2 对于教学数据库的四个基本表：,数据库系统 习题课一 第三章习题,参考答案：,SELECT C.C#,CNAME FROM S,SC,C WHERE S.S#=SC.S# AND SC.C#=C.C# AND SEX=M;,SELECT T.T#,TNAME FROM S,SC,C,T WHERE S.S#=SC.S# AND SC.C#=C.C# AND C.T#=T.T# AND SEX=M;,SELECT C# FROM C WHERE C# NOT IN ( SELECT C# FROM S,SC WHERE S.S#=SC.S# AND SNAME=WANG);,数据库系统 习题课一 第三章习题,SELECT DISTINCT X.S# FROM SC AS X，SC AS Y WHERE X.S#=Y.S# AND X.C# != Y.C#;,SELECT S# FROM SC GROUP BY S# HAVING COUNT(*)1;,S(S#,SNAME,AGE,SEX) SC(S#,C#,SCORE) C(C#,CNAME,T#) T（T#，TNAME，TITLE）,数据库系统 习题课一 第三章习题,6)检索至少有学号为S2和S4的学生选修的课程的课程号,SELECT DISTINCT X.C# FROM SC AS X，SC AS Y WHERE X.S#=S2 AND Y.S# =S4 AND X.C# = Y.C#;,7)检索全部学生都选修的课程的课程号与课程名,SELECT C#, CNAME FROM C WHERE NOT EXISTS ( SELECT * FROM S WHERE NOT EXISTS ( SELECT * FROM SC WHERE C.C#=SC.C# AND SC.S#=S.S#);,数据库系统 习题课一 第三章习题,7)检索全部学生都选修的课程的课程号与课程名,SELECT C.C#, CNAME FROM SC,C WHERE SC.C#=C.C# GROUP BY C.C#,CNAME HAVING COUNT(*)=(SELECT COUNT(S#) FROM S),另一解法：,8)检索选修课程包含LIU老师所授全部课程的学生学号,数据库系统 习题课一 第三章习题,SELECT DISTINCT S# FROM SC AS X WHERE NOT EXISTS ( SELECT * FROM C ,T WHERE C.T#=T.T# AND TNAME=LIU AND NOT EXISTS ( SELECT * FROM SC AS Y WHERE Y.S#=X.S# AND Y.C#=C.C#);,1)统计有学生选修的课程门数,2)求选修C4课程的女同学的平均年龄,SELECT COUNT(DISTINCT C#) FROM SC；,SELECT AVG(AGE) FROM S WHERE SEX=女 AND S# IN ( SELECT S# FROM SC WHERE C# =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人的课程才 统计）。要求显示课程号和人数，查询结果按人数 降序排列，若人数相同，按课程号升序排列。,SELECT C.C#, AVG(SCORE) FROM SC,C,T WHERE SC.C#=C.C# AND C.T#=T.T# AND TNAME=LIUGROUP BY C.C#;,数据库系统 习题课一 第三章习题,SELECT C#, COUNT(S#) FROM SC GROUP BY C# HAVING COUNT(S#)10 ORDEY BY 2 DESC, 1 ASC;,5)检索学号比WANG同学大，而年龄比他小的学生姓名,6)在表SC中检索成绩为空值的学生学号和课程号。,SELECT SNAME FROM S WHERE S# ALL ( SELECT S# FROM S WHERE SNAME=WANG) AND AGE ALL ( SELECT AGE FROM S WHERE SNAME=WANG);,数据库系统 习题课一 第三章习题,SELECT S#, C# FROM SC WHERE SCORE IS NULL;,数据库系统 习题课一 第三章习题,7)检索姓名以L打头的所有学生的姓名和年龄,SELECT SNAME, AGE FROM S WHERE SNAME LIKE L%;,8)检索年龄大于女同学平均年龄的男同学姓名和年龄,SELECT SNAME, AGE FROM S WHERE SEX=M AND AGE( SELECT AVG(AGE) FROM S WHERE SEX=F);,9)检索年龄大于所有女同学年龄的男同学姓名和年龄,SELECT SNAME, AGE FROM S WHERE SEX=M AND AGE ( SELECT MAX(AGE) FROM S WHERE SEX=F);,1) 往关系C中插一个课程元组(C8,VC+,T6),2)检索所授每门课程平均成绩均大于80分的教师 姓名，并把检索到的值送往另一个已存在的表 FACULTY(TNAME),INSERT INTO C VALUES(C8,VC+,T6)；,INSERT INTO FACULTY SELECT DISTINCT TNAME FROM (SELECT TNAME,C.C# ,AVG(SCORE) FROM T,C,SCWHERE T.T#=C.T# AND C.C#=SC.C# GROUP BY TNAME,C.C#）AS RESULE(TNAME,C#, AVG_SCORE) AS X WHERE 80=ALL(SELECT AVG_SCORE FROM RESULT AS Y WHERE Y.TNAME=X.TNAME);,3.12 试用SQL更新语句表达下列对3.2题的教学数据库 中关系S、SC、C 、T的更新操作：,数据库系统 习题课一 第三章习题,参考答案：,数据库系统 习题课一 第三章习题,2)检索所授每门课程平均成绩均大于80分的教师 姓名，并把检索到的值送往另一个已存在的表 FACULTY(TNAME),INSERT INTO FACULTY SELECT DISTINCT TNAME FROM C,SC,T WHERE C.C#=SC.C# AND C.T#=T.T# AND T# NOT IN( SELECT T# FROM C WHERE C# IN ( SELECT C# FROM SC GROUP BY C# HAVING AVG(SCORE)=80 ) );,另一解法：,S(S#,SNAME,AGE,SEX) SC(S#,C#,SCORE) C(C#,CNAME,T#) T（T#，TNAME，TITLE）,3)在SC中删除尚无成绩的选课元组,4)把选修LIU老师课程的女同学选课元组全部删去。,DELETE FROM SC WHERE SCORE IS NULL;,数据库系统 习题课一 第三章习题,DELETE FROM SC WHERE C# IN (SELECT C# FROM C,T WHERE C.T#=T.T# AND TNAME=LIU) AND S# IN (SELECT S# FROM S WHERE SEX=F)；,5)把MATHS课不及格的成绩全改为60分。,UPDATE SC SET SCORE=60 WHERE SCORE60 AND C# IN (SELECT C# FROM C WHERE CANME=MATHS)；,6)把低于所有课程总平均成绩的女同学成绩提高5%,8)在表SC中，当某个成绩低于全部课程的平均成绩时， 提高5%。,UPDATE SC SET SCORE=SC