《数据库原理2010试卷A》答案

长江学院2009—2010 学年第 二学期考试试卷（A ）
数据库原理 课程 闭卷 课程类别：必修
一．填空题（每小题2分，共40分） 请将答案填入下表格
二、设有关系 R 和 S ，（15分，每题3分）
R S 计算： （1） R ∪ S （2） R ╳S （3）
R S （4）∏ B σ A>4(S) （5）R ─ σ A<4(R)
（1）R ∪ S
R S
∏ B σ A>4(S)
R ─ σ A<4(R)
三、现有一个工程公司数据库，包括职工、部门、工程、客户四个关系模式： 职工（职工编号，姓名，性别，出生日期，部门编号）
工程（工程编号，工程名称，职工编号，客户编号）
客户（客户编号，客户名称，地址）
试用专门的关系运算选择、投影、连接求出以下结果：(8分，每题2分)
(1) 查询所有男职工的信息。

σ性别=’男’(职工)
(2) 年龄大于20岁的职工姓名。

π姓名(σYear(GetDate())-Year(出生日期)>20 (职工))
(3) 查询职工“徐立”负责的的工程名称。

π工程名称(σ姓名=’徐立’ (工程∞职工))
(4) 查询测试部所有职工的姓名。

π姓名 (σ部门名称=’测试部’ (职工∞部门))
（5）查询人事部职工负责的工程信息。

π工程编号,工程名称,客户编号(σ部门名称=’人事部’ (职工∞部门∞工程))
四、有 4 个关系模式如下：（每题2分，共16分）
出版社（出版社编号，出版社名称）
图书（图书编号，书名，出版社编号，定价）
作者（作者编号，姓名）
著书（图书编号，作者编号，作者排序）
注：作者排序 =1 表示第一作者，依此类推。

用 SQL 语句，完成：
（1）检索图书《数据库原理》的定价
Select 定价 From 图书 Where 书名=’数据库原理’
（2）检索“谭浩强”参与编写的所有书的书名
Select 书名 From 图书，作者，著书 Where 图书.图书编号=著书.图书编号 And 著书.作者编号=作者.作者编号 And 作者.姓名=’谭浩强’（3）检索所有定价超过 20 元的书名。

Select 书名 From 图书 Where 定价 > 20
（4）检索图书书名中含有“VB”的图书
Select * From 图书 Where 书名 Like ‘%VB%’
（5）统计每个出版社图书的平均定价。

Select avg(定价),出版社名称 From 出版社,图书
Where 出版社.出版社编号=图书.出版社编号
Group by 出版社名称
（6）将科学出版社的所有图书定价下调 5% 。

Update 图书 Set 定价=定价*0.95
Where 出版社编号=(Select 出版社编号 From 出版社 Where 出版社名称=’科学出版社’)
（7）列出所有图书的书名、第一作者姓名和出版社名称。

Select 书名，姓名，出版社名称
From 图书，出版社，作者，著书
Where 图书.图书编号=著书.图书编号 And 出版社.出版社编号=图书.出版社编号 And 作者.作者编号=著书.作者编号
（8）显示“人民出版社”的出版的图书的详细信息。

Select 图书.* ,出版社名称 From 出版社，图书
Where 出版社.出版社编号=图书.出版社编号 And 出版社名称=’人民出版社’
五、（10分）
1．设数据库中有两个基本表：
职工表EMP（E#，ENAME，AGE，SALARY，D#），
其属性分别表示职工工号、姓名、年龄、工资和工作部门的编号。

部门表DEPT（D#，DNAME，MGR#）,
其属性分别表示部门编号、部门名称和部门经理的职工工号。

试指出每个表的主键和外键。

（4分）
职工表的主键：E# 职工编号，外键：D# 部门编号
部门表的主键：D# 部门编号，外键：MGR#
、在第1题的两个基本表中，写出下列查询的关系代数表达式和SQL语句：检索每个部门经理的工资，要求显示其部门编号、部门名称、经理工号、经理姓名和经理工资。

（4分）
∏ D#,DName,MGR#,EName,Salary (Dept∞EMp)
Select D#,DName,MGR#,EName,Salary
From EMP，DEPT
Where EMP.D#=DEPT.D#
3．在第1题的两个基本表中，建一个年龄大于50岁的查询，属性为（D#，DNAME，E#，ENAME，AGE，SALARY）。

（2分）
Select D#，DNAME，E#，ENAME，AGE，SALARY
From EMP，DEPT
Where EMP.D#=DEPT.D# AND age>50
六、设某汽车运输公司数据库中有三个实体集。

一是“车队”实体集，属性有车队号、车队名等；二是“车辆”实体集，属性有牌照号、厂家、出厂日期等；三是“司机”实体集，属性有司机编号、姓名、电话等。

设车队与司机之间存在“聘用”联系，每个车队可聘用若干司机，但每个司机只能应聘于一个车队，车队聘用司机有个聘期；车队与车辆之间存在“拥有”联系，每个车队可拥有若干车辆，但每辆车只能属于一个车队；司机与车辆之间存在着“使用”联系，司机使用车辆有使用日期和公里数两个属性，每个司机可使用多辆汽车，每辆汽车可被多个司机使用。

（1）试画出ER图，并在图上注明属性、联系类型、实体标识符；
（2）将ER图转换成关系模型，并说明主键和外键。

解：（1）ER图如图5所示。

（2）转换成的关系模型应具有4个关系模式：
车队（车队号，车队名）
车辆（牌照号，厂家，生产日期，车队号）
司机（司机编号，姓名，电话，车队号，聘期）
使用（司机编号，车辆号，使用日期，公里数）
六．综合题（本大题共2小题，每小题5分，共10分）
某汽车运输公司数据库中有一个记录司机运输里程的关系模式：
R（司机编号，汽车牌照，行驶公里，车队编号，车队主管）此处每个汽车牌照对应一辆汽车。

“行驶公里”为某司机驾驶某辆汽车行驶的总公里数。

如果规定每个司机属于一个车队，每个车队只有一个主管。

（1）试写出关系模式R的基本FD和关键码。

（2）说明R不是2NF模式的理由，并指出数据冗余之所在。

试把R分解成2NF模式集。

解：（1）根据已知条件，可写出基本的FD有三个：
司机编号→车队编号
车队编号→车队主管
（司机编号，汽车牌照）→行驶公里
从上述三个FD，可知R的关键码为（司机编号，汽车牌照）。

（2）从上述三个FD，可推出下列FD成立：
（司机编号，汽车牌照）→（车队编号，车队主管）
这是一个局部FD。

因此R不是2NF模式。

此时在R的关系中，每个司机只属于一个车队及主管人员，但要记载某司机驾驶过10辆汽车的行驶公里数，在R的关系中要出现10个元组。

也就是这10个元组的司机相同，其车队编号和车队主管要重复出现10次，这就是数据冗余。

R应分解成 R1（司机编号，汽车牌照，行驶公里）
R2（司机编号，车队编号，车队主管）
这两个模式都是2NF模式。

（3）R1已是3NF模式，但R2不是3NF模式。

因为在R2中的基本FD有两个：
司机编号→车队编号，
车队编号→车队主管。

显然，存在传递依赖：司机编号→车队主管。

此时在R2的关系中，一个车队只有一个主管人员，但这个车队有20名司机，则在关系中就要有20个元组。

这20个元组的车队编号相同，而车队主管要重复出现20次，这就是数据冗余。

R2应分解成R21（司机编号，车队编号）
R22（车队编号，车队主管）
这样，ρ={ R1，R21，R22 }，其中每个模式均是3NF模式。