数据库系统工程师-2016年案例分析真题解析【下篇】

四季读书网 2026-04-16 10:12:25 1 0

【第 1 题】（题型：简答题）

题目：

阅读下列说明和图，回答问题 1 至问题 4，将解答填入答题纸的对应栏内。

【说明】

某会议中心提供举办会议的场地设施和各种设备，供公司与各类组织机构租用。场地包括一个大型报告厅、一个小型报告厅以及诸多会议室。这些报告厅和会议室可提供的设备有投影仪、白板、视频播放 / 回放设备、计算机等。为了加强管理，该中心欲开发一会议预订系统，系统的主要功能如下。

（1）检查可用性。客户提交预订请求后，检查预订表，判定所申请的场地是否在申请日期内可用；如果不可用，返回不可用信息。

（2）临时预订。会议中心管理员收到客户预定请求的通知之后，提交确认。系统生成新临时预订存入预订表，并对新客户创建一条客户信息记录加以保存。根据客户记录给客户发送临时预订确认信息和支付定金要求。

（3）分配设施与设备。根据临时预订或变更预定的设备和设施需求，分配所需设备（均能满足用户要求）和设施，更新相应的表和预订表。

（4）确认预订。管理员收到客户支付定金的通知后，检查确认，更新预订表，根据客户记录给客户发送预订确认信息。

（5）变更预订。客户还可以在支付余款前提交变更预订请求，对变更的预订请求检查可用性，如果可用，分配设施和设各；如果不可用，返回不可用信息。管理员确认变更后，根据客户记录给客户发送确认信息。

（6）要求付款。管理员从预订表中查询距预订的会议时间两周内的预定，根据客户记录给满足条件的客户发送支付余款要求。

（7）支付余款。管理员收到客户余款支付的通知后，检查确认，更新预订表中的已支付余款信息。

现采用结构化方法对会议预定系统进行分析与设计，获得如图 1-1 所示的上下文数据流图和图 1-2 所示的 0 层数据流图（不完整）。

【问题 1】（2 分）

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

【问题 2】（4 分）

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

【问题 3】（6 分）

根据说明和图中术语，补充图 1-2 之中缺失的数据流及其起点和终点。

【问题 4】（3 分）

如果发送给客户的确认信息是通过 Email 系统向客户信息中的电子邮件地址进行发送的，那么需要对图 1-1 和 1-2 进行哪些修改？用 150 字以内文字加以说明。

【答案】

【问题 1】

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

【解析】

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

【问题 2】

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

【解析】

根据功能描述：

多个功能都涉及 "预订表" 的查询和更新，对应 D1；

临时预订功能需要创建客户信息记录，对应 D2 客户表；

分配设施与设备功能需要操作场地和设备的存储，对应 D3 场地表和 D4 设备表。

【问题 3】

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

【解析】

逐一核对说明中的功能与 0 层数据流图的匹配：

支付余款功能需要客户提交已支付余款凭据，数据流从客户到支付余款处理；

要求付款功能需要从预订表查询符合条件的预订，数据流从预订表到要求付款处理；

确认预订功能需要向客户发送确认信息，数据流从确认预订处理到客户；

要求付款、变更预订、确认预订功能都需要读取客户记录来发送通知，数据流从客户表到对应处理过程。

【问题 4】

将 Email 系统作为外部实体添加到图 1-1 和图 1-2 中，将所有原本指向客户（E1）的确认类数据流（临时预订确认信息、预订确认信息、变更确认信息）的终点修改为 Email 系统。

【解析】

原数据流图中直接将确认信息发送给客户，当通过 Email 系统中转时，Email 系统成为新的外部实体，所有客户确认类数据流的终点需要调整为 Email 系统，由 Email 系统完成最终的发送动作。

【第 2 题】（题型：SQL 填空题）

题目：

阅读下列说明，回答问题 1 至问题 3，将解答填入答题纸的对应栏内。

【说明】

某单位公用车辆后勤服务部门数据库的部分关系模式如下：

驾驶员：EMP（Eno, Ename, Age, Sex, telephone），各属性分别表示驾驶员工号、姓名、年龄、性别和电话号码；

车辆：CAR（Cno,Brand,Capacity），各属性分别表示汽车车牌号、品牌名和排量；

调度：SCHEDULE（Sno, Eno, Cno, StartTime, Endtime），各属性分别表示调度号、驾驶员工号、汽车车牌号、发车时间和收车时间；

奖金：BONUS（Eno, Year, Month, Amount），各属性分别表示驾驶员工号、年、月和当月的奖金数量。

有关车辆调度的相关说明如下：

公车的行驶时间只能在工作时间内，因此规定调度表中每天安排发车的时间在上午 07:00:00 至下午 18:00:00 范围内。

【问题 1】（4 分）

请将下面创建调度关系的 SQL 语句的空缺部分补充完整，要求指定关系的主码、外码，以及调度表中每天安排发车的时间在上午 07:00:00 至下午 18:00:00 范围内的约束（由函数 Time Get_time (DATETIME StartTime) 返回出车的时间）。

CREATE TABLE SCHEDULE （

Sno CHAR (10),

Eno CHAR (10) （a） ,

Cno CHAR (8) （b） ,

StartTime DATETIME （c） ,

EndTime DATETIME,

PRIMARY KEY （d））;

【问题 2】（6 分）

（1）创建所有 “奥迪” 品牌汽车的调度信息的视图 AudiSCHEDULE，属性有 Eno、Ename、Cno、Brand、StartTime 和 EndTime，请将下面 SQL 语句的空缺部分补充完整。

CREATE （e）

SELECT EMP.Eno, Ename, CAR.Cno, Brand, StartTime, EndTime

FROM EMP, CAR, SCHEDULE

WHERE （f） ;

（2）驾驶员的奖金在收车时间写入时，由出车时间段自动计算，并用触发器来实现奖金的自动维护，函数 float Bonus_value (DATETIME StartTime, DATETIME EndTime) 依据发车时间和收车时间来计算本次出车的奖金。系统在每月初自动增加一条该员工的当月奖金记录，初始金额为零。请将下面 SQL 语句的空缺部分补充完整。

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

【问题 3】（5 分）

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

（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=' 大金龙 ' ) ;

【答案】

【问题 1】

（a）REFERENCES EMP (Eno)

（b）REFERENCES CAR (Cno)

（c）CHECK (Get_time (StartTime) BETWEEN '07:00:00' AND '18:00:00')

（d）Sno

【解析】

（a）Eno 是驾驶员表 EMP 的主键，作为调度表的外键，需要用 REFERENCES 指定关联关系；

（b）Cno 是车辆表 CAR 的主键，作为调度表的外键，同样需要 REFERENCES 指定；

（c）根据要求，需要用 CHECK 约束限定发车时间在工作时段内，通过 Get_time 函数提取时间部分进行范围判断；

（d）Sno 是调度表的主键，唯一标识每条调度记录。

【问题 2】

（e）VIEW AudiSCHEDULE

(Eno,Ename,Cno,Brand,StartTime,EndTime)

（f）EMP.Eno=SCHEDULE.Eno AND CAR.Cno=SCHEDULE.Cno AND Brand=' 奥迪 '

（g）TRIGGER

（h）UPDATE

（i）Amount=Amount+Bonus_value

(nrow.StartTime, nrow.EndTime)

（j）BONUS.Eno=nrow.Eno

【解析】

（1）创建视图需要用 CREATE VIEW 语句，同时指定视图的属性列表；WHERE 子句需要关联三个表，并筛选出品牌为 "奥迪" 的记录。

（2）创建触发器需要用 CREATE TRIGGER 语句；奖金在收车时间写入时更新，对应调度表的 UPDATE 操作；触发器中需要将本次出车的奖金累加到当月奖金记录中，通过 nrow 获取新更新的调度记录信息，关联到对应的奖金记录。

【问题 3】

（k）CAR.Cno, Brand

（l）COUNT (*)>=ALL

（m）NOT IN 或 <>ANY（两者填其一即可）

（n）SCHEDULE, CAR

（o）SCHEDULE.Cno=CAR.Cno

【解析】

（1）查询调度次数最多的车辆，需要按车牌号和品牌分组，HAVING 子句中用 COUNT (*)>=ALL 判断当前分组的调度次数大于等于所有车辆的调度次数；

（2）查询未安排过特定品牌车辆的驾驶员，需要用 NOT IN 或 <>ANY 排除已安排过的驾驶员，子查询中需要关联调度表和车辆表，筛选出 "大金龙" 品牌的调度记录对应的驾驶员。

【第 3 题】（题型：数据库设计题）

题目：

阅读下列说明，回答问题 1 至问题 3；将解答填入答题纸的对应栏内。

【说明】

某销售公司当前的销售业务为商城实体店销售。现该公司拟开展网络销售业务，需要开发一个信息化管理系统。请根据公司现有业务及需求完成该系统的数据库设计。

【需求描述】

（1）记录公司所有员工的信息。员工信息包括工号、身份证号、姓名、性别、出生日期和电话，并只登记一部电话。

（2）记录所有商品的信息。商品信息包括商品名称、生产厂家、销售价格和商品介绍。系统内部用商品条码唯一区别每种商品。

（3）记录所有顾客的信息。顾客信息包括顾客姓名、身份证号、登录名、登录密码、和电话号码。一位顾客只能提供一个电话号码。系统自动生成唯一的顾客编号。

（4）顾客登录系统之后，在网上商城购买商品。顾客可将选购的商品置入虚拟的购物车内，购物车可长期存放顾客选购的所有商品。顾客可在购物车内选择商品、修改商品数量后生成网购订单。订单生成后，由顾客选择系统提供的备选第三方支付平台进行电子支付，支付成功后系统需要记录唯一的支付凭证编号，然后由商城根据订单进行线下配送。

（5）所有的配送商品均由仓库统一出库。为方便顾客，允许每位顾客在系统中提供多组收货地址、收货人及联系电话。一份订单所含的多个商品可能由多名分检员根据商品所在仓库信息从仓库中进行分拣操作，分拣后的商品交由配送员根据配送单上的收货地址进行配送。

（6）新设计的系统要求记录实体店的每笔销售信息，包括营业员、顾客、所售商品及其数量。

【概念模型设计】

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

【逻辑结构设计】

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

员工（工号，身份证号，姓名，性别，出生日期，电话）

商品（条码，商品名称，生产厂家，销售价格，商品介绍，（a））

顾客（编号，姓名，身份证号，登录名，登录密码，电话）

收货地点（收货 ID，顾客编号(下划虚线)，收货地址，收货人，联系电话）

购物车（顾客编号，商品条码，商品数量）

订单（订单 ID，顾客编号(下划虚线)，商品条码(下划虚线)，商品数量，（b））

分拣（分拣 ID，分拣员工号(下划虚线)，（c），分拣时间）

配送（配送 ID，分拣 ID(下划虚线)，配送员工号(下划虚线)，收货 ID(下划虚线)，配送时间，签收时间，签收快照）

销售（销售 ID，营业员工号(下划虚线)，顾客编号(下划虚线)，商品条码(下划虚线)，商品数量）

【问题 1】（4 分）

补充图 2-1 中的 “配送” 联系所关联的对象及联系类型。

【问题 2】（6 分）

补充逻辑结构设计中的（a）、（b）和（c）三处空缺。

【问题 3】（5 分）

对于实体店销售，若要增加送货上门服务，由营业员在系统中下订单，与网购的订单进行后续的统一管理。请根据该需求，对图 3-1 进行补充，并修改订单关系模式。

【答案】

【问题 1】

【解析】

根据需求描述，配送业务由配送员执行，基于分拣后的商品，配送至顾客指定的收货地点，因此配送联系关联配送员、分拣、收货地点三个实体，且每个配送员可执行多次配送，每个分拣结果可对应一次配送，每个收货地点可接收多次配送，均为 1:* 的联系类型。

【问题 2】

（a）所在仓库

（b）支付凭证编号

（c）订单 ID

【解析】

（a）需求中提到商品由仓库统一出库，因此商品表需要记录所在仓库信息；

（b）订单支付成功后需要记录支付凭证编号，因此订单表需要添加该属性；

（c）分拣操作是针对订单中的商品进行的，因此分拣表需要关联订单 ID。

【问题 3】

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

【解析】

实体店送货上门服务需要营业员创建订单，因此需要在 ER 图中添加营业员与订单的 "店购" 联系；同时订单表需要关联销售 ID，以区分网购订单和店购订单，实现统一管理。

【第 4 题】（题型：数据库规范化题）

题目：

阅读下列说明，回答问题 1 至问题 3，将解答填入答题纸的对应栏内。

【说明】

某小区由于建设时间久远，停车位数量无法满足所有业主的需要，为公平起见，每年进行一次抽签来决定车位分配。小区物业拟建立一个信息系统，对停车位的使用和收费进行管理。

【需求描述】

（1）小区内每套房屋可能有多名业主，一名业主也可能在小区内有多套房屋。业主信息包括业主姓名、身份证号、房号、房屋面积，其中房号不重复。

（2）所有车位都有固定的编号，且同一年度所有车位的出租费用相同，但不同年份的出租费用可能不同。

（3）所有车位都参与每年的抽签分配。每套房屋每年只能有一次抽签机会。抽中车位的业主需一次性缴纳全年的车位使用费用，且必须指定唯一的汽车使用该车位。

（4）小区车辆出入口设有车牌识别系统，可以实时识别进出的汽车车牌号。为方便门卫确认，系统还需登记汽车的品牌和颜色。

【逻辑结构设计】

根据上述需求，设计出如下关系模式：

业主（业主姓名，业主身份证号，房号，房屋面积）

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

【问题 1】（6 分）

对关系 “业主”，请回答以下词题：

（1）给出 “业主” 关系的候选键。

（2）它是否为 2NF，用 60 字以内文字简要叙述理由。

（3）将其分解为 BCNF，分解后的关系名依次为：A1,A2，...，并用下划线标示分解后的各关系模式的主键。

【问题 2】（6 分）

对关系 “车位”，请回答以下问题：

（1）给出 “车位” 关系的候选键。．

（2）它是否为 3NF，用 60 字以内文字简要叙述理由。

（3）将其分解为 BCNF，分解后的关系名依次为：B1,B2，...，并用下划线标示分解后的各关系模式的主键。

【问题 3】（3 分）

若临时车辆进入小区，按照进入和离开小区的时间进行收费（每小时 2 元）。试增加 “临时停车” 关系模式，用 100 字以内文字简要叙述解决方案。

【答案】

【问题 1】

（1）候选键：（房号，业主身份证号）

（2）不是 2NF。候选键（房号，业主身份证号）部分决定非主属性 “房屋面积”，存在部分函数依赖。

（3）分解后的关系模式：

A1（房号，业主身份证号）

A2（房号，房屋面积）

A3（业主身份证号，业主姓名）

【解析】

（1）每套房屋有多名业主，每名业主可有多套房屋，因此需要房号和业主身份证号共同唯一标识一条业主记录；

（2）房屋面积仅由房号决定，属于部分函数依赖，不符合 2NF 的要求（2NF 要求消除非主属性对候选键的部分函数依赖）；

（3）分解为 BCNF 需要消除所有部分函数依赖和传递函数依赖，将房屋信息、业主基本信息、业主房屋关联关系分别存储。

【问题 2】

（1）候选键：（车位编号，使用年份）、（房号，使用年份）、（车牌号，使用年份）（给出其中一个即可）

（2）不是 3NF。存在非主属性 “汽车品牌”“汽车颜色” 对候选键的传递函数依赖，如（车位编号，使用年份）→车牌号→汽车品牌。

（3）分解后的关系模式：

B1（使用年份，费用）

B2（车牌号，汽车品牌，汽车颜色）

B3（车位编号，使用年份，房号，车牌号）（顺序可调整）

【解析】

（1）车位每年分配一次，因此需要车位编号 + 使用年份、房号 + 使用年份或车牌号 + 使用年份共同唯一标识一条车位使用记录；

（2）汽车品牌和颜色由车牌号决定，而车牌号依赖于候选键，因此存在传递函数依赖，不符合 3NF 的要求（3NF 要求消除非主属性对候选键的传递函数依赖）；

（3）分解为 BCNF 需要将年份费用、汽车信息、车位使用信息分别存储，消除传递函数依赖。

【问题 3】

增加临时停车关系模式：临时停车（车牌号，进入时间，离开时间，费用），其中车牌号和进入时间作为主键，离开时间为空表示车辆未离开，费用可根据时间差自动计算。

【解析】

临时停车需要记录车牌号、进入时间、离开时间，其中车牌号和进入时间共同作为主键，费用可根据进入和离开时间的差值计算，离开时间为空表示车辆仍在小区内。

【第 5 题】（题型：事务与 SQL 编程题）

题目：

阅读下列说明，回答问题 1 和问题 2，将解答填入答题纸的对应栏内。

【说明】

某图书馆的图书借还业务使用如下关系模式：

书目（ISBN，书名，出版社，在库数量）

图书（书号，ISBN，当前位置）

其中在库数量为当前书目可借出的图书的数量，每本图书入库后都会有当前位置，借出后当前位置字段改为空值。每一条书目信息对应多本相同的图书，每一本图书只能对应一条书目。

借还书业务的基本流程如下描述：

（1）读者根据书名查询书目，当前书目的在库数量大于 0 时可借阅。

（2）读者借出一本图书时，进行出库操作：根据该图书的书号将该图书的当前位置字段值改为空值，并根据其 ISBN 号将对应书目的在库数量减 1。

（3）读者归还一本图书时，进行入库操作：系统根据当前书架的空余位置自动生成该本书的存放位置，并根据该图书的书号将其当前位置字段值改为生成的存放位置，然后将对应书目的在库数量加 1。

（4）借还书时，逐一扫描每本图书的书号并进行出、入库操作。

【问题 1】（7 分）

引入两个伪指令：a=R (X) 表示将在库数量 X 值读入到变量 a 中；W (a，X) 表示将变量 a 的值写入到在库数量 X 中。入库操作用下标 I 表示，出库操作用下标 O 表示。

将出库和入库操作分别定义为两个事务，针对并发序列：

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)。其中变量 aI 和 ao 分别代表入库事务和出库事务中的局部变量。

（1）假设当前 X 的值为 3，则执行完上述并发序列的伪指令后，X 的值是多少？简述产生这一错误的原因（100 字以内）。

（2）为了解决上述问题，引入独占锁指令 XLock (X) 对数据 X 进行加锁，解锁指令 Unlock (X) 对数据 X 进行解锁。入库操作用下标 I 表示，如 XLockI (X)；出库操作用下标 O 表示，如 Unlocko (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;

}

【答案】

【问题 1】

（1）X 的值为 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)。

【解析】

（1）初始 X=3，出库事务读 X=3，减 1 后为 2；入库事务读 X=3，加 1 后为 4；入库事务的写入覆盖了出库事务的写入，最终 X=4，属于典型的丢失修改并发问题。

（2）2PL 协议要求事务在执行过程中先加锁，执行完后解锁，且不允许在解锁后再加锁。因此需要让一个事务完整执行（加锁、读写、解锁）后，再执行另一个事务。

【问题 2】

（a）书号 = BookNo

（b）在库数量 + Amount

（c）图书.ISBN = 书目.ISBN

（d）COMMIT

【解析】

（a）UPDATE 图书表需要根据输入的书号定位到具体图书记录；

（b）Amount 为 1 表示入库（在库数量 + 1），为 - 1 表示出库（在库数量 - 1），因此用在库数量 + Amount 实现统一修改；

（c）需要通过图书表的 ISBN 关联到书目表，确保修改的是对应书目的在库数量；

（d）所有操作执行成功后需要提交事务，确保数据持久化。

知识点盘点：

【试题一知识点】

・数据流图（DFD）的基本元素：外部实体、处理过程、数据存储、数据流

・上下文数据流图与 0 层数据流图的对应关系

・数据流图的完整性检查方法

・外部实体的识别与数据流调整规则

【试题二知识点】

・SQL 表创建语句：主键、外键、CHECK 约束的定义

・视图的创建与查询

・触发器的创建与使用：AFTER 触发器、行级触发器

・分组查询与 HAVING 子句的使用

・子查询与 NOT IN、ANY/ALL 操作符的使用

【试题三知识点】

・实体联系图（ER 图）的设计：实体、联系、联系类型的识别

・从 ER 图到关系模式的转换规则

・数据库需求分析与概念模型设计方法

・关系模式的优化与扩展

【试题四知识点】

・数据库规范化：1NF、2NF、3NF、BCNF 的定义与判断

・候选键的识别方法

・关系模式的分解方法：保持函数依赖、无损连接分解

・实际业务场景下的关系模式设计

【试题五知识点】

・事务并发问题：丢失修改、不可重复读、幻读

・并发控制协议：两段锁协议（2PL）的定义与应用

・SQL 存储过程的编写：事务控制（COMMIT、ROLLBACK）

・关联更新的实现：使用 EXISTS 子句实现跨表关联更新

・图书借还业务的数据库操作逻辑

THE END -

点击下方卡片关注我点个小赞你必上岸↓↓↓

点个小“赞” 你必上岸

本文地址： https://sjds.net/635896.html

文章来源：四季读书网