2017年上半年 数据库系统工程师 下午试卷 案例

《2017年上半年 数据库系统工程师 下午试卷 案例》由会员分享，可在线阅读，更多相关《2017年上半年 数据库系统工程师 下午试卷 案例（21页珍藏版）》请在装配图网上搜索。

1、2017 年上半年 数据库系统工程师 下午试卷 案例1. 阅读下列说明和图，回答问题 1 至问题 4 ，将解答填入答题纸的 对应栏内。【说明】 某医疗器械公司作为复杂医疗产品的集成商，必须保持高质量部件的 及时供应。为了实现这一目标，该公司欲开发一采购系统。系统的主 要功能如下：1. 检查库存水平。采购部门每天检查部件库存量，当特定部件的库存 量降至其订货店时，返回低存量部件及库存量。2. 下达采购订单。采购部门针对低存量部件及库存量提交采购请求， 向其供应商(通过供应商文件访问供应商数据)下达采购订单，并存 储于采购订单文件中。3. 交运部件。当供应商提交提单并交运部件时，运输和接收(S/R

2、) 部门通过执行以下三步过程接收货物：(1) 验证装运部件。通过访问采购订单并将其与提单进行比较来验证 装运的部件， 并将提单信息发给 S/R 职员。如果收货部件项目出现 在采购订单和提单上，则己验证的提单和收货部件项目将被送去检 验。否则 S /R 职员提交的装运错误信息生成装运错误通知发送给供 应商。(2) 检验部件质量。通过访问质量标准来检查装运部件的质量，并将 己验证的提单发给检验员。如果部件满足所有质量标准，则将其添加 到接受的部件列表用于更新部件库存。如果部件未通过检查，则将检 验员创建的缺陷装运信息生成缺陷装运通知发送给供应商。(3) 更新部件库存。库管员根据收到的接受的部件列表

3、添加本次采购 数量，与原有库存量累加来更新库存部件中的库存量。标记订单采购 完成。现采用结构化方法对该采购系统进行分析与设计，获得如图 1-1 所 示的上下文数据流图和图1-2所示的 0层数据流图。卑购订单土更諫葡件库存枣4尹沃:弓:-抵sm信島魁.腰量劣时列表提覃宙忌订卑牯息爭应曲整息已脸证揑览觇苗非也倍恳岳晋请車L. 関库存更新ap件戟裁黑购完应标记r握单帯下达采购订单.朋UE藝迄辄件埠菇运错侵信息,靳订单恬问题：1.1（5 分）使用说明中的词语，给出图 1-1 中的实体 E1-E5 的名称。问题：1.2（4 分）使用说明中的词语，给出图 1-2 中的数据存储 D1-D4 的名称。问题：1

4、.3（4 分）根据说明和图中术语，补充图 1-2 中缺失的数据 流及其起点和终点。问题：1.4（2 分）用 200 字以内文字，说明建模图 1-1 和图 1-2 时 如何保持数据流图平衡。2. 阅读下列说明，回答问题1 至问题 3，将解答填入答题纸的对应栏 内。【说明】 某房屋租赁公司拟开发一个管理系统用于管理其持有的房屋、租客及 员工信息。请根据下述需求描述完成系统的数据库设计。【需求描述】1. 公司拥有多幢公寓楼，每幢公寓楼有唯一的楼编号和地址。每幢公 寓楼中有多套公寓，每套公寓在楼内有唯一的编号（不同公寓楼内的 公寓号可相同）。系统需记录每套公寓的卧室数和卫生间数。2. 员工和租客在系统

5、中有唯一的编号（员主编号和租客编号）。3. 对于每个租客，系统需记录姓名、多个联系电话、一个银行账号（方 便自动扣房租）、一个紧急联系人的姓名及联系电话。4. 系统需记录每个员工的姓名、类别、一个联系电话和月工资。员工 类别可以经理或维修工，也可兼任。每个经理可以管理多幢公寓楼。 每幢公寓楼必须由一个经理管理。 系统需记录每个维修工的业务技 能，如：水暖维修、电工、木工等。5. 租客租赁公寓必须和公司签订租赁合同。一份租赁合同通常由一个 或多个租客（合租）与该公寓楼的经理签订，一个租客也可租赁多套公 寓。合同内容应包含签订日期、开始时间租期、押金和月租金。 【概念模型设计】根据需求阶段收集的信

6、息，设计的实体联系图（不完整）如图 2-1 所 示。经理管理签约包含*租赁j公寓I图2J实体联系图公寓楼员工维修工租客【逻辑结构设计】 根据概念模型设计阶段完成的实体联系图，得出如下关系模式（不完 整）:联系电话 （电话号码，租客编号）租客 （租客编号，姓名，银行账号，联系人姓名，联系人电话） 员工 （员工编号，姓名，联系电话，类别，月工资，（ a ） 公寓楼 （ （b） ，地址，经理编号） 公寓（楼编号，公寓号，卧室数，卫生间数） 合同（合同编号，租客编号，楼编号，公寓号，经理编号，签订日期 起始日期，租期，（c ），押金）问题：2.1（ 4.5 分）补充图 2-1 中的签约联系所关联的实体

7、及联系类型。问题：2.2（4.5 分）补充逻辑结构设计中的（ a） （b）（c ） 三处 空缺。问题：2.3（6 分）租期内，公寓内设施如出现问题，租客可在系统 中进行故障登记，填写故障描述，每项故障由系统自动生成唯一的故 障编号，由公司派维修工进行故障维修，系统需记录每次维修的维修 日期和维修内容。请根据此需求，对图 2-1 进行补充，并将所补充 的 ER 图内容转换为一个关系模式，请给出该关系模式。3. 【说明】 某社会救助基金会每年都会举办多项社会公益救助活动，需要建立一 个信息系统，对之进行有效管理。【需求描述】1. 任何一个实名认证的个人或者公益机构都可以发起一项公益救助 活动，基金

8、会需要记录发起者的信息。如果发起者是个人，需要记录 姓名、身份证号和一部电话号码；如果发起者是公益机构，需要记录 机构名称、统一社会信用代码、一部电话号码、唯一的法人代表身份 证号和法人代表姓名。一个自然人可以是多个机构的法人代表。2. 公益救助活动需要提供详实的资料供基金会审核，包括被捐助人姓 名、身份证号、一部电话号码、家庭住址。3. 基金会审核并确认项目后，发起公益救助的个人或机构可以公开宣 传井募捐，募捐得到的款项进入基金会账户。4. 发起公益救助的个人或机构开展救助行动，基金会根据被捐助人所 提供的医疗发票或其它信息，直接将所筹款项支付给被捐助者。5. 救助发起者针对任一被捐助者的公

9、益活动只能开展一次。【逻辑结构设计】根据上述需求，设计出如下关系模式:公益活动(发起者编号，被捐助者身份证号，发起者电话号码，发起 时间，结束时间，募捐金额)，其中对于个人发起者，发起者编号为 身份证号；对于机构发起者，发起者编号为统一社会信用代码。 个人发起者(姓名，身份证号，电话号码) 机构发起者(机构名称，统一社会信用代码，电话号码，法人代表身 份证号，法人代表姓名) 被捐助者(姓名，身份证号，电话号码，家庭住址)问题:3.1(6分)对关系机构发起者,请回答以下问题：(1) 列举出所有候选键。(2) 它是否为3NF，用100字以内文字简要叙述理由。(3) 将其分解为BC范式，分解后的关系

10、名依次为：机构发起者1,机构发起者2，.，并用下划线标示分解后的各关系模式的主键。问题:3.2(6分)对关系“公益活动，请回答以下问题：(1) 列举出所有候选键。(2) 它是否为2NF，用100字以内文字简要叙述理由。(3) 将其分解为BC范式，分解后的关系名依次为:公益活动1,公益活动2，.，并用下划线标示分解后的各关系模式的主键。问题：3.3（3 分）基金会根据被捐助人提供的医疗发票或其它信息， 将所筹款项支付给被捐助者。可以存在分期多次支付的情况，为了统 计所筹款项支付情况（详细金额和时间） ,试增加支付记录关系模式， 用 100 字以文字简要叙述解决方案。4. 阅读下列说明，回答问题

11、1 至问题 5 ，将解答填入答题纸的对应 栏内。【说明】 某公司要对其投放的自动售货机建立商品管理系统，其数据库的部分 关系模式如下：售货机: VEM（VEMno， Location） ，各属性分别表示售货机编号、 部署地点；商品: GOODS（Gno， Brand， Price） ，各属性分别表示商品编号、 品牌名和价格；销售单：SALES（Sno, VEMno, Gno, SDate, STime），各属性分别表 示销售号、售货机编号、商品编号、日期和时间。缺货单：OOS（VEMno, Gno, SDate, STime ）,各属性分别表示售货机 编号、商品编号、 日期和时间。相关关系模式

12、的属性及说明如下： （1）售货机摆放固定种类的商品, 售货机内每种商品最多可以储存 10 件。管理员在每天结束的时候将售货机中所有售出商品补全(2)每售出一件商品，就自动向销售单中添加一条销售记录。如果一天内某个售货机上某种商品的销售记录达到 10 条，则表明该售货机上该商品已售完，需要通知系统立即补货，通过自动向缺货单中添加 一条缺货记录来实现。根据以上描述，回答下列问题，将SQL语句的空缺部分补充完整。问题:4.1(3分)请将下面创建销售单表的SQL语句补充完整，要求指定关系的主码 和外码约束。CREATE TABLE SALES(Sno CHAR(8) (a)VEM no CHAR(5)

13、 (b)Gno CHAR(8) (c)SDate DATE,STime TIME);问题：4.2(4分)创建销售记录详单视图SALES_Detail，要求按日期统计每个售货机 上各种商品的销售数量，属性有VEM no、Locatio n、Gno、Bra nd、 Price、amount和SDate。为方便实现，首先建立视图 SALES_Total，然后利用 SALES_Total 完成视图 SALES_Detail 的 定义。CREATE VIEW SALES _ Total(VEM no,G no,SDate,amou nt) ASSELECT VENno ,Gno ,SDate , cou

14、nt(*)FROM SALESGROUP BY (d);CREATE VIWE (e) ASSELECT VEM.VEM no, Location , GOODS.G no , Bra nd, Price, amou nt, SDateFROM VEM, GOODS, SALES_TotalWHERE (f) AND (g)问题：4.3(3分)每售出一件商品，就自动向销售单中添加一条销售记录。如果一天内 某个售货机上某种商品的销售记录达到10条，则自动向缺货单中添 加一条缺货记录。需要用触发器来实现缺货单的自动维护。程序中的 GetTime ()获取当前时间。CREAT(h) OOS_TRG

15、AFTER (i) ON SALESREFERENCING new row AS nrowFOR EACH ROWBEGININSERT INTO OOSSELECT SALES .VENno, j) GetTime ()FROMSALESWHERE SALES.VEM no = nrow.VEM no AND SALES.G no = n row.G noAND SALES.SDate = n row.SDateGROUP BY SALES.VEMno，SALES.Gno，SALES.SDateHAVING count (*) 0 AND mod(count (*)， 10)=0;END问题

16、：4.4(3分)查询当天销售最多的商品编号、品牌和数量。程序中的GetDateO 获取当天日期。SELECT GOODS.Gno ， Brand，(k)FROM GOODS,SALESWHERE GOODS.G no 二SALES.GNO AND SDATE 二GetDateOGROUP BY (1)HAVING(M) (SELECT cou nt(*)FROM SALELSWHERE SDATE = GetDate()GROUP BY Gno);问题：4.5(2分)查询一件都没有售出的所有商品编号和品牌。SELECT Gno ,BrandFROM GOODSWHERE GNO(N)SELEC

17、T DISTINCT GNOFROM(o);5. 阅读下列说明，回答问题 1 和问题 2 ，将解答填入答题纸的对应栏内。【说明】某抢红包软件规定发红包人可以一次抛出多个红包，由多个人来抢。要求每个抢红包的人最多只能抢到同一批次中的一个红包，且存在多个人同时抢同一红包的情况。给定的红包关系模式如下:Red（ID，BatchID，SenderID，Money，ReceiverID）其中 ID 唯一标识每一个红包； BatchID 为发红包的批次，一个BatchlD值可以对应多个ID值；endeCD为发红包人的标识；oney为红包中的钱数； ReceiverID 记录抢到红包的人的标识。发红包人一次

18、抛出多个红包，即向红包表中插入多条记录，每条记录表示 一个红包， 其 ReceiverID 值为空值。抢某个红包时，需要判定该红包记录的 ReceiverID 值是否为空，不 为空时表示该红包己被抢走，不能再抢，为空时抢红包人将自己的标 识写入到 ReceiverID 宇段中，即为抢到红包。问题:5.1（9分）引入两个伪指令a = R（X）和W（b, X）。其中a = R（X）表示读取 当前红包记录的ReceiverlD字段（记为数据项X）到变量a中， W（b，X）表示将抢红包人的唯一标识b的值写入到当前红包记录的 ReceiverID字段（数据项X）中，变量a为空值时才会执行W（b，X） 操

19、作。假设有多个人同时抢同一红包（即同时对同一记录进行操作）， 用ai=Ri （X）和Wi（bi， X）表示系统依次响应的第i个人的抢红包 操作。假设当前数据项X为空值，同时有三个人抢同一红包，则 （1）如下的调度执行序列：a1 =R1， a2 = R2（X），W1（b1，X），W2 （b2，X）， a3 = R3（X） 抢到红包的是第几人?并说明理由。(2)引入共享锁指令SLocki(X)、独占锁指令XLocki(X)和解锁指令 ULocki(X),其中下标i表示第i个抢红包人的指令。如下的调度执 行序列：SLock1(X)，al = R1(X)，SLock2(X)，a2 = R2(X)，XL

20、ock1(X)是否会产生死锁?并说明理由。(3)为了保证系统第一个响应的抢红 包人为最终抢到红包的人，请使用上述。)中引入的锁指令，对上述 中的调度执行序列进行修改，在满足2PL协议的前提下，给出一 个不产生死锁的完整的调度执行序列。问题：5.2(6分)下面是用SQL实现的抢红包程序的一部分，请补全空缺处的代码 CREATE PROCEDURE ScrambleRed (IN BatchNo VARCHAR(20)， 一 红包批号(IN RecvrNo VARCHAR(20)接收红包者BEGIN是否已抢过此批红包if exists( SELECT * FROM RedWHERE BatchID

21、 = BatchNo AND ReceiverID = RecvrNo) thmretur n T;end if;-读取此批派发红包中未领取的红包记录IDDECLARE NonRecvedNo VARCHAR(30);DECLARE NonRecvedNo CURSOR FORSELECT IDFROM RedWHERE BatchID = BatchNo AND ReceiverID IS NULL;-打开游标OPEN NonRecvedRed;FETCH NonRecvedRed INTO NonRecvedNo;while not error抢红包事务BEGIN TRANSACTION;

22、/写入红包记录UPDATE RED STE RECDIVER ID =RecvrNoWHERE ID = nonRECVED AND(a)/执行状态判定IfV修改的记录数=1 THEN COMMIT;(b) ;Return 1;ElseROLLBACK;End if;(c) ;End while一关闭游标CLOSE NonRecved RDReturn 0;END答案1.答案解析：（5 分）E1：供应商E2：采购部E3：检验员E4：库管员E5： S/R 职员依据题干中下达采购订单。采购部门针对低存量部件及库存量提交采 购请求，向其供应商（通过供应商文件访问供应商数据）下达采购订 单，并存储于采

23、购订单文件中。可以判断出 E1 为供应商 依据题干中检查库存水平。采购部门每天检查部件库存量，当特定部 件的库存量降至其订货店时，返回低存量部件及库存量。，可以判断出E2为采购部依据题干中通过访问质量标准来检查装运部件的质量，并将己验证的 提单发给检验员。如果部件满足所有质量标准，则将其添加到接受的 部件列表用于更新部件库存。，可以判断出 E3 为检验员 依据题干中库管员根据收到的接受的部件列表添加本次采购数量，可 以判断出E4为库管理员依据题干中如果收货部件项目出现在采购订单和提单上，则己验证的提单和收货部件项目将被送去检验。否则 S/R 职员提交的装运错误信息生成装运错误通知发送给供应商。

24、，可以判断出 E5 为 S/R 职员 答案解析：（4 分）D1：库存表D2：采购订单表D3：质量标准表D4：供应商表依据题干中更新部件库存。库管员根据收到的接受的部件列表添加本次采购数量，与原有库存量累加来更新库存部件中的库存量。，结合零层图的数据流，可以得出 D1 为库存表依据题干中下达采购订单。采购部门针对低存量部件及库存量提交采 购请求，向其供应商（通过供应商文件访问供应商数据）下达采购订 单，并存储于采购订单文件中。和更新部件库存。库管员根据收到的 接受的部件列表添加本次采购数量，与原有库存量累加来更新库存部 件中的库存量。标记订单采购完成，结合零层图的数据流，可以得出D2为采购订单表

25、，D4为采购订单表依据题干中检验部件质量。通过访问质量标准来检查装运部件的质 量，并将己验证的提单发给检验员。如果部件满足所有质量标准，则 将其添加到接受的部件列表用于更新部件库存。，结合零层图的数据 流，可以得出D3为质量标准表答案解析： （4分）装运错误通知：P3 （验证装运部件）E1 （客户）缺陷装运通知：P4 （校验部件质量）E1 （客户）产品检验：P3 （验证装运部件）P4 （校验部件质量）检查库存信息： P1 （检查库存水平） D1 （库存表）依据题干中（1）中：否则 S/R 职员提交的装运错误信息生成装运错 误通知发送给供应商。，结合零层图可以发现缺失数据流：装运错误 通知：P3

26、 （验证装运部件） E1 （客户）依据题干中（ 2）中：如果部件未通过检查，则将检验员创建的缺陷 装运信息生成缺陷装运通知发送给供应商。，结合零层图可以发现缺失数据流：缺陷装运通知：P4 （校验部件质量）E1 （客户）依据题干中（1）中：如果收货部件项目出现在采购订单和提单上， 则己验证的提单和收货部件项目将被送去检验。，结合零层图可以发现缺失数据流：产品检验：P3 （验证装运部件）P4 （校验部件质量）依据题干中1、检查库存水平。采购部门每天检查部件库存量，当特 定部件的库存量降至其订货店时，返回低存量部件及库存量。，结合 零层图可以发现缺失数据流：检查库存信息：P1 （检查库存水平）D1

27、（库存表）答案解析：（2 分） 父图中某个加工的输入输出数据流必须与其子图的输入输出数据流 在数量上和名字上相同。父图的一个输入（或输出）数据流对应于子图中几个输入（或输出）数据流，而子图中组成的这些数据流的数据项全体正好是父图中的这一个数据流。2.答案解析：依据题干中租客租赁公寓必须和公司签订租赁合同。一份租赁合同通 常由一个或多个租客（合租）与该公寓楼的经理签订，一个租客也可 租赁多套公寓。合同内容应包含签订日期、开始时间租期、押金和月 租金。，说明签约应该是经理与租赁之间的，而已份租赁包括一位或 多位租客，以及一个或多个公寓，所以可以考虑为：经理实体集与租 赁（由租客和公寓组合成一个大的

28、实体集）之间的联系 在结合题干中每个经理管多个公寓楼，每个公寓楼由一个经理管理， 和一个楼有多个公寓的描述，可以判定联系的类型为1：* 答案解析：（4.5 分）（a）业务技能（b）楼编号（ c ）月租金从题干中系统需记录每个员工的姓名、类别、一个联系电话和月工资。 员工类别可以经理或维修工，也可兼任。每个经理可以管理多幢公寓 楼。每幢公寓楼必须由一个经理管理。系统需记录每个维修工的业务 技能，如：水暖维修、电工、木工等说明需要记录的属性有：姓名、 类别、一个联系电话、月工资和业务技能。因此（a）处应为：业务 技能 题干中每幢公寓楼有唯一的楼编号和地址以及每幢公寓楼必须由一 个经理管理同时管理联

29、系没有转换成一个独立的关系，也就意味着管 理联系被合并到了公寓楼的实体对应的关系中，因此，公寓楼实体对 应的关系的属性应该有：楼编号、地址、经理编号；因此（b）处应 该为：楼编号 依据题干中合同内容应包含签订日期、开始时间租期、押金和月租金。 结合关系合同（合同编号，租客编号，楼编号， 公寓号，经理编号 签订日期，起始日期，租期，（c），押金），可以得出（c）处应该为: 月租金答案解析：（6 分） 维修（故障编号，维修工，维修日期，维修内容，楼编号，公寓号， 租客编号）题干中租期内，公寓内设施如出现问题，租客可在系统中进行故障登 记，填写故障描述，每项故障由系统自动生成唯一的故障编号，由公 司

30、派维修工进行故障维修，系统需记录每次维修的维修日期和维修内 容说明，维修应该与租赁（由租客和公寓组合成一个大的实体集）之间 存在多对多的联系，同时需要有自己的属性：故障编号、维修日期、 维修内容。维修（故障编号，维修工，维修日期，维修内容，楼编号，公寓号，租客编号）公寓租赁超工租客公寓搂3.答案解析：（6 分）（1）统一社会信用代码（2）否，存在传递依赖 ：统一社会信用代码-法人代表身份证号，法人代表身份证号-法人代表姓名（3）机构发起者 1（机构名称，统一社会信用代码，电话号码，人代表身份证号）主键：统一社会信用代码机构发起者 2（人代表身份证号，法人代表姓名）主键：法人代表身份证号答案解析

31、：（6 分）（1）发起者编号+被捐助者身份证号（2）否：存在部分函数依赖：发起者编号-（发起者电话号码）对码（发起者编号，被捐助者身份证号）存在部分函数依赖3）公益活动 1（发起者编号，发起者电话号码）主键：发起者编公益活动 2（发起者编号，被捐助者身份证号，发起时间，结束时间， 募捐金额）主键：发起者编号+被捐助者身份证号 答案解析：支付记录（支付编号，发起者编号，被捐助者身份证号， 支付金额，支付时间，被捐助人的相关信息）（被捐助人的相关信息 为医疗发票或其他信息），支付编号唯一标识每一次支付4.答案解析：（3 分）（a）PRIMARY KEY（b）REFERENCES VEM（VEMno

32、）（c）REFERENCES GOODS（Gno）答案解析：（6 分）（d）VEMno，Gno，SDate（e）SALES_Detail（VEMno，Location，Gno，Brand，Price，Amount， SDate）（f）VEM.VEMno=SALES_Total. VEMno（g）GOODS.Gno= SALES_Total. Gnof 和 g 可以互换答案解析：（3 分）（h）TRIGGER（i）INSERT（j）SALES.Gno，SALES.SDate 答案解析：（3 分）（k）COUNT（*） AS 数量（l）GOODS.Gno，Brand（m）COUNT（*）=ALL 答案解析：（2 分）（n）NOT IN（o）SALES5.答案解析：（9 分）（1）第 2 人，并发操作出现了丢失更新的问题，第2 个的更新覆盖 了第 1 个的更新，原因是破坏了事物的隔离性。（2）会产生死锁，由于数据X同时被1和2加锁了 S锁，再对方没 有释放的时候，都无法加成功X锁，导致1和2 一直都处于等待的状 态3) XLock( X) a=R( X) W( b， X) UNLock( X)答案解析：（6 分）（a）BatchID=BatchNo（b）CLOSE NonRecvedRed（c）END TRANSACTION