一、问答题
1、【说明】阅读下列说明，回答问题1至问题3,将解答填入答题纸的对应栏内。
某社区蔬菜团购网站，为规范商品收发流程，便于查询客户订单情况，需要开发个信息系统。请根据下述需求描述完成该系统的数据库设计。
【需求描述】
(1)记录蔬菜供应商的信息，包括供应商编号、地址和一个电话。
(2)记录社区团购点的信息，包括团购点编号、地址和一个电话。
(3)记录客户信息，包括客户姓名和一个电话。客户可以在不同的社区团购点下订单，不直接与蔬菜供应商发生联系。
(4)记录客户订单信息，包括订单编号、团购点编号、客户电话、订单内容和日期。
【概念模型设计】 根据需求阶段收集的信息，设计的实体联系图（不完整）如图1-1所示。
【问题1】（6 分）
根据需求描述，补充图1-1的实体联系图。
【问题 2】（4 分）
补充下列逻辑结构设计中的（a）、（b）两处空缺，并描述完整性约束关系。
【逻辑结构设计】
根据概念模型设计阶段完成的实体联系图，得出如下关系模式（不完整）：
蔬菜供货商 (供货商编号，地址，电话)
社区团购点 (团购点编号，地址，电话)
供货 (供货商编号， (a) )
客户 (姓名，客户电话)
订单 (订单编号，团购点编号， (b) ，订单内容，日期)
【问题 3】（5 分）
若社区蔬菜团购网站还兼有代收快递的业务，请增加新的“快递”实体，并给出客户实体和快递实体之间的“收取”联系，对图1-1进行补充。“快递”关系模式包括快递编号、客户电话和日期
参考答案：
（一）
（二）
（a）团购点编号、（b）客户电话
供货关系中，主键为（供货商编号，团购点编号）。
订单关系宏，客户电话作为外键，连接到客户表。
（三）
快递 (快递编号, 客户电话, 日期)
2、试题二（共15分）
阅读下列说明，回答问题 1至问题 3，将解答填入答题纸的对应栏内。
【说明】
为防控新冠疫情，一些公共设施需要定期消毒， 管理部门为高效完成工作并记录必要的工作信息，设计了相应的数据库，其中有一个表用来记录公共汽车的消毒情况，表的结构如下:
消毒记录(日期，车牌号，行驶路线，消毒人员工号，消毒人员姓名)
其中车牌号和消毒人员工号唯一，同一辆车保持固定的行驶路线。假设同一人员每天可以负责多辆车的消毒工作。
请回答以下问题。
【问题1】（6分）
给出消毒记录表中成立且左侧只有一个属性的所有函数依赖关系。题中设计的消毒记录表是否满足2NF请用100字以内的文字说明原因。
【问题2】（6分）
如果要将消毒记录表规范化为满足3NF，请用100字以内的文字简要说明解决方案，并给出各个新表的主码和外码。
【问题3】（3分）
如果每辆车每日有多次消毒，需要记录每次消毒的消毒时间，在问题2设计结果的基础上，如何在不破坏3NF且不增加冗余的前提下做到？请简单说明方案。
参考答案：
（一）
（1）车牌号→行使路线
（2）消毒人员工号→消毒人员姓名
（3）（日期，车牌号）→消毒人员工号
消毒记录表 不属于 2NF，因为该关系的主键为（日期，车牌号），因此存在（日期，车牌号）→行使路线，即非主属性对键的部分函数依赖。
（二）
对人员分配表进行模式分解，消除存在的非主属性对码的部分函数依赖。
（1）消毒记录1（车牌号，行使路线）
（2）消毒记录2（消毒人员工号，消毒人员姓名）
（3）消毒记录3（日期，车牌号，消毒人员工号）
（三）如上
修改问题2中的
消毒记录3（日期，消毒时间，车牌号，消毒人员工号）
新关系中，如果在同一时间段消毒人员只能给一辆车消毒的话，这存在两个候选码：
（日期，消毒时间，车牌号）和（日期，消毒时间，消毒人员工号）
由于没有非主属性，则自然达到3NF。
3、试题三（共15分）
阅读下列说明，回答问题1至问题3，将解答填入答题纸的对应栏内。
【说明】
某竞赛管理系统的部分数据库关系模式如下:
选手： PLAYER(Pno,Pname,Sex,Region,Tel),各属性分别表示参赛选手的编号、姓名、性别、地区和联系电话；
竞赛项目: CONTEST(Cno,Cname,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分）
查询所有未参加 ’AI’ 类别竞赛的选手，要求输出选手的编号(Pno)， 查询结果按照选手编号的升序排列。此功能由下面的SQL语句实现，请补全。
SELECT Pno FROM (f) _
WHERE Pno (g) (
SELECT (h) FROM PC,Contest
WHERE (i) _
(j) Type =’AI’)
(k) Pon;
【问题3】（4分）
由于某种原因，编号为’TE06’的竞赛项目在正式举办前被取消了。而此前系统中已经记录了些选手的报名参 赛情况，因此需要在系统中删除’E06'的竞赛项目记录， 以及该竞赛的所有报名参赛纪录。根据问题1在选手参赛表PC上定义的参照完整性约束，此功能可以由下面的SQL语句实现，请补全。
(l) FROM (m) WHERE Cno= ‘E06’;
(n) FROM (o) WHERE Cno= ‘E06’;
参考答案：
a player
b contest
c not null
d check(rank in(‘一’,’二’,’三’,’无’))
e primary key(pno,cno)
f player
g not in
h pno
i pc.cno=contest.cno
j and
k order by
l delete
m pc
n delete
o contest
说明：先删除子表中的记录，在删除主表中的记录
4、试题四（共15分）
阅读下列说明，回答问题 1 至问题 3，将解答填入答题纸的对应栏内。
【说明】
某企业网上书城系统的部分关系模式如下：
书籍信息表: 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, limit_amount), 其中属性含义分别为:书籍编码、销售分类、再购额度；
书籍最低库存表: 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_no)
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。
表4-1 事务运行部分调度示意表
请说明T4时刻，用户2事务读取到的all_nums 数值是多少?
请说明T8时刻，all_nums数据是否出现不一致性问题? 如出现，请说明属于哪一种数据不一致性。
参考答案：
（一）
a sum
b bno
c 10000
d 1
e commit
f after
g all_nums
h for each row
i NEW.all_nums
j OLD.all_nums
k bookorders
（二）
参见问题1
（三）
all_nums=500-2=498
数据不一致。用户2读取了用户1回滚前的数据，属于 读脏数据 的不一致性。
5、阅读下列说明，回答问题1至问题3,将解答填入答题纸的对应栏内。
【说明】
某数据库系统采用数据转储方式对数据和日志文件进行离线备份，用检查点机制进行恢复。假设部分其日志文件如表5-1所示。
日志记录内容中：<Ti,START>表示事务Ti开始执行，<Ti,COMMIT>表示事务Ti提交，<Ti,D,V1,V2>表示事务Ti将数据项D的值由V1修改为V2。例如：<T1,I,22,3>表示事务T1将数据项I的值由22修改为3。CRASH表明系统发生掉电故障。
请根据上述描述，回答以下问题。
表5-1 日志记录列表
【问题1】（6分）
假设各数据项的初始值为： I=22, J=45, K=9, 系统出错恢复后，I, J, K的数值会恢复为多少？
【问题2】（4分）
请给出系统恢复时需要redo的事务列表和需要undo的事务列表。
【问题 3】（5分）
假设掉电造成磁盘介质损坏，数据库无法启动，请用100字以内的文字简要说明其恢复过程。
参考答案：（一）
T1、T2都完成，而T3未提交需要回滚。
因此：I=3、J=5、K=9
（二）
Redo队列：T2
Undo队列：T3
（三）
由于磁盘介质故障，必修用前期完全备份的副本和备份后的日志才能恢复到错误前的状态。
基本步骤如下：
（1）加载数据库的最近的完全备份文件，把数库恢复到备份时的状态。
（2）加载备份后，数据库运行时开启的日志文件，通过扫描该日志文件，把数据库恢复到系统出错误之前的状态。本例中，对T1、T2做redo操作，对T3做undo操作。
喵呜刷题：让学习像火箭一样快速，快来微信扫码，体验免费刷题服务，开启你的学习加速器！
