关系数据库设计理论为什么我的表设计总是出问题一句话总结函数依赖是数据之间确定性的数学描述通过范式1NF→2NF→3NF→BCNF逐步消除数据冗余和更新异常是设计高效数据库表结构的核心理论武器。一、为什么需要设计理论假设你设计了一个学生选课信息表学号姓名系名系主任课程号课程名学分成绩001张三计算机赵教授C01数据库385001张三计算机赵教授C02数据结构490002李四电子钱教授C01数据库378003王五计算机赵教授C02数据结构488看起来一张表能存所有信息但这张表存在严重问题1.1 数据冗余张三的姓名、系名、系主任重复了 2 次数据库课程的名称、学分重复了 2 次如果计算机系有 1000 个学生赵教授的名字就要存 1000 次1.2 更新异常修改异常赵教授升为院长系主任换人。需要改 1000 条记录漏改一条就数据不一致。插入异常新系成立还没有学生。无法插入系信息学号是主键不能为空。删除异常张三退学删除他的记录时连带的计算机系系主任是赵教授这条信息也丢失了。1.3 根本原因n这张表把**多个实体学生、系、课程和多个关系属于、选课**混在了一起。关系数据库设计理论就是教我们如何把一张大杂烩表拆分成结构合理、没有冗余的小表。二、函数依赖设计理论的数学基石2.1 什么是函数依赖函数依赖Functional Dependency, FD是关系数据库设计理论的核心概念。定义如果属性集 X 的值能唯一确定属性集 Y 的值则称X 函数决定 Y或Y 函数依赖于 X记作X → Y。类比数学函数y f(x)给定 x 就能唯一确定 y。2.2 示例分析在学生表中学号 → 姓名给定学号 001姓名一定是张三学号 → 系名给定学号 001系名一定是计算机系名 → 系主任给定系名计算机系主任一定是赵教授课程号 → 课程名给定课程号 C01课程名一定是数据库课程号 → 学分给定课程号 C01学分一定是 3(学号, 课程号) → 成绩给定学号和课程号成绩唯一确定注意反过来不成立。知道姓名张三不能确定学号可能有重名。2.3 函数依赖的分类类型定义示例平凡依赖Y ⊆ X即 X → Y 是显然成立的(学号, 姓名) → 姓名非平凡依赖Y ⊄ X学号 → 系名完全依赖X → Y且 Y 不依赖于 X 的任何真子集(学号, 课程号) → 成绩部分依赖X → Y但 Y 依赖于 X 的某个真子集(学号, 课程号) → 姓名学号就能决定姓名传递依赖X → Y, Y → Z则 X → Z 是传递依赖学号 → 系名 → 系主任部分依赖和传递依赖就是导致数据冗余和更新异常的罪魁祸首三、范式表设计的体检标准范式Normal Form, NF是关系模式的分级标准每一级范式消除一类问题。┌─────────────────────────────────────────┐ │ BCNF ⊂ 3NF ⊂ 2NF ⊂ 1NF ⊂ 关系 │ │ 越往后约束越严格冗余越少 │ └─────────────────────────────────────────┘3.1 第一范式1NF原子性规则每个属性值必须是不可再分的原子值。❌ 不符合 1NF学号姓名联系方式001张三13800138000, zhangsanqq.com联系方式可以拆成手机号和邮箱两列。✅ 符合 1NF学号姓名手机号邮箱001张三13800138000zhangsanqq.com1NF 是所有关系数据库的基本要求。达不到 1NF就不是关系数据库。3.2 第二范式2NF消除部分依赖规则在满足 1NF 的基础上消除非主属性对主键的部分依赖。前提表必须有组合主键单属性主键一定满足 2NF。回顾问题表主键是(学号, 课程号)姓名、系名、系主任 只依赖于学号主键的一部分→部分依赖课程名、学分 只依赖于课程号主键的一部分→部分依赖只有成绩完全依赖于整个主键 (学号, 课程号)拆分方案学生表主键学号学号姓名系名001张三计算机002李四电子课程表主键课程号课程号课程名学分C01数据库3C02数据结构4选课表主键学号 课程号学号课程号成绩001C0185001C0290002C0178拆分后每个非主属性都完全依赖于主键消除了部分依赖。3.3 第三范式3NF消除传递依赖规则在满足 2NF 的基础上消除非主属性对主键的传递依赖。回顾学生表学号姓名系名系主任学号 → 系名 → 系主任“系主任通过系名传递依赖于学号”问题如果系主任换了需要改该系所有学生记录。拆分方案学生表主键学号学号姓名系号001张三D01002李四D02系表主键系号系号系名系主任D01计算机赵教授D02电子钱教授拆分后每个非主属性都直接依赖于主键没有传递依赖。3.4 BCNFBoyce-Codd Normal Form规则在满足 3NF 的基础上消除主属性对候选键的部分依赖和传递依赖。BCNF 是 3NF 的强化版要求每个决定因素都是候选键。3NF 但非 BCNF 的示例学生导师表假设一个学生只能有一个导师一个导师可以有多个学生一个学生可以有多个专业学生专业导师张三计算机赵教授张三人工智能赵教授李四计算机钱教授函数依赖(学生, 专业) → 导师主键学生 → 导师假设一个学生只能有一个导师这里学生是决定因素但学生不是候选键。因此不满足 BCNF。BCNF 拆分学生导师表主键学生学生导师张三赵教授李四钱教授学生专业表主键学生 专业学生专业张三计算机张三人工智能李四计算机实际工程中3NF 或 BCNF通常就是设计目标。更高的范式4NF、5NF用于特殊场景一般课程不要求掌握。四、范式总结范式核心要求解决的问题拆分方法1NF属性不可再分数据重复、查询困难把复合属性拆开2NF消除部分依赖插入/删除异常、冗余按主键拆分出独立表3NF消除传递依赖更新异常、冗余把传递依赖链拆开BCNF所有决定因素都是候选键更隐蔽的冗余进一步拆分五、设计原则规范化的代价与反规范化5.1 规范化并非越多越好过度规范化如拆到 5NF会导致表数量过多查询需要大量 JOIN性能下降开发和维护复杂度增加5.2 反规范化Denormalization在特定场景下有意保留部分冗余以提高查询性能数据仓库为了分析查询故意冗余存储报表系统为了快速展示预计算并存储汇总数据高并发读取用空间换时间反规范化是高级优化手段初学者应先掌握规范化再根据实际情况权衡。六、动手练习练习 1分析函数依赖给定以下订单信息表主键订单号 商品号| 订单号 | 商品号 | 商品名 | 单价 | 数量 | 客户号 | 客户名 | 下单日期 |分析有哪些函数依赖哪些是部分依赖哪些是传递依赖将其规范化到 3NF。练习 2判断范式级别判断以下关系模式属于第几范式模式 A学生(学号, 姓名, 课程号, 课程名, 成绩)主键(学号, 课程号)模式 B员工(员工号, 姓名, 部门号, 部门名, 部门经理)主键员工号模式 C选课(学生, 课程, 教师, 成绩)假设每个学生每门课只有一个成绩每个教师只教一门课每门课有多个教师。主键(学生, 课程)。练习 3规范化设计将以下表规范化到 3NF员工项目表员工号, 员工名, 部门号, 部门名, 项目号, 项目名, 职责, 工时约束一个员工属于一个部门一个员工可以参与多个项目一个项目有多个员工。七、常见误区与避坑指南误区正确理解“所有表都必须到 3NF”大多数情况到 3NF/BCNF 即可数据仓库常反规范化“拆分表越多越专业”过度拆分增加 JOIN 开销得不偿失“1NF 只是把列拆细”1NF 还要求每个单元格是原子值不能是数组/集合“主键一定是单属性”组合主键很常见如选课表的主键是(学号, 课程号)“BCNF 和 3NF 没区别”3NF 允许主属性有传递依赖BCNF 更严格八、下篇预告下一篇我们将学习数据库设计方法——从需求分析到 E-R 图设计再到逻辑结构设计和物理设计完整走完数据库设计的全过程。这是将理论应用到实践的关键一步。
关系数据库设计理论为什么我的表设计总是出问题一句话总结函数依赖是数据之间确定性的数学描述通过范式1NF→2NF→3NF→BCNF逐步消除数据冗余和更新异常是设计高效数据库表结构的核心理论武器。一、为什么需要设计理论假设你设计了一个学生选课信息表学号姓名系名系主任课程号课程名学分成绩001张三计算机赵教授C01数据库385001张三计算机赵教授C02数据结构490002李四电子钱教授C01数据库378003王五计算机赵教授C02数据结构488看起来一张表能存所有信息但这张表存在严重问题1.1 数据冗余张三的姓名、系名、系主任重复了 2 次数据库课程的名称、学分重复了 2 次如果计算机系有 1000 个学生赵教授的名字就要存 1000 次1.2 更新异常修改异常赵教授升为院长系主任换人。需要改 1000 条记录漏改一条就数据不一致。插入异常新系成立还没有学生。无法插入系信息学号是主键不能为空。删除异常张三退学删除他的记录时连带的计算机系系主任是赵教授这条信息也丢失了。1.3 根本原因n这张表把**多个实体学生、系、课程和多个关系属于、选课**混在了一起。关系数据库设计理论就是教我们如何把一张大杂烩表拆分成结构合理、没有冗余的小表。二、函数依赖设计理论的数学基石2.1 什么是函数依赖函数依赖Functional Dependency, FD是关系数据库设计理论的核心概念。定义如果属性集 X 的值能唯一确定属性集 Y 的值则称X 函数决定 Y或Y 函数依赖于 X记作X → Y。类比数学函数y f(x)给定 x 就能唯一确定 y。2.2 示例分析在学生表中学号 → 姓名给定学号 001姓名一定是张三学号 → 系名给定学号 001系名一定是计算机系名 → 系主任给定系名计算机系主任一定是赵教授课程号 → 课程名给定课程号 C01课程名一定是数据库课程号 → 学分给定课程号 C01学分一定是 3(学号, 课程号) → 成绩给定学号和课程号成绩唯一确定注意反过来不成立。知道姓名张三不能确定学号可能有重名。2.3 函数依赖的分类类型定义示例平凡依赖Y ⊆ X即 X → Y 是显然成立的(学号, 姓名) → 姓名非平凡依赖Y ⊄ X学号 → 系名完全依赖X → Y且 Y 不依赖于 X 的任何真子集(学号, 课程号) → 成绩部分依赖X → Y但 Y 依赖于 X 的某个真子集(学号, 课程号) → 姓名学号就能决定姓名传递依赖X → Y, Y → Z则 X → Z 是传递依赖学号 → 系名 → 系主任部分依赖和传递依赖就是导致数据冗余和更新异常的罪魁祸首三、范式表设计的体检标准范式Normal Form, NF是关系模式的分级标准每一级范式消除一类问题。┌─────────────────────────────────────────┐ │ BCNF ⊂ 3NF ⊂ 2NF ⊂ 1NF ⊂ 关系 │ │ 越往后约束越严格冗余越少 │ └─────────────────────────────────────────┘3.1 第一范式1NF原子性规则每个属性值必须是不可再分的原子值。❌ 不符合 1NF学号姓名联系方式001张三13800138000, zhangsanqq.com联系方式可以拆成手机号和邮箱两列。✅ 符合 1NF学号姓名手机号邮箱001张三13800138000zhangsanqq.com1NF 是所有关系数据库的基本要求。达不到 1NF就不是关系数据库。3.2 第二范式2NF消除部分依赖规则在满足 1NF 的基础上消除非主属性对主键的部分依赖。前提表必须有组合主键单属性主键一定满足 2NF。回顾问题表主键是(学号, 课程号)姓名、系名、系主任 只依赖于学号主键的一部分→部分依赖课程名、学分 只依赖于课程号主键的一部分→部分依赖只有成绩完全依赖于整个主键 (学号, 课程号)拆分方案学生表主键学号学号姓名系名001张三计算机002李四电子课程表主键课程号课程号课程名学分C01数据库3C02数据结构4选课表主键学号 课程号学号课程号成绩001C0185001C0290002C0178拆分后每个非主属性都完全依赖于主键消除了部分依赖。3.3 第三范式3NF消除传递依赖规则在满足 2NF 的基础上消除非主属性对主键的传递依赖。回顾学生表学号姓名系名系主任学号 → 系名 → 系主任“系主任通过系名传递依赖于学号”问题如果系主任换了需要改该系所有学生记录。拆分方案学生表主键学号学号姓名系号001张三D01002李四D02系表主键系号系号系名系主任D01计算机赵教授D02电子钱教授拆分后每个非主属性都直接依赖于主键没有传递依赖。3.4 BCNFBoyce-Codd Normal Form规则在满足 3NF 的基础上消除主属性对候选键的部分依赖和传递依赖。BCNF 是 3NF 的强化版要求每个决定因素都是候选键。3NF 但非 BCNF 的示例学生导师表假设一个学生只能有一个导师一个导师可以有多个学生一个学生可以有多个专业学生专业导师张三计算机赵教授张三人工智能赵教授李四计算机钱教授函数依赖(学生, 专业) → 导师主键学生 → 导师假设一个学生只能有一个导师这里学生是决定因素但学生不是候选键。因此不满足 BCNF。BCNF 拆分学生导师表主键学生学生导师张三赵教授李四钱教授学生专业表主键学生 专业学生专业张三计算机张三人工智能李四计算机实际工程中3NF 或 BCNF通常就是设计目标。更高的范式4NF、5NF用于特殊场景一般课程不要求掌握。四、范式总结范式核心要求解决的问题拆分方法1NF属性不可再分数据重复、查询困难把复合属性拆开2NF消除部分依赖插入/删除异常、冗余按主键拆分出独立表3NF消除传递依赖更新异常、冗余把传递依赖链拆开BCNF所有决定因素都是候选键更隐蔽的冗余进一步拆分五、设计原则规范化的代价与反规范化5.1 规范化并非越多越好过度规范化如拆到 5NF会导致表数量过多查询需要大量 JOIN性能下降开发和维护复杂度增加5.2 反规范化Denormalization在特定场景下有意保留部分冗余以提高查询性能数据仓库为了分析查询故意冗余存储报表系统为了快速展示预计算并存储汇总数据高并发读取用空间换时间反规范化是高级优化手段初学者应先掌握规范化再根据实际情况权衡。六、动手练习练习 1分析函数依赖给定以下订单信息表主键订单号 商品号| 订单号 | 商品号 | 商品名 | 单价 | 数量 | 客户号 | 客户名 | 下单日期 |分析有哪些函数依赖哪些是部分依赖哪些是传递依赖将其规范化到 3NF。练习 2判断范式级别判断以下关系模式属于第几范式模式 A学生(学号, 姓名, 课程号, 课程名, 成绩)主键(学号, 课程号)模式 B员工(员工号, 姓名, 部门号, 部门名, 部门经理)主键员工号模式 C选课(学生, 课程, 教师, 成绩)假设每个学生每门课只有一个成绩每个教师只教一门课每门课有多个教师。主键(学生, 课程)。练习 3规范化设计将以下表规范化到 3NF员工项目表员工号, 员工名, 部门号, 部门名, 项目号, 项目名, 职责, 工时约束一个员工属于一个部门一个员工可以参与多个项目一个项目有多个员工。七、常见误区与避坑指南误区正确理解“所有表都必须到 3NF”大多数情况到 3NF/BCNF 即可数据仓库常反规范化“拆分表越多越专业”过度拆分增加 JOIN 开销得不偿失“1NF 只是把列拆细”1NF 还要求每个单元格是原子值不能是数组/集合“主键一定是单属性”组合主键很常见如选课表的主键是(学号, 课程号)“BCNF 和 3NF 没区别”3NF 允许主属性有传递依赖BCNF 更严格八、下篇预告下一篇我们将学习数据库设计方法——从需求分析到 E-R 图设计再到逻辑结构设计和物理设计完整走完数据库设计的全过程。这是将理论应用到实践的关键一步。
