2004年下半年数据库系统工程师下午试卷及答案

试题中通用的表示规则

　　关于概念数据模型、关系模式、关系数据库的表结构标记，除非试题中有特殊声明，否则将使用如下的通用标记规则。

　　1．概念数据模型的标记规则分别如图1、图2和图3所示。其中： 

　　(1)实体类型用长方形表示，长方形内为实体类型名称。

    (2)实体类型间的联系用直线表示。表示“1对1” 联系的直线两端不带箭头：表示“1对多”联系的直线，在多的一端带箭头；表示“多对多”联系的直线两端都带箭头。

图1　实体类型和联系的规则

　　 (3)超类型和子类型之间的联系,从超类型到子类型之间画连线,在分支点处画 △ 号

图2超类型和子类型标记规则

图3 实体类型的属性标记规则

　　(4)表示实体属性时，把长方形分成上、下两部分，实体名称填入上段，把实体属性填入下段。若该属性仅为主键属性时，则该属性名下画实下划线；若该属性仅是外键属性时，则该属性名下画虚下划线；若该属性既是主键属性，又是外键属性时，则在该属性名下面画实下划线；若该属性既不是主键属性，又不是外键属性时，则在该属性名下不做标记。

　　2．关系模式的标记规则如下：

　　　关系名（属性名1，属性名2，…，属性名n）
　　其中：

　　(1)若该属性仅为主键属性时，则该属性名下画实下划线；

　　(2)若该属性仅是外键属性时，则该属性名下画虚下划线；

　　(3)若该属性既是主键属性，又是外键属性时，则在该属性名下面画实下划线：

　　(4)若该属性既不是主键属性，又不是外键属性时，则在该属性名下不做标记。

　　3．关系数据库的表结构标记规则如下：
　　　　　表名

　　其中:

　　(1)若该列名仅为主键的列名时，则该列名下画实下划线；

　　(2)若该列名仅是外键的列名时，则该列名下画虚下划线：

　　(3)若该列名既是主键的列名，又是外键的列名时，则在该列名下面画实下划线；

　　(4)若该列名既不是主键的列名，又不是外键的列名时，则在该列名下不做标记。

    试题一(15分)

　　阅读下列说明和数据流图，回答问题1至问题3，将解答填入答题纸的对应栏内。

    某图书管理系统的主要功能是图书管理和信息查询。对于初次借书的读者，系统自动生成读者号，并与读者基本信息(姓名、单位、地址等)一起写入读者文件。 系统的图书管理功能分为四个方面：购入新书、读者借书、读者还书以及图书注销。

　　1、购入新书时需要为该书编制入库单。入库单内容包括图书分类目录号、书名、作者、价格、数量和购书日期，将这些信息写入图书目录文件并修改文件中的库存总量(表示到目前为止，购入此种图书的数量)。

　　2、读者借书时需填写借书单。借书单内容包括读者号和所借图书分类目录号。系统首先检查该读者号是否有效，若无效，则拒绝借书；若有效，则进一步检查该读者已借图书是否超过最大限制数(假设每位读者能同时借阅的书不超过5本)，若已达到最大限制数，则拒绝借书；否则允许借书，同时将图书分类目录号、读者号和借阅日期等信息写入借书文件中。

　　3、读者还书时需填写还书单。系统根据读者号和图书分类目录号，从借书文件中读出与该图书相关的借阅记录，标明还书日期，再写回到借书文件中，若图书逾期，则处以相应的罚款。

　　4、注销图书时，需填写注销单并修改图书目录文件中的库存总量。 系统的信息查询功能主要包括读者信息查询和图书信息查询。其中读者信息查询可得到读者的基本信息以及读者借阅图书的情况；图书信息查询可得到图书基本信息和图书的借出情况。

　　图书管理系统的顶层图如图1-1所示；图书管理系统的第0层DFD图如图1-2所示 其中，加工2的细化图如图1-3所示。

　【数据流图1-1】

图1-1图书管理系统顶层图

　【数据流图1-2】

图1-2图书管理系统第0层DFD图

　【数据流图1-3】

　【问题1】(2分)

　　数据流图1-2中有两条数据流是错误的，请指出这两条数据流的起点和终点。

　【问题2】(6分)

　　数据流图1-3中缺少三条数据流，请指出这三条数据流的起点和终点。

　【问题3】(7分)

　　根据系统功能和数据流图填充下列数据字典条目中的(1)和(2)：

　　查洵请求信息=[查询读者请求信息|查询图书请求信息]
　　读者情况=读者号+姓名+所在单位+{借书情况}
　　管理工作请求单=__(1)__
　　入库单=__(2)__

    试题二(20分)

　　阅读下列说明，回答问题1至问题5，将解答填入答题纸的对应栏内。

    [说明]某工厂的信息管理数据库的部分关系模式如下所示：职工(职工号，姓名，年龄，月工资，部门号，电话，办公室)部门(部门号，部门名，负责人代码，任职时间)关系模式的主要属性、含义及约束如表2-1所示，“职工”和“部门”的关系示例分别如表2-2和表2-3所示。

表2-1　主要属性、含义及约束

表2-2　“职工”关系

表2-2　“部门”关系

　　【问题1】

　　根据上述说明，由SQL定义的“职工”和“部门”的关系模式，以及统计各部门的人数C、工资总数Totals、平均工资Averages的D_S视图如下所示，请在空缺处填入正确的内容。(6分)

　　Create Table　部门（部门号　CHAR(1) __(a)__，
　　　　　　　　　　　　部门名　　　CHAR(16)，
　　　　　　　　　　　　负责人代码 CHAR(4)，
　　　　　　　　　　　　任职时间 DATE， __(b)__(职工号))；
　　Create Table　职工（职工号 CHAR(4)，
　　　　　　　　　　　　姓名 CHAR(8)，
　　　　　　　　　　　　年龄 NUMBER(3)，
　　　　　　　　　　　　月工资 NUMBER(4)，
　　　　　　　　　　　　部门号 CHAR(1)，
　　　　　　　　　　　　电话 CHAR(8)，
　　　　　　　　　　　　办公室 CHAR(8)，
　　　　　　　　　　　　__(a)__(职工号)，
　　　　　　　　　　　　__(c)__(部门号)，
　　　　　　　　　　　　 CHECKL(__(d)__));
　　Create View D_S(D,C,Totals,Averages)As，
　　　　　　　　　　　（Select 部门号，__(e)__
　　　　　　　　　　　　from 职工
　　　　　　　　　　　　__(f)__

　　【问题2】

　　对于表2-2、表2-3所示的“职工”和“部门”关系，请指出下列各行是否可以插入，为什么?(3分)

　　【问题3】

　　在问题l定义的视图D_S上，下面那个查询或更新是允许执行的，为什么? (3分)

　　(1)Update D_S set=3 where D=4；

　　(2)Delete from D_S where C>4；

　　(3)Select D，Averages fromD_S where C>(Select C from D_S where D=：dept)；

　　(4)Select D,C From D_S where Totals>10000；

　　(5)Select * from D_S；

　　【问题4】

　　查询每个部门中月工资最高的“职工号”的SQl查询语句如下：

　　Select 职工号 from 职工E
　　where 月工资=(Select Max(月工资)
　　　from职工as M
　　　where M.部门号=E.部门号)

　　(1)请用30字以内文字简要说明该查询语句对查询效率的影响。(3分)

　　(2)对该查询语句进行修改，使它既可以完成相同功能，又可以提高查询效率。(3分)

　　【问题5】

　　假定分别在“职工”关系中的“年龄”和“月工资”字段上创建了索引，如下的Select查询语句可能不会促使查询优化器使用索引，从而降低查询效率，请写出既可以完成相同功能又可以提高查询效率的SQL语句。(2分)

　　Select 姓名，年龄，月工资 from 职工

　　where 年龄>45 or 月工资<1000；

    试题三(20分)

　　阅读下列说明，回答问题1至问题5，将解答填入答题纸的对应栏内。

　　【说明】

　　某仓储超市采用POS(Point of Sale)收银机负责前台的销售收款，为及时掌握销售信息，并依此指导进货，拟建立商品进、销、存数据库管理系统。该系统的需求分析已经基本完成，下面将进入概念模型的设计。

　　【需求分析结果】

　　1．销售业务由POS收银机来辅助实现。POS机外接条码阅读器，结帐时收银员将商品的条码通过阅读器输入POS机中。所售商品数量默认值为1，可以由收银员修改。POS机根据输入的商品信息，打印出图3-1所示的购物清单。

图3-1购物清单

　　2．将经销的商品分为直销商品和库存商品两大类。直销商品的保质期较短，如食品类，由供应商直接送达超市，管理员将过期的商品返还给供应商处理；库存商品由采购员向供应商提交订购单，供应商根据订购单送货。超市会不定期对库存商品按照折扣率进行打折优惠。

　　直销商品和库存商品的送货单样表分别如图3-2、图3-3所示，其中直销商品生产批号的前六位表示生产日期。

　　3．超市的硬件拓扑结构如图3-4所示。

图3-4硬件系统拓扑结构

　　4．业务处理过程：

　　由POS机存储每一笔销售记录，在每个工作日结束前汇总当日各商品的销售量至中心数据库(销售日汇总)；根据当日的销售日汇总更新存货表；每笔进货记入进货表中，并及时更新存货表。

　　【概念模型设计】

　　根据需求阶段收集的信息，设计的实体联系图和关系模式(不完整)如下：

　　1．实体联系图

图3-5实体联系图

　　2．关系模式

　　销售详单(销售流水号，商品编码，数量，金额，收银员，时间)
　　销售日汇总(日期，商品编码，数量)
　　存货表(商品编码，数量)
　　进货表(送货号码，商品编码，数量，日期)
　　商品(＿(b)＿)

　　【问题1】

　　对直销商品和库存商品进行概括，给出超类和子类，填入图3-5中(a)处所示的虚线框内，并补充联系。(5分)

　　【问题2】

　　根据你的实体联系图，完成(b)处的商品关系模式，并增加子类型的实体关系模式。(3分)

　　【问题3】

　　对所有关系模式，以下划线指出各关系模式的主键。(4分)

　　【问题4】

　　如果将商品信息只存储在中心数据库中，与在各POS机上存储其备份相比，从前台销售效率和更新商品库两方面论述各自的优缺点(不超过300字)。(4分)

　　【问题5】

　　如果考虑引入积分卡，根据累积消费金额计算积分点，再根据积分点在顾客购物时进行现金返还，并修改顾客的累积消费金额和积分点。请给出新增加的积分卡关系模式，并对销售详单关系模式进行修正，指出修正后关系模式和新增关系模式的候选键和外键。(4分)

[NextPage]

   试题四(20分)

　　阅读下列说明，回答问题1至问题3，将解答填入答题纸的对应栏内。

　　【说明】

　　M公司为某旅游公司设计机票销售专用数据库，其关系模式如图4-1所示。

图4-1机票销售专用数据库

　　关系模式的主要属性、含义及约束如表4-1所示，属性间的函数依赖关系如图4-2所示，属性间函数依赖的标记方法如图4-3所示。

表4-1主要属性、含义及约束

　　旅客旅行前需要向旅行社提出申请，说明要参加的旅行团队。旅行社建立的旅行申请包括，旅行出发日期和到达日期的机票预订、购票等信息。旅行社还需要为每个团队制定‘旅程”和“搭乘航班”表。有关“旅程”和“搭乘航班”的示例如表4-2、表4-3所示。

表4-2“旅程”示例

表4-3“搭乘航班”示例

　　【问题1】

　　对关系“航班”，请回答以下问题：(6分)

　　(1)列举出所有不属于任何候选键的属性(非键属性)。

　　(2)关系“航班”可达到第几范式，用不超过60个字的内容叙述理由。

　　【问题2】

　　对关系“旅客”，请回答以下的问题：(6分)

　　(1)针对“旅客”关系，用100字以内文字简要说明会产生什么问题，并加以修正。

　　(2)列出修正后的关系模式的所有候选键。

　　(3)把“旅客”分解为第三范式，并用图4-1所示的关系模式的形式表示，分解后的关系名依次取旅客1、旅客2、…。

　　【问题3】

　　对关系“搭乘航班”，请回答以下的问题：(8分)

　　(1)把非平凡的多值依赖属性(图4-2中没有表示)的例子用满足图4-3的方式表示出来。

　　(2)关系“搭乘航班”是boyce codd范式而不是第四范式，请用200字以内文字阐述理由。

　　(3)把“搭乘航班”关系分解成第四范式，并采用图4-1所示的关系模式的形式表示，分解后的关系名依次取搭乘航班1、搭乘航班2、…。

    下午试题参考答案

    试题一

[问题1]解答

　　起点：读者文件 终点；登记读者信息或3

　　起点：处理查询请求 或2 终点：读者文件

[问候2]解答

　　起点；图书目录文件 终点：图书信息查询或2.2

　　起点：借书文件 终点：读者信息查询或2.1

　　起点；借书文件 终点；图书信息查询或2.2

[问题3]解答

　　(1)[入库单｜借书单｜还书单｜注销单]

　　(2)分类目录号+书名+作者+价格+数量+购书日期

    试题二

[问题1]解答

　　(a)PRIMARY KEY
　　(b)FOREIGN KEY(负贵人代码) REFERENCES 职工
　　(c)FOREIGN KEY(部门号)REFERENCES部门
　　(d)月工资>=500 AND 月工资<=5000
　　(e)coumt(*),Sum(月工资),Avg(月工资)
　　(f)Croup by 部门号

[问题2]解答

　　(1)该行不能插入“职工”关系，它违反了实体完整性中主码必须唯一区分关系中的每一个属性。
　　(2)该行可以插入“职工”关系，尽管部门号、电话和办公室为空，但是它表示该雇员没有分配到某个部门。
　　(3)该行不能插入“职工”关系，它违反了参照完整性。因为6在关系“部门’中不存在。
[问题3]解答

　　此间考查的是对视图定义的掌握。
　　(1)和(2)都不能更新，因为使用分组合聚集函数定义的视图是不可更新的。
　　(3)不一定，视子查询的返回值而定，(4)和(5)允许查询。

[问题4]解答

　　此间考察的是查询效率的问题。在涉及相关查询的某些情形中，构造临时关系可以提高查询效率。
　　(1)对于外层的职工关系E中的每一个元组，都要对内层的整个职工关系M进行检索，因此查询效率不高。
　　(2)解答一
　　改正后的SQL语句使用了临时表：
　　　Select Max(月工资)as 最高工资，部门号 into Temp from职工
　　　　Group by部门号
　　　Select 职工号 from职工,Temp
　　　　where月工资=最高工资 and 职工．部门号=Temp.部门号
　　解答二
　　　Select 职工号 from 职工，(Select Max(月工资) as 最高工资，部门号
　　　Group by 部门号) as depMax
　　　　where 月工资=最高工资 and 职工.部门号=depMax.部门号

[问题5]解答

　　此问主要考察在查询中注意where子句中使用索引的问题。
　　　Select 姓名，年龄，月工资 from 职工
　　　　where 年龄>45；
　　　union
　　　Select 姓名，年龄，月工资 from 职工
　　　　where 年龄 月工资<1000；

    试题三

[问题1]解答

[问题2]解答

　　商品(商品编号，商品名称，供应商，单价)
　　直销商品(商品编号，生产批号，消费期限)
　　库存商品(商品编号，折扣率)

[问题3]解答

　　销售详单(销售流水号，商品编码，数量，金额，收银员，时间)
　　销售日汇总（日期，商品编码，数量）
　　存货表（商品编码，数量）
　　进货表（送货号码，商品编码，数量，日期）
　　商品（商品编码，商品名称，供应商，单价）
　　直销商品（商品编号，生产批号，消费期限）
　　库存商品（商品编号，折扣率）

[问题4]解答

　　1．采用商品信息集中存储在中心数据库中，则在销售前台的每笔计费中，都必须从中心数据库提取商品名称和单价，增加网络的负载，在业务繁忙时直接影响到前台的销售效率；同时，如果发生网络故障，则该POS机不能工作。 采用这种方式，对商品库的更新，如引入新的商品和修改商品价格，会及时体现在前台的销售业务中。

　　2．采用商品信息存储在中心数据库中，各Pos机存储商品表的备份，POS机直接从本地读取商品信息，减少了网络的负载，可以提高交易的效率；同时即使有短时间的网络故障，也不影响该POS机的正常使用，只有当存在商品信息变更时才需要与中心数据库同步。

　　采用这种方式，必须在每次商品信息变更时同步各POS机的数据。

[问题5]解答

　　1．对销售详单做如下的修改，增加积分卡号属性。
　　销售详单（销售流水号，商品编号，数量，金额，收银员，时间，积分卡号）

　　2．加积分卡关系：积分卡(积分卡号，累积消费金额，积分点)

    试题四

[问题1]解答

　　(1)“航班”关系模式的候选键为(航班名，飞行日期)，非键属性为；航空公司名称，出发地点，出发时间，目的地，到达时间。

　　(2)“航班”是属于1NF的。因为非主属性航空公司名称，出发地点，目的地不完全函数依赖于候选键(航班名，飞行日期)。

　　该关系模式存在如下函数依赖：
　　航班名→航空公司名称，出发地点，目的地；
　　(航班名，飞行日期)→出发时间，到达时间。

[问题2]解答

　　参考答案1

　　(1)在题中给出的“旅客”关系中，不同的团队会有相同的旅客编号，所以，旅客编号不能作为候选键，如果同一旅客不同时间参加不同的团队将导致“身份证号无法确定关系中的每一个元组，所以“身份证号”也不能作为候选键。为此，需要增加一个“团队编号”的属性。又由于{(身份证号，团队编号)→旅客编号；(旅客编号，团队编号)→身份证号；身份证号一(姓名，联系方法，出生日期，性别)}，所以该关系模式存在部分函数依赖，导致旅客参加多少次团队，其姓名等信息将重复多少次。

　　(2)候选键：(身份证号，团队编号)和(旅客编号，团队编号)

　　(3)“旅客”分解为第三范式如下所示：

　　参考答案2

　　(1)在题中给出的“旅客”关系中，不同的团队会有相同的旅客编号，所以，旅客编号不能作为候选键，如果同一旅客不同时间参加不同的团队将导致“身份证号”无法确定关系中的每一个元组，所以“身份证号”也不能作为候选键。为此，可以通过修改“旅客编号”属性的定义加以解决，旅客编号由“团队编号+队内编号”来解决。这时关系的候选键为“旅客编号”，该关系模式存在传递依赖，导致旅客参加多少次团队，其姓名等信息将重复多少次。

　　(2)候选键；旅客编号。

　　(3)“旅客”分解为第三范式如下所示：

[问题3]解答

　　(1)旅程编号→→旅客编号，旅程编号→→{搭乘日期，航班名}

　　(2)在“搭乘航班”关系中，存在着非平凡的多值依赖，旅程编号→旅客编号，旅程编号→→{搭乘日期，航班名}，而该关系模式的候选键为(旅程编号，旅客编号，搭乘日期，航班名)，所以，根据第四范式的定义，该关系模式BCNP不是第四范式。

　　(3)把分解成第四范式的结果，用与图4-1所示的关系模式的形式表示出来。