大型数据库系统期末考试试题及详细答案

大型数据库系统期末考试试题及详细答案考试说明：满分100分，考试时间120分钟，闭卷考试；试题涵盖数据库基础理论、范式设计、SQL高级操作、事务与锁、索引优化、分布式数据库基础等核心知识点一、单项选择题（共15题，每题2分，共30分）1.下列不属于大型数据库系统核心特性的是（）A.高并发处理能力B.海量数据存储C.单用户独占访问D.高可用容错2.数据库系统中，保证数据“要么全部执行，要么全部不执行”的事务特性是（）A.原子性B.一致性C.隔离性D.持久性3.消除了非主属性对主键的传递函数依赖的范式是（）A.1NFB.2NFC.3NFD.BCNF4.在SQL中，用于实现分组统计的关键字是（）A.ORDERBYB.GROUPBYC.DISTINCTD.LIMIT5.下列索引类型中，查询效率最高、适合等值查询的是（）A.全文索引B.唯一索引C.主键索引D.联合索引6.事务隔离级别中，能够彻底解决幻读问题的是（）A.读未提交B.读已提交C.可重复读D.串行化7.数据库死锁的核心成因是（）A.事务并发度过低B.多个事务互相持有对方所需资源且不释放C.索引失效D.数据冗余过高8.下列操作中，不会触发数据库锁机制的是（）A.SELECT查询（普通快照读）B.UPDATE更新C.DELETE删除D.INSERT插入9.分布式数据库中，CAP定理中“A”代表的特性是（）A.一致性B.可用性C.分区容错性D.安全性10.数据库视图的本质是（）A.物理存储的数据表B.虚拟表，基于SQL查询结果封装C.索引集合D.事务日志文件11.下列不属于数据库完整性约束的是（）A.主键约束B.外键约束C.模糊查询约束D.非空约束12.SQL语句中，HAVING子句的作用是（）A.过滤单行数据B.过滤分组后的统计结果C.排序数据D.分页数据13.大型数据库分表的主要目的是（）A.简化数据表结构B.降低单表数据量，提升查询和读写性能C.保证数据一致性D.提高数据安全性14.数据库回滚日志（undolog）的主要作用是（）A.记录数据修改前状态，支持事务回滚B.记录修改后状态，用于数据恢复C.优化索引查询D.同步分布式节点数据15.下列数据库中，属于典型大型分布式关系型数据库的是（）A.SQLiteB.MySQL单机版C.OceanBaseD.Access二、填空题（共10空，每空2分，共20分）1.数据库三级模式结构包括外模式、________和内模式。2.函数依赖中，若X→Y，且Y→X不成立，Y→Z，则Z对X存在________函数依赖。3.SQL中，用于批量删除表中所有数据且不记录日志的关键字是________。4.InnoDB存储引擎中，默认的事务隔离级别是________。5.数据库锁主要分为共享锁和________两大类。6.分布式数据库数据分片的三种方式：水平分片、垂直分片、________。7.数据库中，________是唯一标识数据表中每一条记录的字段或字段组合。8.优化SQL查询的核心原则是________全表扫描。9.事务的四大特性统称为________特性。10.数据库备份中，________备份是指仅备份上一次全量备份后变化的数据。三、简答题（共4题，每题5分，共20分）1.简述2NF、3NF、BCNF三者的区别与联系。2.简述数据库事务隔离级别中，读已提交和可重复读的核心区别，以及各自存在的问题。3.简述索引的优缺点，以及大型数据库中索引使用的禁忌。4.简述分布式数据库CAP定理的核心内容，以及实际生产中的取舍原则。四、综合应用题（共3题，共30分）1.SQL编程题（10分）现有学生表student（学号s_id，姓名s_name，性别s_sex，班级s_class，成绩s_score）请编写SQL语句实现以下需求：（1）查询班级为“计算机1班”，成绩大于80分的所有学生信息；（2）统计每个班级的学生总人数和平均成绩，筛选出平均成绩大于75分的班级；（3）给所有成绩低于60分的学生成绩统一加10分。2.范式设计题（10分）现有数据表：课程成绩表（学号，姓名，课程号，课程名，成绩，授课教师，教师电话）（1）分析该表存在哪些数据冗余和异常问题（插入、删除、更新异常）；（2）将该表拆解为符合3NF的数据表，标注各表主键、外键。3.数据库优化分析题（10分）某大型电商MySQL数据库，随着订单量增长，订单查询SQL执行速度越来越慢，经排查发现：订单表数据量超千万，查询语句无索引、频繁出现全表扫描，且高并发场景下偶尔出现事务超时、锁等待问题。请结合所学知识，给出索引优化、SQL优化、架构优化三个维度的具体解决方案。参考答案及详细解析一、单项选择题答案及解析1.C解析：大型数据库系统面向多用户、高并发场景，支持多用户同时访问，单用户独占是小型数据库的特征。2.A解析：原子性（Atomicity）即事务不可分割，要么全部执行成功，要么全部回滚；一致性是数据前后一致，隔离性是事务互不干扰，持久性是提交后数据永久保存。3.C解析：1NF消除列不可分；2NF消除非主属性对主键的部分依赖；3NF消除非主属性对主键的传递依赖；BCNF消除主属性的传递依赖。4.B解析：GROUPBY为分组关键字，ORDERBY用于排序，DISTINCT用于去重，LIMIT用于分页。5.C解析：主键索引唯一性最强、查询速度最快，InnoDB主键索引为聚簇索引，数据与索引绑定，等值查询效率最优。6.D解析：读未提交存在脏读、不可重复读、幻读；读已提交解决脏读，存在不可重复读、幻读；可重复读解决脏读、不可重复读，存在幻读；串行化解决所有并发问题。7.B解析：死锁的四个必要条件：互斥、请求保持、不可剥夺、循环等待，核心是多个事务循环持有对方资源且不释放。8.A解析：InnoDB普通快照读SELECT不加锁，UPDATE、DELETE、INSERT等写操作均会触发行锁或表锁。9.B解析：CAP定理：C（一致性）、A（可用性）、P（分区容错性），分布式系统无法同时满足三者。10.B解析：视图是虚拟表，不存储物理数据，仅存储查询逻辑，查询视图时会执行底层SQL语句。11.C解析：数据库完整性约束包括实体完整性（主键）、参照完整性（外键）、域完整性（非空、唯一、范围），无模糊查询约束。12.B解析：WHERE过滤分组前的单行原始数据，HAVING过滤GROUPBY分组后的统计结果。13.B解析：大型数据库单表数据量过千万后性能骤降，分表可拆分数据，降低单表压力，提升读写效率。14.A解析：undolog保存数据修改前镜像，用于事务回滚；redolog保存修改后记录，用于崩溃恢复。15.C解析：SQLite、Access为轻量小型数据库，单机MySQL不具备分布式能力，OceanBase是阿里自研大型分布式关系型数据库。二、填空题答案及解析1.模式（概念模式）解析：三级模式：外模式（用户视图）、模式（全局逻辑结构）、内模式（物理存储结构）。2.传递解析：非直接依赖，通过中间属性传递产生的依赖为传递函数依赖。3.TRUNCATE解析：DELETE删除数据会记录日志、可回滚；TRUNCATE清空表数据，不记录事务日志、速度更快、不可回滚。4.可重复读（RR）解析：MySQLInnoDB引擎默认隔离级别为可重复读。5.排他锁（写锁）解析：共享锁（读锁）：可读不可写，多事务共享；排他锁（写锁）：独占读写，其他事务阻塞。6.混合分片解析：分布式数据分片三种标准方式：水平、垂直、混合分片。7.主键解析：主键唯一、非空，是数据表记录的唯一标识。8.避免解析：全表扫描遍历所有数据，效率极低，SQL优化核心是通过索引避免全表扫描。9.ACID解析：原子性、一致性、隔离性、持久性首字母缩写为ACID。10.增量解析：全量备份备份所有数据，增量备份仅备份新增、修改、删除的变化数据，效率更高。三、简答题参考答案1.2NF、3NF、BCNF的区别与联系联系：三者均为数据库范式，用于消除数据冗余、解决数据异常，满足高阶范式必然满足低阶范式（BCNF⊂3NF⊂2NF）。区别：①2NF：在1NF基础上，消除非主属性对主键的部分函数依赖，解决部分依赖导致的冗余；②3NF：在2NF基础上，消除非主属性对主键的传递函数依赖，彻底解决非主属性冗余问题；③BCNF：在3NF基础上，消除主属性对主键的部分、传递依赖，解决主键自身依赖导致的异常，是更严格的范式。实际生产中，数据库设计一般满足3NF即可，BCNF会牺牲部分查询效率。2.读已提交与可重复读的区别及存在问题核心区别：①读已提交：事务只能读取已提交事务的数据，每次查询都会读取最新数据；②可重复读：事务开启后，多次读取同一数据，结果始终一致，不受其他事务修改提交的影响。存在问题：①读已提交：无脏读，但存在不可重复读（同一事务两次查询结果不同）、幻读问题；②可重复读：无脏读、不可重复读，但存在幻读（其他事务新增/删除数据，导致本事务数据集合变化）。3.索引的优缺点及使用禁忌优点：大幅提升查询、排序、分组效率，降低数据库IO开销，约束数据唯一性。缺点：占用磁盘存储空间；降低写入、更新、删除效率（修改数据需同步更新索引）；增加数据库维护成本。使用禁忌：①数据量小的表无需建索引；②频繁更新、写入的字段不适合建索引；③区分度低的字段（如性别、状态）不建索引；④避免创建冗余、无效索引；⑤禁止对索引字段使用函数、模糊前置匹配，导致索引失效。4.CAP定理核心内容及生产取舍核心内容：分布式数据库无法同时满足一致性（C）、可用性（A）、分区容错性（P）三大特性。一致性指所有节点数据实时同步一致；可用性指系统随时可正常访问响应；分区容错性指网络分区故障时系统仍可运行。生产取舍：①分布式系统P必须保证（网络分区不可避免），因此只能在C和A之间取舍；②金融、支付等核心场景优先保证CP，牺牲部分可用性，确保数据绝对一致；③电商、资讯等非核心场景优先保证AP，牺牲实时一致性，通过最终一致性保障业务可用。四、综合应用题参考答案1.SQL编程题答案（1）查询指定班级高分学生：SELECT*FROMstudentWHEREs_class='计算机1班'ANDs_score>80;（2）统计班级人数和平均分并筛选：SELECTs_class,COUNT(s_id)AS学生总人数,AVG(s_score)AS平均成绩FROMstudentGROUPBYs_classHAVINGAVG(s_score)>75;（3）低分学生成绩加分：UPDATEstudentSETs_score=s_score+10WHEREs_score<60;2.范式设计题答案（1）原表存在的问题：数据冗余：姓名、课程名、教师、教师电话重复存储，同一学生多门课程、同一教师多门课程都会产生大量冗余数据。异常问题：①插入异常：新增课程无学生选课，无法录入课程和教师信息；②删除异常：删除某学生所有课程成绩，会连带删除课程、教师信息；③更新异常：教师电话变更，需要修改所有相关记录，易出现数据不一致。（2）3NF数据表拆解：①学生表（学号【主键】，姓名）②教师表（教师编号【主键】，授课教师，教师电话）③课程表（课程号【主键】，课程名，教师编号【外键，关联教师表】）④成绩表（学号【外键】，课程号【外键】，成绩），主键为（学号+课程号）联合主键拆解后无传递依赖、部分依赖，彻底消除冗余和各类数据异常，符合3NF标准。3.数据库优化题答案（1）索引优化①为订单高频查询字段（订单号、用户ID、下单时间、订单状态）建立B+树索引；②针对常用组合查询建立联合索引，遵循最左匹配原则；③删除订单表冗余、失效索引，减少写入开销；④对状态、性别等低区分度字段不建索引，避免索引失效。（2）SQL优化①优化查询语句，避免S