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

2025年最新数据库系统工程师(软考中级下午题)试题与答案2025年数据库系统工程师（软考中级）下午科目试题（共5道必答题，每题20分，总分100分，考试时长150分钟）试题一（共20分）某城市社区智慧养老服务平台拟搭建数据库支撑全场景业务流转，需求调研结果如下：1.基础实体规则：每个社区分配唯一社区编号，存储名称、行政地址、服务站负责人联系电话属性，单个社区下辖若干在册老人；每位老人以18位身份证号作为唯一标识，存储姓名、年龄、入住养老服务点时间、健康等级（枚举值：自理/半失能/全失能）、紧急联系电话属性，每位老人仅归属1个社区管辖。2.家属关联规则：家属实体以身份证号为唯一标识，存储姓名、与老人亲属关系、本人联系电话属性；业务约束为1位老人最多可绑定6名直系家属，1名家属于多场景下最多可绑定3位不同的老人。3.服务人员规则：服务人员以工号为唯一标识，存储姓名、资质等级（初级/中级/高级）、入职时间属性，每位服务人员仅归属1个社区服务站管理；平台上线的服务项目以项目编号为唯一标识，存储项目名称、计费单位、单次基准单价属性，单个服务人员经资质认证后可承接多类符合等级要求的服务项目，单个服务项目可由多名满足资质门槛的服务人员承接。4.订单与评价规则：服务订单以全局唯一订单号为标识，存储下单时间、预约服务时间、实际服务完成时间、订单状态（枚举值：待接单/服务中/已完成/已取消）属性，每笔订单对应1位服务对象老人、1名接单服务人员、1类选定服务项目，下单操作可由老人本人或绑定家属发起；订单完成后用户可选择提交1条评价记录，评价以评价ID为唯一标识，存储1-5分整数值评分、文字评价内容、评价提交时间，无订单则无法生成对应评价，未评价订单不产生评价记录。5.健康档案规则：老人健康档案以档案编号为唯一标识，存储记录生成时间、血压值、血糖值、静息心率、专属医护医嘱属性，业务约束为每位老人每日最多生成1条健康档案记录，由社区医护人员上门巡检同步上传。平台初始设计的部分ER图缺失部分实体、联系与属性，结合上述需求回答以下问题：1.（6分）请指出ER图中缺失的2个实体、1个多对多联系，并补充该多对多联系的专属属性。2.（8分）请给出以下联系的实体基数（对应ER图从左到右的实体数量映射关系）：（1）社区与老人（2）老人与家属（3）服务人员与服务项目（4）订单与评价。3.（6分）补全以下关系模式的缺失属性，并明确标注各关系模式的主键与外键：关系模式1：家属（）关系模式2：亲属关联（老人身份证号，家属身份证号，）关系模式3：健康档案（）试题一参考答案1.缺失实体：家属、健康档案；缺失多对多联系：可承接（关联服务人员与服务项目实体）；可承接联系的专属属性：资质认证通过时间、服务限价系数。2.各实体联系基数如下：（1）社区与老人：1对多（1个社区对应0或多位老人，1位老人仅对应1个社区）（2）老人与家属：多对多（1位老人对应0或多位家属，1名家属对应1-3位老人）（3）服务人员与服务项目：多对多（1名服务人员对应0或多个服务项目，1个服务项目对应多名符合要求的服务人员）（4）订单与评价：1对1（1笔订单对应0或1条评价，1条评价仅对应1笔订单）3.各关系模式补全结果：家属（家属身份证号，姓名，与老人默认关系，联系电话），主键：家属身份证号，无外键亲属关联（老人身份证号，家属身份证号，绑定时间），主键：（老人身份证号，家属身份证号），外键：老人身份证号参照老人关系的身份证号，家属身份证号参照家属关系的身份证号健康档案（档案编号，老人身份证号，记录时间，血压，血糖，静息心率，专属医嘱），主键：档案编号，外键：老人身份证号参照老人关系的身份证号，额外唯一约束：（老人身份证号，记录日期），保障单日单老人仅1条记录符合业务规则。试题二（共20分）智慧养老平台上线1年后全量业务数据突破2300万条，单节点MySQL数据库订单表查询延迟超过2s，无法支撑高峰时段1200QPS的访问需求，技术团队计划基于国产云原生分布式数据库完成架构升级，优化逻辑结构与数据分片策略。现有初始设计的老人冗余关系模式如下：老人冗余表（身份证号，姓名，社区编号，社区名称，社区行政地址，健康等级，家属1身份证号，家属1姓名，家属2身份证号，家属2姓名，家属3身份证号，家属3姓名）回答以下问题：1.（7分）请指出上述老人冗余表存在的3类典型关系模式弊端，将其分解为符合第三范式（3NF）的关系模式集合，同时验证分解结果满足无损连接性、保持函数依赖要求。2.（8分）平台两类核心查询场景分别为：场景A：按社区维度统计季度服务订单总量、老人健康等级分布占比；场景B：老人通过本人身份证号查询个人历史3年所有订单明细。现有数据分片方案可选分片键包括订单号、社区编号、老人身份证号三类，请选择适配双场景的混合分片策略，说明分片键选型理由与具体分片实现规则，同时设计平台冷热数据分离的落地规则。3.（5分）平台要求社区服务站运维人员仅能查询本社区下辖的老人信息、服务人员信息与订单数据，无法跨社区访问其他社区的业务数据，请基于云原生分布式数据库的RBAC权限体系设计行级访问控制的落地方案。试题二参考答案1.该冗余表三类典型弊端：①数据冗余度极高，同一个社区的上百名老人会重复存储相同的社区名称、行政地址属性，浪费存储空间；②插入异常：新筹备的社区尚未录入老人信息时，无法完成社区基础数据的入库操作；③删除异常：某社区所有在册老人全部迁走后，社区的基础信息会被同步误删除。3NF分解后关系模式集合：社区（社区编号，社区名称，社区行政地址，负责人联系电话）老人（身份证号，姓名，社区编号，健康等级，紧急联系电话）亲属关联（老人身份证号，家属身份证号，绑定时间）家属（家属身份证号，姓名，与老人关系，联系电话）分解过程中所有函数依赖均未出现跨模式拆分，满足保持函数依赖要求，且各模式的交集为对应关系的主键，可通过自然连接还原初始全量数据，满足无损连接性。2.混合分片策略选型：采用异构双分片路由规则，针对统计类场景A以社区编号为分片键做范围分片，分片区间设置为每50个社区映射1个数据分片，同一社区的所有订单、老人数据全部存储在同一物理分片内，跨社区统计查询无需跨节点做数据聚合，大幅降低OLAP类查询延迟；针对点查类场景B以老人身份证号为分片键做哈希分片，基于身份证号的散列值均匀分布到64个物理分片，避免单点数据热点，老人查询个人全量订单仅需路由到单个分片完成检索。冷热分离规则：将生成时间超过3年、状态为已完成/已取消的归档订单数据自动同步到对象存储对接的分布式分析引擎集群，热数据全量保留在内存优化型存储池，冷数据仅在触发历史数据溯源场景时加载，存储成本降低70%以上。3.行级权限控制方案：创建社区运维专属角色，给该角色分配老人、订单、服务人员表的查询权限，同时绑定全局行安全策略，该策略自动为该角色发起的所有查询语句追加`where数据所属社区编号=当前登录用户归属社区编号`的过滤条件，应用端无需修改原有业务SQL，数据库层自动完成行级数据过滤，从底层避免越权访问风险。试题三（共20分）基于升级后的分布式数据库完成SQL开发与优化工作，结合业务需求回答以下问题：1.（8分）编写符合SQL2023规范的语句：（1）查询2024年第四季度所有社区健康等级为全失能的老人的月均助餐订单次数，输出字段为社区编号、社区名称、老人身份证号、姓名、月均订单数，结果按社区编号升序排序。（2）创建存储过程`calc_month_avg_score`，输入参数为指定社区编号，输出该社区当月所有服务人员的平均服务评分，规则为未产生用户评价的订单默认按4分基准分计入统计。（3）定义行级触发器，当任意订单的状态字段被更新为“已完成”时，自动生成一条关联该订单号的空白评价记录，初始评分为空值。2.（6分）定义完整性约束断言，要求平台所有资质等级为高级的服务人员，当前承接的有效服务项目总数量不得少于3项。3.（6分）技术团队上线后发现如下查询语句存在性能瓶颈：`select*from订单whereto_char(下单时间,'%Y%m')='202412'and订单状态='已完成'`，扫描全表耗时超过3s，请分析慢查询的核心原因，给出SQL改写方案与索引优化策略。试题三参考答案1.（1）对应查询SQL：```sqlselectc.社区编号,c.社区名称,o.老人身份证号,p.姓名,count(1)/3as月均订单数from订单ojoin老人pono.老人身份证号=p.身份证号join社区conp.社区编号=c.社区编号join服务项目sono.项目编号=s.项目编号wherep.健康等级='全失能'ands.项目名称='助餐服务'ando.下单时间>='2024-10-0100:00:00'ando.下单时间<'2025-01-0100:00:00'ando.订单状态in('待接单','服务中','已完成')groupbyc.社区编号,c.社区名称,o.老人身份证号,p.姓名orderbyc.社区编号asc;```（2）对应存储过程：```sqlcreateprocedurecalc_month_avg_score(inp_community_idvarchar(32),outp_avg_scoredecimal(3,2))beginselectavg(coalesce(e.评分,4))intop_avg_scorefrom订单ojoin服务人员sono.服务人员工号=s.工号leftjoin评价eono.订单号=e.关联订单号wheres.归属社区编号=p_community_idando.下单时间>=date_format(curdate(),'%Y-%m-0100:00:00')ando.订单状态='已完成';end;```（3）对应触发器：```sqlcreatetriggertg_gen_empty_evalafterupdateon订单foreachrowwhennew.订单状态='已完成'andold.订单状态<>'已完成'begininsertinto评价(关联订单号,评分,评价内容,评价时间)values(new.订单号,null,null,now());end;```2.对应断言定义：```sqlcreateassertionsenior_service_limitcheck(notexists(select工号from服务人员where资质等级='高级'and(selectcount(distinct项目编号)from可承接where服务人员工号=服务人员.工号)<3));```3.慢查询核心原因：语句中对索引列`下单时间`做了函数运算`to_char()`，数据库优化器无法命中下单时间字段的普通B+树索引，触发全表扫描。优化方案：将查询条件改写为`下单时间between'2024-12-0100:00:00'and'2024-12-3123:59:59'`，创建联合覆盖索引`idx_order_status_date(订单状态,下单时间)`，优化后查询耗时可降低到10ms以内。试题四（共20分）平台支付对账模块采用分布式架构部署，事务T1负责扣减老人账户余额、新增订单待结算金额，事务T2负责统计当日平台累计总营收，初始状态为老人A账户余额1200元，待结算订单应付金额150元，当日累计营收初始值为72600元。回答以下问题：1.（7分）现有3种并发调度序列：序列1为T1读取余额→T1扣减余额150→T2读取营收值→T1将新余额写入磁盘→T2读取订单应付金额150→T2更新总营收为72750写入；序列2为T1读取余额→T2读取营收值→T2读取订单应付金额150→T1扣减余额150→T2更新总营收写入→T1提交；序列3为T2读取营收值→T1读取余额→T1扣减余额→T1更新余额后触发回滚→T2再次读取营收值。请指出哪一个是冲突可串行化调度，哪一个会触发不可重复读异常，分别说明判定依据。2.（7分）若事务T1的扣减余额操作部署在存储账户库的节点A，事务T1的订单入账操作部署在存储订单库的节点B，采用2PC协议保障分布式事务原子性，请描述该场景下2PC的完整执行流程，说明若协调者发送完commit消息后立刻宕机的故障解决方案。3.（6分）平台当前备份策略为每周日凌晨2点执行全量物理备份，每日凌晨2点执行增量备份，事务日志实时同步到独立的异地日志集群，周三上午10点数据库存储节点发生介质故障导致磁盘数据完全损坏，请写出完整的故障恢复操作流程，保障所有已提交事务数据零丢失。试题四参考答案1.序列1是冲突可串行化调度，通过优先图判断调度的冲突操作无环，等价于先执行完T1所有操作再执行T2操作的串行调度，满足一致性要求；序列3会触发不可重复读异常，T2首次读取营收值后，T1修改数据触发回滚，T2再次读取同一营收字段得到了不同的中间值，符合不可重复读的定义。2.2PC执行流程：第一阶段协调者向节点A、节点B发送事务预提交请求，两个节点分别执行本地事务写入undo、redo日志，返回成功/失败响应给协调者；第二阶段协调者收到全量成功响应后，向所有节点发送commit指令，两个节点正式提交本地事务完成数据落盘。协调者宕机故障解决方案：存活的参与者节点之间发起分布式协商，读取各自节点的事务日志判断预提交阶段是否收到全量成功响应，若所有参与者都收到预提交确认则自动执行本地事务提交，若存在任意参与者返回未完成本地事务执行，则自动执行本地事务回滚，无需等待宕机协调者恢复。3.故障恢复流程：①从离线备份介质恢复最近一次周日的全量物理备份数据到新存储节点；②依次按时间顺序恢复周一、周二的两次增量备份数据，还原到周二凌晨2点的全量数据状态；③加载异地日志集群存储的所有事务日志，采用redo操作正向重放所有备份结束后到周三10点故障前的所有已提交事务操作；④采用undo操作回滚所有故障时刻未完成提交的活跃事务，最终数据保持事务一致性，已提交数据零丢失。试题五（共20分）2025年软考新考纲纳入AI原生数据库应用要求，平台拟上线基于向量数据库的老人异常行为智能预警功能，通过摄像头采集老人居家视频帧生成特征向量，实时识别老人摔倒、长时间停留阳台等高风险行为。回答以下问题：1.（7分）请说明传统关系型数据库无法支撑亿级健康特征向量相似性检索的核心原因，对比关系型数据库与向量数据库的典型适用场景差异。2.（7分）平台当前存储的老人人脸识别特征向量规模突破12亿条，单节点检索耗时超过200ms，拟采用分布式向量数据库集群部署，请说明HNSW索引的核心优势，设计针对12亿级向量数据的水平分片策略与检索流程，将单次检索延迟降低到20ms以内。3.（6分）AI原生数据库的自治运维特性可实