2025年最-新数据库系统工程师考试(案例分析)试题与答案

2025年最新数据库系统工程师考试(案例分析)试题与答案试题一（共25分）某连锁生鲜零售企业2024年启动全链路库存管理数据库系统迭代项目，覆盖全国37个区域仓储中心、2142家线下门店、线上电商前置仓的库存协同管控，核心业务规则梳理如下：1.区域仓储中心的核心属性包含区域ID、区域名称、负责人工号、联系电话、覆盖城市范围；每个仓储中心可管辖若干线下门店，门店属性包含门店ID、门店名称、详细地址、营业面积、店长工号、所属仓储中心ID。2.合作供应商属性包含供应商ID、供应商名称、统一社会信用代码、联系人、联系电话、主营品类集合；生鲜SKU属性包含SKU编号、SKU名称、规格、分类、进价、建议售价、保质期阈值；单个供应商可供应至多1200种SKU，同一种SKU可由最多8家合格供应商供货，供货关系需要额外记录单次合作的供货周期、近6个月供货合格率、约定最大供货量。3.门店员工属性包含员工工号、姓名、岗位、入职日期、联系电话，店长属于员工的特殊子集，仅能对应一家任职门店。4.核心库存流转单据分为三类：入库单关联对应仓储中心、供应商、操作员工，记录入库日期、入库总件数、总金额，单张入库单可包含至多50条入库明细，每条明细对应单一SKU、入库数量、入库单价、生产日期；出库单关联对应门店、仓储中心、操作员工，记录出库日期、出库总件数、总金额，明细对应SKU、出库数量、出库单价；调拨单关联调出仓储中心、调入仓储中心、操作员工，记录调拨日期、调拨原因，明细对应SKU、调拨数量。项目前期初步绘制的ER图遗漏部分实体、联系和关联属性，请结合业务规则完成如下问题：1.1请列出ER图中所有缺失的实体，以及实体间所有多对多联系的完整类型（5分）1.2将“供货”多对多联系转化为等价的独立关系模式，明确标注所有关系的主键和外键（6分）1.3若项目组为了简化表结构，直接将入库明细属性并入入库单关系模式，合并后的入库单关系模式为入库单（入库单ID、仓储中心ID、供应商ID、操作员工ID、入库日期、入库总件数、总金额、SKU编号、入库数量、入库单价、生产日期），请指出该模式存在的三类典型数据异常，分别结合业务场景举例说明（7分）1.4现有门店员工关系模式定义为员工（工号，姓名，岗位，入职日期，联系电话，所属门店ID，门店名称），请判断该关系模式当前满足第几范式，将其无损分解到第三范式（3NF），标注分解后所有关系的主键与外键（7分）试题一参考答案1.1缺失实体为：入库单、出库单、调拨单、入库明细、出库明细、调拨明细。实体间多对多联系共4组：供应商与SKU之间的“供货”多对多联系；入库单与SKU之间通过入库明细实现的多对多联系；出库单与SKU之间通过出库明细实现的多对多联系；调拨单与SKU之间通过调拨明细实现的多对多联系。1.2供货关系对应的独立关系模式为：供货（供应商ID，SKU编号，供货周期，近6个月供货合格率，约定最大供货量）。其中主键为（供应商ID，SKU编号），外键1为供应商ID，参照供应商关系的主键供应商ID；外键2为SKU编号，参照SKU关系的主键SKU编号。1.3合并后的关系存在三类异常：第一类是插入异常，若某供应商已完成资质审核但尚未产生任何入库单数据，则无法在系统中录入该供应商的基础信息，因为入库单ID作为主键的一部分处于空值状态，违反实体完整性约束；第二类是删除异常，若某批次的所有入库单数据因归档操作被删除，会连带删除对应SKU的基础属性信息，导致SKU的进价、保质期阈值等全局依赖数据丢失；第三类是更新异常，若某SKU的建议进价发生调整，该SKU对应的上万条入库明细记录需要同步更新，一旦某条记录更新失败，会出现同一SKU对应多个进价的不一致问题，直接导致库存成本核算错误。1.4该关系模式当前仅满足第一范式（1NF），存在部分函数依赖：所属门店ID→门店名称，而非完全依赖主键工号。无损分解到3NF的两个关系模式为：①员工基础信息（工号，姓名，岗位，入职日期，联系电话，所属门店ID），主键为工号，外键为所属门店ID，参照门店关系的主键门店ID；②门店基础信息（门店ID，门店名称，详细地址，营业面积，店长工号，所属仓储中心ID），主键为门店ID，外键1为店长工号参照员工基础信息的工号，外键2为所属仓储中心ID参照仓储中心关系的主键区域ID。该分解不存在部分函数依赖和传递函数依赖，完全符合3NF约束。试题二（共25分）该库存管理系统上线后，核心业务高峰时段（每日早7-9点生鲜补货、晚17-19点线下店出库峰值）并发事务数最高可达1.27万笔/秒，核心事务定义如下：T1为SKU出库扣减事务，逻辑为读取当前仓储中心SKUA的库存数X，判断X≥出库申请数Y后执行X=X-Y，写回新库存X，生成出库流水记录；T2为SKU入库事务，逻辑为读取当前仓储中心SKUA的库存数X，执行X=X+入库数量Z，写回新库存X，生成入库流水记录；T3为日度库存快照统计事务，逻辑为读取当前仓储中心所有SKU的库存数生成统计报表，不对任何库存数据执行修改操作。完成如下并发控制与故障恢复相关问题：2.1假设SKUA初始库存X=100，某条并发调度序列的执行顺序为：T1读取X→T2读取X→T1执行X=X-30→T2执行X=X+50→T1写入新X→T2写入新X，请指出该调度存在的典型并发异常类型，计算最终得到的X实际值，说明该异常对生鲜零售业务的直接影响（7分）2.2项目组初期采用2级封锁协议规避并发异常，请说明2级封锁协议的具体定义，判断该协议能否解决2.1中描述的并发异常问题，如果不能，请说明需要升级至哪一级封锁协议，以及该协议通过什么规则彻底规避该类异常（8分）2.3系统采用ARIES日志机制实现故障恢复，某日系统发生非计划断电导致事务故障，断电前日志末尾的记录序列为：<T1start>、<T1,X,100,70>、<T2start>、<T2,X,100,150>、<T3start>、<T3,库存快照生成完成>，最近一次检查点记录显示已提交事务集合为空，请说明系统重启后的故障恢复三步操作流程，分别对T1、T2、T3三个事务执行什么恢复操作，最终SKUA的正确X值为多少（6分）2.4为了避免高峰时段统计类事务阻塞写事务，项目组引入快照隔离机制优化并发性能，请说明快照隔离的核心实现原理，以及相较于传统两阶段锁调度的性能优势（4分）试题二参考答案2.1该调度属于典型的“丢失修改”并发异常，两个事务对同一数据的写入操作互相覆盖，最终得到的X值为150。该异常直接导致SKUA的实际库存比正确值多30，相当于已经成功出库的30件生鲜商品没有从库存中扣减，会出现库存账面数远大于实际存量的“虚库存”问题，直接触发超卖，导致线上线下多端订单履约失败，给生鲜企业带来客诉和商品损耗的双重损失。2.22级封锁协议的定义为：事务在修改数据之前必须先对其加X排他锁，直到事务结束才释放；事务读取数据之前必须先对其加S共享锁，读完后即可释放S锁。2级封锁协议无法解决2.1中的丢失修改问题，仅能解决“读脏数据”和“不可重复读”两类异常。需要升级到3级封锁协议，即改进的严格两阶段封锁协议，该协议要求事务读取数据加的S共享锁必须持续到事务结束才能释放，同时写锁也保持到事务结束，通过“先执行写操作的事务先持有X锁”的规则，确保两个修改同一数据的事务不能并行执行写操作，后续事务必须等前一个事务完全提交释放锁后才能进入临界区执行修改，彻底避免两个事务的写入操作互相覆盖的丢失修改问题。2.3故障恢复的三步流程为：第一步执行分析阶段，从最近检查点开始扫描日志，构建活跃事务列表，确定三个事务均未提交；第二步执行UNDO阶段，反向回滚所有未提交的写事务的修改操作，将数据恢复到修改前的初始状态；第三步执行REDO阶段，按照日志顺序重放所有已提交事务的修改，确保所有已提交操作的数据持久化。本次场景中三个事务均未提交，因此需要对T1、T2的修改操作执行UNDO回滚，将X恢复到初始值100；对T3的快照生成操作执行回滚丢弃，最终SKUA的正确X值为100。2.4快照隔离的核心原理是：事务启动时系统会为其分配一个基于全局时间戳的数据快照，所有读操作全部直接读取快照版本，不需要对数据加任何读锁；写操作仅对数据的新版本执行修改，不会阻塞其他事务的读操作。该机制下读事务和写事务完全不会互相阻塞，并发吞吐量相比传统两阶段锁调度可提升3-8倍，完全满足高并发场景下统计类事务低延迟执行的需求。试题三（共25分）企业需要搭建库存效期智能预警模块，支撑临期商品提前促销调度，核心关联的关系模式如下：仓储中心（w_id,w_name,w_addr,director,tel），SKU（sku_id,sku_name,category,spec,cost_price,suggest_price,shelf_life），库存流水（流水id,w_id,sku_id,in_out_flag,num,produce_date,operate_time），其中in_out_flag=1代表入库操作、in_out_flag=-1代表出库操作，SKU当前库存总量等于同一w_id、同一sku_id下所有库存流水的num字段求和，效期剩余天数=SKU的保质期阈值shelf_life减去（当前系统日期减去商品生产日期produce_date）的日期间隔。完成如下SQL设计与模式优化问题：3.1编写SQL语句，查询2025年3月全品类中效期剩余天数小于等于7天的所有SKU在各区域仓储中心的当前库存数量，输出字段为w_id、w_name、sku_id、sku_name、current_num、remaining_days，按remaining_days升序排序（7分）3.2编写SQL语句，统计2025年第一季度每个供应商的供货及时率，其中供货及时率=按时完成入库的单据数量/该供应商总入库单据数量，按时的定义为入库日期不晚于采购单约定的到货日期，输出字段为供应商id、供应商名称、总入库单数、及时单数、供货及时率，按供货及时率降序排序（7分）3.3现有线上订单关系模式Order（order_id,user_id,sku_id,sku_num,order_amount,order_time,user_name,user_tel,sku_name,sku_price,store_id,store_name），请梳理该模式的所有函数依赖，指出其存在的三类数据异常，将其无损分解为满足BCNF的多个独立关系模式，标注每个模式的主键和外键（6分）3.4该效期预警查询语句上线后，单节点数据库执行耗时长达12.7s，远低于预期2s以内的性能要求，请从索引设计、查询改写、执行计划优化三个维度给出可落地的具体优化方案（5分）试题三参考答案3.1参考SQL语句如下：```sqlSELECTw.w_id,w.w_name,s.sku_id,s.sku_name,SUM(i.num)AScurrent_num,s.shelf_life-DATEDIFF(CURDATE(),duce_date)ASremaining_daysFROM仓储中心wJOIN库存流水iONw.w_id=i.w_idJOINSKUsONi.sku_id=s.sku_idWHEREi.operate_time>='2025-03-0100:00:00'ANDi.operate_time<'2025-04-0100:00:00'GROUPBYw.w_id,w.w_name,s.sku_id,s.sku_name,s.shelf_life,duce_dateHAVING(s.shelf_life-DATEDIFF(CURDATE(),duce_date))<=7ANDSUM(i.num)>0ORDERBYremaining_daysASC;```3.2参考SQL语句如下：```sqlSELECTvider_id,vider_name,COUNT(DISTINCTi.bill_id)AStotal_bill,SUM(CASEWHENi.inbound_date<=p_arr.arrive_dateTHEN1ELSE0END)ASontime_bill,ROUND(SUM(CASEWHENi.inbound_date<=p_arr.arrive_dateTHEN1ELSE0END)/COUNT(DISTINCTi.bill_id),4)ASontime_rateFROM供应商pLEFTJOIN入库单iONvider_id=vider_idLEFTJOIN采购单p_arrONi.purchase_bill_id=p_arr.purchase_bill_idWHEREi.inbound_date>='2025-01-01'ANDi.inbound_date<'2025-04-01'GROUPBYvider_id,vider_nameORDERBYontime_rateDESC;```3.3该模式的函数依赖包括：order_id→user_id,sku_id,sku_num,order_amount,order_time,store_id；user_id→user_name,user_tel；sku_id→sku_name,sku_price；st