数据库核心知识点总结
约 2501 字大约 8 分钟
2026-01-15
一、数据库设计的阶段及任务
数据库设计分为六个核心阶段,各阶段的核心任务如下:
| 设计阶段 | 核心任务 |
|---|---|
| 需求分析阶段 | 收集并分析用户的业务需求、数据需求和处理需求,梳理业务流程,输出数据字典和数据流图,明确系统边界。 |
| 概念结构设计阶段 | 基于需求分析结果,构建独立于具体数据库管理系统(DBMS)的概念模型(如ER图),描述实体、属性及实体间的关系,保证模型的完整性和一致性。 |
| 逻辑结构设计阶段 | 将ER模型转化为具体DBMS支持的关系模型(表结构),进行数据规范化处理,定义主键、外键、约束等,同时优化关系模式。 |
| 物理结构设计阶段 | 设计数据库的物理存储方案,包括存储路径、文件组织方式、索引设计、分区策略、存储参数配置等,目标是提升数据存取效率。 |
| 数据库实施阶段 | 依据设计结果创建数据库、表、视图等对象,导入测试数据,编写应用程序与数据库的交互接口,开展功能测试和性能测试。 |
| 数据库运行与维护阶段 | 日常监控数据库性能,执行数据备份与恢复、故障排查、数据更新、索引优化、结构调整等工作,保障数据库稳定运行。 |
二、逻辑模型设计阶段:ER模型转化为关系模型的步骤
- 实体转化为关系:每个实体对应一个关系(表),实体的属性作为表的列,实体的标识符(主键)作为表的主键。
- 一对一(1:1)关系转化:
- 方案1:将两个实体的属性合并为一个表,选取其中一个实体的主键作为整体主键。
- 方案2:两个实体分别建表,在任意一个表中添加另一个表的主键作为外键,并添加唯一约束。
- 一对多(1:n)关系转化:在“多”方的表中添加“一”方表的主键作为外键,通过外键实现关联。
- 多对多(m:n)关系转化:新建中间表,表中包含两个关联实体的主键(作为联合主键),可额外添加关系自身的属性(如订单与商品的关联表可添加“购买数量”)。
- 特殊属性处理:
- 复合属性:拆分为多个简单属性;
- 多值属性:新建表存储(或使用DBMS支持的数组类型);
- 派生属性:一般不存储,查询时动态计算。
三、从1NF到BCNF的规范化过程
1. 各范式的核心定义
| 范式 | 核心要求 | 解决的核心问题 |
|---|---|---|
| 1NF(第一范式) | 关系中的每个属性都是原子值(不可再分),无复合属性、多值属性。 | 消除属性的可分性和冗余 |
| 2NF(第二范式) | 在1NF基础上,非主属性完全函数依赖于主键(消除部分函数依赖)。 | 消除非主属性对主键的部分依赖 |
| 3NF(第三范式) | 在2NF基础上,非主属性不传递依赖于主键(消除传递函数依赖)。 | 消除非主属性的传递依赖 |
| BCNF(鲍依斯-科德范式) | 在3NF基础上,所有函数依赖的左部都是候选键(消除主属性的部分/传递依赖)。 | 消除主属性的依赖冗余 |
2. 规范化步骤
- 满足1NF:拆分复合属性、多值属性,使每个属性具有原子性。
- 升级至2NF:若存在非主属性对主键的部分依赖,拆分表,让非主属性完全依赖于主键。
- 升级至3NF:若存在非主属性对主键的传递依赖,拆分表,使非主属性直接依赖于主键。
- 升级至BCNF:若存在主属性对候选键的部分/传递依赖,进一步拆分表,确保所有函数依赖的左部都是候选键。
四、设计不良的关系模式的问题及解决方法
1. 主要问题(数据冗余与操作异常)
- 数据冗余:同一数据重复存储(如学生表中重复存储系部名称、系主任信息),浪费存储空间。
- 插入异常:无法插入合法数据(如新建系部但暂无学生时,系部信息无法存入)。
- 删除异常:删除数据时误删关联信息(如删除最后一个学生时,系部信息也被删除)。
- 更新异常:修改冗余数据时需修改多处,易导致数据不一致(如修改系主任时,需修改所有该系学生的记录)。
2. 解决方法
- 核心方案:进行数据规范化,通过拆分关系模式逐步满足更高范式(如从1NF优化至3NF/BCNF),消除依赖异常和数据冗余。
- 辅助方案:合理设计主键、外键约束,建立索引优化查询,使用视图屏蔽冗余数据,定期清理和维护数据。
五、视图表与视图的区别和联系
1. 定义
- 视图(View):数据库中的虚拟表,基于一个或多个表(或其他视图)的查询结果构建,不存储实际数据,仅保存查询定义。
- 视图表:通常指视图对应的虚拟表结构,包括视图的列名、数据类型、字段约束等元数据,是视图的具体表现形式。
2. 联系
- 视图表是视图的“结构载体”,视图的逻辑结构通过视图表呈现;
- 两者都基于底层表,数据均来自底层表的实时查询,不存储实际数据。
3. 区别
- 本质不同:视图是数据库的对象(查询定义),视图表是视图的结构描述(元数据);
- 用途不同:视图用于简化查询、屏蔽数据复杂度,视图表用于展示视图的列结构和关联关系。
六、事务的ACID属性及提交/回滚
1. 事务的ACID属性
- 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部执行,要么全部不执行。
- 一致性(Consistency):事务执行前后,数据库的完整性约束(如主键唯一、外键关联)保持不变。
- 隔离性(Isolation):多个事务并发执行时,彼此隔离,互不干扰(避免脏读、不可重复读、幻读)。
- 持久性(Durability):事务提交后,对数据库的修改永久保存,即使系统崩溃也不会丢失。
2. 事务的提交与回滚
- 提交(Commit):事务执行完成后,将所有修改永久写入数据库,事务结束。
- 回滚(Rollback):事务执行过程中出现错误或用户主动终止时,撤销事务中所有已执行的修改,恢复到事务开始前的状态。
七、数据库备份的基本步骤
- 确定备份需求:明确备份的范围(全库/部分表)、备份频率(实时/每日/每周)、备份介质(本地磁盘/云存储/磁带)。
- 选择备份类型:
- 全量备份:备份整个数据库的所有数据;
- 增量备份:仅备份上一次备份后修改的数据;
- 差异备份:备份上一次全量备份后修改的数据。
- 执行备份操作:使用DBMS自带工具(如SQL Server的SSMS、MySQL的mysqldump)或第三方备份工具执行备份。
- 验证备份有效性:恢复备份数据到测试环境,检查数据完整性和可用性。
- 存储与管理备份:将备份文件存储在安全的位置,做好备份日志记录(备份时间、类型、路径),定期清理过期备份。
八、SQL Server系统数据库及企业数据库设计方案
1. SQL Server的系统数据库
| 系统数据库 | 作用 |
|---|---|
master | 存储SQL Server的系统级信息(如登录账户、数据库配置、系统参数),是核心数据库。 |
model | 新建数据库的模板,所有新建数据库都以model为基础复制结构。 |
msdb | 存储SQL Server代理的作业、计划、备份恢复历史等信息。 |
tempdb | 临时数据库,存储临时表、临时变量、查询中间结果等,重启SQL Server后数据清空。 |
resource | 隐藏的系统数据库,存储SQL Server的系统对象(如系统视图、存储过程)。 |
2. 企业多数据库的最佳解决方案
针对企业多个数据库共享相同基础数据/对象的需求,最佳方案是使用SQL Server的“数据库快照”+“用户定义数据类型/公共表”+“存储过程/函数封装”,或更高效的创建公用数据库+数据库链接/同义词:
- 创建公用基础数据库:将所有数据库共享的基础数据(如员工信息、部门信息)、数据库对象(如通用存储过程、函数)集中存储在一个公用数据库中。
- 使用数据库链接/同义词:其他业务数据库通过数据库链接(Linked Server)访问公用数据库的对象,或创建同义词(Synonym)简化访问(如
CREATE SYNONYM dbo.Departments FOR CommonDB.dbo.Departments)。 - 配置权限与同步:为业务数据库分配访问公用数据库的只读/读写权限,通过触发器或SQL Server代理作业实现基础数据的实时同步。
- 利用用户自定义类型:将共享的字段类型(如手机号、身份证号)定义为用户自定义数据类型,统一各数据库的字段规范。
