上半数据库系统工程师考试下午真题.doc

1、2014 年上半年数据库系统工程师考试下午真题（标准参考答案）1阅读下列说明和图，回答问题 1 至问题 4，将解答填入答题纸的对应栏内。【说明】某巴士维修连锁公司欲开发巴士维修系统，以维护与维修相关的信息。该系统的 主要功能如下：1)记录巴士 ID 和维修问题。巴士到车库进行维修，系统将巴士基本信息和 ID 记录在巴士列表文件中，将待维修机械问题记录在维修记录文件中，并生成维修订单。2)确定所需部件。根据维修订单确定维修所需部件，并在部件清单中进行标记。3)完成维修。机械师根据维修记录文件中的待维修机械问题，完成对巴士的维修， 登记维修情况；将机械问题维修情况记录在维修记录文件中，将所用部件记

2、录在部件 清单中，并将所用部件清单发送给库存管理系统以对部件使用情况进行监控。巴士司机可查看已维修机械问题。4)记录维修工时。将机械师提供的维修工时记录在人事档案中,将维修总结发送 给主管进行绩效考核。5)计算维修总成本。计算部件清单中实际所用部件、人事档案中所用维修工时的 总成本；将维修工时和所用部件成本详细信息给会计进行计费。现采用结构化方法对巴士维修系统进行分析与设计，获得如图 1-1 所示的上下文 数据流图和图 1-2 所示的 0 层数据流图。【问题 1】（5 分）使用说明中的词语，给出图 1-1 中的实体 E1?E5 的名称。【问题 2】（4 分）使用说明中的词语，给出图 1-2 中

3、的数据存储 D1?D4 的名称。【问题 3】(3 分)说明图 1-2 中所存在的问题。【问题 4】（3 分）根据说明和图中术语，釆用补充数据流的方式，改正图 1-2 中的问题。要求给出 所补充数据流的名称、起点和终点。参考答案：【问题 1】（5 分）E1：巴士司机 E2：机械师 E3：会计 E4：主管 E5：库存管理系统【问题 2】（4 分）D1：巴士列表文件 D2：维修记录文件 D3：部件清单 D4：人事档案【问题 3】（3 分）缺失以下数据流：（1）名称：待维修机械问题；起点：D2；终点：3 完成维修。（2）名称：实际所用部件；起点：D3；终点：5 计算总成本。【问题 4】（3 分）补充以

4、下数据流：（1）名称：待维修机械问题；起点：D2；终点：3 完成维修。（2）名称：实际所用部件；起点：D3；终点：5 计算总成本。试题分析：根据第 3)点：巴士司机可查看已维修机械问题，可知 E1 为巴士司机；根据第 3)点：机械师根据维修记录文件中的待维修机械问题，完成对巴士的维修， 登记维修情况，可知 E2为机械师；根据第 5)点：将维修工时和所用部件成本详细信息给会计进行计费，可知 E3为会计；根据第 4)点：将机械师提供的维修工时记录在人事档案中,将维修总结发送 给主管进行绩效考核，可知 E4 为主管；根据第 3)点：将所用部件清单发送给库存管理系统以对部件使用情况进行监控，可知 E5

5、 为库存管理系统。根据第 1)点：系统将巴士基本信息和 ID 记录在巴士列表文件中，可知 D1 为巴士列表文件；根据第 1)点：将待维修机械问题记录在维修记录文件中，并生成维修订单，可知 D2 为维修记录文件；根据第 2)点：根据维修订单确定维修所需部件，并在部件清单中进行标记，可知 D3 为部件清单；根据第 4)点：将机械师提供的维修工时记录在人事档案中，可知 D4为人事档案。根据系统主要功能及 0 层数据流图，不难得知缺失的数据流。2阅读下列说明，回答问题 1 至问题 3，将解答填入答题纸的对应栏内。【说明】某健身俱乐部要开发一个信息管理系统，该信息系统的部分关系模式如下：员工（员工身份证

6、号，姓名，工种，电话，住址）会贝（会员手机号，姓名，折扣）项目（项目名称，项目经理，价格）预约单（会员手机号，预约曰期，项目名称，使用时长）消费（流水号，会员手机号，项目名称，消费金额，消费日期）有关关系模式的属性及相关说明如下：1)俱乐部有多种健身项目，不同的项目每小时的价格不同。俱乐部实行会员制， 且需要电话或在线提前预约。2)每个项目都有一个项目经理，一个经理只能负责一个项目。3)俱乐部对会员进行积分，达到一定积分可以进行升级，不同的等级具有不同的折扣。 根据以上描述，回答下列问题：【问题 1】请将下面创建消费关系的 SQL 语句的空缺部分补充完整，要求指定关系的主码、 外码，以及消费金

7、额大于零的约束。CREATE TABLE 消费（流水号 CHAR(12) (a) PRIMARY KEY ,会员手机号 CHAR(11),项目名称 CHAR (8),消费金额 NUMBER (b) CHECK（消费金额0）,消费日期 DATE,(c) FOREIGN KEY（会员手机号）REFERENCES 会员（会员手机号）,(d) FOREIGN KEY（项目名称）REFERENCES 项目（项目名称）,); 【问题 2】1)手机号为 18812345678 的客户预约了 2014 年 3 月 18 日两个小时的羽毛球场地，消费流水号由系统自动生成。请将下面 SQL 语句的空缺部分补充完整

8、。INSERT into 消费（流水号，会员手机号，项目名称，消费金额，消费日期）SELECT 201403180001，18812345678，羽毛球, (e)项目.价格*会员.折扣,2014/3/18FROM 会员，项目，预约单WHERE 预约单.项目名称=项目.项目名称 AND (f) 预约单.会员手机号=会员.会员手机号 AND 项目.项目名称=羽毛球AND 会员.会员手机号=18812345678；(2)需要用触发器来实现会员等级折扣的自动维护，函数 float vip_value(char(11) 会员手机号）依据输入的手机号计算会员的折扣。请将下面 SQL 语句的空缺部分补充 完

9、整。CREATE TRIGGER VIP_TRG AFTER(g) UPDATE 0N (h) 会员 REFERENCING new row AS nrowFOR EACH ROWBEGINUPDATE 会员SET (i) 折扣=vip_value(nrow. 会员手机号)WHERE (j) 会员手机号= nrow. 会员手机号 ；END【问题 3】请将下面 SQL 语句的空缺部分补充完整。1)俱乐部年底对各种项目进行绩效考核，需要统计出所负责项目的消费总金额 大于等于十万元的项目和项目经理，并按消费金额总和降序输出。SELECT 项目.项目名称，项目经理，SUM(消费金额）FROM 项目，消

10、费WHERE (k) 项目.项目名=消费.项目名 GROUP BY (l) 项目名 HAVING SUM（消费金额）=100000ORDER BY (m) SUM（消费金额） DESC ;查询所有手机号码以“888”结尾，姓“王”的员工姓名和电话。SELECT 姓名，电话FROM 员工WHERE 姓名 (n) LIKE 王% AND 电话 (o) LIKE %888 3阅读下列说明，回答问题 1 至问题 3，将解答填入答题纸的对应栏内。【说明】某图书馆的管理系统部分需求和设计结果描述如下：图书馆的主要业务包括以下几项：(1)对所有图书进行编目，每一书目包括 ISBN 号、书名、出版社、作者、排

11、名， 其中一部书可以有多名作者，每名作者有唯一的一个排名；(2)对每本图书进行编号，包括书号、ISBN 号、书名、出版社、破损情况、存 放位置和定价，其中每一本书有唯一的编号，相同 ISBN 号的书集中存放，有相同的 存储位置，相同ISBN 号的书或因不同印刷批次而定价不同；(3)读者向图书馆申请借阅资格，办理借书证，以后凭借书证从图书馆借阅图书。 办理借书证时需登记身份证号、姓名、性别、出生年月日，并交纳指定金额的押金。 如果所借图书定价较高时，读者还须补交押金，还书后可退还所补交的押金；(4)读者借阅图书前，可以通过 ISBN 号、书名或作者等单一条件或多条件组合 进行查询。根据查询结果，

12、当有图书在库时，读者可直接借阅；当所查书目的所有图 书己被他人借走时，读者可进行预约，待他人还书后，由馆员进行电话通知；(5)读者借书时，由系统生成本次借书的唯一流水号，并登记借书证号、书号、 借书日期，其中同时借多本书使用同一流水号，每种书目都有一个允许一次借阅的借 书时长，一般为90 天，不同书目有不同的借书时长，并且可以进行调整，但调整前所 借出的书，仍按原借书时长进行处理；(6)读者还书时，要登记还书日期，如果超出借书时长，要缴纳相应的罚款；如果所还图书由借书者在持有期间造成破损，也要进行登记并进行相应的罚款处罚。初步设计的该图书馆管理系统，其关系模式如图 4-1 所示。【问题 1】对

13、关系“借还”，请回答以下问题：(1)列举出所有候选键；(2)根据需求描述，借还关系能否实现对超出借书时长的情况进行正确判定？用 60 字以内文字简要叙述理由。如果不能,请给出修改后的关系模式（只修改相关关系 模式属注时，仍使用原关系名，如需分解关系模式，请在原关系名后加 1，2，等进 行区别)【问题 2】对关系“图书”，请回答以下问题：(1)写出该关系的函数依赖集；(2)判定该关系是否属于 BCNF，用 60 字以内文字简要叙述理由。如果不是，请 进行修改，使其满足 BCNF,如果需要修改其它关系模式，请一并修改，给出修改后 的关系模式（只修改相关关系模式属性时，仍使用原关系名，如需分解关系模

14、式，请 在原关系名后加1，2，.等进行区别）。【问题 3】对关系“书目”，请回答以下问题：(1)它是否属于第四范式，用 60 字以内文字叙述理由。(2)如果不是，将其分解为第四范式，分解后的关系名依次为：书目 1，书目 2，。 如果在解决【问题 1】、【问题 2】时，对该关系的属性进行了修改，请沿用修改后的属性。参考答案：【问题 1】（1）流水号、书号；借书证号、书号、借书日期；（2）不能；原因是：当更新借书时长时，就会导致原来借书使用的时长丢失，并采用最新更新的时长。借还 1（流水号、书号、借书证号、借书日期、应还日期）借还 2（流水号、书号、还书日期）借还 3（流水号、书号、罚款金额、罚款

15、原因）【问题 2】（1）书号（ISBN 号、书名、出版社、破坏情况、存放位置、定价）；ISBN 号（书名、出版社、存放位置）（2）不属于，原因是：存在传递依赖书目（ISBN 号、书名、出版社、作者、排名、存放位置、借书时长）图书 1（书号、ISBN 号、价格）【问题 3】（1）否，存在多值依赖，ISBN 号作者书目 1（ISBN 号、书名、出版社、存放位置、借书时长）书目 2（ISBN 号、作者、排名）4阅读下列说明，回答问题 1 至问题 3，将解答填入答题纸的对应栏内。【说明】某高速路不停车收费系统（ETC)的业务描述如下：(1)车辆驶入高速路入口站点时，将驶入信息（ETC 卡号，入口编号，

16、驶入时间） 写入登记表；(2)车辆驶出高速路出口站点（收费口）时，将驶出信息（ETC 卡号，出口编号， 驶出时间）写入登记表；根据入口编号、出口编号及相关收费标准，清算应缴费用， 并从绑定的信用卡中扣除费用。一张 ETC 卡号只能绑定一张信用卡号，针对企业用户，一张信用卡号可以绑定多 个 ETC 卡号。使用表绑定（ETC 卡号，信用卡号）来描述绑定关系，从信用卡（信 用卡号，余额）表中扣除费用。针对上述业务描述，完成下列问题：【问题 1】在不修改登记表的表结构和保留该表历史信息的前提下，当车辆驶入时，如何保 证当前ETC 卡已经清算过，而在驶出时又如何保证该卡已驶入而未驶出？请用 100 字以

17、内文字简述处理方案。【问题 2】当车辆驶出收费口时，从绑定信用卡余额中扣除费用的伪指令如下：读取信用卡 余额到变量 X，记为 x = R(A)；扣除费用指令 x = x - a;写信用卡余额指令记为 W(A, x)。 (1)当两个绑定到同一信用卡号的车辆同时经过收费口时，可能的指令执行序 列为：xl=R(A)，xl =xl-al, x2 = R(A), x2 = x2-a2, W(A，xl)，W(A，x2)。此时会出现什么问题？（100 字以内）(2)为了解决上述问题，弓丨入独占锁指令 XLock(A)对数据 A 进行加锁，解锁指 令Unlock(A)对数据 A 进行解锁。请补充上述执行序列，

18、使其满足 2PL 协议。【问题 3】下面是用 E-SQL 实现的费用扣除业务程序的一部分，请补全空缺处的代码。CREATE PROCEDURE 扣除(IN ETC 卡号 VARCHAR(20)，IN 费用 FLOAT) BEGINUPDATE 信用卡 SET 余额=余额一：费用FROM 信用卡，绑定WHERE 信用卡.信用卡号=绑定.信用卡号 AND (a) ;if error then ROLLBACK;else (b) ;END参考答案：【问题 1】新增一个收费记录表（ETC 卡、驶入时间、费用、状态），状态为：已缴/未缴，当驶入时判断收费记录表的状态是否为已缴。当驶出时查询是否有该 ET

19、C 卡未缴费的记录。【问题 2】出现丢失更新【问题 3】a、绑定.ETC 卡号= :ETC 卡号b、COMMIT ；5阅读下列说明和图，回答问题 1 至问题 3，将解答填入答题纸的对应栏内。【说明】某家电销售电子商务公司拟开发一套信息管理系统，以方便对公司的员工、家电销售、家电厂商和客户等进行管理。【需求分析】（1）系统需要维护电子商务公司的员工信息、客户信息、家电信息和家电厂商信息等。员工信息主要包括：工号、姓名、性别、岗位、身份证号、电话、住址，其中岗位包括部门经理和客服等。客户信息主要包括：客户 ID、姓名、身份证号、电话，住址、账户余额。家电信息主要包括：家电条码、家电名称、价格、出厂

20、日期、所属厂商。家电厂商信息包括：厂商 ID、厂商名称、电话、法人代表信息、厂址。（2）电子商务公司根据销售情况，由部门经理向家电厂商订购各类家电。每个家电厂商只能由一名部门经理负责。（3）客户通过浏览电子商务公司网站查询家电信息，与客服沟通获得优惠后，在线购买。【概念模型设计】根据需求阶段收集的信息，设计的实体联系图（不完整）如图 2-1 所示。【逻辑结构设计】根据概念模型设计阶段完成的实体联系图，得出如下关系模式不完整）客户（客户 ID、姓名、身份证号、电话、住址、账户余额）员工（工号、姓名、性别、岗位、身份证号、电话、住址）家电（家电条码、家电名称、价格、出厂日期、（1）家电厂商（厂商

21、ID、厂商名称、电话、法人代表信息、厂址、（2）购买（订购单号、（3）、金额）【问题 1】（6 分） 补充图 2-1 中的联系和联系的类型。【问题 2】（6 分）根据图 2-1，将逻辑结构设计阶段生成的关系模式中的空（1）-（3）补充完整。 用下划线指出“家电”、“家电厂商”和“购买”关系模式的主键。【问题 3】（3 分）电子商务公司的主营业务是销售各类家电，对账户有佘额的客户，还可以联合第二方基金公司提供理财服务，为此设立客户经理岗位。客户通过电子商务公司的客户经理和基金公司的基金经理进行理财。每名客户只有一名客户经理和一名基金经理负责客户经理和基金经理均可负责多名客户。请根据该要求，对图

22、2-1 进行修改，画出修改后 的实体间联系和联系的类型。参考答案：【问题 1】（6 分）【问题 2】（6 分）（1）厂商 ID（2）工号（3）家电条码，客户 ID，工号家电关系的主键：家电条码；家电厂商关系的主键：厂商 ID；购买关系的主键：订购单号【问题 3】（3 分）试题分析：根据实际生活经验，不难得知家电厂商与家电之间的关系为一对多；家电与客户之间的关系，此处的家电是指家电的类型，因此一种类型的家电可以被多个客户购买，一个客户也可以购买多种不同类型的家电，所以家电与客户之间的关系为多对多；根据题意：每名客户只有一名客户经理和一名基金经理负责客户经理和基金经理均可负责多名客户，可知客户经理与客户的关系是一对多，基金经理与客户的关系也是一对多。根据题目中的需求分析，不难得出关系模式的答案。