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

【1】

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

【说明】

某市为了规范疫苗接种工作，提升效率，并为抗击疫情提供疫苗接种数据支撑，需要开发一个信息系统。请根据下述需求描述完成该系统的数据库设计。

【需求描述】

（1）记录疫苗供应商的信息，包括供应商名称、地址和一个电话。

（2）记录接种医院信息，包括医院名称、地址和一个电话。

（3）记录被接种者个人信息，包括姓名、身份证号和一个电话。

（4）记录接种者的疫苗接种信息，包括接种医院信息、被接种者信息、疫苗供应商名称和接种日期。为了提高免疫力，接种者可能需要进行多次疫苗接种（每天最多接种一次），但每次都可以在全市任意一家医院进行疫苗接种。

【概念模型设计】

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

【逻辑结构设计】

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

供应商（供应商名称，地址，电话）
医院（医院名称，地址，电话）
供货（供应商名称，(a)，供货内容）
被接种者（姓名，身份证号，电话）
接种（被接种者身份证号，(b)，医院名称，供应商名称）

问题1：根据问题描述，补充图1-1的实体联系图（不增加新的实体）。

问题2：补充逻辑结构设计结果中的（a），（b）两处空缺，并标注主键和外键完整性约束。

答：a：医院名称。b：接种日期。

表名	主键	外键
供货	供应商名称、医院名称	供应商名称、医院名称
接种表	被接种者身份证号、接种日期	供应商名称、医院名称

问题3：若医院还兼有核酸检测的业务，检测者可能需要进行多次核酸检测（每天最多检测1次），但每次都可以在全市任意一家医院进行检测。请在图1-1中增加"被检测者"实体和相应的属性、医院和被检测者之间的"检测"联系和必要的属性，并给出新增加的关系模式。"被检测者"实体包括姓名、身份证号、地址和一个电话。"检测"联系需要包括检测日期和检测结果等。

被检测者（姓名，身份证号，地址，电话）
检测（被检测者身份证号，医院名称，检测时间，检测结果）

被检测者表主键为身份证号。检测表主键为被检测者身份证号和检测时间，外键为医院名称和被检测者身份证号。

---

【2】

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

【说明】

某卡丁车场地为方便车手线上查询自己的圈速成绩，设计了相应的关系模型。模型中有三个表：

冲线记录表（序列号，日期，冲线时刻，圈速，车号，组别，手机号，车手姓名）
赛车表（车号，组别，车型）
车手表（车手姓名，手机号，年龄，性别）

其中序列号唯一确定一条冲线记录，车型和组别可由车号确定，车手手机号唯一，车手姓名可重复。

问题1：冲线记录表中是否含有数据冗余？如果存在冗余，请列出冗余属性。

答：存在数据冗余，冗余属性为组别和车手姓名。

问题2：请分别给出冲线记录表、赛车表和车手表的主码和外码。

答：

表名	主码	外码
冲线记录表	序列号	车号、手机号
赛车表	车号	无
车手表	手机号	无

问题3：题干描述的冲线记录表是否满足3NF？如果不满足，请给出导致不满足3NF的函数依赖关系，并用50字以内的文字简要说明解决方案。

答：不满足3NF。因为3NF要求非主属性既不传递于码，也不部分依赖于码。导致不满足3NF的函数依赖关系为：序列号->车号->组别；序列号->手机号->车手姓名。要满足3NF，需要将冲线记录表中的组别和车手姓名属性删除。

---

【3】

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

【说明】

某工程项目管理系统的部分关系模式如下：

项目：PROJECT（Jno，Jname，City，Date），各属性分别表示项目代码、项目名称、项目所在城市和项目开始日期
零件：PART（Pno，Pname，Color，Sno，Stock），各属性分别表示零件代码、零件名称、零件颜色、零件所在仓库代码及库存量
供应情况：PJC（Pno，Jno，Qty），各属性分别表示零件代码、项目代码、使用量
仓库：STORE（Sno，Sname，Address），各属性分别表示仓库代码、仓库名称、仓库地址
有关上述关系模式的说明如下：
（1）下划线标出的属性是表的主键。
（2）零件表的属性Sno参照了仓库表的主码。一个零件只存放在一个仓库，库存量最低为0。
（3）供应情况表的属性Pno和Jno分别参照了零件表和项目表的主码。
根据上述描述，回答下列问题，将SQL语句的空缺部分补充完整。

问题1：请将下面创建零件表PART的SQL语句补充完整，要求定义实体完整性约束、参照完整性约束，以及其他完整性约束。

CREATE (a) PART(
Pno CHAR(10) (b)
Pname CHAR(20),
Color CHAR(4),
Sno CHAR(4) REFERENCES (c) (Sno)
Stock INT (d)
);

CREATE TABLE PART(
Pno CHAR(10) PRIMARY KRY
Pname CHAR(20),
Color CHAR(4),
Sno CHAR(4) REFERENCES STORE (Sno)
Stock INT check(Stock>=0)
);

问题2：创建视图PARTUSED，给出在项目中已使用零件的代码和使用量。此视图的创建语句如下，请补全。

CREATE VIEW PARTUSED AS SELECT Pno，(e) AS Usage FROM PJC (f) BY (g);

CREATE VIEW PARTUSED AS SELECT Pno，SUM(Qty) AS Usage FROM PJC GROUP BY Pno;

问题3：在视图PARTUSED的基础上，查询所有零件的信息，要求输出每种零件的零件代码、零件名、零件颜色和零件总量（使用量和库存量之和），查询结果按照零件总量降序排列。此功能由下面的SQL语句实现，请补全。

SELECT Pno,Pname,Color,(h) AS Total
FROM PART,PART_USED
WHER PART.Pno=PART_USED.Pno
(i)
SELECT Pno,Pname,Color,Stock AS Total
FROM PART
WHERE Pno (j)
(SELECT DISTINCT Pno FROM PJC
ORDER BY Total (k));

SELECT Pno,Pname,Color,(Stock+Usage) AS Total
FROM PART,PART_USED
WHER PART.Pno=PART_USED.Pno
UNION
SELECT Pno,Pname,Color,Stock AS Total
FROM PART
WHERE Pno NOT IN
(SELECT DISTINCT Pno FROM PJC
ORDER BY Total DESC;

问题4：由于某种原因，要拆除代码为'A006'的仓库，该仓库中的零件转入'A002'仓库存放。据此更新数据库的功能由下面的SQL语句实现，请补全。

UPDATE (i) SET (m) WHERE Sno='A006';
(n) FROM (o) WHERE Sno='A006';

UPDATE PART SET Sno='A002' WHERE Sno='A006';
DELETE FROM STORE WHERE Sno='A006';

---

【4】

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

【说明】

某银行账户系统的部分简化后的关系模式如下：

账户表：accounts（a_no，a_name，a_status，a_bal,open_branch_no，open_branch_name，phone_no），属性含义分别为：账户编码、账户名称、账户状态（1-正常、2-冻结、3-挂失）、账户余额、开户网点编号、开户网点名称、账户移动电话

账户交易明细表：trade_details(t_date,optr_no,serial_no,t_branch,a_no,t_type,t_amt,t_result)，属性含义分别为：交易日期、操作员编码、流水号、交易网店编号、账户编码、交易类型（1-存款、2-取款）、交易金额、交易结果（1-成功、2-失败、3-异常、4-已取消）

网点当日余额总表：branch_sum（b_no，b_date，b_name，all_bal），属性含义分别为：网店编号、汇总日期、网点名称、网店开户账户的总余额

系统提供常规的账户存取款交易，并提供账户余额变更通知服务。该账户系统是7*24小时不间断的提供服务；网点当日余额汇总操作一般在当日晚上12点左右，运维人员在执行日终处理操作中完成。

问题1：下面是系统日终时生成网点当日余额汇总数据的存储过程程序，请补全空缺处的代码。

CREATE PROCEDURE BranchBalanceSum(IN s_date char(8))
DECLARE
all_balance  number(142);
v_bran_no varchar(10);
v_bran_name varchar(30);
(a) c_sum_bal IS
SELECT open_branch_no,open_branch_name,sum(a_bal)
FROM accounts GROUP BY open_branch_no,open_branch_name;
BEGIN
OPEN c_sum_bal;
LOOP
(b) c_num_bal INTO v_bran_no,(c);
IF c_sum_bal%%NOTFOUND THEN//未找到记录
(d);
END IF;
INSERT INTO branch_sum VALUES(v_bran_no,s_date,v_bran_name,all_balance);
END LOOP;
CLOSE (e);
COMMIT;
EXCEPTION WHEN OTHERS THEN
(f)
END;

-- 创建一个名为 BranchBalanceSum 的存储过程，接受一个输入参数 s_date，类型为 char(8)
CREATE PROCEDURE BranchBalanceSum(IN s_date char(8))
DECLARE
    -- 声明变量 all_balance，用于存储每个网点的账户余额总和
    all_balance  number(14,2);
    -- 声明变量 v_bran_no，用于存储网点编号
    v_bran_no varchar(10);
    -- 声明变量 v_bran_name，用于存储网点名称
    v_bran_name varchar(30);
    -- 声明游标 c_sum_bal，用于查询每个网点的账户余额总和
    CURSOR c_sum_bal IS
        SELECT open_branch_no, open_branch_name, SUM(a_bal)
        FROM accounts
        GROUP BY open_branch_no, open_branch_name;
BEGIN
    -- 打开游标
    OPEN c_sum_bal;
    LOOP
        -- 从游标中提取一行数据，分别赋值给 v_bran_no、v_bran_name 和 all_balance
        FETCH c_sum_bal INTO v_bran_no, v_bran_name, all_balance;
        -- 如果没有更多数据，退出循环
        EXIT WHEN c_sum_bal%NOTFOUND;
        -- 将提取的数据插入到 branch_sum 表中
        INSERT INTO branch_sum VALUES(v_bran_no, s_date, v_bran_name, all_balance);
    END LOOP;
    -- 关闭游标
    CLOSE c_sum_bal;
    -- 提交事务
    COMMIT;
EXCEPTION
    -- 如果发生异常，回滚事务
    WHEN OTHERS THEN
        ROLLBACK;
END;

问题2：当执行存取款交易导致用户余额发生变更时，账户系统需要给用户发送余额变更短信通知。通知内容为"某时间您的账户执行了某交易，交易金额为XX元，交易后账户余额为XX元"。默认系统先更新账户表，后更新账户交易明细表。

下面是余额变更通知功能对应的程序，请补全空缺处的代码。

CREATE TRIGGER BalanceNotice (g) INSERT on (h)
(i)
when (j)=1
DECLARE
v_phone varchar(30);
v_type varchar(30);
v_bal number(142);
v_msg varchar(300);
BEGIN
SELECT phone_no,a_bal INTO v_phone,v_bal FROM accounts
WHERE a_no=(k)
IF NEW.t_type=1 THEN
vtype:=存款；
END IF;
IF NEW.t_type=2 THEN
vtype:=取款;
END IF;
vmsg:=NEW.t_date ||'，您的账户'||NEW.a_no||'上执行了'||v_type||'交易，交易金额为"||to_string（NEW.t_amt)||'元，交易后账户余额为'||to_string（v_bal）||'元';
SendMsg(v_phone,v_msg); //发送短信
END;

-- 创建一个 AFTER INSERT 类型的行级触发器，作用于 trade_details 表
CREATE TRIGGER BalanceNotice 
AFTER INSERT ON trade_details
FOR EACH ROW
WHEN NEW.t_result = 1  -- 仅在交易成功（t_result = 1）时触发
DECLARE
    v_phone VARCHAR(30);     -- 用于存储手机号
    v_type VARCHAR(30);      -- 存储交易类型（存款或取款）
    v_bal NUMBER(14, 2);     -- 存储账户余额
    v_msg VARCHAR(300);      -- 构造短信内容
BEGIN
    -- 从 accounts 表中查找账户号对应的手机号和当前余额
    SELECT phone_no, a_bal INTO v_phone, v_bal 
    FROM accounts
    WHERE a_no = NEW.a_no;

    -- 判断交易类型并设置对应文字描述
    IF NEW.t_type = 1 THEN
        v_type := '存款';
    ELSIF NEW.t_type = 2 THEN
        v_type := '取款';
    END IF;

    -- 构造短信内容
    v_msg := NEW.t_date || '，您的账户' || NEW.a_no || 
             '上执行了' || v_type || '交易，交易金额为' || 
             TO_CHAR(NEW.t_amt) || '元，交易后账户余额为' || 
             TO_CHAR(v_bal) || '元。';

    -- 调用自定义过程 SendMsg 发送短信
    SendMsg(v_phone, v_msg);
END;

问题3：假设日终某网点当日余额汇总操作和同一网点某账户取款交易同一时间发生，对应事务的部分调度序列如表4-1所示。

（1）在事务提交读隔离级别下，该网点的汇总和取款事务是否成功结束？

（2）如果该数据库提供了多版本并发控制协议，两个事务是否成功结束？

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

时间	某网点账户余额汇总事务	账户取款事务
T0	......	......
T1	读第1个账户，汇总
T2	读第2个账户，汇总
T3		第3个账户发生取款交易
T4		commit
T5	读第3个账户，汇总
T6	......
T7	读最后账户，汇总
T8	......	......

（1）答：不能成功，事务提交读隔离级别下，汇总事务读取数据时先加S锁，并等待事务提交才释放S锁，而账户取款事务为写操作，需要加X锁，但此时无法加X锁。

（2）答：可以成功，多版本并发控制，MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。使用MVCC多版本并发控制比锁定模型的主要优点是在MVCC里， 对检索（读）数据的锁要求与写数据的锁要求不冲突， 所以读不会阻塞写，而写也从不阻塞读。

知识点：

---

【5】

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

【说明】

某数据库系统采用数据转储方式对数据和日志文件进行离线备份，用检查点机制进行恢复。假设某部分日志文件如表5-1所示。日志记录内容中，CHECKPOINT表示检查点，<\Ti，START>表示事务Ti开始执行，<\Ti，COMMIT>表示事务Ti提交，<\Ti，D，V1，V2>表示事务Ti将数据项D的值由V1改成V2，CRASH表示系统发生断电故障。假设各数据项的初始值为：I=3，J=6，K=11。

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

日志记录编号	日志记录内容
LSN1	<\T1，START>
LSN2	<\T1，I，3，5>
LSN3	<\T2，START>
LSN4	<\T1，J，6，7>
LSN5	<\T2，I，5，4>
LSN6	<\T1，COMMIT>
LSN7	CHECKPOINT
LSN8	<\T2，J，7，8>
LSN9	<\T3，START>
LSN10	<\T3，K，11，18>
LSN11	<\T4，START>
LSN12	<\T4，K，18，9>
LSN13	<\T2，COMMIT>
LSN14	CRASH

问题1：系统发生故障时，满足持久化要求的事务有哪些？不满足持久化要求的事务有哪些？

答：T1和T2满足持久化要求，T3和T4不满足持计划要求。

问题2：系统恢复后，数据项I、J、K的数值哪些会恢复为初始值？哪些不会恢复为初始值？请给出系统恢复后I、J、K的值。

答：K恢复为初始化，I，J不会恢复为初始值。I=4，J=8，K=11

问题3：如果系统采用先写数据库再记日志的机制，故障发生前已经提交了事务T2，但没有记录到<\T2，COMMIT>。请给出系统恢复后I、J、K的值。

答：I=5，J=7，K=11。（如果系统采用先写数据库再记录日志的机制，则日志文件中没有<\T2，COMMIT>，即T2表现为未提交，在恢复时，需要撤销所以T2的操作）

问题4：基于问题2和问题3的结果，请用50字以内的文字简要说明为什么先写日志机制更优。

答：写数据库和记录日志是两个不同的操作，这两个操作之间可能发生故障。先写日志，按照日志文件恢复只需要执行undo操作，不会影响数据库正确性；反之先写数据库，日志没有记录修改，无法恢复对数据库的修改。