现在位置: 首页 / 数据库 / 正文

数据库系统工程师2021年真题解析-下午

  • Caijxlinux
  • 数据库
  • 10天前
  • 42热度
  • 0评论

数据库系统工程师2021年下午题目解析

【1】

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

【说明】

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

【需求描述】

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

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

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

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

【概念模型设计】

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

蔬菜团购ER（不完整）

【逻辑结构设计】

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

蔬菜供货商（<u>供货商编号</u>，地址，电话）

社区团购点（<u>团购点编号</u>，地址，电话）

供货（<u>供货商编号</u>，<u>（a）</u>）

客户（姓名、<u>客户电话</u>）

订单（<u>订单编号</u>，团购点编号，<u>（b）</u>，订单内容，日期）

问题1：根据问题描述，补充图1-1的实体联系图。（答案有误，客户-订单-社区团购点之间的连线应该带有星号，因为是多对多的联系）

蔬菜ER

问题2：补充逻辑结构设计结果中的（a）、（b）两处空缺及完整性约束关系。

答：a：团购点编号，b：客户电话。供货关系中，供货商编号和团购点编号作为主键。订单表中客户电话作为外键连接到客户表（注意客户电话要下划虚线，代表外键）。

问题3：若社区蔬菜团购点还兼有代收快递的业务，请增加新的"快递"实体，并给出客户实体和快递实体之间的"收取"联系，对图1-1进行补充。"快递"关系模式包括快递编号、客户电话和日期。（注意：不要遗漏1对多的关系）

新增快递实体

知识点：

数据库的整体设计遵循如下六个流程：需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施，数据库运维。下午考试的五道题也是按照这个流程，由浅入深进行考察。

ER图概念

【2】

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

【说明】

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

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

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

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

答：车牌号->行驶路线。消毒人员工号->消毒人员姓名。不满足2NF，因为消毒记录表的主码是（日期，车牌号），而车牌->行驶路线，所以存在非主属性对码的部份依赖。

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

答：拆分为三个表：路线表（车牌号，行驶路线），消毒人员表（消毒人员工号，消毒人员姓名），消毒记录表（日期，车牌号，消毒人员工号）。路线表主码为车牌号，无外码。消毒人员表主码为消毒人员工号，无外码。消毒记录表主码为（日期，车牌号，消毒人员工号），车牌号为外码，对应路线表，消毒人员工号为外码，对应消毒人员表。

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

答：方案：修改消毒记录表为（日期，车牌号，消毒人员工号，消毒时间）

知识点：

1、该类题目考察数据库的逻辑结构设计，首先需要精读题干，在题干内找到依赖关系。

车牌号和消毒人员工号唯一：车牌和工号不能共享。
避免车辆和消毒人员的多值依赖。消毒人员工号->消毒人员姓名。
同一辆车保持固定的行驶路线：车牌号->行驶路线。
同一人员每天可以负责多辆车的消毒工作：消毒人员：车辆=1:m
隐形约束：每天只给每辆车消毒一次，一辆车只能一个人消毒。

2、需要找出表内的主键（主键：唯一决定某行数据的属性集，其他属性都依赖于主键）

2021下午2函数依赖

3、理解范式的概念（上午和下午都会考察到）

范式 概念
1NF 属性不可再分
2NF 非主属性完全依赖于主键
3NF 非主属性直接依赖于主键
BCNF 消除3NF非主属性依赖（3.5NF）
4NF 消除3NF多值依赖

以下面的图作为例子，讲解2NF和3NF。日期和车牌号两个属性共同组成主键。消毒人员工号、消毒人员姓名和行驶路线为非主属性，通过主键可以推导出消毒人员工号继而也能推导出消毒人员姓名。这种称为非主属性完全依赖于主键。而行驶路线通过上面可以得知，只需要车牌号一个属性即可推导出来，所以只利用了主键的一部分元素，所以没有完全依赖于主键，所以不满足2NF。将行驶路线分离，新增加记录车牌号->行驶路线即可满足2NF。而分解完之后不满足3NF，这是因为主键->消毒人员工号->消毒人员姓名，存在传递依赖，不能称为直接依赖于主键。将消毒人员工号->消毒人员姓名分解为一个新的记录，即可满足3NF。

2021下午2范式概念

【3】

阅读下列说明，回答问题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：请将下面创建选手参赛表PC的SQL语句补充完整，要求定义实体完整性约束、参照完整性约束，以及其他完整性约束。

CREATE TABLE PC(
    Pno CHAR(10) REFERENCES (a) (PNO),
    Cno CHAR(3) REFERENCES (b) (Cno),
    City CHAR(20) (c),
    Rank CHAR(29) (d),
    Point SMALLINT
  (e)
);
CREATE TABLE PC(
    Pno CHAR(10) REFERENCES PLAYER (PNO),
    Cno CHAR(3) REFERENCES CONTEST (Cno),
    City CHAR(20) NOT NULL,
    Rank CHAR(29) CHECK (Rank IN ('一','二','三','无')) ,
    Point SMALLINT
  PRIMARY KEY (Pno,Cno)
);

问题2：查询所有未参加"AI"类别竞赛的选手，要求输出选手的编号（Pno），查询结果按照选手编号的升序排列。此功能由下面的SQL语句实现，请补全。

SELECT Pno FROM (f)
    WHERE Pno (g) (
        SELECT (h)
        FROM PC,Contest
        WHERE (i)
            (j) Type='AI')
(k) Pno;
SELECT Pno FROM PLAYER
    WHERE Pno NOT IN (
    SELECT Pno 
    FROM PC, Contest
    WHERE PC.Cno = CONTEST.Cno
      AND Type = 'AI'
)
ORDER BY Pno;

问题3：由于某种原因，编号为E06的竞赛项目在正式举办前被取消了。而此前系统中已经记录了一些选手的报名参赛情况，因此需要在系统中删除E06的竞赛项目记录，以及该竞赛的所有报名参赛情况。根据问题1在选手参赛表PC上定义的参照完整性约束，此功能可以由下面的SQL语句实现，请补全。

(i) FROM (m) WHERE Cno = 'E06';
(n) FROM (o) WHERE Cno = 'E06';
DELETE FROM PC WHERE Cno = 'E06';
DELETE FROM CONTEST WHERE Cno = 'E06';

知识点：

1、第三题考察SQL语句，需要考生掌握有关SQL语句相关的概念。

SQL基本数据类型

2、SQL表操作（CREATE）

SQL表操作CREATE

3、SQL表操作（增删改）

SQL表操作（增删改）

4、SQL数据操作（增删改查CURD）

SQL数据操作（增删改查CURD）

5、SQL数据操作（SELECT）

SQL数据操作（SELECT）

SQL数据操作（SELECT）-2

左连接（以左表为主），右连接（以右表为主）

SQL数据操作（SELECT）-3

SQL数据操作（SELECT）-4

SQL数据操作（SELECT）-5

SQL数据操作（SELECT）-6

SQL数据操作（SELECT）-7

SQL数据操作（SELECT）-8

6、SQL数据操作（GROUP BY）

SQL数据操作（GROUP BY）

SQL数据操作（GROUP BY）-2

SQL数据操作（GROUP BY）-3

【4】

阅读下列说明，回答问题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:系统定期扫描书籍销售订单表，根据书籍总的销售情况来确定书籍的销售类别。下面是系统中设置某书籍销售类别的存储过程，结束时需显示提交返回。

CREARE PROCEDURE UpdateBookSaleType(IN bno varchar(20))
DECLARE
all_nums number(6);
BEGIN
 SELECT (a)(books_sum) 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
CREARE PROCEDURE UpdateBookSaleType(IN bno varchar(20))
DECLARE
all_nums number(6);
BEGIN
 SELECT sum(books_sum) INTO all_nums FROM orders
    WHERE book_no = bno;(某本书卖了多少本，多少本->临时变量)
    IF all_nums<10000 THEN
        UPDATE books SET sale_type = 0 WHERE book_no = bno;
    ELSE
        UPDATE books SET sale_type = 1 WHERE book_no = bno;
    END IF
    COMMIT
END

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

CREATE TRIGGER BookOrders Trigger (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
CREATE TRIGGER BookOrders Trigger after update of all_nums ON books
for each row
WHEN NEW.all_nums < (SELECT level FROM bookminlevel WHERE bookminlevel.book_no = OLD.book_no)
    AND OLD.all_nums >= (SELECT level FROM bookminlevel WHERE bookminlevel.book_no = OLD.book_no)
BEGIN
    INSERT INTO bookorders
        (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：假设用户1和用户2同时购买同一书籍，对应事务的部分调度序列如表4-1所示（事务中未进行并发控制），其中T0时刻该数据的库存数量all_nums=500。

表4-1 事务运行部分调度示意表

时间 用户1事务 用户2事务
T0 ...... ......
T1 read(all_nums)
T2 all_nums=all_nums-2
T3 write(all_nums)
T4 read(all_nums)
T5 all_nums=all_nums-3
T6 write(all_nums)
T7 ROLLBACK
T8 ...... ......

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

答：T4时刻用户2事务读取到的all_nums数值是498。出现数据不一致的问题，属于脏读

知识点：

1、第四题考察存储过程，需要掌握存储过程的概念、定义和调用方式。

存储过程

2、考察触发器，需要掌握触发器的概念和创建过程。

触发器

3、数据不一致性

数据不一致

【5】

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

【说明】

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

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

表5-1 日志记录列表

日志记录编号 日志记录内容
LSN1 <T1,START>
LSN2 <T1,I,22,3>
LSN3 <T2,START>
LSN4 <T2,L,32,37>
LSN5 <T3,START>
LSN6 <T2,J,45,5>
LSN7 <T1,COMMIT>
LSN8 <T3,M,53,15>
LSN9 <T3,K,9,11>
LSN10 CHECKPOINT
LSN11 <T2,COMMIT>
LSN13 CRASH

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

答：I=3；J=5；K=9

问题2：请给出系统恢复时需要重做（Redo）的事务列表和需要撤销（Undo）的事务列表。

答：Redo事务列表：T2。Undo事务列表：T3。

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

答：首先重装数据库，随后加载数据库的最近的完全备份文件，把数库恢复到备份时的状态。装载故障前的日志文件副本，通过扫描该日志文件，把数据库恢复到系统出错误之前的状态。

知识点：

1、数据库恢复

数据库备份

2、checkpoint总结：在checkpoint之前已经commit的不理会，一半在checkpoint之前，一半在checkpoint之后的（横跨checkpoint），有commit行为的，数据落盘就不管，未落盘执行redo。在checkpoint之前或之后，没有commit行为的，一律undo（操作不合法）。

CHECKPOINT