数据库归一化和范式

站长

2024年03月19日 22:27 · 阅读数 8

数据库归一化是一种设计技术，用于优化数据库结构，使其更加有序和高效。这个过程通过组织数据来减少冗余和依赖，从而提高数据的完整性和一致性。归一化主要通过以下方式实现：

消除重复数据：通过分解数据表和创建关联，减少数据冗余。这有助于减少存储空间的使用，并简化数据更新、插入和删除操作。
减少数据更新异常：如果数据存储在多个地方，更新数据时可能会出现不一致的情况。归一化通过确保每个数据项只在一个地方存储来防止这种情况。
提高数据完整性：通过使用外键等约束，保证数据之间的关联性和一致性。
简化查询：良好的归一化设计可以使查询更加简单直接。

归一化通常通过不同的“范式”来实现，每个范式都有其特定的规则。常见的范式包括：

第一范式 (1NF)：确保每个表的每个列都是不可分割的基本数据项。
第二范式 (2NF)：在1NF的基础上，确保每个表中的所有数据都与该表的主键相关。
第三范式 (3NF)：在2NF的基础上，确保每个非主键列都直接依赖于主键，而不是间接依赖。

还有更高级的范式，如BCNF、4NF和5NF，但在实际应用中，多数数据库设计通常到达第三范式就足够了。过度归一化可能会导致复杂的表结构和降低查询性能，因此需要根据实际应用的需求和特点来平衡。

详细介绍一下这三个基本范式（1NF、2NF、3NF），并通过MySQL数据库的例子来说明它们。

第一范式 (1NF)

定义：

第一范式要求表的每个列都是原子的，即每个字段都不能再分割成更小的数据单位。

例子：假设有一个学生课程表，记录学生参加的课程。如果一个学生可以参加多个课程，而这些课程都记录在一个字段中，那么这个表就不满足1NF。

不满足1NF的表：

+-----------+------------------+
| StudentID | Courses          |
+-----------+------------------+
| 1         | Math, English    |
| 2         | Biology, Physics |
+-----------+------------------+

为了符合1NF，我们需要把课程分开，使每一行都只表示一个学生和一个课程的关系。

符合1NF的表：

+-----------+---------+
| StudentID | Course  |
+-----------+---------+
| 1         | Math    |
| 1         | English |
| 2         | Biology |
| 2         | Physics |
+-----------+---------+

第二范式 (2NF)

定义：

第二范式基于1NF，要求表的所有非键字段都完全依赖于表的主键。

例子：假设有一个学生课程成绩表，记录学生在各个课程的成绩以及课程教师。

不满足2NF的表（假设主键是 StudentID 和 Course）：

+-----------+---------+--------+--------+
| StudentID | Course  | Grade  | Teacher|
+-----------+---------+--------+--------+
| 1         | Math    | A      | Mr. X  |
| 1         | English | B      | Ms. Y  |
| 2         | Math    | B      | Mr. X  |
| 2         | Physics | C      | Mr. Z  |
+-----------+---------+--------+--------+

在这个表中，Teacher 字段实际上只依赖于 Course 而非整个主键（StudentID 和 Course 的组合）。

为了符合2NF，我们可以把 Teacher 字段移到一个单独的表中。

符合2NF的表：

学生课程成绩表：
+-----------+---------+--------+
| StudentID | Course  | Grade  |
+-----------+---------+--------+
| 1         | Math    | A      |
| 1         | English | B      |
| 2         | Math    | B      |
| 2         | Physics | C      |
+-----------+---------+--------+

课程教师表：
+---------+--------+
| Course  | Teacher|
+---------+--------+
| Math    | Mr. X  |
| English | Ms. Y  |
| Physics | Mr. Z  |
+---------+--------+

第三范式 (3NF)

定义：

第三范式要求一个表已经处于2NF，并且所有非主键列都不依赖于其他非主键列，即消除了传递依赖。

例子：假设有一个学生信息表，其中包含学生的ID、地址以及地址的邮政编码。

不满足3NF的表：

+-----------+-------------------+------------+
| StudentID | Address           | PostalCode |
+-----------+-------------------+------------+
| 1         | 123 Apple St.     | 12345      |
| 2         | 456 Banana Ave.   | 67890      |
| 3         | 789 Cherry Blvd.  | 12345      |
+-----------+-------------------+------------+

在这个表中，PostalCode 依赖于 Address 而非直接依赖于 StudentID（主键），表现出了传递依赖。

为了满足3NF，我们可以把地址和邮政编码分离到另一个表中。

符合3NF的表：

学生表：
+-----------+--------+
| StudentID | AddressID |
+-----------+--------+
| 1         | 1      |
| 2         | 2      |
| 3         | 3      |
+-----------+--------+

地址表：
+----------+-------------------+------------+
| AddressID| Address           | PostalCode |
+----------+-------------------+------------+
| 1        | 123 Apple St.     | 12345      |
| 2        | 456 Banana Ave.   | 67890      |
| 3        | 789 Cherry Blvd.  | 12345      |
+----------+-------------------+------------+

在这个修正后的设计中，PostalCode 现在与 Address 直接相关联，而不是通过 StudentID 间接相关。这样，每个表中的非主键列都直接依赖于它们自己的主键，满足了3NF的要求。

通过这样的设计，我们确保了数据的完整性和减少了数据冗余，同时也简化了数据的维护。from刘金，转载请注明原文链接。感谢！