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

试卷预览

1 问答题 1分

某企业网上书城系统的部分关系模式如下:

书籍信息表: books(book no, book name, press no, ISBN, price, sale type, all nums),其中属性含义分别为:书籍编码、书籍名称、出版商编码、ISBN、 销售价格、销售分类、当前库存数量:

书籍销售订单表: orders(order no, book no, book nums, book price, order date,amount)，其中属性分别为:订单编码、书籍编码、书籍数量、书籍价格、订单日期和总金额。

书籍再购额度表: booklimit(book no, sale_ type, limitamount),其中属性含义分别为:

书籍编码、销售分类、再购额度；

书籍最低库存表: bookminlevel(book no, leve) ，其中属性含义分别为:书籍编码，书籍最低库存数量；

书籍采购表: bookorders(book no, order. _amount)，其中属性含义分别为:书籍编码和采购数量。

有关关系模式的说明如下:

(1)下划线标出的属性是表的主码。

(2)根据书籍销售情况来确定书籍的销售分类：销售数量小于1万的为普通类型，其值为0；1万及以上的为热销类型，其值为1。

(3)系统具备书籍自动补货功能，涉及到的关系模式有:书籍再购额度表、书籍最低库存表、书籍采购表。其业务逻辑是:当某书籍库存小于其最低库存数量时，根据书籍的销售分类以及书籍再购额度表中的再购额度，生成书籍采购表中的采购订单，完成自动补货操作。

【问题1】(5分)

系统定期扫描书籍销售订单表，根据书籍总的销售情况来确定书籍的销售类别。下面是系统中设置某书籍销售类别的存储过程，结束时需显式提交返回。请补全空缺处的代码。

CREATE PROCEDURE UpdateBookSaleType(IN bno varchar(20))

DECLARE

all_nums number(6);

BEGIN

SELECT (a) (book_nums) INTO all_nums FROM orders

WHERE book_no = (b) ;

IF all_nums ＜ (c) THEN

UPDATE books SET sale_type = 0 WHERE book_no = bno;

ELSE

UPDATE books SET sale_type = (d) WHERE book_no = bno;

END IF;

(e) ;

END;

【问题2】 (6分)

下面是系统中自动补货功能对应的触发器，请补全空缺处的代码。

CREATE TRIGGER BookOrdersTrigger （f） update

of (g) on books

(h)

WHEN (i) ＜(SELECT level FROM bookminlevel

WHERE bookminlevel.book_no = OLD.book_no)

AND (j) >=(SELECT level FROM bookminlevel

WHERE bookminlevel.book_no = OLD.book_mo)

BEGIN

INSERT INTO (k)

(SELECT book_no,limit_amount

FROM booklimit as TMP

WHERE TMP.book_no = OLD.book_no

AND TMP.sale_type = OLD.sale_type);

END;

【问题3】 (4分)

假设用户1和用户2同时购买同一书籍，对应事务的部分调度序列如表4-1所示(事务中未进行并发控制)，其中T0时刻该书籍的库存数量all nums=500。

请说明T4时刻，用户2事务读取到的all nums数值是多少?请说明T8时刻，all nums数据是否出现不一致性问题?如出现，请说明属于哪一种数据不一致性。

查看答案 开始考试

正确答案：

本题解析：

【问题1】

a: sum

b:bno

c:10000

d:1

e:commit

【问题2】

f： after

g： all_nums

h: for each row

i: NEW.all_nums

j: OLD.all_nums

k: bookorders

【问题3】

说明T4时刻，用户2事务读取到的all_nums数值是498。

新版章节练习，考前压卷，完整优质题库+考生笔记分享，实时更新，软件，

在T8时刻，all_nums数据会出现不一致性的问题，由于用户2事务读到了用户1修改过的all_nums,然后在T7时刻用户1事务回滚了之前对all_nums的修改，把all_nums恢复到了500。最终用户2事务读到的数据是498，读到的是脏数据。所以是属于读脏数据的不一致。

2 问答题 1分

为防控新冠疫情，一些公共设施需要定期消毒，管理部门为高效完成工作并记承必要的工作信息，设计了相应的数据库，其中有一个表用来记承公共汽车的消毒情况，表的结构如下：

消毒记录(日期，车牌号，行驶路线，消毒人员工号，消毒人员姓名)

其中车牌号和消毒人员工号唯一,同辆车保持固定的行驶路线。假设同一人员每天可以负责多辆车的消毒工作。

【问题1】(6分)

给出消毒记录表中成立且左侧只有一个属性的所有函数依赖关系。题中设计的消毒记录表是否满足2NF请用100字以内的文字说明原因。

【问题2】 (6分)

如果要将消毒记录表规范化为满足3NF,请用100字以内的文字简要说明解决方案，并给出各个新表的主码和外码。

【问题3】 (3分)

如果每辆车每日有多次消毒，需要记录每次消毒的消毒时间，在问题2设计结果的基础上，如何在不破坏3NF且不增加冗余的前提下做到？请简单说明方案。

查看答案 开始考试

正确答案：

本题解析：

【问题1】

左侧只有一个属性的函数依赖关系有2个，分别为：

车牌号->行驶路线

消毒人员工号->消毒人员姓名

不满足2NF，根据题干可以得到消毒记录表的候选键为（日期，车牌号），由于存在车牌号->行驶路线，属于部分依赖。根据2NF的要求，不存在非主数据对主属性的部分依赖，所以不满足2NF。

【问题2】

由于消毒记录表不满足2NF，需要将消毒记录表进行拆分成多个表，拆分的表如下：

消毒记录1（车牌号，行驶路线）

消毒记录2（消毒人员工号，消毒人员姓名）

消毒记录3（车牌号，日期，消毒人员工号）

由于拆分后的三个表都不存在非主属性对主属性的部分和传递函数依赖，所以都满足3NF。

3 问答题 1分

某社区蔬菜团购网站，为规范商品收发流程，便于查询客户订单情况，需要开发个信息系统。请根据下述需求描述完成该系统的数据库设计。

[需求描述]

(1)记录蔬菜供应商的信息，包括供应商编号、地址和一个电话。

(2)记录社区团购点的信息，包括团购点编号、地址和一个电话。

(3)记录客户信息，包括客户姓名和一个电话。客户可以在不同的社区团购点下订单，不直接与蔬菜供应商发生联系。

(4)记录客户订单信息，包括订单编号、团购点编号、客户电话、订单内容和日期。

[概念模型设计]

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

[逻辑结构设计]

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

蔬菜供货商(供货商编号，地址，电话)

社区团购点(团购点编号，地址，电话)

供货(供货商编号，(a)）

客户(姓名，客户电话)

订单(订单编号，团购点编号，订单内容，日期，（b）)

【问题1】(6分)

根据问题描述，补充图1-1的实体联系图。

【问题2】 (4分)

补充逻辑结构设计结果中的(a)、(b)两处空缺及完整性约束关系。

【问题3】(5分)

若社区蔬菜团购网站还兼有代收快递的业务，请增加新的“快递”实体，并给出客户实体和快递实体之间的“收取’联系，对图1进行补充。“快递” 关系模式包括快递编号、客户电话和日期。

查看答案 开始考试

正确答案：

本题解析：

【问题1】

依据题干中的关系设计（a）处应该为主键的定义，（b）和（c）处应该为外键的定义：

（a）PRIMARY KEY

（b）REFERENCES VEM（VEMno）

（c）REFERENCES GOODS（Gno）

【问题2】

本题是考查创建视图的语法：

CREATE VIEW 视图名（列名 [,· · · n]）

AS SELECT 查询子句

（d）处应该制定分组的依据，由于SELECT语句后跟随了 VEMno ，Gno ，SDate这些列，所以（d）处应该列表这些列

（e）处应该为创建视图的名称：SALES_Detail（VEMno，Location，Gno，Brand，Price，Amount，SDate）

（f）和（g）处应该制定查询语句中涉及到的3个关系之间的连接条件：（f）VEM.VEMno=SALES_Total. VEMno ；（g）GOODS.Gno= SALES_Total. Gno；f和g可以互换

【问题3】

本题是考查创建触发器的语句

CREATE TRIGGER 触发器名

[{BEFORE|AFTER}]

{事件}

ON ＜对象名>

REFERENCING {OLD AS old}|{NEW AS new}

[FOR EACH ROW] [WHEN 条件]

{触发器执行体}

（h）处应该为关键字：TRIGGER

（i）处应该是插入数据时触发，所以应该为INSERT

OOS（VEMno，Gno，SDate， STime）共有4个属性且为全码表，INSERT语句必须插入所有列的值，因此：

（j）处应该为：SALES.Gno，SALES.Sdate

【问题4】

题干中要求返回数量，因此（k）COUNT（*） AS 数量

（l）处是分组的依据，分组的依据为商品编号，由于SELECT 后跟随了列GOODS.Gno，Brand，所以（l）处应为：GOODS.Gno，Brand

（m）处是在分组的基础上制定筛选的条件；而子查询返回的是一系列的单个属性的值的集合，应该使用>=ALL来取得最大值，所以（m）处应填写COUNT（*）>=ALL

【问题5】

依据子查询 SELECT DISTINCT GNO FROM（o）；应该是从销售表中查询出有销售记录的商品编号，所以（o）处应为：SALES

（n）处为筛选条件，只有没有出现在销售表中的商品编号，也就意味着没有销售过，因此（n）处应为：NOT IN

4 问答题 1分

某数据库系统采用数据转储方式对数据和日志文件进行离线备份，用检查点机制进行恢复。

假设部分其日志文件如表5-1所示。日志记录内容中：

【问题1】 (6分)

假设各数据项的初始值为: 1-22, J=45, K-9,系统出错恢复后，I,J,K的数值会恢复为多少？

【问题2】 (4分)

请给出系统恢复时需要重做(Redo)的事务列表和需要撤销(undo)的事务列表。

【问题3】 (5分)

假设掉电造成磁盘介质损坏，数据库无法启动，请用100字以内的文字简要说明其恢复过程。

查看答案 开始考试

正确答案：

本题解析：

【问题1】

I,J,K的数值会恢复为I=3,J=5,K=9

【问题2】

Redo: T2

Undo：T3

【问题3】

由于掉电造成磁盘介质损坏，数据库已经无法启动，恢复过程的步骤如下所示：

（1）装入最新的数据库后备副本，使数据库恢复到最近一次转储时的一致性状态。

（2）转入相应的日志文件副本，重做已完成的事务。

5 问答题 1分

某竞赛管理系统的部分数据库关系模式如下：

选手: PLAYER(PnO Phame, Sex. Region, Tel),各属性分别表示参赛选手的编号、姓名、性别、地区和联系电话:竞赛项目: CONTEST(CnO, Chame, Type, Date),各属性分别表示竞赛项目的编号、名称、类别和举办日期；

选手参赛: PC(Pno. Cno, City, Rank, Point),各属性分别表示选手编号、竞赛项目编号、竞赛所在城市、选手取得的名次和积分。

有关关系模式的说明如下：

(1)下划线标出的属性是表的主码。

(2)选手参赛表的属性Pno和Cno分别参照了选手表和竞赛项目表的主码。

(3)一个选手参加一项竞赛有一个名次和一-个积分，名次有4个取值(“一”“二” “三”无”)。另外，竞赛所在城市不能为空。

根据以上描述，回答下列问题，将SQL语句的空缺部分补充完整。

【问题1】 (5分)

请将下面创建选手参赛表PC的SQL语句补充完整，要求定义实体完整性约束、参照完整性约束，以及其他完整性约束。

CREATE TABLE PC(

Pno CHAR(10) REFERENCES(a)(Pno),

Cno CHAR(3) REFERENCES(b)(Cno)，

City CHAR(20)(c)

Rank CHAR(20)(d)

PointSMALLIT,

(e)

【问题2】(6分)

查询所有未参加

查看答案 开始考试

正确答案：

本题解析：

【问题1】

（a）：PLAYER

（b）：CONTEST

（c）：NOT NULL

（d）：CHECK(Rank in(’一’, ’二’, ’三’, ’无’))

（e）：PRIMARY KEY (Pno,Cno)

【问题2】

f: PLAYER

g: not in

h: pno

i: pc.cno=contest.cno

j: and

k: order by

【问题3】

l：delete

m：PC

n：delete

o：CONTEST