2016年数据库系统工程师真题解析：数据流图、SQL与事务并发实战

对于备考数据库系统工程师的考生而言，历年真题是最宝贵的学习资料。通过对真题的深入剖析，不仅能熟悉考试题型与难度，更能系统性地巩固数据库设计的核心知识体系。本文将对2016年下午案例分析真题进行逐题精讲，涵盖数据流图补全、SQL编程、ER图设计、规范化理论及事务并发控制等核心考点，帮助大家查漏补缺，高效备考。

试题一：会议预订系统数据流图分析

题目描述：
某会议中心欲开发一会议预订系统，主要功能包括：检查可用性、临时预订、分配设施与设备、确认预订、变更预订、要求付款、支付余款。现采用结构化方法进行分析与设计，已获得上下文数据流图（图1-1）和0层数据流图（图1-2，不完整）。

【问题1】（2分）
使用说明中的词语，给出图1-1中的实体E1～E2的名称。

【答案与解析】

• E1：客户
• E2：会议中心管理员（或管理员）

解析： 根据功能描述，客户是提交预订请求、变更请求、支付费用的主体；会议中心管理员是处理预订确认、变更确认、要求付款的主体，分别对应上下文数据流图中的两个外部实体E1和E2。

【问题2】（4分）
使用说明中的词语，给出图1-2中的数据存储D1～D4的名称。

【答案与解析】

• D1：预订表
• D2：客户表
• D3：场地表（或设施表/场地设施表）
• D4：设备表（D3和D4顺序可互换）

解析： 根据功能描述交叉验证：多个功能涉及“预订表”的查询和更新，对应D1；“临时预订”功能需要创建客户信息记录，对应D2客户表；“分配设施与设备”功能需要操作场地和设备信息，对应D3和D4。

【问题3】（6分）
根据说明和图中术语，补充图1-2之中缺失的数据流及其起点和终点。

【答案与解析】

数据流	起点	终点
已支付余款凭据	E1（客户）	7（支付余款）
距预订会议时间两周内的预订	D1（预订表）	6（要求付款）
预订确认信息	4（确认预订）	E1（客户）
客户记录	D2（客户表）	6（要求付款）
客户记录	D2（客户表）	5（变更预订）
客户记录	D2（客户表）	4（确认预订）

解析： 需逐一核对说明中的功能描述与图示数据流的匹配关系：

• “支付余款”功能需要客户提交支付凭证。
• “要求付款”功能需要从预订表中查询符合条件的记录。
• “确认预订”功能需要向客户发送确认信息。
• “要求付款”、“变更预订”、“确认预订”都需要读取客户记录以获取联系方式。

【问题4】（3分）
如果发送给客户的确认信息改为通过Email系统向客户信息中的电子邮件地址发送，需要对图1-1和1-2进行哪些修改？

【答案与解析】
需要将“Email系统”作为一个新的外部实体添加到图1-1和图1-2中。同时，将所有原本终点为客户（E1）的确认类数据流（如临时预订确认信息、预订确认信息、变更确认信息）的终点修改为这个新增的“Email系统”实体。

解析： 原流程是系统直接面向客户发送信息。引入Email系统作为中介后，它成为了一个新的外部服务提供者，所有需要邮件通知的数据流都应指向它，由它完成最终的投递动作。

试题二：车辆调度管理系统SQL编程

题目描述：
某单位公用车辆后勤服务部门数据库包含驾驶员（EMP）、车辆（CAR）、调度（SCHEDULE）、奖金（BONUS）等关系模式。需完成建表、视图、触发器及复杂查询等SQL操作。

【问题1】（4分）
补充创建调度表（SCHEDULE）的SQL语句，要求指定主码、外码，以及发车时间在07:00至18:00的约束。

CREATE TABLE SCHEDULE (
Sno CHAR(10),
Eno CHAR(10)       （a）      ,
Cno CHAR(8)        （b）      ,
StartTime DATETIME       （c）      ,
EndTime DATETIME,
PRIMARY KEY        （d）      );

【答案与解析】

• （a）REFERENCES EMP(Eno)
• （b）REFERENCES CAR(Cno)
• （c）CHECK (Get_time(StartTime) BETWEEN '07:00:00' AND '18:00:00')
• （d）Sno

解析： (a)(b)用于定义外键，关联到驾驶员表和车辆表的主键。(c)使用CHECK约束和自定义函数Get_time确保业务规则。(d)调度号Sno是此表的主键。

【问题2】（6分）
（1）创建所有“奥迪”品牌汽车的调度信息视图AudiSCHEDULE。

CREATE         （e）       
    AS 
SELECT EMP.Eno, Ename, CAR.Cno, Brand, StartTime, EndTime
FROM EMP, CAR, SCHEDULE
WHERE         （f）       ;

（2）创建触发器，在调度记录更新（收车）时，自动计算并累加驾驶员奖金。

CREATE      （g）    Bonus_TRG AFTER     （h）   ON SCHEDULE
REFERENCING new row AS nrow
FOR EACH ROW
BEGIN
UPDATE BONUS
SET      （i）     
WHERE      （j）    AND Year = Get_Year(nrow.StartTime)
AND Month = Get_Month(nrow.StartTime);
END

【答案与解析】
（1）

• （e）VIEW AudiSCHEDULE(Eno, Ename, Cno, Brand, StartTime, EndTime)
• （f）EMP.Eno=SCHEDULE.Eno AND CAR.Cno=SCHEDULE.Cno AND Brand='奥迪'

（2）

• （g）TRIGGER
• （h）UPDATE
• （i）Amount = Amount + Bonus_value(nrow.StartTime, nrow.EndTime)
• （j）BONUS.Eno = nrow.Eno

解析： (e)创建视图需用CREATE VIEW并指定列名。(f)关联三表并过滤品牌。(g)(h)创建在UPDATE操作后的触发器。(i)调用函数计算奖金并累加。(j)通过工号关联到具体驾驶员的奖金记录。

【问题3】（5分）
（1）查询调度次数最多的汽车车牌号及其品牌。
（2）查询从未被调度过“大金龙”品牌车辆的驾驶员。

-- (1)
SELECT CAR.Cno, Brand
FROM CAR, SCHEDULE
WHERE CAR.Cno=SCHEDULE.Cno
GROUP BY      （k）    
HAVING     （l）   (SELECT COUNT(*)
FROM SCHEDULE
GROUP BY Cno );

-- (2)
SELECT Eno, Ename
FROM EMP
WHERE Eno      （m）   (
SELECT Eno
FROM      （n）  
WHERE      （o）   
AND Brand='大金龙' );

【答案与解析】

• （k）CAR.Cno, Brand
• （l）COUNT(*) >= ALL
• （m）NOT IN 或 <> ANY
• （n）SCHEDULE, CAR
• （o）SCHEDULE.Cno = CAR.Cno

解析： (k)按车辆分组。(l)使用>= ALL判断当前分组调度次数大于等于所有其他分组。(m)使用NOT IN排除子查询结果。(n)(o)子查询需关联两表以找到调度过“大金龙”的驾驶员。

试题三：销售公司数据库设计

题目描述：
某销售公司需开发一个整合实体店与网络销售的业务管理系统。需根据需求描述，完善ER图（图3-1）和逻辑结构设计。

逻辑结构设计（不完整）：

• 商品（条码，商品名称，生产厂家，销售价格，商品介绍，（a））
• 订单（订单ID，顾客编号，商品条码，商品数量，（b））
• 分拣（分拣ID，分拣员工号，（c），分拣时间）

【问题1】（4分）
补充图3-1中的“配送”联系所关联的对象及联系类型。

【答案与解析】

解析： “配送”联系关联配送员、分拣、收货地点三个实体。联系类型均为一对多（1:*），即一个配送员可执行多次配送，一次分拣产生一个配送任务，一个收货地点可接收多次配送。

【问题2】（6分）
补充逻辑结构设计中的（a）、（b）和（c）三处空缺。

【答案与解析】

• （a）所在仓库
• （b）支付凭证编号
• （c）订单ID

解析： (a)商品需记录其存储仓库。(b)订单支付后需记录唯一支付凭证。(c)分拣操作是针对特定订单进行的，需关联订单ID。

【问题3】（5分）
若实体店销售增加送货上门服务，营业员需在系统中下订单，并与网购订单统一管理。请补充ER图，并修改订单关系模式。

【答案与解析】

修改后的订单关系模式：
订单（订单ID，顾客编号，商品条码，商品数量，支付凭证编号，销售ID）

解析： 需在ER图中增加营业员与订单之间的“店购”联系。同时在订单表中增加外键“销售ID”，用于关联实体店销售记录，从而实现线上线下订单的统一管理。

试题四：停车位管理数据库规范化

题目描述：
小区停车位管理系统涉及业主、车位等关系模式，现有设计存在规范化问题，需进行分析与分解。

现有关系模式：

• 业主（业主姓名，业主身份证号，房号，房屋面积）
• 车位（车位编号，房号，车牌号，汽车品牌，汽车颜色，使用年份，费用）

【问题1】（6分）
对关系“业主”：
（1）给出候选键。
（2）判断是否为2NF，简述理由。
（3）将其分解为BCNF。

【答案与解析】
（1）候选键：（房号，业主身份证号）。
（2）不是2NF。理由：非主属性“房屋面积”仅由候选键中的“房号”部分决定，存在部分函数依赖。
（3）BCNF分解：

• A1（房号，业主身份证号）
• A2（房号，房屋面积）
• A3（业主身份证号，业主姓名）

【问题2】（6分）
对关系“车位”：
（1）给出候选键。
（2）判断是否为3NF，简述理由。
（3）将其分解为BCNF。

【答案与解析】
（1）候选键：（车位编号，使用年份）、（房号，使用年份）、（车牌号，使用年份）（三者任选其一）。
（2）不是3NF。理由：存在非主属性“汽车品牌”、“汽车颜色”对候选键的传递函数依赖。例如（车位编号，使用年份）→ 车牌号 → (汽车品牌，汽车颜色)。
（3）BCNF分解：

• B1（使用年份，费用）
• B2（车牌号，汽车品牌，汽车颜色）
• B3（车位编号，使用年份，房号，车牌号）

【问题3】（3分）
若增加临时停车收费功能，如何设计“临时停车”关系模式？

【答案与解析】
增加关系模式：临时停车（车牌号，进入时间，离开时间，费用）。
以（车牌号，进入时间）为主键。“离开时间”为空表示车辆尚未离场。“费用”可根据离开时间 - 进入时间的差值计算。

试题五：图书借还事务与SQL编程

题目描述：
图书馆借还书业务涉及书目和图书两个表，存在并发操作。需分析并发问题，并用存储过程实现出入库操作。

• 书目（ISBN，书名，出版社，在库数量）
• 图书（书号，ISBN，当前位置）

【问题1】（7分）
引入伪指令a = R(X)（读）和W(a, X)（写）。针对出库(O)和入库(I)事务的并发序列：
a_O = R_O(X), a_I = R_I(X), a_O = a_O - 1, W_O(a_O, X), a_I = a_I + 1, W_I(a_I, X)
（1）若X初始为3，执行后X值是多少？简述错误原因。
（2）引入独占锁XLock(X)和Unlock(X)，给出一种满足2PL协议的执行序列。

【答案与解析】
（1）最终X = 4。
错误原因：出库事务的修改（X:3→2）被后续入库事务的修改（X:3→4）覆盖，导致丢失更新，违反了事务的隔离性。
（2）满足2PL协议的一种执行序列：
XLock_O(X), XLock_I(X), a_O = R_O(X), a_O = a_O - 1, W_O(a_O, X), Unlock_O(X), a_I = R_I(X), a_I = a_I + 1, W_I(a_I, X), Unlock_I(X)
（注：此序列中入库事务在出库事务释放锁后才开始执行，实际符合2PL的序列不唯一，只要保证每个事务内加锁解锁操作不交叉即可）

【问题2】（8分）
补全用SQL实现的出入库业务存储过程空缺代码。

CREATE PROCEDURE IOstack (IN BookNo VARCHAR(20), IN Amount INT) {
// 输入合法性验证
if not (Amount = 1 or Amount = -1 ) return -1;
// 修改图书表当前位置
UPDATE 图书 SET 当前位置 = GetPos(BookNo, Amount) // 系统生成
WHERE         （a）      ;
if error then {ROLLBACK; return -2;}
// 修改在库数量
UPDATE 书目 SET 在库数量 =      （b）    
WHERE EXISTS (
SELECT *
FROM 图书
WHERE 书号 = BookNo AND
     （c）     ）;
if error then { ROLLBACK; return -3;}
     （d）    ;
return 0;
}

【答案与解析】

• （a）书号 = BookNo
• （b）在库数量 + Amount
• （c）图书.ISBN = 书目.ISBN
• （d）COMMIT

解析： (a)根据输入的书号定位图书记录。(b)Amount为1表示入库（+1），-1表示出库（-1），用累加实现通用。(c)通过ISBN关联两表，确保更新正确的书目。(d)所有操作成功则提交事务。

核心知识点盘点

通过以上五道真题的解析，我们可以系统回顾数据库系统工程师考试的核心考点：

1. 数据流图（DFD）设计：掌握外部实体、加工、数据存储、数据流的识别与绘制，能够检查并补充数据流的完整性。
2. SQL综合应用：熟练编写DDL（建表、约束）、DML（复杂查询、子查询）、DCL（视图、触发器），理解其在实际业务中的应用。
3. ER模型与数据库设计：能够根据需求描述绘制和补充ER图，并将其转化为规范的关系模式，处理扩展需求。
4. 规范化理论：深刻理解1NF至BCNF的定义，能准确判断范式级别、找出候选键，并能进行正确的模式分解以消除冗余和异常。
5. 事务与并发控制：理解典型的并发问题（如丢失修改），掌握两段锁协议（2PL）等并发控制机制，并能编写包含事务控制的存储过程。

希望这份详细的真题解析能帮助你巩固知识，明确备考方向。更多数据库、系统架构方面的深度讨论与技术资源，欢迎访问云栈社区与广大开发者交流学习。