数据库系统工程师-2014年案例分析真题解析【下篇】
四季读书网
5
数据库系统工程师-2014年案例分析真题解析【下篇】
阅读下列说明和图，回答问题 1 至问题 4, 将解答填入答题纸的对应栏内。某巴士维修连锁公司欲开发巴士维修系统，以维护与维修相关的信息。该系统的主要功能如下：(1) 记录巴士 ID 和维修问题。巴士到车库进行维修，系统将巴士基本信息和 ID 记录在巴士列表文件中，将待维修机械问题记录在维修记录文件中，并生成维修订单。(2) 确定所需部件。根据维修订单确定维修所需部件，并在部件清单中进行标记。(3) 完成维修。机械师根据维修记录文件中的待维修机械问题，完成对巴士的维修，登记维修情况；将机械问题维修情况记录在维修记录文件中，将所用部件记录在部件清单中，并将所用部件清单发送给库存管理系统以对部件使用情况进行监控。巴士司机可查看已维修机械问题。(4) 记录维修工时。将机械师提供的维修工时记录在人事档案中，将维修总结发送给主管进行绩效考核。(5) 计算维修总成本。计算部件清单中实际所用部件、人事档案中所用维修工时的总成本；将维修工时和所用部件成本详细信息给会计进行计费。现采用结构化方法对巴士维修系统进行分析与设计，获得如图 1-1 所示的上下文数据流图和图 1-2 所示的 0 层数据流图。使用说明中的词语，给出图 1-1 中的实体 E1~E5 的名称。使用说明中的词语，给出图 1-2 中的数据存储 D1~D4 的名称。根据说明和图中术语，釆用补充数据流的方式，改正图 1-2 中的问题。要求给出所补充数据流的名称、起点和终点。E1：巴士司机 E2：机械师 E3：会计 E4：主管 E5：库存管理系统D1：巴士列表文件 D2：维修记录文件 D3：部件清单 D4：人事档案存储待维修和已维修问题的是维修记录文件，对应 D2；处理 3（完成维修）只有输出数据流，没有输入数据流；数据存储 D2（维修记录文件）和 D3（部件清单）只有输入数据流而没有输出数据流；父图与子图不平衡，图 1-2 中缺少图 1-1 中的数据流 “维修情况”。处理过程必须有输入和输出数据流，处理 3 缺少输入；数据存储必须有进有出，D2 和 D3 只有输入没有输出；父图的所有数据流必须在子图中体现，子图缺少父图的 “维修情况” 数据流。给处理 3 补充输入数据流 “待维修机械问题”，来自 D2；给处理 5 补充输入数据流 “实际所用部件”，来自 D3；补充父图中的 “维修情况” 数据流，从 E2 到处理 3。【第 2 题】（题型：数据库设计 - SQL 语句填空）阅读下列说明，回答问题 1 至问题 3，将解答填入答题纸的对应栏内。某健身俱乐部要开发一个信息管理系统，该信息系统的部分关系模式如下：预约单（会员手机号，预约日期，项目名称，使用时长）(外键：会员手机号)消费（流水号，会员手机号，项目名称，消费金额，消费日期）（外键：会员手机号，项目名称）（1）俱乐部有多种健身项目，不同的项目每小时的价格不同。俱乐部实行会员制，且需要电话或在线提前预约。（2）每个项目都有一个项目经理，一个经理只能负责一个项目。（3）俱乐部对会员进行积分，达到一定积分可以进行升级，不同的等级具有不同的折扣。请将下面创建消费关系的 SQL 语句的空缺部分补充完整，要求指定关系的主码、外码，以及消费金额大于零的约束。（1）手机号为 xxxxxxxxxxx 的客户预约了 2014 年 3 月 18 日两个小时的羽毛球场地，消费流水号由系统自动生成。请将下面 SQL 语句的空缺部分补充完整。INSERT INTO 消费（流水号，会员手机号，项目名称，消费金额，消费日期）SELECT ‘201403180001’，‘xxxxxxxxxxx’，‘羽毛球’, （e） ,WHERE 预约单.项目名称 = 项目.项目名称 AND （f） AND 会员.会员手机号 =‘xxxxxxxxxxx’；（2）需要用触发器来实现会员等级折扣的自动维护，函数 float vip_value (char (11) 会员手机号）依据输入的手机号计算会员的折扣。请将下面 SQL 语句的空缺部分补充完整。CREATE TRIGGER VIP_TRG AFTER （g） 0N （h） REFERENCINGnew row AS nrow（1）俱乐部年底对各种项目进行绩效考核，需要统计出所负责项目的消费总金额大于等于十万元的项目和项目经理，并按消费金额总和降序输出。SELECT 项目。项目名称，项目经理，SUM（消费金额）HAVING SUM (消费金额)>=100000（2）查询所有手机号码以 "888” 结尾，姓 “王” 的员工姓名和电话。a) PRIMARY KEY 或 NOT NULL UNIQUEc) FOREIGN KEY（会员手机号）REFERENCES 会员（会员手机号）d) FOREIGN KEY（项目名称）REFERENCES 项目（项目名称）（b）要求消费金额大于零，需添加 CHECK 约束；（c）（d）会员手机号和项目名称是外键，需分别引用会员表和项目表的主键。i) 折扣 = vip_value (nrow. 会员手机号)j) 会员.会员手机号 = nrow. 会员手机号（e）消费金额 = 项目价格 × 使用时长 × 会员折扣，需从三个表中获取对应字段计算；（g）（h）当消费表插入新记录后触发触发器，所以是 AFTER INSERT ON 消费；（i）（j）触发器需要根据新插入的消费记录中的会员手机号，调用函数更新会员表的折扣字段。（l）GROUP BY 子句需要包含 SELECT 中所有非聚合字段；（m）按消费金额总和降序排序，使用 SUM (消费金额) DESC；（n）姓 “王” 使用 LIKE ' 王 %' 匹配以王开头的姓名；（o）手机号以 888 结尾使用 LIKE '%888' 匹配。【第 3 题】（题型：数据库设计 - ER 图与关系模式）阅读下列说明和图，回答问题 1 至问题 3, 将解答填入答题纸的对应栏内。某家电销售电子商务公司拟开发一套信息管理系统，以方便对公司的员工、家电销售、家电厂商和客户等进行管理。（1）系统需要维护电子商务公司的员工信息、客户信息、家电信息和家电厂商信息等。员工信息主要包括：工号、姓名、性别、岗位、身份证号、电话、住址，其中岗位包括部门经理和客服等。客户信息主要包括：客户 ID、姓名、身份证号、电话，住址、账户余额。家电信息主要包括：家电条码、家电名称、价格、出厂日期、所属厂商。家电厂商信息包括：厂商 ID、厂商名称、电话、法人代表信息、厂址。（2）电子商务公司根据销售情况，由部门经理向家电厂商订购各类家电。每个家电厂商只能由一名部门经理负责。（3）客户通过浏览电子商务公司网站查询家电信息，与客服沟通获得优惠后，在线购买。根据需求阶段收集的信息，设计的实体联系图（不完整）如图所示。根据概念模型设计阶段完成的实体联系图，得出如下关系模式〔不完整）客户（客户 ID、姓名、身份证号、电话、住址、账户余额）员工（工号、姓名、性别、岗位、身份证号、电话、住址）家电（家电条码、家电名称、价格、出厂日期、（1） ）家电厂商（厂商 ID、厂商名称、电话、法人代表信息、厂址、 （2） ）根据图，将逻辑结构设计阶段生成的关系模式中的空（1）~（3）补充完整。 用下划线指出 “家电”、“家电厂商” 和 “购买” 关系模式的主键。电子商务公司的主营业务是销售各类家电，对账户有佘额的客户，还可以联合第二方基金公司提供理财服务，为此设立客户经理岗位。客户通过电子商务公司的客户经理和基金公司的基金经理进行理财。每名客户只有一名客户经理和一名基金经理负责，客户经理和基金经理均可负责多名客户。请根据该要求，对图进行修改，画出修改后的实体间联系和联系的类型。家电（家电条码、家电名称、价格、出厂日期、厂商 ID）家电厂商（厂商 ID、厂商名称、电话、法人代表信息、厂址、部门经理工号）家电厂商由部门经理负责，需添加部门经理工号作为外键；购买联系是 n:m 关系，需包含客户 ID 和家电条码作为外键。客户经理（员工的一种岗位）与客户是 1:n 的理财服务关系；【第 4 题】（题型：数据库设计 - 关系模式规范化）阅读下列说明和图，回答问题 1 至问题 3, 将解答填入答题纸的对应栏内。(1) 对所有图书进行编目，每一书目包括 ISBN 号、书名、出版社、作者、排名，其中一部书可以有多名作者，每名作者有唯一的一个排名；(2) 对每本图书进行编号，包括书号、ISBN 号、书名、出版社、破损情况、存放位置和定价，其中每一本书有唯一的编号，相同 ISBN 号的书集中存放，有相同的存储位置，相同 ISBN 号的书或因不同印刷批次而定价不同；(3) 读者向图书馆申请借阅资格，办理借书证，以后凭借书证从图书馆借阅图书。办理借书证时需登记身份证号、姓名、性别、出生年月日，并交纳指定金额的押金。如果所借图书定价较高时，读者还须补交押金，还书后可退还所补交的押金；(4) 读者借阅图书前，可以通过 ISBN 号、书名或作者等单一条件或多条件组合进行查询。根据查询结果，当有图书在库时，读者可直接借阅；当所查书目的所有图书己被他人借走时，读者可进行预约，待他人还书后，由馆员进行电话通知；(5) 读者借书时，由系统生成本次借书的唯一流水号，并登记借书证号、书号、借书日期，其中同时借多本书使用同一流水号，每种书目都有一个允许一次借阅的借书时长，一般为 90 天，不同书目有不同的借书时长，并且可以进行调整，但调整前所借出的书，仍按原借书时长进行处理；(6) 读者还书时，要登记还书日期，如果超出借书时长，要缴纳相应的罚款；如果所还图书由借书者在持有期间造成破损，也要进行登记并进行相应的罚款处罚。初步设计的该图书馆管理系统，其关系模式如图 4-1 所示。(2) 根据需求描述，借还关系能否实现对超出借书时长的情况进行正确判定？用 60 字以内文字简要叙述理由。如果不能，请给出修改后的关系模式（只修改相关关系模式属性时，仍使用原关系名，如需分解关系模式，请在原关系名后加 1，2，… 等进行区别)。(2) 判定该关系是否属于 BCNF，用 60 字以内文字简要叙述理由。如果不是，请进行修改，使其满足 BCNF, 如果需要修改其它关系模式，请一并修改，给出修改后的关系模式（只修改相关关系模式属性时，仍使用原关系名，如需分解关系模式，请在原关系名后加 1，2，... 进行区别）。(1) 它是否属于第四范式，用 60 字以内文字叙述理由。(2) 如果不是，将其分解为第四范式，分解后的关系名依次为：书目 1，书目 2，…。 如果在解决【问题 1】、【问题 2】时，对该关系的属性进行了修改，请沿用修改后的属性。（2）不能。理由：还书时需读取书目中的借书时长，但借书时长可能在借书后调整，无法按原时长判定。借还（流水号，借书证号，书号，借书日期，借书时长，还书日期，罚款金额，罚款原因）候选键：流水号标识一次借书行为，书号标识具体图书，两者组合唯一确定一条借还记录；原关系模式中没有存储借书时的借书时长，若书目中的借书时长被修改，会导致还书时无法按原时长判定是否超时，因此需要在借还表中增加借书时长字段，借书时将当时的借书时长存入。（1）函数依赖集 F={书号→（ISBN 号，破损情况，定价），ISBN 号→（书名，出版社，存放位置）}（2）不属于 BCNF。理由：存在非主属性（书名、出版社、存放位置）对码（书号）的传递函数依赖。图书（书号，ISBN 号(外键，这里要标注下划虚线)，破损情况，定价）书目（ISBN 号，书名，出版社，作者，排名，存放位置，借书时长）函数依赖：书号唯一确定一本图书的 ISBN 号、破损情况和定价；ISBN 号唯一确定书目的基本信息；BCNF 要求每个决定因素都是候选键，原关系中 ISBN 号是决定因素但不是候选键，存在传递依赖，因此不属于 BCNF，需要将书目信息和图书信息分离为两个表。（1）不属于第四范式。理由：存在嵌入的多值依赖 ISBN 号→→（作者，排名），且 ISBN 号不是主键。书目 1（ISBN 号，书名，出版社，存放位置，借书时长）第四范式要求不存在非平凡的多值依赖，原书目关系中，一个 ISBN 号对应多个作者和排名，存在多值依赖，且 ISBN 号是主键，但多值依赖的属性（作者、排名）与主键不独立，因此需要分解为两个表，分别存储书目基本信息和作者信息。【第 5 题】（题型：数据库设计 - 事务与并发控制）阅读下列说明，回答问题 1 至问题 3, 将解答填入答题纸的对应栏内。（1）车辆驶入高速路入口站点时，将驶入信息（ETC 卡号，入口编号，驶入时间） 写入登记表；（2）车辆驶出高速路出口站点（收费口）时，将驶出信息（ETC 卡号，出口编号， 驶出时间）写入登记表；根据入口编号、出口编号及相关收费标准，清算应缴费用，并从绑定的信用卡中扣除费用。一张 ETC 卡号只能绑定一张信用卡号，针对企业用户，一张信用卡号可以绑定多个 ETC 卡号。使用表绑定（ETC 卡号，信用卡号）来描述绑定关系，从信用卡（信用卡号，余额）表中扣除费用。在不修改登记表的表结构和保留该表历史信息的前提下，当车辆驶入时，如何保证当前 ETC 卡已经清算过，而在驶出时又如何保证该卡已驶入而未驶出？请用 100 字以内文字简述处理方案。当车辆驶出收费口时，从绑定信用卡余额中扣除费用的伪指令如下：读取信用卡余额到变量 X，记为 x = R (A)；扣除费用指令 x = x - a；写信用卡余额指令记为 W (A, x)。（1）当两个绑定到同一信用卡号的车辆同时经过收费口时，可能的指令执行序列为：x1=R (A)，x1 =x1-a1，x2 = R (A)，x2 = x2-a2，W (A，x1)，W (A，x2)。此时会出现什么问题？（100 字以内）（2）为了解决上述问题，引入独占锁指令 XLock (A) 对数据 A 进行加锁，解锁指令 Unlock (A) 对数据 A 进行解锁。请补充上述执行序列，使其满足 2PL 协议。下面是用 E-SQL 实现的费用扣除业务程序的一部分，请补全空缺处的代码。CREATE PROCEDURE 操作（IN ETC 卡号 VARCHAR (20), IN 费用 FLOAT)UPDATE 信用卡 SET 余额 = 余额 - 费用WHERE 信用卡.信用卡号 = 绑定.信用卡号 AND （a） ；驶入时，检查表中该 ETC 卡号的所有记录，确保不存在未驶出（无出口编号）的记录；驶出时，检查表中该 ETC 卡号的记录，确保存在已驶入且未驶出的记录，处理后标记为已驶出。驶出时确保存在已驶入但未驶出的记录，处理后完成该行程的记录。（1）会出现丢失更新问题。第二个事务读取了第一个事务未提交的余额，更新后覆盖了第一个事务的更新结果，导致实际扣除金额小于应扣除金额。XLock (A)，x1=R (A)，x1 =x1-a1，W (A，x1)，Unlock (A)，XLock (A)，x2 = R (A)，x2 = x2-a2，W (A，x2)，Unlock (A)丢失更新：两个事务同时读取同一数据，分别修改后提交，后提交的事务覆盖了先提交的事务的修改；2PL 协议（两段锁协议）：事务分为加锁阶段和解锁阶段，加锁阶段只能加锁不能解锁，解锁阶段只能解锁不能加锁，通过独占锁保证同一时间只有一个事务能修改数据。（a）需要通过绑定表关联 ETC 卡号和信用卡号，因此条件为绑定.ETC 卡号 = 输入的 ETC 卡号；知识点盘点：
【试题一知识点】
・数据流图（DFD）的基本元素：外部实体、处理过程、数据存储、数据流・数据流图的平衡规则：父图与子图的数据流必须一致，处理过程的输入输出必须完备・数据流图的绘制规范：数据存储必须有输入和输出数据流，处理过程不能只有输入或只有输出【试题二知识点】
・SQL 表创建语句：主键、外键、CHECK 约束的定义・SQL 数据插入：使用 SELECT 查询结果作为插入数据源・SQL 触发器：触发时机、触发事件、行级触发器的语法・SQL 聚合查询：GROUP BY、HAVING 的使用，排序规则【试题三知识点】
・ER 图的设计：实体、属性、联系的识别，联系类型（1:1、1:n、n:m）的确定・ER 图到关系模式的转换规则：实体转换为表，联系转换为外键或独立表・关系模式的主键与外键设计：主键的唯一性，外键的引用关系【试题四知识点】
・候选键的识别：能唯一确定关系中每条记录的属性或属性组・关系模式的规范化：BCNF、4NF 的定义和判定规则・函数依赖与多值依赖：函数依赖的传递性，多值依赖的定义・关系模式的分解：消除传递依赖和多值依赖的分解方法・并发控制问题：丢失更新、脏读、不可重复读、幻读的定义和场景・两段锁协议（2PL）：加锁阶段和解锁阶段的规则，保证事务的可串行性・事务处理：COMMIT 和 ROLLBACK 的使用，保证数据的一致性・存储过程：SQL 存储过程的语法，参数传递和错误处理・业务规则的数据库实现：通过数据检查保证业务逻辑的正确性THE END -
点击下方卡片关注我 点个小赞你必上岸↓↓↓
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至23467321@qq.com举报，一经查实，本站将立刻删除;如已特别标注为本站原创文章的，转载时请以链接形式注明文章出处，谢谢！
