SQL 规范化 (1NF - 5NF)：初学者指南

SQL 中的规范化是什么？

在关系数据库环境中，规范化是指一种组织数据的方法，旨在消除数据冗余和其他异常。简而言之，规范化涉及将大的、复杂的表拆分成更小、更易于管理的表，同时保持各表之间的数据关系。这种方法特别适用于处理大型数据集时，以确保数据的一致性和完整性。

我们先简单看一下一些经常使用归一化的场景。

数据完整性

设想一个存储客户信息的数据库。如果没有进行规范化，当客户的年龄发生变化时，就需要在数据库的多个位置进行更新，这增加了数据不一致的风险。通过规范化数据，我们可以将信息分解成通过唯一标识符相互关联的独立表，这样可以确保数据的准确性和一致性。

效率查询

让我们考虑一个包含多个相关表的复杂数据库，这些表中存储了冗余信息。在这种情况下，涉及到表连接的查询不仅会变得更加复杂，还会消耗更多资源。通过规范化，可以将数据拆分成更小的、专门化的表，每个表只包含必要的信息，从而简化查询并减少对复杂连接操作的需求。

存储优化

冗余数据的一个主要问题是它会占用不必要的存储空间。例如，如果在每条订单记录中都存储相同的产品详细信息，就会造成数据的重复。通过规范化，可以通过将数据分离到不同的表中来消除这种冗余。

为什么 SQL 规范化很重要？

规范化在数据库设计中扮演着关键角色。以下是其重要性的几个方面：

• 减少冗余：冗余指的是相同信息的多次存储。避免这一点的有效方法是将数据分解成更小的表。
• 提高查询性能：规范化后的较小表可以支持更快的查询执行。
• 最小化更新异常：使用规范化的表结构，可以更轻松地更新数据，而不影响其他记录。
• 增强数据完整性：确保数据的一致性和准确性。

通过规范化，可以有效地管理和组织数据，从而提高数据库的整体质量和性能。

什么原因导致需要标准化？

如果一张表没有经过适当的规范化处理，就会出现数据冗余，这不仅浪费存储空间，还会使数据库的处理和更新变得复杂。推动规范化需求的因素有很多，其中包括数据冗余以及管理表间关系的困难。以下是几种情况说明为什么需要规范化：

• 插入、删除和更新异常：如果数据没有正确管理，对表中的任何更改（如插入新记录、更新现有数据或删除记录）都可能导致数据错误或不一致。这些异常可能会导致数据的意外丢失或损坏。
• 管理关系的困难：在非规范化的数据库结构中，维护复杂的表间关系会更加困难。
• 其他推动因素：部分依赖和传递依赖也是促使进行规范化的因素之一。部分依赖会导致数据冗余和更新异常；而传递依赖则可能导致数据异常。接下来我们会探讨如何处理这些依赖关系，以确保数据库达到规范化的状态。

我们将在接下来的部分中研究如何处理这些依赖关系以确保数据库规范化。

不同类型的数据库规范化

到目前为止，我们已经介绍了SQL规范化是什么，为什么它很重要，以及促使进行规范化的各种原因。数据库规范化有不同的级别，每个级别都代表着不同程度的数据组织。

在本节中，我们将简要概述这些不同的规范化级别，并在接下来的部分中对它们进行更详细的探讨。

第一范式 (1NF)

在这个规范化级别中，确保表中的每一列都只包含原子值，即列中的每个条目都是不可分割的。换句话说，就像在电子表格中，每个单元格只存储单一的信息。1NF 保证了数据的原子性，确保每个列单元格只包含单个值，并且每个列都有独特的名称。

第二范式 (2NF)

在此基础上，通过消除部分依赖关系来进一步规范化数据。这意味着非键字段必须完全依赖于整个主键，而非主键的一部分。也就是说，每列都必须直接与主键相关，而不是与其他非键字段相关。

第三范式 (3NF)

在此级别上，进一步消除传递依赖关系。这意味着除了主键之外，非键字段不应依赖于其他非键字段。3NF 构建在2NF的基础上，提供了更高的数据完整性。

Boyce-Codd 范式 (BCNF)

这是一种比3NF更为严格的规范化形式，用于解决其他类型的异常情况。在BCNF中，每个决定因素（即影响其他列的列）都应该是候选键。

第四范式 (4NF)

这是建立在BCNF基础上的规范化级别，它关注的是消除多值依赖关系。4NF 确保表中不存在不属于主键的一部分却与主键有多值依赖关系的列。

第五范式 (5NF)

5NF 是为了解决连接依赖关系的最高级别的规范化形式。它用于特定情况，通过将表进一步拆分成更小的表来减少冗余。

数据库规范化与现实世界的例子

我们已经突出显示了所有数据标准化级别。让我们通过示例和解释进一步深入探讨它们中的每一个。

第一范式 (1NF)

1NF 确保每个列单元格只包含单一的原子值。以一个图书馆数据库为例，该数据库中有一个表用于存储图书信息（如书名、作者、类别和借阅者）。如果这个表没有规范化，那么借阅者列 (borrowed_by) 可能会包含用逗号分隔的多名借阅者的姓名列表。这种情况违反了1NF的原则，因为一个单元格中包含了多个值。下面是一个违反1NF的表的例子，正如前面所述。

解决方案是什么？

在1NF中，我们通过创建一个单独的借阅者表并与图书表建立关联来解决问题。这些表可以通过在借阅者表中添加一个外键来链接，这个外键引用图书表的主键。这样做可以确立表之间的关系，从而确保数据的一致性。

您可以在下面找到对此的表示：

Books table

Borrowers table

第二范式 (2NF)

正如前面所述，这个规范化级别是在1NF的基础上建立的，它确保主键没有部分依赖。换句话说，所有非键字段都必须完全依赖于整个主键，而不仅仅依赖于主键的一部分。

从已经实现的1NF开始，我们已经有了一张图书表和一张借阅者表（如1NF部分所述）。

现在，假设我们要链接这些表来记录借款。最初的方法可能是简单地向books 表添加一个borrower_id 列，如下所示：

这看起来像是一个解决方案，但它违反了2NF，因为borrower_id 仅部分依赖于 book_id。一本书可以有多个借阅者，但在此结构中，单个borrower_id 只能链接到一本书。这会产生部分依赖性。

解决方案是什么？

我们需要实现图书和借阅者之间的多对多关系，以实现2NF。这可以通过引入一个单独的表来完成：

Book_borrowings table

该表在图书和借阅者之间建立了明确的关系。 book_id 和borrower_id 充当外键，引用各自表中的主键。这种方法确保borrower_id 依赖于books 表的整个主键(book_id)，符合2NF。

第三范式 (3NF)

3NF 在 2NF 的基础上进一步发展，它消除了传递依赖。当一个非键属性依赖于另一个非键属性，而后者又依赖于主键时，就形成了传递依赖。这实际上反映了传递依赖的性质。

从我们已经实现的 2NF 来看，我们的图书馆数据库中有三个表：

Books table

Borrowers table

Book_borrowings table

2NF 结构看似高效，但可能会存在隐藏的依赖关系。假设我们在 books 表中新增了一个 due_date 列。初看起来这似乎是合理的，但实际上它会引入一个传递依赖。具体来说：

• due_date 列依赖于 book_borrowings 表中的 borrowing_id（一个非键属性）。
• borrowing_id 又依赖于 books 表中的 book_id（主键）。

这样一来，due_date 实际上依赖于一个中间的非键属性 borrowing_id，而不是直接依赖于主键 book_id。这种情况违反了3NF的要求。

解决方案是什么？

我们可以通过更新 book_borrowings 表以包含 due_date 和 returned_date 列，将 due_date 列移动到最合适的表。

以下是更新后的表格：

通过将 due_date 列放入 book_borrowing 表中，我们成功消除了传递依赖。

这意味着 due_date 现在直接取决于 book_id 和borrower_id 之间的组合关系。在此上下文中，book_id 和borrower_id 充当复合外键，它们一起构成book_borrowings 表的主键。

Boyce-Codd 范式 (BCNF)

BCNF 是基于函数依赖关系的，它考虑了关系中的所有候选键。函数依赖（FD）定义了关系数据库中属性之间的关系。FD 描述了某一列的值决定了另一列的值。函数依赖非常重要，因为它们通过识别这些依赖关系并确保数据在表之间适当地分布来指导规范化过程。

BCNF 是3NF的一种更严格的形式。它确保表中的每一个决定因素（即唯一标识一行的一组属性）都是候选键（即唯一标识一行的最小属性集）。换句话说，所有决定因素都应该能够充当主键。

BCNF 确保每一个函数依赖（FD）都有一个超键作为其决定因素。也就是说，如果存在 X → Y（即X决定Y）这样的依赖关系，那么X必须是关系中的候选键（或超键）。需要注意的是，这里的X和Y指的是数据表中的列。

作为 3NF 的构建，我们有三个表：

Books table

Borrowers table

Book_borrowings table

尽管3NF结构通常是好的，但在book_borrowings表中可能存在隐藏的决定因素。假设一个借阅者在同一时间内不能借阅同一本书超过一次，则book_id和borrower_id的组合可以唯一标识一条借阅记录。

这种结构违反了BCNF，因为book_id和borrower_id的组合集并不是表的主键（主键实际上是borrowing_id）。

解决方案是什么？

为了达到BCNF的要求，我们可以选择将book_borrowings表分解成两个独立的表，或者将组合属性集作为主键。

方法 1（分解表）

在这种方法中，我们将book_borrowings表拆分为两个独立的表：

• 一个表以borrowing_id为主键，并包含borrow_date、due_date和returned_date。
• 另一个表用于关联图书和借阅者，其中book_id和borrower_id作为外键，并可能包含与特定借阅事件相关的其他属性。

方法 2（将组合属性集作为主键）

另一种方法是将book_id和borrower_id作为复合主键，以唯一标识每条借阅记录。然而，这种方法的局限性在于，如果允许借阅者多次借阅同一本书，那么它就不能很好地满足需求。

最终，选择哪种方法取决于具体的数据需求以及如何希望对借阅关系进行建模。

第四范式 (4NF)

当某个属性可以有多个值，并且这些值独立于主键时，就会形成多值依赖。这种依赖关系较为复杂，但通过一个示例可以更好地理解。

我们之前在解释其他范式时使用的图书馆示例并不适用于4NF。4NF 通常适用于单个属性可能具有多个与主键不直接相关的从属属性的情况。

让我们使用一个不同的场景。想象一个存储出版物信息的数据库。在这个数据库中，有一个“publications”表，包含以下列：标题、作者、出版年份和关键字。

上面的表结构违反了 4NF，因为：

• keywords 列对主键 Publication_id 具有多值依赖关系。这意味着一个出版物可以有多个关键字，并且这些关键字独立于该出版物的唯一标识符。

解决方案是什么？

我们可以创建一个单独的表。

Publication_keywords table

新创建的表（Publication_keywords）在出版物和关键字之间建立多对多关系。每个出版物可以有多个关键字，通过publication_id（外键）链接，每个关键字可以与多个出版物关联。

至此，我们成功消除了多值依赖，实现了4NF。

第五范式 (5NF)

5NF 是为了消除连接依赖而设计的最复杂的规范化形式。即使表已经达到了4NF，有时仍然需要连接多个表中的数据来回答某些特定的查询。

简单来说，5NF 确保通过连接多个表而不会得出单个表中未提供的额外信息。

当表已经被规范化至3NF或4NF时，连接依赖的情况较少出现，因此为5NF创建一个清晰且简单的示例相对困难。

不过，让我们看一下 5NF 可能相关的场景：

想象一下一个大学数据库，其中包含“课程”和“入学”的规范化表。

Courses table

Enrollments table

假设这些表已经在 3NF 或 4NF 中，则可能存在连接依赖关系，具体取决于数据的存储方式。例如，课程的先决条件要求存储在“课程”表中，作为“先决条件_课程_id”列。

乍一看这似乎很有效。但是，考虑一个需要检索学生注册的课程及其各自先决条件的查询。在这种情况下，您需要联接“课程”和“注册”表，然后可能联接“课程”表以检索先决条件信息。

解决方案是什么？

为了潜在地消除连接依赖性并实现 5NF，我们可以引入一个单独的“课程先决条件”表：

Course_prerequisite table

这种方法将先决条件信息分开，并允许在“Enrollments”和“Course_precessions”表之间的单个联接中有效检索已注册的课程及其先决条件。

**注意：**我们假设学生每门课程只能有一个先决条件。

5NF 是一种非常复杂且罕见的标准化类型，因此当有人刚刚开始数据学习之旅时，您可能找不到应用程序。然而，它将增加知识，并使您在遇到复杂数据库时做好准备。

开始体验 Chat2DB Pro

如果你正在寻找一款强大、基于 AI 的数据库管理工具，快来试试 Chat2DB 吧！无论你是数据库管理员、开发者还是数据分析师，Chat2DB 都能通过 AI 的强大功能简化你的工作。

👉现在享受 Chat2DB Pro 30 天免费试用 (opens in a new tab)，即刻体验所有高级功能。