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

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

  • Caijxlinux
  • 数据库
  • 21天前
  • 77热度
  • 0评论

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

【1】

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

【说明】

某新能源汽车公司为了提升效率，需要开发一个汽车零件采购系统。请根据下述需求描述完成该系统的数据库设计。

【需求描述】

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

（2）记录零件的信息，包括零件的编码、名称和价格。

（3）记录车型信息，包括车型的编号、名称和规格。

（4）记录零件采购信息。某个车型的某种零件可以从多家供应商采购，某种零件也可以被多个车型采用，某家供应商也可以供应多种零件，还包括采购数量和采购日期。

【概念结构设计】

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

零件供应ER

【逻辑结构设计】

供应商（名称，地址，电话）
零件（编码、名称、价格）
车型（编号，名称，规格）
采购（车型编号，供应商名称，(a)，(b)，采购日期）

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

零件采购ER（完整）

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

答：a：零件编码，b：采购数量。主键：（车型编号，供应商名称，零件编号，采购日期）。外键：车型编号，供应商名称，零件编号。

问题3：该汽车公司现新增如下需求，记录车型在全国门店的销售情况。门店信息包括门店的编号、地址和电话。销售包括销售数量和销售日期等。对原有设计进行以下修改以实现该需求：

（1）在图1-1中体现门店信息及车型销售情况，并标明新增的实体和联系，以及必要属性。

销售ER

（2）给出新增加的关系模式，并标注主键和外键完整性约束。

门店（门店编号，地址，电话）主键：门店编号。外键：无。
销售（门店编号，车型编号，销售日期，销售数量）主键：（门店编号，车型编号，销售日期）。外键：门店编号、车型编号

【2】

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

【说明】

一汽车厂商对配件进行统一管理，设计了相应的数据库，其中一个表记录了维修配件的使用信息。其表结构如下：

维修配件使用表（车牌号，维修时间，配件编码，配件名称，配件供应商，配件仓库编码，仓库地址，维修配件数量）

其中，车牌号和配件编码满足唯一性。假设同一辆车在同一次维修情况下可能需要多种维修配件；一种配件只能存放于一个配件仓库，一种配件只能由一个配件供应商提供。维修时间精确到秒。

问题1：题干中给出的维修配件使用表存在数据冗余，请给出具体的冗余属性并说明因此会出现哪些异常？

答：冗余属性：配件名称，配件供应商，配件仓库编码，仓库地址。存在插入、修改和删除异常。

问题2：维修配件表是否满足BCNF？如果不满足请对其进行模式分解，使分解后的关系模式满足BCNF，并标记出主键和外键。

答：不满足BCNF。

维修配件表（车牌号，维修时间，配件编码，维修配件数量）主键：（车牌号，维修时间，配件编码）。外键：配件编码。
配件表（配置编码，配件名称，配件供应商，配件仓库编码）主键：配件编码。外键：配件仓库编码。
仓库表（配件仓库编码，仓库地址）主键：配件仓库编码。外键：无。

【3】

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

【说明】

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

学生：STUDENT（Sno，Sname，Ssex，Sage，Sdept），各属性分别表示学号、姓名、性别、年龄、所在系名。
课程：COURSE（Cno，Cname，Cpno，Ccredit），各属性分别表示课程号、课程名、先修课的课程号、学分。
选课：SC（Sno，Cno，Grade），各属性分别表示学号、课程号、成绩。
有关关系模式的说明如下：
（1）下划线标出的属性是表的主键
（2）课程名取值唯一
根据以上描述，回答下列问题，将SQL语句的空缺部分补充完成。

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

CREATE TABLE COURSE(
Cno CHAR(4) PRIMARY KEY,
Cname CHAR(30) (a),
Cpno CHAR(4) REFERENCES (b),(c),
Ccredit INT
);
CREATE TABLE COURSE(
  Cno CHAR(4) PRIMARY KEY,                    -- 课程号为主键，保证唯一性且非空
  Cname CHAR(30) UNIQUE,                      -- 课程名唯一，对应题意“课程名取值唯一”
  Cpno CHAR(4) REFERENCES COURSE(Cno),        -- Cpno 是先修课程号，外键引用同一张表的课程号（自引用）
  Ccredit INT                                 -- 学分，整数
);

问题2：有一门课程号为"C036"的新开课要求所有学生选修。该课的基本信息已经录入课程表COURSE中，现需在选课表SC中插入该课的选课记录。实现该功能的SQL语句如下，请补全。

(d) INTO SC (Sno,(e))
SELECT Sno,(f)
FROM (g);
INSERT INTO SC (Sno,Cno)
SELECT Sno,'C036'
FROM STUDENT;

问题3：查询每一门课程的间接先修课（先修课的先修课），要求输出课程号和间接先修课的课程号。即使某门课程没有先修课，也需要输出，不过其间接先修课为空。此功能由下面的SQL语句实现，请补全。

SELECT K1.Cno,(h)
FROM COURSE K1(i)
OUTER JOIN
COURSE K2(j)(k);
SELECT K1.Cno,K2.Cpno
FROM COURSE K1 LEFT
OUTER JOIN
COURSE K2 ON K1.Cpno=K2.Cno;

问题4：查询选修了课程表中已有全部课程的学生，要求输出学号和姓名。此功能由下面的SQL语句实现，请补全。

SELECT Sno，Sname FROM STUDENT
WHERE NOT EXISTS
(SELECT * FROM (l)
WHERE (m)
(SELECT * FROM (n)
WHERE (o)));
SELECT Sno，Sname FROM STUDENT
WHERE NOT EXISTS
(SELECT * FROM COURSE
WHERE NOT EXISTS
(SELECT * FROM SC
WHERE Sno=STUDENT.Sno AND Cno=COURSE.Cno);

知识点：

要理解外层子查询的结果，关键在于明确 “外层子查询在检查什么” 以及 “外层 NOT EXISTS 如何影响主查询”。我们通过一个具体场景，分步拆解逻辑：

📜 场景设定

  • 学生表（STUDENT）：张三（Sno=1）、李四（Sno=2）
  • 课程表（COURSE）：数学（Cno=101）、英语（Cno=102）
  • 选课表（SC）：
    • 张三选修了数学和英语（两门课）
    • 李四只选修了数学（一门课）

🧠 外层子查询的逻辑目标

问题：对每个学生来说，是否存在至少一门课程是他 没选修 的？

  • 如果存在 → 外层子查询结果为 TRUE → 主查询的 NOT EXISTS 为 FALSE → 排除该学生。
  • 如果不存在 → 外层子查询结果为 FALSE → 主查询的 NOT EXISTS 为 TRUE → 保留该学生。

🛠️ 分步执行示例（以李四为例）

1. 主查询选中李四（Sno=2）

SELECT Sno, Sname FROM STUDENT WHERE ...  -- 当前检查李四

2. 外层子查询遍历所有课程

SELECT * FROM COURSE C
WHERE NOT EXISTS (
  SELECT * FROM SC
  WHERE SC.Sno = 2 AND SC.Cno = C.Cno  -- 检查李四是否选了该课程
)
  • 课程数学（Cno=101）：
    • 内层子查询：检查 SC 表中是否存在 Sno=2 且 Cno=101 的记录。
    • 结果：存在 → 内层 NOT EXISTS 为 FALSE → 外层 WHERE 条件不成立。
  • 课程英语（Cno=102）：
    • 内层子查询：检查 SC 表中是否存在 Sno=2 且 Cno=102 的记录。
    • 结果：不存在 → 内层 NOT EXISTS 为 TRUE → 外层 WHERE 条件成立。

3. 外层子查询的最终结果

  • 外层子查询会找到 英语（Cno=102） 这门课程（因为李四未选修）。
  • 因此，外层子查询的结果是 存在至少一门未选修的课程 → 返回 TRUE。

4. 主查询的 NOT EXISTS 判断

  • 主查询的条件是 WHERE NOT EXISTS (外层子查询结果)。
  • 因为外层子查询结果为 TRUE → NOT EXISTS(TRUE) → 结果为 FALSE。
  • 李四被排除。

🌟 关键结论

学生情况 外层子查询结果（是否有未选修的课程？） 主查询的 NOT EXISTS 结果 是否保留学生？
选修了所有课程（张三） 无 → FALSE TRUE ✅ 保留
未选修某门课程（李四） 有 → TRUE FALSE ❌ 排除

💡 通俗理解

  • 外层子查询 就像一个“侦探”，它的任务是：
    找出学生未选修的课程。

    • 如果侦探发现至少一门未选修的课程 → 向主查询报告“发现异常”（结果为 TRUE）。
    • 如果侦探遍历所有课程后一无所获 → 向主查询报告“一切正常”（结果为 FALSE）。

  • 主查询的 NOT EXISTS 则根据侦探的报告决定是否保留该学生：

    • 如果侦探报告“发现异常”（TRUE）→ NOT EXISTS(TRUE) 为 FALSE → 排除学生。
    • 如果侦探报告“一切正常”（FALSE）→ NOT EXISTS(FALSE) 为 TRUE → 保留学生。

❗ 特别注意

  • 外层子查询的“存在性”判断：
    只要找到 一门未选修的课程，外层子查询就会立即返回 TRUE（无需检查剩余课程），这是一种优化（短路逻辑）。

  • 关联子查询的依赖关系：
    外层子查询中的 C.Cno（课程号）和主查询中的 STUDENT.Sno（学号）是动态关联的，每个学生的检查会重新执行一次外层子查询。

通过这种“反向排除”的机制，外层子查询巧妙地实现了 “检查是否存在未满足条件的情况”，最终筛选出符合要求的学生。虽然逻辑绕，但理解了执行顺序后，就能清晰看到它的精妙之处！

【4】

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

【说明】

某企业内部信息系统部分简化后的关系模式如下：

员工表：EMPLOYEES(Eid，Ename，Address，Phone，Jid)，属性含义分别为：员工编号、员工姓名、家庭住址、联系电话、岗位级别编号。
岗位级别表：JOB_LEVELS(Jid，Jname，Jbase_salary)，属性含义分别为：岗位级别编码、岗位名称、岗位基本工资。
员工工资表：SALARY(Eid，attendance_wage，merit_pay，overtime_wage，salary，text，year，month)，属性含义分别为：员工编码、考勤工资、绩效工资、加班工资、最终工资、税、年份、月份。

该企业在每月25日计算员工的工资。首先是根据考勤系统以及绩效系统中的数据计算出员工的考勤、绩效和加班工资，存入到员工工资表，其次结合员工的岗位基本工资，计算出最终工资完成对员工工资表记录的更新。最后依据员工工资表完成工资的发放。

问题1：下面是月底25日计算某员工最终工资的存储过程程序，请补全空缺处的代码。

CREATE PROCEDURE SalaryCalculation((a)) empld char(8),IN iYEAR number(4),IN iMonth number(2))
DECLARE
attendance number(14.2);
merit number(14.2);
overtime number(14.2);
base number(14.2);
all_salary number(14.2);
BEGIN
SELECT attendance_wage,merit_pay,overtime_wage
INTO (b)
FROM SALARY
WHERE Eid= empld FOR UPDATE
SELECT Jbase_salary INTO :base
FROM EMPLOYEES T1,(c)
WHERE T1.Jid=T2.Jid AND T1.Eid= empld;
all_salary:=attendance + merit + overtime + base;
UPDATE SALARY SET salary = :all_salary
WHERE (d) AND year = iYear AND month = iMonth;
(e)
EXCEPTION
WHEN OTHERS THEN
(f)
END;
CREATE PROCEDURE SalaryCalculation(IN empld char(8),IN iYEAR number(4),IN iMonth number(2))
DECLARE
attendance number(14.2);
merit number(14.2);
overtime number(14.2);
base number(14.2);
all_salary number(14.2);
BEGIN
SELECT attendance_wage,merit_pay,overtime_wage
INTO :attendance,:merit,:overtime
FROM SALARY
WHERE Eid= empld FOR UPDATE
SELECT Jbase_salary INTO :base
FROM EMPLOYEES T1,JOB_LEVELS T2
WHERE T1.Jid=T2.Jid AND T1.Eid= empld;
all_salary:=attendance + merit + overtime + base;
UPDATE SALARY SET salary = :all_salary
WHERE Eid=empld AND year = iYear AND month = iMonth;
COMMIT
EXCEPTION
WHEN OTHERS THEN
ROLLBACK
END;

问题2：为了防止对员工工资表的非法修改（包括内部犯罪），系统特意规定了员工工资表修改的业务规则：对员工工资表的修改只能在每月的25日的上班时间进行。下面是员工工资表修改业务规则对应的程序，请补全空缺处的代码。

CREATE TRIGGER CheckBusinessRule
(g) INSERT OR DELETE OR (h) ON SALARY
FOR EACH (i)
BEGIN
IF (TO_CHAR(sysdate,DD) <> (j))
OR(to number (TO_CHAR (svsdate,HH24))
(k) BETWEEN 8 AND 18) THEN
Raise_Error;//抛出异常
END IF
END;
CREATE TRIGGER CheckBusinessRule
BEFORE INSERT OR DELETE OR UPDATE ON SALARY
FOR EACH ROW
BEGIN
IF (TO_CHAR(sysdate,DD) <> '25'
OR(to number (TO_CHAR (svsdate,HH24))
NOT BETWEEN 8 AND 18) THEN
Raise_Error;//抛出异常
END IF
END;

问题3：人事部门具有每月对员工进行额外奖罚的权限，该奖罚也反映到员工的最终工资上。假设当某月计算一位员工的最终工资时，同一时间人事部门对该员工执行了奖励2000元的事务操作，对应事务的部分调度序列如表4-1所示。

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

时间 计算最终工资事务 人事部门奖罚事务
T0 ...... ......
T1 读取考勤、绩效和加班工资
T2 根据奖罚更新最新工资
T3 commit
T4 读取岗位工资
T5 计算最终工资并写入
T6 commit
T7 ......

（1）请说明该事务调度存在哪种并发问题？

答：修改丢失。计算最终工资事务覆盖了人事部门奖励事务对最终工资值的更新。

（2）采用2PL是否可以解决该并发问题？是否会产生死锁？

答：可以解决并发问题，2PL并不要求事务必须一次将所有需求的数据加锁，可能会出现事务A 加锁字段X，事务A 加锁字段X，然后 A 想加锁Y，B 想加锁X，就会互相等待对方释放，形成死锁。

【5】

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

【说明】

某装备的组装过程需要经过多道程序，由于工作空间狭小，只能同时有一人在操作间工作，所以在每道工序之间需要先完成使用配件的出库后，操作人员携带配件到操作间进行安装工作，安装过程中需要扫描配件编码以自动记录该配件的安装情况。

假使存在三个事务用于处理某一类配件的某次安装实施，事务T1负责出库登记，T2负责安装登记，T3负责将未使用的配件重新入库。所以三个事务执行完成后，T1出库的数量应等于T2安装的数量与T3重新入库的数量之和。数据项I记录配件的库存数量，数据项J记录成功安装的数量。

某次组装过程出库了12个配件，安装了6个。假设数据库系统采用检查点机制对故障进行恢复，部分日志文件如表5-1所示。日志记录内容中：<\Ti，START>表示事务T i开始执行，<\Ti，COMMIT>表示事务Ti提交，<\Ti，D，V1，V2>表示事务Ti将数据项D的值由V1修改为V2。例如<\Ti，I，22，3>表示事务T1将数据项I的数值从22修改为3。<\Ti，D，V>表示将事务Ti回滚数据项D的值回滚到V。<\Ti，ABORT>表示事务Ti回滚失败。CRASH表示系统磁盘出错。请回答以下问题。

日志记录编号 日志记录内容
LSN1 <\T1,START>
LSN2 <\T1,I,20,8>
LSN3 <\T2,START>
LSN4 <\T2,J,0,1>
LSN5 <\T2,J,1,5>
LSN6 <\T3,START>
LSN7 <\T2,J,5,6>
LSN8 <\T1,COMMIT>
LSN9 CHECKPOINT
LSN10 <\T2,COMMIT>
LSN11 <\T3,I,8,(a)>
LSN13 CRASH
LSN14 (b)
LSN15 (c)

问题1：请用100字以内的文字简要说明数据库系统常见的故障类型，并说明表5-1的日志记录表明数据库出现哪种类型的故障。

答：数据库系统的常见故障：事务故障：指程序执行错误而引起事务非预期、异常终止的故障。系统故障：指硬件故障、软件（如DBMS、OS或应用程序）漏洞导致内存信息丢失，影响正在执行的事务，但未破坏存储在外存上的信息。介质故障：指数据库的存储介质发生故障，如磁盘损坏、瞬间强磁场干扰等。日志记录说明出现介质故障（CRASH系统磁盘出错）

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

答：需要重做（Redo）的事务：T2。需要撤销（Undo）的事务：T3。

问题3：根据题干中所描述的业务逻辑，填写表5-1日志记录中的空白（a）；请给出Undo恢复的补偿日志记录填写空白（b）和（c）。

答：（a）：14。（b）：<\T3,I,8>。（c）：<\T3,ABORT>。

知识点：

✅ 问题分析

一、业务规则复述：

事务之间的关系如下：

  • T1：配件出库，从库存 I 中扣除。
  • T2：配件安装，更新安装数量 J。
  • T3：未使用配件入库，更新库存 I。

最终要求： T1 出库数量 = T2 安装数量 + T3 入库数量 即： 出库数量 = 安装数量 + 退库数量 题中给出：

  • 出库了 12 个（LSN2：20 → 8）
  • 安装了 6 个（J: 0 → 6 通过 LSN4、5、7）

所以：

  • 退回入库数量应为 6
  • 即 T3 将库存 I 从 8 → 14

🧩 解题：

🔹 填写 (a)

根据上文推理，T3 把未使用的 6 个配件重新入库，I 的值从 8 改为 14。

  • ✅ (a) 应为 14

因此：

LSN11: <T3, I, 8, 14>

🔹 系统崩溃时机：

LSN13: CRASH

  • 所以：T3 未提交

需要对 T3 进行 Undo 恢复操作。

🔹 填写 (b) 和 (c) —— Undo 补偿日志（CLR）

恢复时要写补偿日志（CLRs，Compensation Log Records）来记录回滚操作：

  • T3修改了 I：8 → 14，未提交，需要恢复为 8
  • 所以 Undo 操作将 I 从 14 改回 8

格式：<Ti, D, V> 表示将 D 的值恢复到 V

✅ 填空：

(b): <T3, I, 8> (c): <T3, ABORT>