软考系统分析师数据库系统知识全梳理数据库系统是软考系统分析师的核心考点占比约12%~18%上午综合知识选择、下午案例分析必考论文也常涉及。以下按大纲要求分模块系统梳理标注高频考点、难点、解题技巧。一、数据库系统基础上午选择高频1. 核心概念数据库DB长期存储、有组织、可共享、统一管理的数据集合。数据库管理系统DBMS操纵和管理数据库的软件如Oracle、MySQL核心功能DDL数据定义建库、表、视图、索引DML数据操纵增删改查INSERT/DELETE/UPDATE/SELECTDCL数据控制授权、回收、事务控制数据库维护、备份恢复、性能优化数据库系统DBSDB DBMS 应用程序 DBA 用户2. 三级模式 - 两级映像必背外模式子模式/用户模式用户视图局部逻辑结构一个DB可有多个模式概念模式全局逻辑结构所有实体及关系的定义唯一内模式物理模式物理存储结构文件、索引、存储路径两级映像外模式/模式映像 →逻辑独立性逻辑结构变应用不变模式/内模式映像 →物理独立性存储结构变应用不变3. 数据模型概念模型E-R模型实体、属性、联系逻辑模型关系模型主流、层次、网状、面向对象物理模型与硬件、OS、DBMS相关的存储实现二、关系数据库核心选择案例计算1. 关系代数计算题必考基本运算并∪、交∩、差−、笛卡尔积×选择σ、投影π高级运算自然连接⋈公共属性等值连接去重列等值连接/θ连接按条件连接除运算÷判断“包含全部”场景2. 函数依赖与Armstrong公理函数依赖FDX→YX确定则Y唯一确定Armstrong公理推理规则自反律Y⊆X → X→Y增广律X→Y → XZ→YZ传递律X→Y, Y→Z → X→Z推导规则合并X→Y, X→Z → X→YZ分解X→YZ → X→Y, X→Z伪传递X→Y, WY→Z → WX→Z合并和增广容易弄混淆只在右边增加的事合并两边同时增加的是增广。可以记为祐禾两增3. 键与完整性必背键的分类超键唯一标识元组可含冗余候选键最小超键无冗余可多个主键候选键中选一个唯一非空外键另一表主键建立关联主属性候选键包含的属性非主属性反之三大完整性实体完整性主键非空、唯一参照完整性外键是另一表主键或NULL用户自定义完整性年龄、性别、范围等规则4. 规范化理论难点必考核心消除冗余、异常插入/删除/更新1NF属性原子不可再分2NF满足1NF消除非主属性对主键的部分函数依赖3NF满足2NF消除非主属性对主键的传递函数依赖BCNF修正3NF所有决定因素都是候选键消除主属性部分/传递依赖4NF消除非平凡且非函数依赖的多值依赖解题步骤范式判断找主键/候选键 → 2. 分主/非主属性 → 3. 查部分依赖 → 4. 查传递依赖 → 5. 查主属性依赖5. 模式分解无损连接分解分解后自然连接可还原原关系保持函数依赖分解分解后所有FD仍被保持算法无损连接判定表格法、保持依赖判定、3NF/BCNF分解算法三、数据库设计案例大题核心四阶段流程必考需求分析数据流图DFD、数据字典、业务规则概念设计E-R模型实体、属性、1:1/1:N/M:N联系、弱实体、继承逻辑设计E-R转关系实体 → 表1:1 → 任一方加外键1:N → N端加1端主键作外键M:N → 新建关系表含双方主键多元联系、弱实体、子类处理物理设计存储结构、索引、分区、聚簇、存取路径四、SQL语言基础复杂查询1. 核心语法DDLCREATE/ALTER/DROP TABLE/VIEW/INDEXDMLSELECT重点、INSERT、DELETE、UPDATEDCLGRANT、REVOKE事务BEGIN、COMMIT、ROLLBACK2. 高级查询案例常考连接查询内连接、外连接左/右/全、自然连接分组与筛选GROUP BY HAVING子查询相关/非相关、EXISTS、IN、ALL/ANY集合运算UNION、INTERSECT、EXCEPT视图、索引、序列、存储过程/触发器示例高频SELECT部门名称,COUNT(员工ID)人数,AVG(薪资)平均FROM部门LEFTJOIN员工ON部门.ID员工.部门IDWHERE入职2020-01-01GROUPBY部门名称HAVINGCOUNT(员工ID)5ORDERBY平均DESC;五、事务管理与并发控制高频1. 事务ACID必背原子性A全成功或全回滚一致性C事务前后数据一致隔离性I事务间互不干扰持久性D提交后永久生效2. 并发问题丢失修改脏读读未提交数据不可重复读同一事务两次读不同幻读读到新增/删除数据3. 封锁机制共享锁S锁/读锁加S可再S不可加X排他锁X锁/写锁加X不可加任何锁三级封锁协议一级X锁至结束 → 防丢失修改二级S锁读 → 防脏读三级S锁至结束 → 防不可重复读先明确两个基础锁X锁排他锁/写锁写数据加独占别人不能读、不能写S锁共享锁/读锁读数据加共享别人可以读、不能写事务结束 事务提交Commit / 回滚Rollback级别X锁写S锁读解决问题一级事务结束释放不加锁丢失修改二级事务结束释放读完立即释放丢失修改 脏读三级事务结束释放事务结束释放丢失修改 脏读 不可重复读封锁级别越高并发度越低一致性越高不可重复读的核心读锁过早释放三级把读锁留到事务结束彻底杜绝幻读三级封锁依然无法解决需要串行化/间隙锁问题产生原因几级能解决丢失修改并发更新覆盖一级脏读读未提交数据二级不可重复读中途数据被修改三级幻读范围内新增/删除三级解决不了需串行化4. 隔离级别4级读未提交RU→ 读已提交RC→ 可重复读RR→ 串行化S级别越高一致性越强并发越低六、数据库恢复与安全1. 故障类型事务故障、系统故障、介质故障、计算机病毒2. 恢复技术日志文件记录修改前/后值UNDO/REDO检查点减少恢复时间备份静态/动态、海量/增量、异地备份恢复策略事务故障UNDO系统故障UNDO未提交、REDO已提交介质故障重装备份 REDO3. 安全机制用户认证、权限管理GRANT/REVOKE视图、存储过程、触发器加密、审计、入侵检测七、分布式数据库高频1. 特点分布性、逻辑整体性、自治性、透明性2. 分片与分布分片水平行、垂直列、混合分布透明性分片透明、位置透明、局部数据模型透明3. CAP定理必背C一致性、A可用性、P分区容错三选二分布式下P必须保证 → CP或AP4. BASE理论基本可用、软状态、最终一致性对CAP的延伸八、数据仓库与数据挖掘1. 数据仓库DW4特性面向主题、集成、稳定、时变vs 操作型DB分析型/事务型、只读/频繁更新、历史/当前模型星型、雪花型、事实星座2. 数据挖掘DM任务分类、聚类、关联规则如Apriori、预测、时序分析九、NoSQL非关系型数据库分类键值Redis、文档MongoDB、列族HBase、图Neo4j适用场景高并发、海量数据、灵活 schema、分布式扩展十、高频考点与应试技巧1. 上午选择高频三级模式两级映像、独立性范式判断1NF/2NF/3NF/BCNF函数依赖、候选键、无损连接事务ACID、锁、隔离级别、并发问题CAP/BASE、NoSQL分类SQL基础语法、关系代数2. 下午案例必考E-R设计 转关系模式规范化与模式分解SQL复杂查询、视图、索引并发控制、故障恢复、性能优化分布式数据库设计3. 解题技巧范式题先找主键 → 查部分依赖 → 查传递依赖E-R转关系M:N必建新表1:N外键放N端无损连接表格法判断SQL多表用连接分组必聚合过滤先WHERE后HAVING十一、论文方向数据库相关数据库设计方法与实践高并发系统的数据库优化分布式数据库架构设计数据仓库与BI系统建设数据库安全与容灾方案