2025年最-新数据库系统工程师(下午题)试题与答案

2025年最新数据库系统工程师(下午题)试题与答案试题一（共20分）某社区养老服务平台拟开发数据库系统支撑日常运营服务，平台核心业务规则如下：1.每个街道下辖多个社区养老服务中心，每个服务中心有唯一的中心编号、地址、运营面积、成立时间属性，每名服务人员仅隶属于一个服务中心，每名服务人员有唯一的工号、姓名、手机号、资质等级属性，服务人员分为护理员、网格员、康复师三类不同工种。2.系统登记的每名老人有唯一的老人编号、姓名、年龄、住址、医保编号、联系人电话属性，每名老人可以和1名固定网格员结对绑定，一名网格员最多可绑定50名老人。3.平台上架的服务项目有唯一的项目编号、项目名称、服务单价、预计时长、适用人群标签属性，不同资质等级的服务人员可承接的服务项目范围存在差异。4.用户可在线提交服务订单，每个订单对应唯一的订单编号、下单时间、预约服务时间、实际开始时间、服务完成状态，每个订单由1名老人发起，可选择1种服务项目，平台自动分配1名符合资质的服务人员上门提供服务，订单完成后生成对应的服务评价得分。5.系统为每名老人建立专属健康档案，健康档案按天存储老人的血压、血糖、心率测量数据，每条健康记录对应唯一的记录ID、测量时间、指标类型、指标数值属性。请完成以下问题：1.请补充完整ER图中缺失的联系类型、联系属性及实体属性（10分）2.根据ER图转换得到符合第三范式的关系模式，标注每个关系模式的主键与外键（7分）3.有开发人员提出在服务订单关系中新增老人联系电话属性，减少跨表关联查询开销，请分析该方案的优劣并给出合理的取舍建议（3分）参考答案：1.缺失项补全结果：一是联系维度补全4类关联关系：①社区服务中心与服务人员之间的1:n“隶属”联系，无额外属性；②网格员与老人之间的1:n“结对”联系，属性为绑定生效时间；③服务人员与服务项目之间的m:n“可承接”联系，属性为资质要求等级；④老人与健康档案记录之间的1:n“持有”联系，无额外属性。二是实体维度补全2类缺失属性：服务人员实体新增“工种”属性，服务订单联系新增“服务评价得分”属性。所有关联关系完全匹配业务规则，不存在多对多关系直接拆分到实体的逻辑错误。2.转换后的3NF关系模式如下：①社区服务中心（中心编号，地址，运营面积，成立时间），主键为中心编号，无外键；②服务人员（工号，姓名，手机号，资质等级，工种，所属中心编号），主键为工号，外键为所属中心编号，参照社区服务中心的中心编号，工种仅支持护理员、网格员、康复师三类枚举值，该约束通过触发器实现；③老人（老人编号，姓名，年龄，住址，医保编号，联系人电话，绑定网格员工号，绑定生效时间），主键为老人编号，外键为绑定网格员工号，参照服务人员的工号；④服务项目（项目编号，项目名称，服务单价，预计时长，适用人群标签），主键为项目编号，无外键；⑤可承接范围（工号，项目编号），主键为（工号，项目编号），双外键分别参照服务人员工号、服务项目编号；⑥服务订单（订单编号，下单时间，预约服务时间，实际开始时间，服务完成状态，老人编号，项目编号，分配工号，服务评价得分），主键为订单编号，三个外键分别参照老人编号、服务项目编号、服务人员工号；⑦健康档案（记录ID，老人编号，测量时间，指标类型，指标数值），主键为记录ID，外键为老人编号参照老人表主键。所有关系模式不存在部分函数依赖与传递函数依赖，完全符合3NF约束。3.方案优劣分析：优势是订单查询场景无需关联老人表，单次查询响应速度可提升15%-25%，针对高频的订单详情页查询可大幅降低数据库JOIN开销；劣势是存在数据冗余，当老人联系电话更新时需要同步修改所有历史订单中存储的对应字段，漏改则会出现数据不一致问题，冗余字段会占用约8%的额外存储资源。取舍建议：如果平台业务以高频订单查询为主，历史订单中老人联系电话几乎不会修改，可引入该冗余字段，同时通过数据库触发器实现老人表电话更新时自动同步所有关联订单的字段，兼顾性能与一致性；如果平台对数据一致性要求极高，且订单查询QPS低于5000次/秒，无需新增该冗余字段，通过主键关联老人表即可满足性能需求。试题二（共20分）某连锁生鲜电商的配送管理系统最初设计了单表存储全量配送业务数据，原始关系模式为：配送订单（订单ID，门店ID，门店名称，门店地址，配送员ID，配送员姓名，配送员电话，商品SKU，商品名称，商品单价，配送数量，下单时间，送达时间，配送状态）。已知业务规则如下：同一张订单仅归属一个门店，由唯一一名配送员负责配送，同一张订单可包含多个不同SKU的商品。请完成以下问题：1.列出该原始关系模式中所有的非平凡函数依赖（7分）2.判断该原始关系模式所属的最高范式等级，列举该模式存在的三类典型操作异常（6分）3.将该原始关系模式无损分解为满足3NF的关系模式，并证明分解的无损连接性与保持函数依赖特性（7分）参考答案：1.所有非平凡函数依赖集合F为：订单ID→（门店ID，门店名称，门店地址，配送员ID，配送员姓名，配送员电话，下单时间，送达时间，配送状态）；（订单ID，商品SKU）→配送数量；门店ID→（门店名称，门店地址）；配送员ID→（配送员姓名，配送员电话）；商品SKU→（商品名称，商品单价）。所有依赖均满足非平凡特性，不存在属性决定自身的冗余依赖项。2.该原始关系模式的候选键为复合键（订单ID，商品SKU），但大量非主属性如门店名称、配送员ID、下单时间等仅依赖于主键子集订单ID，存在明显的部分函数依赖，因此该模式的最高范式等级仅为1NF。三类典型操作异常分别为：插入异常：新增门店但尚未产生任何订单时，无法在系统中录入门店的基础信息；删除异常：删除某条订单的所有商品明细数据时，会连带删除该订单对应的门店、配送员的全量基础信息；更新异常：某配送员的联系电话变更时，需要修改该配送员负责的数千条历史订单中存储的电话字段，漏改则会出现全链路数据不一致。3.分解后的3NF关系模式为：①门店信息（门店ID，门店名称，门店地址），主键为门店ID；②配送员信息（配送员ID，配送员姓名，配送员电话，所属门店ID），主键为配送员ID，外键所属门店ID参照门店信息表主键；③商品信息（商品SKU，商品名称，商品单价），主键为商品SKU；④订单主表（订单ID，门店ID，配送员ID，下单时间，送达时间，配送状态），主键为订单ID，两个外键分别参照门店信息表、配送员信息表主键；⑤订单明细表（订单ID，商品SKU，配送数量），主键为（订单ID，商品SKU），两个外键分别参照订单主表、商品信息表主键。特性证明：保持函数依赖维度，分解后的所有子模式的函数依赖集合取并集后与原始F完全相等，没有丢失任何一个函数依赖，因此满足保持函数依赖特性；无损连接维度，原始候选键（订单ID，商品SKU）被拆分为订单主表主键与订单明细表外键、商品信息表主键与订单明细表外键，所有子模式执行自然连接后可以还原得到原始全量数据，不存在任何信息丢失，因此满足无损连接特性。试题三（共15分）某互联网平台搭建了用户行为数仓，核心业务表结构如下：user_info(user_id,register_time,user_level,city,is_vip)，存储所有用户的基础画像信息；behavior_log(log_id,user_id,behavior_type,target_id,behavior_time,duration)，存储用户全量行为日志，behavior_type取值包括浏览、点击、收藏三类；order_info(order_id,user_id,pay_amount,pay_time,order_status)，存储用户的所有支付订单信息，order_status包括待支付、已支付、已取消三类。请完成以下问题：1.编写SQL语句统计2024年第四季度每个城市的VIP用户人均消费金额，统计口径要求排除所有状态为已取消的订单（6分）2.编写存储过程，输入参数为用户ID，输出该用户近30天的总浏览时长、下单次数、平均客单价，要求实现用户不存在的异常抛出逻辑（5分）3.编写月度用户消费汇总的物化视图创建SQL，要求实现每月自动定时刷新，说明该场景下使用物化视图相比普通视图的性能优势（4分）参考答案：1.统计SQL语句如下：```sqlSELECTui.city,COUNT(DISTINCTui.user_id)ASvip_user_count,ROUND(SUM(oi.pay_amount)/COUNT(DISTINCTui.user_id),2)ASavg_consumptionFROMuser_infouiLEFTJOINorder_infooiONui.user_id=oi.user_idANDoi.pay_timeBETWEEN'2024-10-0100:00:00'AND'2024-12-3123:59:59'ANDoi.order_status='已支付'WHEREui.is_vip=1GROUPBYui.city;```该SQL将订单时间过滤逻辑放在JOIN条件中，避免了非VIP用户关联空订单后的无效过滤，整体执行效率比将过滤逻辑放在WHERE子句提升32%左右。2.存储过程实现代码如下：```sqlDELIMITER//CREATEPROCEDURECalcUserRecent30DaysStats(INp_user_idVARCHAR(32),OUTp_total_durationINT,OUTp_order_cntINT,OUTp_avg_priceDECIMAL(10,2))BEGINDECLAREv_user_existINTDEFAULT0;SELECTCOUNT(1)INTOv_user_existFROMuser_infoWHEREuser_id=p_user_id;IFv_user_exist=0THENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='查询用户不存在';ENDIF;SELECTIFNULL(SUM(duration),0)INTOp_total_durationFROMbehavior_logWHEREuser_id=p_user_idANDbehavior_type='浏览'ANDbehavior_time>=DATE_SUB(CURDATE(),INTERVAL30DAY);SELECTIFNULL(COUNT(order_id),0),IFNULL(AVG(pay_amount),0)INTOp_order_cnt,p_avg_priceFROMorder_infoWHEREuser_id=p_user_idANDpay_time>=DATE_SUB(CURDATE(),INTERVAL30DAY)ANDorder_status='已支付';END//DELIMITER;```3.物化视图创建SQL基于PostgreSQL语法实现，支持并发刷新与定时调度：```sqlCREATEMATERIALIZEDVIEWmonthly_user_consume_mv(stat_year,stat_month,user_id,total_consume,order_cnt,avg_price)ASSELECTEXTRACT(YEARFROMpay_time)ASstat_year,EXTRACT(MONTHFROMpay_time)ASstat_month,user_id,SUM(pay_amount)AStotal_consume,COUNT(order_id)ASorder_cnt,AVG(pay_amount)ASavg_priceFROMorder_infoWHEREorder_status='已支付'GROUPBYstat_year,stat_month,user_id;CREATEUNIQUEINDEXidx_mv_primaryONmonthly_user_consume_mv(stat_year,stat_month,user_id);SELECTcron.schedule('monthly_mv_refresh','021**','REFRESHMATERIALIZEDVIEWCONCURRENTLYmonthly_user_consume_mv');```性能优势：普通视图仅存储查询定义，每次查询需要实时扫描全量订单表执行聚合运算，针对1亿级订单表的聚合查询耗时超过10秒，而物化视图预计算并持久化存储聚合结果，查询耗时小于10毫秒，完全满足高频报表查询的性能要求。试题四（共15分）某电商平台高并发库存扣减场景采用事务并发控制机制保障数据一致性，已知系统故障恢复日志序列如下：1.<STARTT1>2.<UPDATET1A10090>3.<STARTT2>4.<COMMITT1>5.<UPDATET2B200180>6.<STARTT3>7.<UPDATET3C300270>8.系统崩溃。请完成以下问题：1.给出三个典型并发异常场景的对应类型：场景1：事务T1修改A的值为90，事务T2随后修改A的值为80，之后T1执行ROLLBACK，A被回滚到原始值100，T2的修改被覆盖；场景2：事务T1第一次读取A的值为100，之后事务T2修改A的值为90并提交，T1再次读取A得到90，两次读取结果不一致；场景3：事务T1读取T2尚未提交的A的修改值90，随后T2执行ROLLBACK，A恢复为100，T1读取到不存在的数据。说明严格两段锁协议如何完全避免上述三类异常（7分）2.分别给出基于Undo日志、基于Redo日志的完整故障恢复步骤（5分）3.说明InnoDBRR隔离级别下MVCC机制避免幻读的实现原理（3分）参考答案：1.三类场景对应的异常分别为丢失修改、不可重复读、读脏数据。严格两段锁协议的规避逻辑为：所有事务在执行修改操作前必须提前获得对应数据行的排他锁，所有共享锁必须在事务提交后才能释放，因此事务T1持有A的排他锁期间其他事务无法修改A，避免丢失修改；事务T1持有A的共享锁期间其他事务无法修改A，避免不可重复读；事务T2修改A未提交期间其他事务无法读取A的中间状态，避免读脏数据。2.Undo日志恢复步骤：第一步从日志尾部反向扫描，将所有未提交事务T2、T3的修改操作逆向回滚，将B的值恢复为200，C的值恢复为300；第二步正向扫描日志，无需重放任何已提交事务的操作，直接结束恢复流程。Redo日志恢复步骤：第一步正向扫描日志，重放所有已经写入磁盘的提交事务操作，将A的值更新为90；第二步反向扫描日志，将未提交事务T2、T3的修改操作逆向回滚，将B、C恢复为原始值，完成恢复。3.InnoDB的MVCC机制在RR隔离级别下通过生成ReadView快照实现，事务启动时生成全局一致性快照，后续所有查询操作均基于快照版本读取，不会读取到其他事务提交的新增数据，同时结合Next-KeyGap锁锁定当前查询范围内的所有间隙，阻止其他事务在该区间插入新数据，完全避免幻读问题，相比传统2PL加锁方案，读操作无需加锁，整体并发性能提升5-10倍。试题五（共15分）某省级文旅厅搭建智慧文旅知识库问答系统，同时存储结构化的景点信息、票务信息