数据库核心知识点总结
一、数据库设计的阶段及任务
数据库设计分为六个核心阶段，各阶段的核心任务如下：
| 设计阶段 | 核心任务 |
|---|---|
| 需求分析阶段 | 收集并分析用户的业务需求、数据需求和处理需求，梳理业务流程，输出数据字典和数据流图，明确系统边界。 |
| 概念结构设计阶段 | 基于需求分析结果，构建独立于具体数据库管理系统（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代理作业实现基础数据的实时同步。
- 利用用户自定义类型：将共享的字段类型（如手机号、身份证号）定义为用户自定义数据类型，统一各数据库的字段规范。
