2026年软考-数据库系统工程师应用技术真题

2026年软考-数据库系统工程师应用技术真题试题一：数据库设计与规范化理论应用【背景说明】某大型跨国制造企业计划开发一套“智能供应链管理系统”以整合其全球范围内的采购、库存、生产及物流数据。该系统旨在通过实时数据分析优化供应链响应速度，降低库存成本。经过需求分析，系统设计了如下实体及其属性：1.供应商：供应商编号（唯一标识）、供应商名称、所在国家、联系电话、信用等级。2.零件：零件编号（唯一标识）、零件名称、规格型号、单价、重量、颜色。3.仓库：仓库编号（唯一标识）、仓库名称、地理位置、容量、负责人。4.采购订单：订单编号（唯一标识）、下单日期、预计到货日期、总金额、经办人、供应商编号。5.订单明细：明细ID（唯一标识）、订单编号、零件编号、采购数量、单价。6.库存：库存ID（唯一标识）、仓库编号、零件编号、当前库存量、安全库存阈值。系统初步设计了如下的关系模式（主键加粗下划线表示）：供应商(<u>供应商编号</u>,供应商名称,所在国家,联系电话,信用等级)零件(<u>零件编号</u>,零件名称,规格型号,单价,重量,颜色)仓库(<u>仓库编号</u>,仓库名称,地理位置,容量,负责人)采购订单(<u>订单编号</u>,下单日期,预计到货日期,总金额,经办人,供应商编号)订单明细(<u>明细ID</u>,订单编号,零件编号,采购数量,单价)库存(<u>库存ID</u>,仓库编号,零件编号,当前库存量,安全库存阈值)【问题1】（3分）根据上述关系模式和语义，请分析“订单明细”关系模式属于第几范式？请用100字以内的文字说明理由。【问题2】（4分）对“订单明细”关系模式，若设定“明细ID”为代理键，且业务规则规定：(订单编号,零件编号)可以唯一确定一条明细。请判断是否存在部分函数依赖？若存在，请指出；若不存在，请说明该模式最高满足第几范式。【问题3】（6分）随着业务扩展，系统需要记录零件的“分类”信息，包括分类编号和分类名称。一个零件只能归属一个分类，一个分类包含多个零件。若将“分类编号”和“分类名称”直接加入到“零件”关系中，请分析此举可能导致的数据异常问题（插入、删除、更新异常），并给出改进后的关系模式设计。【问题4】（4分）为了提高查询性能，DBA决定在“库存”表上创建索引。假设该表中有1,000,000条记录，数据页大小为16KB，每条记录长度为200字节。若使用B+树索引结构，索引键长为8字节，指针长为6字节。请估算该B+树索引的高度（假设B+树节点充满率为100%，请写出计算过程）。计算公式参考：设阶数为m，则节点最大子树指针数为m。m树高h满足：×【问题5】（3分）在E-R图设计中，供应商与采购订单之间存在“1:N”的联系。请将此联系转换为关系模式，并指出该关系模式的主键和外键。试题二：SQL应用与数据库实现【背景说明】某在线教育平台使用MySQL数据库，其中包含学员、课程、选课及成绩记录。相关表结构如下：学员表```sqlCREATETABLEStudent(SidINTPRIMARYKEY,SnameVARCHAR(50)NOTNULL,GenderCHAR(1)CHECK(GenderIN('M','F')),EnrollmentDateDATE,EmailVARCHAR(100)UNIQUE);```课程表```sqlCREATETABLECourse(CidINTPRIMARYKEY,CnameVARCHAR(100)NOTNULL,CreditDECIMAL(3,1),TeacherVARCHAR(50),DeptVARCHAR(50));```选课记录表```sqlCREATETABLEEnrollment(SidINT,CidINT,ScoreDECIMAL(5,2),SemesterVARCHAR(20),PRIMARYKEY(Sid,Cid,Semester),FOREIGNKEY(Sid)REFERENCESStudent(Sid),FOREIGNKEY(Cid)REFERENCESCourse(Cid));```【问题1】（5分）请编写SQL语句，查询“2024春季”学期中，平均成绩高于80分的课程名称、授课教师及该课程的平均成绩（保留两位小数），并按平均成绩降序排列。【问题2】（6分）请编写一个触发器`tr_Update_Score`，实现如下业务逻辑：当在`Enrollment`表中更新或插入成绩时，如果成绩小于60分，则自动向名为`Fail_Log`的日志表（包含字段：LogID自增,Sid,Cid,Score,OperateTime）中插入一条记录，记录操作时间和该次不及格的成绩信息。假设`Fail_Log`表已存在，结构为：`Fail_Log(LogIDINTAUTO_INCREMENTPRIMARYKEY,SidINT,CidINT,ScoreDECIMAL(5,2),OperateTimeDATETIME)`。【问题3】（4分）为了方便统计，需要创建一个视图`v_Student_Summary`，显示每位学生的学号、姓名、已修总学分（只计算成绩及格>=60的课程）。请补全该视图的创建SQL语句。```sqlCREATEVIEWv_Student_SummaryASSELECTs.Sid,s.Sname,____(1)____ASTotalCreditsFROMStudentsLEFTJOINEnrollmenteONs.Sid=e.SidLEFTJOINCoursecONe.Cid=c.CidWHERE____(2)____GROUPBY____(3)____;```【问题4】（5分）现有SQL查询语句：```sqlSELECTSnameFROMStudentWHERESidIN(SELECTSidFROMEnrollmentWHEREScore>90);```请将该查询改写为等价的连接查询语句。并从查询优化角度，简要说明在什么情况下子查询形式比连接形式执行效率更高？试题三：事务管理与并发控制【背景说明】某银行核心数据库系统采用严格两阶段锁协议（Strict2PL）进行并发控制。现有两个事务和，对账户A和账户B（初始余额均为1000元）进行操作。操作序列如下：时间$T_1$$T_2$$t_1$$XLock(A)$$t_2$Read(A):$a=1000$$t_3$$XLock(B)$$t_4$Read(B):$b=1000$$t_5$Write(B):$b-200$$t_6$Unlock(B)$t_7$$XLock(A)$$t_8$$A=A+100$$t_9$Write(A):$a=1100$$t_{10}$Unlock(A)$t_{11}$Read(A):$a=1100$$t_{12}$Write(A):$a+200$$t_{13}$Unlock(A)【问题1】（3分）根据上述调度，请判断和之间是否发生了死锁？如果是，请指出构成死锁的等待环；如果不是，请说明理由。【问题2】（4分）假设系统采用“等待-图”来检测死锁。请画出在时刻（即申请XLoc【问题3】（5分）若将隔离级别设置为“可重复读”，并使用MVCC（多版本并发控制）技术，和同时开始。读取A，读取B，然后更新A，更新B。请简述MVCC是如何实现“可重复读”隔离级别的，即在读取A之后，即使随后提交了对A的修改，再次读取A时为何看不到的修改？【问题4】（4分）数据库日志是保证事务原子性和持久性的关键。请简述undolog和redolog的作用区别，并写出检查点技术在数据库恢复过程中的两个主要好处。【问题5】（4分）在银行转账业务中，为了保证一致性，通常需要将转账操作封装在一个事务中。假设从账户X转500元到账户Y，请用伪SQL代码（包含BEGINTRANSACTION,COMMIT等）及必要的校验逻辑实现该事务。试题四：数据库性能优化【背景说明】某社交网络平台的“动态Feed”模块主要承载用户发帖、点赞及评论功能。随着用户量激增，数据库服务器CPU和I/O负载经常达到100%，响应缓慢。DBA决定对数据库进行性能诊断与优化。相关表结构如下：User(用户表)：Uid(PK),Username,Status,RegDateTweet(微博表)：Tid(PK),Uid(FK),Content,PublishTime,ViewCountComment(评论表)：Cid(PK),Tid(FK),Uid(FK),Content,CommentTime系统中最频繁的查询是获取某用户的最新10条动态：```sqlSELECTTid,Content,PublishTimeFROMTweetWHEREUid=?ORDERBYPublishTimeDESCLIMIT10;```【问题1】（5分）经分析，上述查询执行计划显示进行了全表扫描。请针对该查询，给出两种合理的索引优化方案，并比较这两种方案在写入性能（INSERT/UPDATE）方面的差异。【问题2】（4分）除了索引优化，DBA还发现`ViewCount`字段更新频繁，导致表锁争用严重。请提出一种数据库设计模式或架构调整方案，以解决该高频更新带来的性能瓶颈。【问题3】（6分）假设数据库表空间使用的是默认的页大小，且表中包含大量的VARCHAR类型大文本字段。为了优化I/O性能，DBA考虑进行表分区。请说明：1.对于`Tweet`表，选择“范围分区”还是“哈希分区”更合适？为什么？2.写出对`Tweet`表按`PublishTime`进行年度分区的SQL语法框架（以MySQL或PostgreSQL语法为例）。【问题4】（5分）在执行复杂的统计分析查询（如统计过去一年的热门话题）时，查询涉及数亿行数据的聚合运算。请列举三种常见的查询优化技术（不涉及硬件升级），并简要解释其原理。试题五：分布式数据库与NoSQL【背景说明】某物联网公司负责收集全球智能电表的读数。数据特征如下：1.数据量极大：每天产生约50亿条读数记录。2.写入吞吐量极高：每秒需处理约10万次写入请求。3.查询模式：主要是根据电表ID和时间范围查询历史读数，偶尔进行聚合分析（如某区域某月的总用电量）。4.数据一致性：允许最终一致性，但数据不丢失。【问题1】（4分）针对该应用场景，关系型数据库（RDBMS）面临哪些主要挑战？请列举两点。【问题2】（5分）该公司决定采用HBase作为存储引擎。HBase是基于列族的NoSQL数据库。请设计HBase的表结构（RowKey设计是关键）。假设：电表ID为`MeterID`，时间戳为`TS`，读数值为`Value`。请给出RowKey的设计方案，并解释该设计为何能优化“根据电表ID和时间范围查询”的性能。【问题3】（4分）在分布式数据库系统中，CAP定理（Consistency,Availability,Partitiontolerance）指出系统无法同时满足这三项。请结合上述智能电表场景，说明在架构设计时应如何权衡CAP？具体选择了哪两项？【问题4】（5分）为了进一步提高系统的可用性和扩展性，系统采用了分片机制。若按照`MeterID`进行哈希分片，当数据量持续增长需要扩容（增加节点）时，会发生什么问题？请给出一种解决该扩容问题的机制。【问题5】（2分）MapReduce编程模型常用于大规模数据集的并行运算。请简述Map函数和Reduce函数的主要职责。答案与解析试题一【问题1】答案：属于1NF。理由：关系模式中每个属性都是不可再分的原子值，满足第一范式（1NF）的定义。【问题2】答案：存在部分函数依赖。分析：主键是“明细ID”，而“订单编号”和“零件编号”函数依赖于“明细ID”。如果存在(订单编号,零件编号)->(采购数量,单价)，且(订单编号,零件编号)是候选键，则相对于“明细ID”这个代理主键，所有非主属性都完全函数依赖于主键（明细ID），因此满足2NF。注：若题目隐含候选键为(订单编号,零件编号)而主键设为明细ID，则不存在部分依赖，满足2NF。若主键设为(订单编号,零件编号)，且明细ID仅依赖其中一部分，则存在部分依赖。根据题目描述“明细ID为代理键”，通常代理键无业务含义，其他属性依赖于它，故满足2NF。但若题目意图考察(订单编号,零件编号)作为主键的情况，且存在明细ID只依赖订单编号的情况，则存在部分依赖。修正解析：题目中“订单明细”关系模式主键是“明细ID”。因此，不存在部分函数依赖（因为只有一个属性的主键，不存在主键子集）。该模式最高满足第二范式（2NF），因为不存在非主属性对候选键的传递依赖（暂不考虑传递依赖，通常此类简单结构满足3NF，除非存在传递依赖，例如：零件编号->零件名称，若零件名称在此表中，则存在传递依赖）。根据给出的属性，仅有订单编号、零件编号、采购数量、单价。如果单价由零件编号决定，且零件编号不是主键，则存在传递依赖（明细ID->零件编号->单价），不满足3NF。标准答案参考：若认为(订单编号,零件编号)是候选键，且主键是明细ID，则满足2NF。若存在单价依赖零件编号，则不满足3NF。本题问是否存在部分函数依赖：对于单属性主键，不存在部分函数依赖。【问题3】答案：数据异常：1.插入异常：若存在一个新分类，但暂时没有该分类的零件，则无法插入该分类信息（因为零件表主键不能为空）。2.删除异常：若删除了某个分类下的最后一个零件，则该分类的信息也会随之丢失。3.更新异常：若分类名称需要修改，必须更新所有属于该分类的零件记录，否则会出现数据不一致。改进后的关系模式：零件(<u>零件编号</u>,零件名称,规格型号,单价,重量,颜色,分类编号)分类(<u>分类编号</u>,分类名称)在零件关系中增加外键：分类编号REFERENCES分类(分类编号)。【问题4】答案：计算过程如下：1.计算B+树的阶数m（节点中最大子树指针数）：节点大小=16KB=16384Bytes。索引项大小=KeySize+PointerSize=8+6=14Bytes。m=（注：B+树非叶子节点存储Key和Pointer，叶子节点存储Key和Pointer或数据指针，计算方式略有不同，通常估算公式用此即可）。2.假设B+树高度为h（根节点为第1层）。根节点至少有2个指针，其他节点至少有⌈m最小指针数计算（最坏情况）：第1层：2第2层：2第3层：1170第4层：684由于总记录数为1,000,000，第3层能容纳约68万条记录指针（如果是叶子层），第4层能容纳4亿条。实际上，B+树叶子节点层才存储记录指针。如果树高为2（根+叶子）：叶子节点数=m。每叶子节点记录数≈\lfPageSize/RowSize⌋通常计算索引树高是指查找Key的路径。h=2时，根节点指向叶子节点，最多覆盖m个叶子节点，即1170<h=3时，根->中间->叶子。最多覆盖1170×因此，索引高度为3。【问题5】答案：关系模式：采购订单(<u>订单编号</u>,下单日期,预计到货日期,总金额,经办人,供应商编号)主键：订单编号外键：供应商编号（参照供应商表的供应商编号）试题二【问题1】答案：```sqlSELECTc.Cname,c.Teacher,AVG(e.Score)ASAvgScoreFROMEnrollmenteJOINCoursecONe.Cid=c.CidWHEREe.Semester='2024春季'GROUPBYc.Cid,c.Cname,c.TeacherHAVINGAVG(e.Score)>80ORDERBYAvgScoreDESC;```【问题2】答案：```sqlCREATETRIGGERtr_Update_ScoreAFTERINSERTONEnrollmentFOREACHROWBEGINIFNEW.Score<60THENINSERTINTOFail_Log(Sid,Cid,Score,OperateTime)VALUES(NEW.Sid,NEW.Cid,NEW.Score,NOW());ENDIF;END;CREATETRIGGERtr_Update_Score_ModifyAFTERUPDATEONEnrollmentFOREACHROWBEGINIFNEW.Score<60THENINSERTINTOFail_Log(Sid,Cid,Score,OperateTime)VALUES(NEW.Sid,NEW.Cid,NEW.Score,NOW());ENDIF;END;```（注：实际SQL语法可能因数据库略有差异，核心逻辑为AFTERINSERT/UPDATE并判断NEW.Score）【问题3】答案：(1)SUM(c.Credit)(2)e.Score>=60(3)s.Sid,s.Sname【问题4】答案：等价连接查询：```sqlSELECTS.SnameFROMStudentSJOINEnrollmentEONS.Sid=E.SidWHEREE.Score>90;```解释：当子查询中返回的结果集非常小，且外表很大，或者子查询可以被转化为EXISTS形式且能利用索引时，子查询形式可能比连接形式效率更高。特别是在某些未经过优化的老版本数据库中，对于“NOTIN”或“NOTEXISTS”场景，子查询可能避免大表的排序或哈希连接开销。但在现代优化器中，两者通常会被转换为相同的执行计划。试题三【问题1】答案：是，发生了死锁。理由：在时刻，申请对A加X锁，但A已被持有（），且在等待释放B锁（虽然序列中未显式写出申请B，但若后续需要B，则形成环路；若仅看给出的序列，在锁A，在锁B，时锁A失败等待。此时若不申请B，则只是等，无死锁。但通常此类题目隐含后续要操作B。根据题目描述是死锁题，故假设存在环路）。根据提供的具体操作序列分析：持有A。持有B，申请A->等待。若序列到此为止，只是等待。若后续申请B，则死锁。题目给出的序列中，只操作了A，操作了B和A。这实际上没有构成死锁，只是被阻塞。修正：若题目问“是否发生死锁”，基于给定的严格序列（没有申请B），答案是“否”。等待释放A，执行完毕释放A后，即可执行。但是，如果这是典型的死锁考察题，可能漏写了申请B的步骤。若严格按照给出的表格，没有申请B。标准答案倾向：若严格按照表格，未发生死锁，只是阻塞。若假设题目隐含也要B，则是死锁。鉴于“死锁”关键词，通常预期是死锁场景。让我们假设题目意图是死锁。然而，严谨起见：基于给出的…，全程未请求B。所以没有环路。答案：未发生死锁。在等待释放A，完成后继续。（注：如果这是一道变题，原题通常会有在中间请求B。若必须回答死锁，请指出假设请求B。这里按未发生回答）。【问题2】答案：（假设基于问题3的修正，即存在等待B的情况）等待图：节点：,边：→（等待释放A）（如果也在等待B，则还有→）基于问题1的“未发生死锁”判断，等待图仅为：→。【问题3】答案：MVCC通过为数据的每个版本保存一个创建时间戳（或事务ID）和过期时间戳来实现。当事务开始时，系统会记录其快照版本。当读取数据A时，它会查找创建时间戳早于开始时间且未被标记为删除的版本。即使在执行期间提交了对A的修改，生成的新数据版本的时间戳晚于的开始时间。因此，根据快照可见性规则，仍然只能看到旧版本的数据，从而实现了可重复读。【问题4】答案：UndoLog：记录数据修改前的值。用于事务回滚，将数据恢复到修改前的状态；也用于在持久性恢复中，未提交事务的撤销。RedoLog：记录数据修改后的值。用于已提交事务的重做，在系统崩溃恢复时，将已提交但未刷入磁盘的修改重新应用，确保持久性。检查点技术的好处：1.缩短恢复时间：只需要重做检查点之后的RedoLog。2.减少日志清理工作量：检查点之前的日志段通常可以被标记为可回收或归档。【问题5】答案：```sqlBEGINTRANSACTION;DECLARE@BalanceDECIMAL(18,2);SELECT@Balance=BalanceFROMAccountWHEREAccountID=@X;IF@Balance>=500BEGINUPDATEAccountSETBalance=Balance-500WHEREAccountID=@X;UPDATEAccountSETBalance=Balance+500WHEREAccountID=@Y;COMMITTRANSACTION;ENDELSEBEGINROLLBACKTRANSACTION;或者抛出异常END```试题四【问题1】答案：方案1：在`Uid`上创建普通B-Tree索引。方案2：在`Uid`和`PublishTime`上创建复合索引，顺序为。差异：方案1的索引较小，写入时维护成本较低，但查询可能需要回表或额外排序。方案2的索引较大，因为包含了更多列，写入时需要维护更多的键值，因此写入性能略低于方案1。但查询性能更好，因为可以直接利用索引的有序性获取数据，避免排序。【问题2】答案：方案：将`ViewCount`字段从`Tweet`表中剥离出来，单独存储在一个扩展表或Redis缓存中。原理：将高频更新的热点字段与低频更新的主体字段分离。更新`ViewCount`时不再锁定`Tweet`表的主记录，减少锁争用和I/O开销。【问题3】答案：1.选择“范围分区”更合适。理由：查询条件中经常包含时间范围（如“查看某用户去年的动态”）。范围分区可以快速定位到特定时间段的分区，进行分区裁剪，减少扫描的数据量。哈希分区无法支持范围查询的高效裁剪。2.SQL框架（MySQL示例）：```sqlPARTITIONBYRANGE(YEAR(PublishTime))(PARTITIONp2022VALUESLESSTHAN(2023),PARTITIONp2023VALUESLESSTHAN(2024),PARTITIONp2024VALUESLESSTHAN(2025),PARTITIONpmaxVALUESLESSTHAN