一、问答题
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）客户电话
供货关系中，主键为（供货商编号，团购点编号）。
订单关系宏，客户电话作为外键，连接到客户表。
（三）
快递 (快递编号, 客户电话, 日期)
解析：
第一题要求根据需求描述补充实体联系图。根据描述，我们知道蔬菜供应商、社区团购点、客户和订单是四个主要实体，它们之间的关系应反映在实体联系图中。
第二题要求补充逻辑结构设计中的两处空缺，并描述完整性约束关系。逻辑结构设计阶段需要定义各个关系模式的属性和主键、外键等完整性约束。根据实体联系图，我们可以确定（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。
解析：
问题1中，根据给定的消毒记录表结构，可以确定存在的函数依赖关系。判断其是否满足2NF，需检查是否存在非主属性对键的部分函数依赖。
问题2中，为满足3NF，需要消除模式中的部分函数依赖。通过对原表进行分解，形成新的表结构，并确定各表的主码和外码。
问题3中，要在不破坏3NF且不增加冗余的前提下记录每次消毒的消毒时间。可以通过新增一个表来实现，该表与已有的消毒记录表通过相关键关联，同时确保新的设计仍然满足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
说明：先删除子表中的记录，在删除主表中的记录
解析：
问题1是关于创建选手参赛表PC的SQL语句补充完整的问题。根据题目描述和数据库关系模式，我们可以为每个空槽进行填充：
a和b槽需要填写参照的表名，分别是PLAYER和CONTEST。c槽表示City字段不能为NULL，因此填写NOT NULL。d槽是定义Rank字段的完整性约束，由于名次只有四个取值，所以使用CHECK语句进行约束。e槽是定义表的主键，由于选手编号和竞赛项目编号共同组成表的主键，所以填写PRIMARY KEY(Pno, Cno)。
问题2是关于查询未参加’AI’类别竞赛的选手的SQL语句补充。从题目描述中我们知道需要使用NOT IN语句进行子查询，查询结果需要按照选手编号升序排列，因此还需要添加ORDER BY语句。而子查询部分需要选择选手表PLAYER和竞赛项目表CONTEST进行联接查询，并通过竞赛项目编号Cno进行匹配，最后筛选出类别不是’AI’的记录。因此，对应的空槽填写为：f填写PLAYER，g填写NOT IN，h填写Pno，i填写PC.Cno = CONTEST.Cno，j填写AND，k填写ORDER BY。
问题3是关于删除编号为’E06’的竞赛项目及其所有报名参赛记录的SQL语句补充。由于存在参照完整性约束，不能直接删除主表中的数据，需要先删除子表中的相关记录。因此，应先删除选手参赛表PC中相关的记录，再删除竞赛项目表CONTEST中的相关记录。对应的空槽填写为：l和m似乎重复了，都是删除PC表中的相关记录；n是删除CONTEST表中的相关记录；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回滚前的数据，属于 读脏数据 的不一致性。
解析：
问题1是关于数据库存储过程和事务处理的。在存储过程UpdateBookSaleType中，需要计算某书籍的总销售数量并据此更新该书籍的销售类别。因此，需要选择计算总数（sum），并根据书籍编码（bno）来过滤订单。根据题目描述，销售数量小于一万的书籍为普通类型，所以需要使用条件判断，若销售数量小于一万则更新销售类别为普通类型（0），否则更新为热销类型（1）。最后需要显式提交事务。
问题2是关于数据库触发器的。自动补货功能的触发器需要在书籍的库存数量低于最低库存时触发，插入采购订单。触发器的触发时机是在更新操作之后，触发对象是books表的all_nums字段。当旧记录的库存数量小于最低库存数量并且大于等于再购额度时，插入采购订单。
问题3是关于数据库并发控制的。在用户1和用户2同时购买同一书籍的情况下，需要考虑事务的并发控制。在T4时刻，用户2事务读取到的all_nums数值是498，因为用户1已经购买了两本书，库存数量减少了2本。而在T8时刻，由于用户1的事务可能尚未提交或回滚，导致用户2读取到的数据可能是不一致的，即读脏数据的不一致性。
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操作。
解析：
根据日志记录，事务T1将数据项I的值由22修改为3，事务T2修改了数据项J的值，而K的值在日志中没有被修改。在系统出错恢复后，I和J的值会分别恢复为日志中记录的最新值，即I=3，J=5。而K的值则保持不变，为9。
喵呜刷题：让学习像火箭一样快速，快来微信扫码，体验免费刷题服务，开启你的学习加速器！
