2013年上半年《数据库系统工程师》案例分析真题

试卷预览

1 问答题 1分

某慈善机构欲开发一个募捐系统，以跟踪记录为事业或项目向目标群体进行募捐而组织的集体性活动。该系统的主要功能如下所述。

（1）管理志愿者。根据募捐任务给志愿者发送加入邀请、邀请跟进、工作任务；管理志愿者提供的邀请响应、志愿者信息、工作时长、工作结果等。

（2）确定募捐需求和收集所募捐赠（资金及物品）。根据需求提出募捐任务、活动请求和捐赠请求，获取所募集的资金和物品。

（3）组织募捐活动。根据活动请求，确定活动时间范围。根据活动时间，搜索场馆，即：向场馆发送场馆可用性请求，获得场馆可用性。然后根据活动时间和地点推广募捐活动，根据相应的活动信息举办活动，从募款机构获取资金并向其发放赠品。获取和处理捐赠，根据捐赠请求，提供所募集的捐赠；处理与捐赠人之间的交互，即：录入捐赠人信息，处理后存入捐赠人信息表；从捐赠人信息表中查询捐赠人信息，向捐赠人发送募捐请求，并将已联系的捐赠人存入已联系的捐赠人表。根据捐赠请求进行募集，募得捐赠后，将捐赠记录存入捐赠表；对捐赠记录进行处理后，存入已处理捐赠表，向捐赠人发送致谢函。根据已联系的捐赠人和捐赠记录进行跟进，将捐赠跟进情况发送给捐赠人。

现采用结构化方法对募捐系统进行分析与设计，获得如图1-1、1-2和1-3所示分层数据流图。

【问题1】（4分）

使用说明中的词语，给出图1-1中的实体E1~E4的名称。

【问题2】（7分）

在建模DFD时，需要对有些复杂加工（处理）进一步精化，图1-2为图1-1中处理3的进一步细化的1层数据流图，图1-3为图1-2中3.1进一步细化的2层数据流图。补全图1-2中加工P1、P2和P3的名称和图1-2与图1-3中缺少的数据流。

【问题3】（4分）

使用说明中的词语，给出图1-3中的数据存储D1~D4的名称。

查看答案 开始考试

正确答案：

本题解析：

【问题1】（4分）

E1：志愿者 E2：捐赠人 E3：募款机构 E4：场馆

【问题2】（7分）

p1:确定活动时间范围；

p2：搜索场馆；

p3：推广募捐活动；

注：数据流没有次序要求；表中2处可以为“确定募捐需求收集所募捐赠”

【问题3】（4分）

D1：捐赠人信息表 D2：已联系的捐赠人表

D3：捐赠表 D4：已处理捐赠表

2 问答题 1分

某航空公司要开发一个订票信息处理系统，该系统的部分关系模式如下：

航班（航班编号，航空公司，起飞地，起飞时间，目的地，到达时间，票价）

折扣（航班编号，开始日期，结束曰期，折扣）

旅客（身份证号，姓名，性别，出生日期，电话，VIP折扣）

购票（购票单号，身份证号，航班编号，搭乘日期，购票金额）

有关关系模式的属性及相关说明如下：

（1） 航班表中的起飞时间和到达时间不包含日期，同一航班不会在一天出现两次及 两次以上；

（2） 各航空公司会根据旅客出行淡旺季适时调整机票的折扣，旅客购买机票的购票金额计算公式为：票价X折扣X VIP折扣，其中旅客的VIP折扣与该旅客已购买过的机票的购票金额总和相关，在旅客每次购票后被修改。VIP折扣值的计算由函数float vip_value(char[18] 身份证号)完成。

【问题1】

请将如下创建购票关系的SQL语句的空缺部分补充完整， 要求指定关系的主键、外键，以及购票金额大于零的约束。

CREATE TABLE 购票（

购票单号 CHAR（　　）（a），

身份证号CHAR（　　），

航班编号CHAR （　　），

搭乘日期DATE，

购票金额FLOAT（b），

（c），

（d），

）；

【问题2】

（1）身份证号为210000196006189999的客户购买了 2013年2月18 日 CA5302航班的机票，购票单号由系统自动生成。下面的SQL语句将上述购票信息加入系统中，请将空缺部分补充完整。

INSERT INTO 购票（购票单号，身份证号，航班编号，搭乘日期，购票金额）

SELECT ‘201303105555’，‘210000196006189999’，‘CA5302’，‘2013/2/18’，（e）

FROM 航班，折扣，旅客

WHERE（f） AND 航班.航班编号=‘CA5302’ AND

AND ‘2013/2/18’ BETWEEN 折扣.开始日期 AND 折扣.结束日期

AND 旅客.身份证号=‘210000196006189999’；

（2）需要用触发器来实现VIP折扣的修改，调用函数vip_value（　　）来实现。请将如下SQL语句的空缺部分补充完整。

CREATE TRIGGER VIP_TRG AFTER（g）ON（h）

REFERENCING new row AS nrow

FOR EACH row

BEGIN

UPDATE 旅客

SET（i）

WHERE（j）；

END

【问题3】

请将如下SQL语句的空缺部分补充完整。

（1）查询搭乘日期在2012年1月1日至2012年12月31日之间，且合计购票金额大于等于10000元的所有旅客的身份证号、姓名和购票金额总和，并按购票金额总和降序输出。

SELECT 旅客.身份证号，姓名，SUM(购票金额）

FROM旅客，购票

WHERE（k）

GROUP BY（l）

ORDER BY（m）；

（2）经过中转的航班与相同始发地和目的地的直达航班相比，会享受更低的折扣。查询从广州到北京，经过一次中转的所有航班对，输出广州到中转地的航班编号、中转地和中转地到北京的航班编号。

SELECT （n）

FROM 航班 航班1，航班 航班2

WHERE（o）；

查看答案 开始考试

正确答案：

本题解析：

【问题1】

（a）PRIMARY KEY

（b）CHECK（购票金额>0）

（c）FOREIGN KEY （身份证号） REFERENCES 旅客（身份证号）

（d）FOREIGN KEY （航班编号） REFERENCES 航班（航班编号）

【问题2】

（1）（e）票价*折扣*VIP折扣

（f）航班.航班编号=折扣.航班编号

（2）（g）INSERT

（h）购票

（i）VIP折扣=vip_value（nrow.身份证号）

（j）旅客.身份证号=nrow.身份证号

【问题3】

（1）（k）旅客.身份证号=购票.身份证号 AND 搭乘日期 BETWEEN ‘2012/1/1’ AND ‘2012/12/31’

（l）旅客.身份证号，姓名 HAVING SUM（购票金额）>=10000

（m）SUM（购票金额） DESC

（2）（n）航班1.航班编号，航班1.目的地，航班2.航班号

（o）航班1.起飞地=‘广州’ AND 航班2.目的地=‘北京’ AND

航班1.目的地=航班2.起飞地

3 问答题 1分

某水果零售超市拟开发一套信息系统，对超市的顾客、水果、员工、采购和销售信息进行管理。

【需求分析】

（1）水果零售超市实行会员制，顾客需具有会员资格才能进行购物，顾客需持所在单位出具的证明信才能办理会员资格，每位顾客具有唯一编号；

（2）超市将采购员和导购员分成若干个小组，每组人员负责指定的若干种水果的采购和导购。每名采购员可采购指定给该组购买的水果；每名导购员都可对顾客选购的本组内的各种水果进行计价和包装，并分别贴上打印条码；

（3）顾客选购水果并计价完毕后进行结算，生成结算单。结算单包括流水号、购买的各种水果信息和顾客信息等，每张结算单具有唯一的流水号；

（4）超市在月底根据结算单对导购员进行绩效考核，根据采购情况对采购员进行考核，同时也根据结算单对顾客消费情况进行会员积分。

初步设计的数据库关系模式如图1-1所示。

关系模式的主要属性，含义及约束如表1-1所示。

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

“结算单”示例如表1-2所示。

表1-2 “结算单”示例

【问题1】（5分）

对关系模式“顾客”，请回答以下问题：

（1）给出所有候选键。

（2）该关系模式可达到第几范式，用60字以内文字简要叙述理由。

【问题2】（6分）

对关系模式“结算单”，请回答以下问题：

（1）用100字以内文字简要说明它会产生什么问题。

（2）将其分解为第三范式，分解后的关系名依次为：结算单1，结算单2，...。并用下划线标注分解后的各关系模式的主键。

【问题3】（4分）

对关系模式“职责”，请回答以下问题：

（1）它是否是第四范式，用100字以内文字叙述理由。

（2）将其分解为第四范式，分解后的关系名依次为：职责1，职责2，...

查看答案 开始考试

正确答案：

本题解析：

【问题1】（5分）

（1）顾客编号，身份证号（2分，各1分）

（2）可以达到第二范式（1分）

理由：“顾客”关系模式中，存在以下函数依赖：

单位名称→单位地址，单位电话

存在非主属性对键的传递依赖，所以“顾客”关系模式可以到到第二范式，但不满足第三范式（2分）

【问题2】（6分）

（1）根据“结算单”关系模式，可知其键为（流水号，条码），而又存在部分函数依赖：

条码→水果名称，销售单价，数量，金额，导购员编号

根据第二范式的要求：不存在非主属性对键的部分依赖。所以“结算单”关系模式不满足第二范式，会造成：插入异常、删除异常和修改异常。

（2）对“结算单”关系模式进行分解后的关系模式及主键如下：

结算单1（流水号，条码）

结算单2（流水号， 顾客编号）

结算单3（条码，水果名称，销售单价，数量，金额，导购员编号）

【问题3】（4分）

（1）不属于第四范式

根据“职责”关系模式可知：其键为（水果名称，采购员编号，导购员编号），而存在多值依赖：

水果名称→→采购员编号

水果名称→→导购员编号

根据第四范式的要求，不允许存在非平凡的多值依赖。因此，“职责”关系模式不满足第四范式。

（2）对“职责”关系模式进行分解后的关系模式如下：

职责1（水果名称，采购员编号）

职责2（水果名称，导购员编号）

4 问答题 1分

某连锁酒店提供网上预订房间业务，流程如下：

（1）客户查询指定日期内所有类别的空余房间数，系统显示空房表（日期，房间类别，数量）中的信息；

（2）客户输入预订的起始日期和结束日期、房间类别和数量，并提交；

（3）系统将用户提交的信息写入预订表（身份证号，起始日期，结束日期，房间类别，数量)，并修改空房表的相关数据。

针对上述业务流程，回答下列问題。

【问题1】（3分）

如果两个用户同时查询相同日期和房间类别的空房数量，得到的空房数量为1，并且这两个用户又同时要求预订，可能会产生什么结果，请用100字以内文字简要叙述。

【问题2】（8分）

引入如下伪指令：将预订过程作为一个事务，将查询和修改空房表的操作分别记为R（A）和W（A，x），插入预订表的操作记为W（B，a），其中x代表空余房间数，a代表预订房间数。则事务的伪指令序列为：x = R（A），W（A，x-a），W（B，a）。

在并发操作的情况下，若客户1、客户2同时预订相同类别的房间时，可能出现的执行序列为：x1=R（A）， x2 = R（A）， W（A， x1-a1）， W（B1，a1）， W（A， x2-a2）， W（B2，a2）。

（1）此时会出现什么问题，请用100字以内文字简要叙述。

（2）为了解决上述问题，引入共享锁指令SLock(X)和独占锁指令XLock(X)对数据 X进行加锁，解锁指令Unlock(X)对数据X进行解锁，请补充上述执行序列，使其满足 2PL协议，使其不产生死锁且持有锁的时间最短。

【问题3】（4分）

下面是实现预订业务的程序，请补全空玦处的代码。其中主变量:Cid，:Bdate， :Edate，

:Rtype， :Num 分别代表身份证号，起始日期，结束日期，房间类别和订房数量。

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

UPDATE 空房表

SET数量=数量 - :Num

WHERE__（a）__;

if error then { ROLLBACK； return -1;}

INSERT INTO 预订表 VALUES （:Cid， :Bdate，:Edate，:Rtype，:Num);

if error then { ROLLBACK; return -2 ; }

__（b）__； .

查看答案 开始考试

正确答案：

本题解析：

【问题1】（3分）

同时预订时，可能会产生一个客户订不到或者把同一房订给两个客户

【问题2】（8分）

出现问题：丢失修改，客户1预订a1数量房间后，对空房数量的修改被T2的修改覆盖，造成数据不一致。（4分）

XLOCK（A），x1=R（A），W（A，x1-a1），XLOCK（B），UNLOCK（A），W(B1，a1），UNLOCK（B）， XLOCK（A）， x2=R（A），W（A，x2-a2），XLOCK（B），UNLOCK（A），W（B2，a2），UNLOCK（B）（4分）

【问题3】（4分）

（a）房间类别=：Rtype AND 日期 BETWEEN ：Bdate AND ：Edate（2分）

（b）COMMIT；Return 0；（2分）

5 问答题 1分

某电视台拟开发一套信息管理系统，以方便对全台的员工、栏目、广告和演播厅等进行管理。

【需求分析】

（1）系统需要维护全台员工的详细信息、栏目信息、广告信息和演播厅信息等。员工的信息主要包括工号、姓名、性别、出生日期、电话和住址等，栏目信息主要包括栏目名称、播出时间和时长等，广告信息主要包括广告编号、价格等，演播厅信息包括房间号、房间面积等；

（2）电视台根据调度单来协调各档栏目、演播厅和场务。一销售档栏目只会占用一个演播厅，但会使用多名场务来进行演出协调。演播厅和场务可以被多个栏目循环使用;

（3）电视台根据栏目来插播广告。每档栏目可以插播多条广告，每条广告也可以在多档栏目插播；

（4）一档栏目可以有多个主持人，但一名主持人只能主持一档栏目；

（5）一名编辑人员可以编辑多条广告，一条广告只能由一名编辑人员编辑。

【概念模型设计】

根据需求阶段收集的信息设计的实体联系图（不完整）如图1-1所示。

图1-1

【逻辑结构设计】

根据概念模型设计阶段完成的实体联系图，得出如下关系模式（不完整）：

演播厅（房间号，房间面积）

栏目（栏目名称，播出时间，时长）

广告（广告编号，销售价格，（1））

员工（工号，姓名，性别，出生日期，电话，住址）

主持人（主持人工号，（2））

插播单（（3），播出时间）

调度单（（4））

【问题1】（7分）

补充图1-1中的联系和联系的类型。

【问题2】（5分）

根据图1-1，将逻辑结构设计阶段生成的关系模式中的空（1）~（4）补充完整，并用下划线指出（1）~（4）所在关系模式的主键。

【问题3】（3分）

现需要记录广告商信息，增加广告商实体。一个广告商可以提供多条广告，一条广告只由一个广告商提供。请根据该要求，对图1-1进行修改，画出修改后的实体间联系和联系的类型。

查看答案 开始考试

正确答案：

本题解析：

【问题1】（7分）

【问题2】（5分）

广告（广告编号，销售价格，编辑人员工号）（属性0.5分，主键0.5分）

主持人（主持人工号，栏目名称）（属性0.5分，主键0.5分）

插播单（栏目名称，广告编号，播出时间）（属性1分，主键0.5分）

调度单（栏目名称，房间号，场务工号）（属性1分，主键0.5分）

【问题3】（3分）