数据库-习题课一

1、1. 试述数据库的三级模式结构及其带来的好处。试述数据库的三级模式结构及其带来的好处。 数据库的三级模式包括外模式、逻辑模式和内模式，以及数据库的三级模式包括外模式、逻辑模式和内模式，以及外模式外模式/ /逻辑模式映像和逻辑模式逻辑模式映像和逻辑模式/ /内模式映像。内模式映像。外模式：外模式：是用户观念下局部数据结构的逻辑描述是用户观念下局部数据结构的逻辑描述, ,是数据库用户是数据库用户( (包括应用程序员和最终用户包括应用程序员和最终用户) )能够看见和使用的局部数据用逻辑能够看见和使用的局部数据用逻辑数据模型对用户用到的数据的描述。数据模型对用户用到的数据的描述。 逻辑模式逻辑模式(

2、(概念模式、模式概念模式、模式),),是数据库中全体数据的逻辑结构和是数据库中全体数据的逻辑结构和特征的描述特征的描述, ,是所有用户的公共数据视图是所有用户的公共数据视图, ,外模式是逻辑模式的一外模式是逻辑模式的一个逻辑子集。个逻辑子集。 内模式：内模式：是对数据库中数据的物理结构和存储方式的描述，是是对数据库中数据的物理结构和存储方式的描述，是数据在数据库内部的表现形式，一个数据库只有一个内部模式。数据在数据库内部的表现形式，一个数据库只有一个内部模式。 数据库系统数据库系统 习题课一习题课一 第一章习题第一章习题 参考答案：参考答案：第一章第一章带来的好处：带来的好处： 1. 1. 数

3、据独立性数据独立性 三级模式是对数据的三个抽象级别，它把数据三级模式是对数据的三个抽象级别，它把数据的具体组织留给的具体组织留给DBMS管理，使用户能逻辑地抽象管理，使用户能逻辑地抽象地处理数据，而不必关心数据在计算机中的具体表地处理数据，而不必关心数据在计算机中的具体表示形式与存储方式，提高数据的独立性。示形式与存储方式，提高数据的独立性。 2. 2. 方便了数据库的设计和实现方便了数据库的设计和实现 数据库系统数据库系统 习题课一习题课一 第一章习题第一章习题 P1795.13 数据库系统数据库系统 习题课一习题课一 第一章习题第一章习题 设某商业集团数据库中有三个实体集。一是设某商业集团

4、数据库中有三个实体集。一是“商商店店”实体集，属性有商店编号、商店名、地址等；二实体集，属性有商店编号、商店名、地址等；二是是“商品商品”实体集，属性有商品编号、商品名、规格、实体集，属性有商品编号、商品名、规格、单价等；三是单价等；三是“ 职工职工”实体集，属性有职工编号、实体集，属性有职工编号、姓名、性别、业绩等。姓名、性别、业绩等。 商店与商品间存在商店与商品间存在“销售销售”联系，每个商店可销联系，每个商店可销售多种商品，每种商品也可放在多个商店销售，每个售多种商品，每种商品也可放在多个商店销售，每个商店销售一种商品，有月销售量；商店与职工间存在商店销售一种商品，有月销售量；商店与职工

5、间存在着着“聘用聘用”联系，每个商店有许多职工，每个职工只联系，每个商店有许多职工，每个职工只能在一个商店工作，商店聘用职工有聘期和月薪。能在一个商店工作，商店聘用职工有聘期和月薪。 试画出试画出ERER图，并在图上标明属性、联系的类型。图，并在图上标明属性、联系的类型。商商 店店商商 品品职职 工工聘用聘用销售销售mn1 m商店编号商店编号商店名商店名地址地址职工编号职工编号姓名姓名业绩业绩性别性别规格规格商品编号商品编号商品名商品名单价单价聘期聘期月薪月薪月销售量月销售量 数据库系统数据库系统 习题课一习题课一 第一章习题第一章习题 参考答案：参考答案： 某商业集团有三个实体集。一个是某商

6、业集团有三个实体集。一个是“公司公司”实体集，实体集，属性有公司编号、公司名、地址等。二是属性有公司编号、公司名、地址等。二是“仓库仓库”实实体集，属性有仓库编号、仓库名、地址等。三是体集，属性有仓库编号、仓库名、地址等。三是“职职工工”实体集，属性有职工编号、姓名、性别等。公司实体集，属性有职工编号、姓名、性别等。公司与仓库间存在与仓库间存在“隶属隶属”关系，每个公司管辖若干个仓关系，每个公司管辖若干个仓库，每个仓库只属于一个公司管辖；仓库与间存在库，每个仓库只属于一个公司管辖；仓库与间存在“聘用聘用”联系，每个仓库可聘用多个职工，每个职工只联系，每个仓库可聘用多个职工，每个职工只能在一个仓

7、库工作，仓库聘用职工有聘期和工资。画能在一个仓库工作，仓库聘用职工有聘期和工资。画出出ERER图，并在图上注明属性、联系的类型。转换成关图，并在图上注明属性、联系的类型。转换成关系模式集，并指出每个关系模式的主键和外键。系模式集，并指出每个关系模式的主键和外键。数据库系统数据库系统 习题课一习题课一 第一章习题第一章习题 P1805.14仓库隶属公司职工聘用仓库名公司编号公司名仓库编号地址职工编号地址性别姓名工资聘期1m1n数据库系统数据库系统 习题课一习题课一 第一章习题第一章习题 参考答案：参考答案： 关系模式集关系模式集公司（公司（公司编号公司编号，公司名，地址），公司名，地址）仓库（仓

8、库（仓库编号仓库编号，仓库名，地址，公司编号），仓库名，地址，公司编号）职工（职工（职工编号职工编号，姓名，性别，仓库编号，聘期，姓名，性别，仓库编号，聘期，工资），工资） 数数据库系统数数据库系统 习题课一习题课一 第一章习题第一章习题 关系模型的参照完整性约束要求不能引用不关系模型的参照完整性约束要求不能引用不存在的实体，通过外键实现。存在的实体，通过外键实现。 当外键不是本关系主键的一部分（主属性），当外键不是本关系主键的一部分（主属性），允许外键为空；否则，不允许为空。允许外键为空；否则，不允许为空。 数据库系统数据库系统 习题课一习题课一 第二章习题第二章习题 2.4外键值何时允许为

9、空？何时不允许为空？外键值何时允许为空？何时不允许为空？参考答案：参考答案：第二章第二章2.6. 设有关系设有关系R和和S，如下：，如下： R A B C 3 6 7 2 5 7 7 2 3 4 4 3S A B C 3 4 5 7 2 3A B C 3 6 7 2 5 77 2 3 4 33 4 5RSA B C 3 6 7 2 5 74 4 3RSA B C 7 2 3RS 数据库系统数据库系统 习题课一习题课一 第二章习题第二章习题 参考答案：参考答案：R A B C 3 6 7 2 5 7 7 2 3 4 4 3S A B C 3 4 5 7 2 3R.A R.B R.C S.A S.

10、B S.C 3 6 7 3 4 5 3 6 7 7 2 3 2 5 7 3 4 5 2 5 7 7 2 3 7 2 3 3 4 5 7 2 3 7 2 3 4 4 3 3 4 5 4 4 3 7 2 3 RS3,2(S)C B5 4 3 2 数据库系统数据库系统 习题课一习题课一 第二章习题第二章习题 R A B C 3 6 7 2 5 7 7 2 3 4 4 3S A B C 3 4 5 7 2 3B5(R) A B C 7 2 3 4 4 3R.A R.B R.C S.A S.B S.C 7 2 3 3 4 5R S 22A B C 7 2 3R S 数据库系统数据库系统 习题课一习题课一

11、 第二章习题第二章习题 数据库系统数据库系统 习题课一习题课一 第二章习题第二章习题 评注：评注：关系代数的运算次序关系代数的运算次序: ( ): ( )、一元、二元、一元、二元 公共属性上的连接是自然连接，要消除结果中的公共属性上的连接是自然连接，要消除结果中的 冗余属性冗余属性 R R中每个元组与中每个元组与S S中每个元组比较、连接中每个元组比较、连接用关系代数表达式表下列查询语句：用关系代数表达式表下列查询语句：1)1)检索年龄小于检索年龄小于1717岁的女学生的学号和姓名岁的女学生的学号和姓名2)2)检索男学生所学课程的课程号和课程名检索男学生所学课程的课程号和课程名 S#,SNAM

12、ES#,SNAME(AGEAGE17 sex=17 sex=女女(S)(S)或或 1,21,2(3 317 4=1;S(S#,SNAME,AGE,SEX)SC(S#,C#,SCORE)C(C#,CNAME,T#)T（T#，TNAME，TITLE） 数据库系统数据库系统 习题课一习题课一 第三章习题第三章习题 6)6)检索至少有学号为检索至少有学号为S2S2和和S4S4的学生选修的课程的课程号的学生选修的课程的课程号SELECT DISTINCT X.C# FROM SC AS X，SC AS YWHERE X.S#=S2 AND Y.S# =S4AND X.C# = Y.C#;7)7)检索全部

13、学生都选修的课程的课程号与课程名检索全部学生都选修的课程的课程号与课程名 SELECT C#, CNAME FROM CWHERE NOT EXISTS ( SELECT * FROM S WHERE NOT EXISTS ( SELECT * FROM SC WHERE C.C#=SC.C# AND SC.S#=S.S#); 数据库系统数据库系统 习题课一习题课一 第三章习题第三章习题 7)7)检索全部学生都选修的课程的课程号与课程名检索全部学生都选修的课程的课程号与课程名 SELECT C.C#, CNAME FROM SC,CWHERE SC.C#=C.C# GROUP BY C.C#,

14、CNAMEHAVING COUNT(*)=(SELECT COUNT(S#) FROM S)另一解法：另一解法：8)8)检索选修课程包含检索选修课程包含LIULIU老师所授全部课程的学生学号老师所授全部课程的学生学号 数据库系统数据库系统 习题课一习题课一 第三章习题第三章习题 SELECT DISTINCT S# FROM SC AS XWHERE 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.

15、C#=C.C#);1)1)统计有学生选修的课程门数统计有学生选修的课程门数2)2)求选修求选修C4C4课程的女同学的平均年龄课程的女同学的平均年龄SELECT COUNT(DISTINCT C#)FROM SC；SELECT AVG(AGE)FROM SWHERE 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 试用试用SQLSQL查询语句表达下列对查询语句表达下列对3.23.2题的题的教学数据库教学数

16、据库 中中四个基本表四个基本表S S、SCSC、C C 、 T T的的查询：查询： 数据库系统数据库系统 习题课一习题课一 第三章习题第三章习题 参考答案：参考答案：3)3)求求LIULIU老师所授课程的每门课程的平均成绩老师所授课程的每门课程的平均成绩4)4)统计每门课程的学生选修人数（超过统计每门课程的学生选修人数（超过1010人的课程才人的课程才 统计）。要求显示课程号和人数，查询结果按人数统计）。要求显示课程号和人数，查询结果按人数 降序排列，若人数相同，按课程号升序排列。降序排列，若人数相同，按课程号升序排列。SELECT C.C#, AVG(SCORE)FROM SC,C,TWHE

17、RE SC.C#=C.C# AND C.T#=T.T# AND TNAME=LIUGROUP BY C.C#; 数据库系统数据库系统 习题课一习题课一 第三章习题第三章习题 SELECT C#, COUNT(S#)FROM SCGROUP BY C# HAVING COUNT(S#)10ORDEY BY 2 DESC, 1 ASC;5)5)检索学号比检索学号比WANGWANG同学大，而年龄比他小的学生姓名同学大，而年龄比他小的学生姓名6)6)在表在表SCSC中检索成绩为空值的学生学号和课程号。中检索成绩为空值的学生学号和课程号。SELECT SNAMEFROM S WHERE S# ALL (

18、 SELECT S# FROM S WHERE SNAME=WANG) AND AGE ( SELECT AVG(AGE) FROM S WHERE SEX=F);9)9)检索年龄大于所有女同学年龄的男同学姓名和年龄检索年龄大于所有女同学年龄的男同学姓名和年龄 SELECT SNAME, AGE FROM SWHERE SEX=M AND AGE ( SELECT MAX(AGE) FROM S WHERE SEX=F);1) 往关系往关系C中插一个课程元组中插一个课程元组(C8,VC+,T6)2)2)检索所授每门课程平均成绩均大于检索所授每门课程平均成绩均大于8080分的教师分的教师 姓名，

19、并把检索到的值送往另一个已存在的表姓名，并把检索到的值送往另一个已存在的表 FACULTY(TNAME)FACULTY(TNAME)INSERT INTO CVALUES(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_SC

20、ORE FROM RESULT AS Y WHERE Y.TNAME=X.TNAME);3.12 试用试用SQLSQL更新语句表达下列对更新语句表达下列对3.23.2题的题的教学数据库教学数据库 中关系中关系S、SC、C 、T的更新操作：的更新操作： 数据库系统数据库系统 习题课一习题课一 第三章习题第三章习题 参考答案：参考答案： 数据库系统数据库系统 习题课一习题课一 第三章习题第三章习题 2)2)检索所授每门课程平均成绩均大于检索所授每门课程平均成绩均大于80分的教师分的教师 姓名，并把检索到的值送往另一个已存在的表姓名，并把检索到的值送往另一个已存在的表 FACULTY(TNAME)I

21、NSERT INTO FACULTYSELECT DISTINCT TNAME FROM C,SC,TWHERE 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)3)在在SCSC中删除尚无中删除尚无成绩的选课元组成绩的选课元组4)4)把选修把

22、选修LIULIU老师课程的女同学选课元组全部删去。老师课程的女同学选课元组全部删去。DELETE FROM SCWHERE SCORE IS NULL; 数据库系统数据库系统 习题课一习题课一 第三章习题第三章习题 DELETE FROM SCWHERE 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)5)把把MATHSMATHS课不及格的成绩全改为课不及格的成绩全改为6060分。分。UPDATE SC SET SCORE=60WHERE SCORE60 AND C# IN (SELECT C# FROM C WHERE CANME=MATHS)；6)6)把低于所有课程总平均成绩的女同学成绩提高把低于所有课程总平均