数据库系统工程师考试真题及答案

数据库系统工程师考试练习题及答案一、单项选择题（共20题，每题1分，共20分）1.在关系数据库中，若属性A函数依赖于属性B（B→A），但A不函数依赖于B，则A与B之间的依赖关系属于（）。A.完全函数依赖B.部分函数依赖C.传递函数依赖D.平凡函数依赖2.设关系模式R（U，F），其中U={A,B,C,D}，F={A→B,B→C,C→D}。则R的候选键是（）。A.AB.BC.CD.D3.事务的ACID特性中，“原子性”指的是（）。A.事务一旦提交，其对数据库的修改是永久的B.事务执行过程中，所有操作要么全部完成，要么全部不完成C.多个事务并发执行时，每个事务的执行结果不受其他事务干扰D.事务处理的数据必须满足数据库的完整性约束4.下列关于B+树索引的描述中，错误的是（）。A.所有叶子节点通过指针连接，便于范围查询B.非叶子节点仅存储索引键和子节点指针，不存储数据记录C.插入和删除操作可能导致节点分裂或合并D.适合作为聚集索引，直接存储数据记录5.在分布式数据库中，数据分片的方式不包括（）。A.水平分片B.垂直分片C.混合分片D.全局分片6.若关系模式R属于3NF，则R一定（）。A.属于BCNFB.不存在非主属性对候选键的部分函数依赖C.不存在主属性对候选键的传递函数依赖D.不存在多值依赖7.在数据库恢复技术中，日志文件的作用是（）。A.记录用户操作日志，用于审计B.记录事务对数据的修改，用于故障恢复C.存储索引信息，提升查询效率D.存储元数据，描述数据库结构8.并发事务T1和T2同时对数据项X进行操作，若T1对X加S锁（共享锁），则T2（）。A.可以加S锁，但不能加X锁（排他锁）B.可以加X锁，但不能加S锁C.既不能加S锁，也不能加X锁D.可以同时加S锁和X锁9.下列SQL语句中，用于修改表结构的是（）。A.UPDATEB.ALTERC.INSERTD.DELETE10.大数据场景下，HBase的存储模型采用（）。A.关系模型B.键值对模型C.列族模型D.文档模型11.设关系R和S的属性个数分别为r和s，则R×S（笛卡尔积）的属性个数为（）。A.r+sB.max(r,s)C.min(r,s)D.r×s12.数据库设计的逻辑设计阶段，主要任务是（）。A.确定数据库的物理存储结构B.将ER模型转换为关系模式C.收集用户需求，建立数据字典D.优化查询性能，设计索引13.事务故障恢复的步骤不包括（）。A.反向扫描日志，执行UNDO操作B.正向扫描日志，执行REDO操作C.清除未完成事务对数据库的修改D.重新启动数据库管理系统14.下列关于数据库完整性约束的描述中，错误的是（）。A.实体完整性通过主键约束实现B.参照完整性通过外键约束实现C.用户自定义完整性通过CHECK约束实现D.触发器可以替代所有完整性约束15.在数据仓库中，维度表用于描述（）。A.业务过程的度量值B.业务过程的上下文环境C.数据的存储结构D.数据的ETL流程16.设关系模式R（A,B,C），F={AB→C,C→B}，则R的主属性是（）。A.AB.BC.CD.A、B、C17.数据库管理系统（DBMS）的核心功能是（）。A.数据定义B.数据操作C.数据控制D.数据库运行管理18.下列关于索引优化的策略中，错误的是（）。A.为经常查询的列建立索引B.为经常更新的列建立索引C.避免在低基数列（如性别）上建立索引D.组合索引的顺序应遵循“最左匹配”原则19.在分布式数据库中，CAP定理指的是（）。A.一致性、可用性、分区容错性B.完整性、原子性、持久性C.并发性、隔离性、安全性D.可扩展性、可靠性、高效性20.设某数据库系统中，事务T1对数据A加X锁，事务T2对数据B加S锁，则（）。A.T1和T2可以并发执行，无冲突B.T1必须等待T2释放锁C.T2必须等待T1释放锁D.系统会发生死锁二、简答题（共5题，每题6分，共30分）1.简述数据库三级模式结构（外模式、模式、内模式）的作用及其之间的映射关系。2.什么是事务的隔离级别？常见的隔离级别有哪些？并说明“可重复读”隔离级别解决了哪些并发问题。3.简述关系模型中函数依赖与多值依赖的区别，并举例说明4NF（第四范式）的定义。4.数据库备份分为哪几类？简述完全备份、差异备份和日志备份的特点及适用场景。5.在分布式数据库中，数据分布策略有哪些？简述水平分片和垂直分片的区别，并说明如何选择分片策略。三、设计题（共2题，每题15分，共30分）1.某医院需设计“患者医生诊疗”管理系统，需求如下：患者信息包括：患者ID（唯一）、姓名、性别、年龄、联系方式；医生信息包括：医生ID（唯一）、姓名、科室、职称；诊疗信息包括：诊疗记录号（唯一）、诊疗日期、诊断结果、处方（可能包含多个药品）；业务规则：一个患者可由多个医生诊疗，一个医生可诊疗多个患者；每个诊疗记录对应唯一的患者和医生；处方中的药品需记录药品名称、数量、单价。要求：（1）绘制ER图（需标注实体属性、联系类型及约束）；（2）将ER图转换为关系模式，并标注主键和外键；（3）分析关系模式是否满足3NF，若不满足需进行分解。2.某电商平台订单数据库存在性能问题，查询“2023年1月1日至2023年1月31日，北京地区用户购买金额超过5000元的订单”时响应缓慢。已知订单表结构为：order（order_id,user_id,amount,create_time,user_city）。要求：（1）分析可能导致查询缓慢的原因；（2）提出优化方案（包括索引设计、查询语句优化、表结构调整等）；（3）说明优化后如何验证性能提升效果。四、应用题（共1题，20分）某银行核心交易系统使用关系数据库，支持账户转账业务（从账户A转账到账户B）。假设当前系统存在以下问题：高并发下，转账操作频繁出现超时；偶尔出现转账后账户余额不正确（如A减少100元但B未增加）；数据库日志文件过大，占用大量存储资源。要求：（1）分析超时可能的原因（从并发控制、锁机制角度）；（2）设计转账事务的ACID特性实现方案（需包含具体的SQL语句和事务控制语句）；（3）提出日志文件优化策略（包括日志类型选择、归档机制等）。答案及解析一、单项选择题1.A解析：完全函数依赖指属性A依赖于B的全部，而非部分；部分依赖是A依赖于B的真子集；传递依赖是A通过中间属性依赖于B；平凡依赖是A包含于B的属性集。2.A解析：候选键是能唯一确定所有属性的最小属性集。A→B→C→D，故A能决定所有属性，且无冗余。3.B解析：原子性（Atomicity）指事务的不可分割性，要么全做，要么全不做。4.D解析：B+树索引是非聚集索引时不存储数据，聚集索引通常使用B+树直接存储数据，但选项D描述错误，因B+树本身不直接决定是否为聚集索引。5.D解析：分布式数据分片方式包括水平、垂直、混合分片，“全局分片”非标准术语。6.B解析：3NF消除了非主属性对候选键的部分和传递依赖，但可能存在主属性的传递依赖（不满足BCNF）。7.B解析：日志文件记录事务对数据的修改操作，用于故障恢复（如REDO/UNDO）。8.A解析：共享锁（S锁）允许其他事务加S锁，但禁止加X锁（排他锁）。9.B解析：ALTER用于修改表结构（如添加列、修改约束）；UPDATE是数据更新，INSERT/DELETE是数据增删。10.C解析：HBase采用列族（ColumnFamily）模型，属于NoSQL的列存储数据库。11.A解析：笛卡尔积的属性个数是两关系属性个数之和。12.B解析：逻辑设计阶段将ER模型转换为关系模式，并优化；物理设计确定存储结构。13.D解析：事务故障恢复通过日志的UNDO/REDO完成，重新启动是系统故障恢复的步骤。14.D解析：触发器可实现复杂约束，但不能替代所有约束（如主键、外键由DBMS直接强制）。15.B解析：数据仓库中，维度表描述业务过程的上下文（如时间、地点、用户），事实表存储度量值。16.D解析：候选键是A（因A→AB→C，且A不能由其他属性推出），故主属性是A、B、C（所有包含在候选键中的属性）。17.D解析：DBMS的核心是数据库运行管理（包括事务管理、并发控制、故障恢复等）。18.B解析：频繁更新的列建立索引会增加维护开销，降低性能。19.A解析：CAP定理指分布式系统中一致性（Consistency）、可用性（Availability）、分区容错性（PartitionTolerance）无法同时满足。20.A解析：X锁和S锁作用于不同数据项（A和B），无冲突，可并发执行。二、简答题1.答案：三级模式结构是数据库系统的核心抽象，包括：外模式（用户模式）：用户可见的局部数据逻辑结构，对应不同用户视图，保证数据独立性。模式（逻辑模式）：全局数据逻辑结构，描述数据的实体、关系及约束，是全体用户的公共视图。内模式（存储模式）：数据在数据库中的物理存储结构（如文件结构、索引方式）。映射关系：外模式/模式映射：定义外模式与模式的对应关系，保证逻辑独立性（模式修改时，只需调整映射，外模式不变）。模式/内模式映射：定义模式与内模式的对应关系，保证物理独立性（内模式修改时，只需调整映射，模式不变）。2.答案：事务的隔离级别定义了事务之间可见性的程度，用于控制并发事务的相互影响。常见隔离级别（从低到高）：读未提交（ReadUncommitted）：允许事务读取其他事务未提交的修改（脏读）。读已提交（ReadCommitted）：只读取已提交的数据（避免脏读，但可能不可重复读）。可重复读（RepeatableRead）：同一事务内多次读取同一数据结果一致（避免脏读、不可重复读，但可能幻读）。串行化（Serializable）：事务串行执行，避免所有并发问题（最高隔离级别，性能最低）。“可重复读”解决了脏读（读取未提交数据）和不可重复读（同一事务内两次读取结果不同），但无法完全避免幻读（因其他事务插入新数据导致结果集变化）。3.答案：函数依赖（FD）：若X→Y，当X的值确定时，Y的值唯一确定（如学号→姓名）。多值依赖（MVD）：若X→→Y，当X的值确定时，Y有一组独立于Z（Z=UXY）的值（如课程→→教师，同一课程有多个教师，与学生无关）。4NF定义：关系模式R∈1NF，且对于所有非平凡多值依赖X→→Y（Y不包含于X，X∪Y≠U），X必含候选键。即消除非平凡且非函数依赖的多值依赖。示例：关系模式R（课程，教师，学生），存在多值依赖课程→→教师和课程→→学生，且课程是候选键。若R中存在课程→→教师（教师不依赖于学生），则R∈4NF；若存在非候选键的X→→Y，则需分解。4.答案：数据库备份分类：完全备份：备份整个数据库，恢复时最快，但时间和空间开销大，适用于数据变化不频繁的场景（如每周一次）。差异备份：备份自上次完全备份以来所有修改的数据，恢复时需完全备份+最后一次差异备份，适用于数据变化较频繁（如每日一次）。日志备份：备份事务日志，记录所有修改操作，恢复时需完全备份+差异备份+日志备份，适用于实时恢复（如每小时一次）。适用场景：完全备份作为基础，差异备份减少完全备份频率，日志备份实现点恢复（如故障前10分钟）。5.答案：分布式数据分布策略：分片（水平、垂直、混合）、复制（全量复制、部分复制）。水平分片：按行分割，同一表的不同行分布到不同节点（如按用户所在城市分片）。垂直分片：按列分割，同一表的不同列分布到不同节点（如用户表分为基本信息表和敏感信息表）。选择策略依据：水平分片：适用于按某个属性范围或值划分数据（如时间、地域），查询常按该属性过滤。垂直分片：适用于列较多且查询常访问部分列（如减少I/O），需保证分片间的连接操作高效。三、设计题1.答案：（1）ER图：实体：患者（患者ID，姓名，性别，年龄，联系方式）；医生（医生ID，姓名，科室，职称）；药品（药品ID，名称，单价）。联系：诊疗（诊疗记录号，诊疗日期，诊断结果），患者与医生是多对多联系（M:N）；诊疗与药品是一对多联系（1:N，一个诊疗记录对应多个药品）。（2）关系模式：患者（患者ID，姓名，性别，年龄，联系方式）主键：患者ID医生（医生ID，姓名，科室，职称）主键：医生ID诊疗记录（诊疗记录号，患者ID，医生ID，诊疗日期，诊断结果）主键：诊疗记录号，外键：患者ID→患者.患者ID，医生ID→医生.医生ID处方（诊疗记录号，药品名称，数量）主键：（诊疗记录号，药品名称），外键：诊疗记录号→诊疗记录.诊疗记录号（注：药品名称若需唯一约束，可单独建立药品表，处方表外键引用药品ID）（3）范式分析：原关系模式中，诊疗记录的非主属性（诊疗日期、诊断结果）完全依赖于主键（诊疗记录号），无部分或传递依赖，满足2NF；且不存在非主属性对候选键的传递依赖，满足3NF。处方表的非主属性（数量）完全依赖于主键（诊疗记录号，药品名称），满足3NF。2.答案：（1）查询缓慢原因：未在create_time、user_city、amount上建立索引，全表扫描耗时；数据量过大，表未分区或分桶；查询条件涉及多列，未使用组合索引；存在锁竞争或事务阻塞。（2）优化方案：索引设计：创建组合索引（user_city,create_time,amount），满足“最左匹配”原则；表分区：按create_time做范围分区（如按月分区），减少扫描数据量；查询优化：明确时间范围（BETWEEN），避免函数操作（如YEAR()）；物化视图：定期汇总北京地区月订单数据（若查询频繁且数据允许延迟）。（3）性能验证：使用EXPLAIN分析查询执行计划，检查是否使用目标索引；对比优化前后的查询耗时（如平均响应时间从5s降至500ms）；监控数据库服务器资源（CPU、I/O），观察负载是否降低；进行压力测试（如模拟100并发查询），验证高负载下的稳定性。四、应用题1.答案：（1）超时原因：高并发下，转账操作需对账户A和B加X锁，若锁粒度大（如表锁）或锁顺序不一致（如T1锁A→B，T2锁B→A），易导致死锁或长等待；事务隔离级别过高（如串行化），并发度低；锁持有时间过长（如事务包含非必要操作，未及时提交）。