2025年数据库设计试题及答案

2025年数据库设计试题及答案一、单项选择题（每题2分，共20分）1.某电商平台订单表需支持高频查询“用户近30天订单”，同时需保证历史订单（超过1年）的归档效率。以下设计中最合理的是（）A.为订单表添加用户ID和下单时间的联合主键B.按用户ID分库，订单表按下单时间做范围分区C.在订单表上创建用户ID的哈希索引D.将历史订单定期迁移至列式存储数据库2.关系模式R（A,B,C,D），函数依赖集F={AB→C,C→D,D→A}，则R的主码是（）A.ABB.BCC.CDD.BD3.分布式数据库中，若某事务需更新北京、上海两个节点的用户余额，为保证原子性，最适合的协议是（）A.两阶段锁协议（2PL）B.时间戳排序协议（TSO）C.三阶段提交协议（3PC）D.乐观并发控制协议4.某银行核心系统要求“同一账户的转账操作必须在100ms内完成，且任何网络中断后数据不丢失”，需重点满足的事务特性是（）A.原子性（Atomicity）与持久性（Durability）B.一致性（Consistency）与隔离性（Isolation）C.原子性与隔离性D.一致性与持久性5.设计医疗影像数据库时，需存储DICOM格式的影像文件（平均50MB），并支持按患者ID、检查类型快速检索。以下方案最优的是（）A.将影像文件直接存储在关系型数据库的BLOB字段中B.影像文件存储于对象存储（如S3），数据库存储元数据（患者ID、检查时间、文件路径）C.使用列式数据库存储影像的二进制数据，按患者ID分块D.将影像文件压缩后存储在文档数据库（如MongoDB）的二进制字段中6.某社交平台用户表（用户ID，姓名，手机号，注册时间，最后登录时间）需支持“按手机号快速查找用户”和“统计每日新增用户数”，应创建的索引是（）A.手机号的唯一索引，注册时间的普通索引B.手机号的哈希索引，注册时间的聚簇索引C.手机号的B+树索引，注册时间的B+树索引D.手机号的覆盖索引，注册时间的复合索引7.以下关于数据仓库设计的描述中，错误的是（）A.事实表通常包含大量度量值和外键B.维度表需定期更新以反映业务变化（如地区划分调整）C.星型模型比雪花模型更适合复杂查询D.需考虑慢变化维（SCD）的处理（如用户地址变更）8.某物联网平台需存储传感器实时数据（每秒10万条，每条包含设备ID、时间戳、温度、湿度），要求支持“按设备ID查询近7天的分钟级温度均值”，最合理的存储方案是（）A.使用关系型数据库，按设备ID分表，时间戳为主键B.使用时序数据库（如InfluxDB），以设备ID和时间戳为标签C.使用文档数据库（如MongoDB），每条记录存储设备ID和时间序列数据数组D.使用列存数据库（如ClickHouse），按设备ID分区，时间戳排序9.为防止“脏读”，数据库的隔离级别至少需设置为（）A.读未提交（ReadUncommitted）B.读已提交（ReadCommitted）C.可重复读（RepeatableRead）D.串行化（Serializable）10.设计分布式数据库的分片策略时，若需保证“同一用户的所有数据存储在同一分片”，应采用的分片键是（）A.随机哈希分片B.范围分片（如按用户ID范围）C.基于用户ID的哈希分片D.基于时间戳的范围分片二、简答题（每题6分，共30分）1.简述第三范式（3NF）与BCNF的区别，并举例说明一个满足3NF但不满足BCNF的关系模式。2.说明聚簇索引与非聚簇索引的核心差异，分别列举一个适合使用聚簇索引和非聚簇索引的场景。3.分布式事务中，为何“网络分区”会破坏CAP定理中的一致性（Consistency）？请结合具体场景解释。4.数据仓库设计中，“事实表”与“维度表”的作用分别是什么？说明星型模型与雪花模型的主要区别。5.数据库备份策略中，“全量备份+增量备份”与“全量备份+日志备份”的适用场景有何不同？三、设计题（20分）某在线教育平台需设计数据库，核心业务包括：用户（学生、教师）注册与登录（需存储姓名、手机号、邮箱、角色）；课程发布（教师创建课程，包含课程名称、简介、开课时间、结束时间、最大人数）；学生选课（记录选课时间，需支持退课）；作业提交（教师发布作业，学生提交作业文件，记录提交时间、得分）；课程评价（学生结课后对课程评分，存储评分（1-5分）和文字评价）。要求：（1）绘制核心实体-关系（ER）图（需标注实体属性、联系类型及约束）；（2）将ER图转换为关系模式，标注主键（PK）和外键（FK）；（3）分析关系模式是否满足3NF，若不满足需说明原因并优化；（4）设计关键索引（至少3个），并说明理由；（5）针对“同时有10万学生选课”的高并发场景，提出2条数据库优化策略。四、综合应用题（30分）某医疗健康数据平台需整合医院HIS系统、体检中心、可穿戴设备（如智能手表）的多源数据，支持以下业务：患者全生命周期健康数据查询（包括诊断记录、检查报告、体检指标、心率/血压等实时监测数据）；医生对患者的跨院诊疗协作（需更新患者的用药记录、手术计划）；基于历史数据的疾病预测分析（如糖尿病风险评估）；符合GDPR/《个人信息保护法》的隐私保护（需支持数据去标识化、权限控制）。要求：（1）设计概念模型（ER图或UML类图），说明主要实体（至少5个）及其属性、实体间联系；（2）设计逻辑模型（关系模式），考虑多源数据的整合方式（如异构数据清洗、主数据管理）；（3）针对实时监测数据（如每5秒采集一次的心率），设计存储方案（包括数据库类型选择、表结构、索引策略）；（4）设计分布式事务方案（如医生跨院更新患者用药记录），需考虑网络延迟和节点故障；（5）提出3项隐私保护技术措施，并说明如何与数据库设计结合。答案--一、单项选择题1.B（按用户ID分库减少跨库查询，时间范围分区便于归档）2.B（推导：BC→B→AB→C→D→A，故BC能决定所有属性）3.C（3PC比2PC更能应对网络分区，减少阻塞）4.A（100ms内完成强调原子性，数据不丢失强调持久性）5.B（对象存储适合大文件，数据库存储元数据便于检索）6.C（手机号需精确查找用B+树，注册时间需范围查询用B+树）7.C（雪花模型通过维度表分层支持更复杂查询）8.B（时序数据库优化时间序列存储，支持标签查询和聚合）9.B（读已提交避免脏读）10.C（用户ID哈希分片保证数据局部性）二、简答题1.区别：3NF要求非主属性不传递依赖于主码，BCNF要求所有决定因素（包括主属性）都包含候选码。例：关系模式R（S,T,J），函数依赖{S→T,T→J,J→S}，候选码为S、T、J。所有属性都是主属性，无传递依赖（满足3NF），但存在T→J（T不是候选码），故不满足BCNF。2.核心差异：聚簇索引决定数据物理存储顺序，一个表仅一个；非聚簇索引存储索引键和行指针，可多个。场景：聚簇索引适合经常按范围查询的表（如订单表按时间排序）；非聚簇索引适合频繁按非主键字段查询（如用户表按手机号查询）。3.网络分区时，分布式系统被分割为多个独立子系统。若要求一致性（所有节点数据相同），则子系统间无法通信时需拒绝写操作（牺牲可用性）；若允许子系统独立写（保持可用性），则数据会不一致（牺牲一致性）。例：银行系统北京、上海节点因网络中断，北京节点扣减用户A余额后，上海节点无法同步，导致数据不一致。4.事实表：存储业务事件的度量值（如销售额）和外键（关联维度）；维度表：描述业务上下文（如时间、地区、产品）。星型模型：事实表直接关联维度表（维度表不进一步分层）；雪花模型：维度表可进一步分解为子维度表（如地区→国家→省份→城市）。5.全量+增量：适合数据修改频繁但备份空间有限的场景（如日志系统），恢复时需按顺序应用全量+所有增量；全量+日志：适合需要精确恢复到某时间点的场景（如核心交易系统），恢复时用全量+日志重放至目标时间。三、设计题（1）ER图：实体：用户（用户ID，姓名，手机号，邮箱，角色）；课程（课程ID，名称，简介，开课时间，结束时间，最大人数，教师ID）；选课记录（记录ID，用户ID，课程ID，选课时间，退课时间）；作业（作业ID，课程ID，教师ID，发布时间）；作业提交（提交ID，作业ID，用户ID，提交时间，文件路径，得分）；课程评价（评价ID，用户ID，课程ID，评分，评价内容，评价时间）。联系：教师（用户角色=教师）发布课程（1:N）；学生（用户角色=学生）选课（N:M，选课记录为联系实体）；教师发布作业（1:N，作业→课程）；学生提交作业（N:M，作业提交为联系实体）；学生评价课程（N:M，课程评价为联系实体）。（2）关系模式：用户（用户ID(PK)，姓名，手机号（唯一），邮箱（唯一），角色）课程（课程ID(PK)，名称，简介，开课时间，结束时间，最大人数，教师ID(FK→用户.用户ID)）选课记录（记录ID(PK)，用户ID(FK→用户.用户ID)，课程ID(FK→课程.课程ID)，选课时间，退课时间（允许空））作业（作业ID(PK)，课程ID(FK→课程.课程ID)，教师ID(FK→用户.用户ID)，发布时间）作业提交（提交ID(PK)，作业ID(FK→作业.作业ID)，用户ID(FK→用户.用户ID)，提交时间，文件路径，得分（允许空））课程评价（评价ID(PK)，用户ID(FK→用户.用户ID)，课程ID(FK→课程.课程ID)，评分（1-5），评价内容，评价时间）（3）3NF分析：所有非主属性均直接依赖于主码，无传递依赖（如课程的教师ID直接依赖课程ID，用户的角色直接依赖用户ID），满足3NF。（4）关键索引：用户表（手机号）：唯一索引，支持快速登录验证；课程表（教师ID，开课时间）：复合索引，支持教师查询自己发布的课程；选课记录（课程ID，选课时间）：索引，支持课程的选课人数统计和截止时间控制；作业提交（作业ID，用户ID）：复合唯一索引，防止同一学生重复提交同一作业。（5）高并发优化策略：分库分表：按用户ID哈希分库，选课记录按课程ID分表，减少单库压力；分布式事务：使用Seata等框架实现选课的原子性（扣减课程剩余名额+插入选课记录）；缓存预热：课程详情页缓存至Redis，减少数据库查询；异步处理：退课操作通过消息队列（如Kafka）异步处理，避免阻塞主流程。四、综合应用题（1）概念模型（ER图）：实体：患者（患者ID，姓名，身份证号（脱敏），出生日期）；医疗机构（机构ID，名称，类型（医院/体检中心））；诊疗记录（记录ID，患者ID，机构ID，诊断时间，诊断结果，医生ID）；检查报告（报告ID，患者ID，检查类型（CT/血检），检查时间，结果文件路径）；监测数据（数据ID，患者ID，设备ID，采集时间，心率，血压）；医生（医生ID，姓名，所属机构ID）。联系：患者在医疗机构就诊（N:M，诊疗记录为联系实体）；患者有检查报告（1:N）；患者通过设备提供监测数据（1:N）；医生属于医疗机构（N:1）；医生创建诊疗记录（N:1）。（2）逻辑模型：患者（患者ID(PK)，匿名ID（哈希身份证号），姓名，出生日期）医疗机构（机构ID(PK)，名称，类型）医生（医生ID(PK)，姓名，机构ID(FK→医疗机构.机构ID)）诊疗记录（记录ID(PK)，患者ID(FK→患者.患者ID)，机构ID(FK→医疗机构.机构ID)，医生ID(FK→医生.医生ID)，诊断时间，诊断结果）检查报告（报告ID(PK)，患者ID(FK→患者.患者ID)，检查类型，检查时间，结果路径（指向对象存储））监测数据（数据ID(PK)，患者ID(FK→患者.患者ID)，设备ID，采集时间，心率，血压）主数据表（源系统标识，源ID，患者ID，同步时间）：用于多源数据整合（如HIS系统的患者ID与可穿戴设备的用户ID映射）。（3）实时监测数据存储方案：数据库类型：选择时序数据库（如TimescaleDB），优化时间序列数据的写入和聚合查询；表结构：监测数据（患者ID，设备ID，采集时间(PK)，心率，血压）；索引策略：按患者ID和设备ID分区（减少跨分区查询），按采集时间创建聚簇索引（支持“近7天数据”的快速范围查询）；数据保留：设置自动过期策略（如保留2年数据，旧数据归档至对象存储）。（4）分布式事务方案（跨院更新用药记录）：采用TCC（Try-Confirm-Cancel）模式：Try阶段：北京医院锁定患者用药记录（标记为“更新中”），上海医院检查患者存在性并锁定；Confirm阶段：北京医院提交用药变更，上海医院同步更新；Cancel阶段：任一节点失败时，回滚锁定并恢复原数据；引入分布