关系数据库设计/规范化

数据库从业人员经常谈论规范化，但它通常被人们误解。有些人认为它是一个学术细节，在现实世界中不切实际。许多从业人员相信规范化在性能方面总是过于昂贵。事实上，规范化具有重要的实际益处。尽管在某些情况下可能存在合理理由避免规范化，但最好从了解成本和效益的角度做出决定，而不是出于无知而将其摒弃。

规范化提供了一套规则和模式，可以应用于任何数据库以避免常见的逻辑不一致。规范化数据库设计通常会提高

• 一致性，因为数据库中可能发生的错误在结构上是不可能的
• 可扩展性，因为对数据库结构的更改只会影响它们逻辑上依赖的数据库部分
• 效率，因为不需要存储冗余信息

数据库界已经确定了几个不同的规范化级别，每个级别都比上一个更严格。这些被称为范式，从一（最低的规范化形式，称为第一范式或1NF）到五（第五范式或5NF）。在实践中，通常会说一种数据库设计比另一种更规范或更不规范，如这些级别所定义的那样。

在实际应用中，您通常会看到1NF、2NF和3NF，偶尔也会看到4NF。第五范式很少见，但本文会简要介绍。第五范式意味着SQL选择新手不会通过与由于多对多依赖而产生的人为伪行进行联接来产生误导性的结果，或者等于或大于三方关系，而三方关系与大多数一对多相比很少见，而一对多关系将由早期形式检测到。

为什么要进行规范化 ?

数据库是对世界的一组事实。数据库管理员希望他们的数据库只包含关于世界的真实事实，而不包含虚假的事实。规范化允许RDBMS防止某些类别的虚假事实出现在数据库中，从而有助于一致性。显然，数据库中的大部分信息无法由计算机验证；唯一可以防止的错误类型是与某些逻辑矛盾不一致的错误。一般来说，随着规范化级别的增加，由于表的结构，一类或多类逻辑错误将变得不可能。

如果数据库从一致状态开始，并且一个明显有效的操作导致数据库变得不一致，那么就会在数据库中发生一个异常。这可以进一步指定为更新、插入或删除异常，具体取决于导致错误的操作是分别进行行更新、插入或删除。

上面的示例显示了更新异常。数据库用户尝试更新员工的地址。但是，由于地址在表中出现多次，并且只更新了一个副本，因此表中现在存在不一致的数据：员工不能同时拥有两个地址。

此示例显示了插入异常。新聘请了一位教职工，但必须等到他被分配至少教授一门课程后，才能将此信息插入数据库。

什么是FD ?

函数依赖，或FD，看起来像

$a\rightarrow b$

这意味着b函数依赖于a。另一种理解方式是说，“a决定b”。

这里的意思是，如果你知道一个字段的值（a），那么你也知道另一个字段的值（b）。例如，如果你知道某人的社会安全号码（在美国），那么你可以找到该人的姓、名等。知道一个数据就足以让你确定其他数据。

另一个例子可能是，如果你知道汽车的序列号，你也可以找到（确定）汽车的制造商和型号。

${\text{serial number}}\rightarrow ({\text{manufacturer}},{\text{model}})$

因此，制造商和型号依赖于序列号。这就是函数依赖。

请注意，这种关系通常不会反过来。如果你知道一辆汽车有特定的制造商，这并不足以找到它的序列号（因为有很多不同的汽车，序列号不同，但都是由同一个制造商制造的）。

了解函数依赖的意义何在 ?

如果你不了解你的函数依赖，那么你将永远被谴责轮回你的更新、插入和删除异常。思考你的FD，你也许能够在整个数据库中实现BCNF，如果没有，至少规范化涅槃中还有两个级别，你可以做3NF，打破低效数据库设计的束缚，这是你微不足道和虚荣的ER建模没有考虑到的。尽管这位DB Kung Fu大师尽了最大努力向你展示ER图示的方式，但它仍然无法与笨拙的掌法风格或坚韧的盲人计算机之拳相提并论。你以为你选择了伟大的关系，并将所有必要的属性挂在你的实体矩形上，并为所有主键属性加了下划线，但最初的视频租赁店特许经营管理系统云状想法的图示分解并没有防止有损联接和丢弃一些FD。你可能会怀疑地说，如果你一开始就不知道FD，那么丢弃FD并不是什么大不了的事，但是FD的意义在于它们就像二手车保修一样，保留FD可以避免更新异常的昂贵维修。

Boyce-Codd范式所做的是将FD的概念与关系表主键属性的概念联系起来，记住表的任何候选键都可以是主键。在BCNF中，所有FD左侧的属性，即FD关系的支配者，都是候选键。

考虑负面情况，即当一个或多个 FD 不是候选键时，则模式不在 BCNF 中，并且模式容易出现无损连接或功能依赖完整性约束无法保持。在此插入一个有说服力的例子。

范式

在我们开始讨论范式之前，重要的是要指出它们只是指南，仅仅是指南。有时，为了满足实际的业务需求，有必要偏离它们。但是，当发生变化时，评估它们对系统可能产生的任何影响并考虑可能出现的不一致性非常重要。话虽如此，让我们来探索范式。

范式就像观看法律剧一样有趣：“我发誓要讲真话，全部真话，绝不隐瞒真相，愿上帝保佑我”。“所有非键属性都将讲述关于键的事实，整个键，以及仅关于键的事实，愿 Codd 保佑我。”（关于 2NF、3NF 和 BCNF）。

一些用于理解每种范式的术语

1NF - 原子值

2NF、3NF、BCNF - 一个键是关系中的一组属性，可用于标识关系中的一行。一个超键是候选键的超集，一个可用于标识行的最小属性集。因此，关系的所有属性都可以是超键。

2NF - 非键属性依赖于其他非键属性，整个候选键，但不依赖于复合键的一部分。
3NF - 非键属性直接依赖于整个候选键，或为候选键的一部分。
BCNF - 非键属性直接依赖于整个候选键，并且不是键的一部分。

以上总结似乎足够好，但事实并非如此，因为超键的定义是关系的所有属性都可以是键，因此，不存在“非键”属性。所以最好用函数依赖来表达范式，X -> A “X 决定 A”，或“A 依赖于 X”。然后 2NF 变为“X 不能是候选键的一部分，但可以是候选键，或者不是超键最小键集中的超键属性”。3NF 变为“X 是一个完整的超键，或者 A 是候选键的一部分”。BCNF 变为“X 是一个完整的超键”。

为什么要有 NF ?

1NF - 使编写查询更容易。示例是 Clients 表，其中 Client 有三个单独的电话号码（可能允许输入办公室、家庭和手机号码）。如果你的数据库有一个客户表，其中包含多个电话号码字段，那么它甚至不是 1NF。

如果要从给定电话号码查找客户，则不处于第一范式会使查询变得复杂且效率低下。由于你不知道电话号码将出现在哪个字段中，因此你必须执行类似以下操作

SELECT Id
  FROM Clients
 WHERE FirstPhoneNumber = '555-123-4567'
       OR SecondPhoneNumber = '555-123-4567'
       OR ThirdPhoneNumber = '555-123-4567';

2NF - 一本教科书说，鉴于 3NF 和 BCNF，它并不重要，并且可能在炫耀为什么人们会将一个表拆分为由外键链接的父关系和子关系。例如，有一个客户表，还有一个承包商表，其中承包商具有不止一项技能，而承包商-技能表拥有承包商的地址字段，意味着拥有一个部分依赖于整个键承包商-技能的非主属性，即地址由承包商决定。如果承包商具有不止一项技能，那么将存在包含相同承包商名称和地址的冗余行。2NF 将要求根据 FD 对表进行拆分，N -> A，以及多值依赖，N ->-> S，或 (Name , Address)，和 (Name, Skill ); 除了是 2NF 之外，这也意味着是 3NF、BCNF 以及 4NF。为什么呢？

3NF - 就像 BCNF 的一种限制形式，因此，也许可以先看看 BCNF，看看它是否可行，然后再进行 3NF。

BCNF - 映射更容易，因为对于每个 FD X->A，X 必须是超键。这使得很容易看出，当将未规范化的 1NF 或 2NF 关系分解为 BC 范式时，如果没有丢失冲突的 FD，就无法完成分解，因此分解不是依赖保持的，因此规范化应还原为 3NF，它始终可以适应试图进行 BCNF 时的冲突 FD。

3NF - 唯一额外的放宽是，如果 FD X-> A 适用，则 A 可以是候选键的一部分。为了看到一个不是 BCNF 的分解（因为它没有保留 FD，但却是 3NF），一个例子会有所帮助，而这个作者没有足够的认知能力来想出一个，因此将给出一些来自互联网和书籍的例子，前提是它们可能会因无关的社会落后原因（通常是法律）而被撤回。例子是：enroll (student, class, assistant)，具有 FD student class -> assistant , assistant -> class (只允许协助一个班级)，以及 part_supply，(contract, supplier, department, project, part, quantity, value)，具有以下 FD

1) project part -> contract

2) supplier department -> part

3) project -> supplier , 以及

4) contract -> 原始关系的其余部分。

对于原始的 part_supply 关系，第二和第三 FD 的决定因素 X 不符合“X 是超键”的声明。

Armstrong 公理的传递公理表明，第一个 FD，1) Project part -> contract，不会使原始关系违反 BCNF，因为 Project,Part 是超键或备用键，因为它传递映射到原始的单属性键，contract。

尝试 BCNF 分解的步骤

推荐的分解步骤是使用一个违反 FD，它在 X -> A 中有一个单一的依赖 A，这可能是由于另一个 Armstrong 公理指出 X-> A B C，始终可以分解为 X -> A , X->B, X -> C，单属性依赖 FD。

（分解公理是另一种表述反射公理和传递公理相结合的方式，即，如果 X 是 Y 的超集，则 X -> Y，并且，在这种情况下，A B C 是 A 的超集，并且 X -> A B C，因此 X 传递 -> A，因为 A B C -> A。）

回到上面两个段落中的陈述，要使用一个 FD X -> A，其中

a) A 是 R 的单个属性，并且

b) X 必须是 R 的子集。

c) FD X -> A 是最小覆盖 FD 集的成员，这意味着原始的 FD 集已被全部转换为具有单个依赖的 FD，并且使用 Armstrong 公理无法创建更小的 FD 集，可以根据 Armstrong 公理重新生成原始的 FD 集。

如果满足 a) 和 b)，则将关系 R 分解为 R - A 和 X A，例如

R = ( contract, supllier, department, project, part, quantity, value) , X = supplier department, A = part

所以 R -A = ( contract, supplier, department, project, quantity, value) , 以及 XA = ( supplier, department, part )。

因为 X -> A，X 可以是 XA 的键，并且 X 可以自然连接到 R - A，因为 R 的属性是 X 的超集。

然后第 2 步是检查生成的分解关系是否为 BCNF，例如，在这个例子中，R-A 不是 BCNF，因为存在 project -> supplier FD，并且 project 不是 (contract supplier dept project, quantity, value) 的超键。

如果违反了 BCNF，则递归，因此现在 R = contract, supplier, dept, project, quantity, value，下一个单属性依赖 FD 是 project -> supplier，因此删除 A (supplier) 以像以前一样形成 X-A，得到 ( contract , department, project, quantity , value )。

分解现在是，

(supplier, department, part) (project, supplier) (contract, department, project, quantity, value)

但是，唯一剩余的违反 FD 是 project, part -> contract，但这里 X (project, part) 不是 R (contract, department, project, quantity, value) 的子集。

因此 BCNF 分解无法继续。此时，放弃尝试实现 BCNF，下一个目标是实现 3NF。

3NF 分解继续进行失败的 BCNF 分解的产物，并在所有情况下实现无损连接、FD 保持分解

分解为 3NF 的技术是，从原始关系的无损分解和违反 FD 集开始。前者是通过尝试分解为 BCNF 创建的，如上所述，后者是 BCNF 分解无法再继续时剩余的 FD。

这是以 BCNF 为目标的理由，因为放弃实现 BCNF 的结果可用于实现 3NF。

为了实现 3NF，对于每个违反 FD X -> A，只需添加一个关系 XA。在这个例子中，最后一个 FD，project, part -> contract，可以形成一个关系，因此成功的 3NF 分解现在是

(supplier, department, part) (project, supplier) (contract, department, project, quantity, value) (project, part, contract).

为了总结，不带例子,

步骤 1：尝试分解为 BCNF。

步骤 2：尝试分解为 3NF。

要执行步骤 1，

a. 记住Armstrong 公理 

反射，如果 X 是 Y 的子集，则 Y -> X，

传递，如果 X -> Y 且 Y -> Z，则 X -> Z，

增强，如果 XB -> YB，则 X-> Y

反射和传递意味着分解：ABC -> A , X -> ABC，则 X -> A，（以及 X->B，以及 X-> C）

联合也被暗示，X -> Y，X -> Z，则 X -> YZ

b) 使用 Armstrong 公理将原始的 FD 集转换为最小覆盖 FD 集。

b1. 使用分解使所有 FD 在右手边都具有单属性（单属性依赖）。

b2. 使用 Armstrong 公理删除冗余的决定因素属性，然后

b3. 删除等效的转换 FD。

c) 使用剩余最小覆盖 FD 集中的一个 FD。

d) 对于这个 FD，X -> A (single)，X 和 A 必须分别为 R 属性的子集和成员。即 R 是 X 联合 A 的超集。

e) 从 R 中移除 A，得到 R - A 作为分解结果之一，以及另一个关系 X A 作为另一个分解结果（即构成 X 和 A 的属性）。

f) 从 FD 的覆盖集中移除已使用的 FD，并返回到 c)。 使用关系集 - R，与每个分解结果 R - A 和 X A 的并集作为待分解关系的源集，并继续直到所有最小覆盖集的 FD 都已耗尽（清空），或者剩余的单一属性 FD 无法用于分解任何正在增长的分解关系集。

如果存在剩余的 FD，则在保留所有 FD 的情况下无法实现 BCNF。

通过构建，切换到 3NF 覆盖关系生成

获取每个属于剩余最小 FD 集的 FD，即每个 FD 的形式为 X->A，其中 A 是一个单一属性的依赖项，使用阿姆斯特朗公理来移除决定因素侧 (X) 的任何冗余，然后移除任何剩余的本质上等效的 FD，直到没有更多 FD 可以移除，而不会丢失重建原始 FD 集的能力。

使用此 FD，构建关系 XA 并从剩余的 FD 集中移除 FD；继续直到所有 FD 都被处理。

归一化的销售宣传

无论是成功 BCNF 转换还是 BCNF/3NF 转换，生成的关系列都可以通过连接重新组合成原始关系，不会丢失或生成虚假行，并保留所有原始的函数依赖。 使用这些归一化的关系时，更新、插入和删除异常不应该发生。

4NF 和 5NF 处理一对多和多对多关系，或多值依赖

如果生成的分解关系具有单一属性的键，则实现 BCNF 可能实现 4NF。 多值依赖表示为 X ->-> A，这意味着对于 X 的每个实例，存在一个固定的 A 值集，这些值集与除 X 之外的任何其他属性都*独立*。 事实上，FD X -> A（其中每个 X 值都隐含一个单一的 A 值）是 MVD 的一个特例。

例如，stereotype_expertise是一个多值关系，具有原型值/多值依赖的专业知识实例

medical_graduate ->-> physiology, anatomy , pharmacology ; 

computer_science_graduate ->-> concrete mathematics, database design, programming ; 

idiot ->->  pharmacology, database design.

在 4NF 中，如果该单一 MVD 的决定因素和多值依赖项是关系中唯一的属性，或者如果决定因素是超键，并且除 X、Y 之外还存在其他属性，则多值依赖将始终成立。 最后一个标准没有意义，因为如果 X 是超键，那么对于每个 X 只有一个行。

4NF 的基本原理通过一个关系的例子来说明，该关系具有 2 个*独立*的多值依赖，例如 Person Skill Language。 一个人拥有一组技能，还有一组语言。 设计问题的一个例子是当一个人添加一门语言时，存在一个设计选择：在具有新的个人语言详细信息的行中对技能使用空值，或者始终为每个人生成技能和语言的叉积，因此对于技能和语言的叉积的每一行，都有个人，所以这方便了查找具有特定技能和语言的人。 在这种类型的表中，由于多重技能 + 语言映射，个人不能作为候选键，实际上所有 3 个属性构成了键。 因此，根据 4NF，需要分解此关系，直到所有 MVD X->->Y 只有 X Y 作为属性，或者 X 是超键（这实际上没有意义，因为对于一个 X 存在多个 Y）。 Person Skill Language 的 4NF 分解是什么？

[http://www.bkent.net/Doc/simple5.htm#label4 William Kent，“关系数据库理论中的五种范式的简单指南”，ACM 通信 26(2)，1983 年 2 月，120-125]

5NF

这位作者的好，但稍微不一致的教科书指出

如果关系中没有复合候选键，则 3NF 分解关系在 5NF 中。

可以推测，一个复合键可以只用其两个或多个属性中的一个投影到更小的分解，当与关系的其余部分重新连接时，将导致虚假行。 5NF 类似于 BCNF 通过其函数依赖的程序分解的声明式版本，以及 3NF 的额外单一 FD 关系的修补，只是它也应用于 MVD，因为无损连接条件被合并到连接依赖的定义中，即 5NF 关系可以分解成任何一组较小的关系，并且所有这些都可以重新连接而不会产生虚假行。

范式，没有逻辑迷恋

1NF

第一范式 (1NF) 为组织良好的数据库设定了最基本的规则：从同一个表中消除重复的列。 为每个相关数据组创建单独的表，并使用唯一的列或列集（主键）标识每一行。

2NF

第二范式 (2NF) 进一步讨论了消除重复数据的概念：满足第一范式的所有要求。 删除应用于表中多行的部分数据，并将它们放在单独的表中。 通过使用外键创建这些新表与其前身之间的关系。

3NF

第三范式 (3NF) 迈出了一大步：满足第二范式的所有要求。 删除不依赖于主键的列。 如果剩余的列（属性）在函数依赖中起作用，则函数依赖的所有属性都在表中，并且属性的决定因素形成一个键，或者所有依赖属性都是键属性。 后者意味着另一个表，它具有决定因素属性作为键，将这些决定因素属性映射到依赖属性，这些属性构成到此表的外部键。

Boyce-Codd 范式

如果表中的属性在函数依赖中充当决定因素属性，那么它和函数依赖的所有其他决定因素属性都在表中，并且它们一起形成表键的超集或等于表键。 函数依赖的所有依赖属性也在表中。

4NF

第四范式 (4NF) 有一个额外的要求：满足第三范式的所有要求。 如果一个关系在 BCNF 中并且它具有单列键，或者表仅具有一个多值依赖的属性，或者多值依赖的决定因素是键的超集或等于键，则该关系在 4NF 中。

第五范式 (5NF)

5NF 的目标是针对关系的所有候选键实现无损连接分解。5NF 是通过不破坏关系中存在的连接依赖性来定义的。连接依赖性是指特定分解将导致无损重构的声明。多值依赖性是连接依赖性的一种特殊情况，表示为由 MVD 的决定因素和依赖属性组成的子关系，以及由原始关系中的所有其他属性和 MVD 的决定因素属性组成的子关系，因此连接发生在决定因素属性上。一篇论文表明，如果一个 3NF 关系的所有候选键都是单个属性，那么所有连接依赖性都将成立（不会有涉及候选键的分解导致无损重构）。

域键范式 (DKNF)

域键范式 消除了插入和删除异常，同时仅依赖于域约束和键约束（不需要任意 CHECK 约束）。不幸的是，没有通用的方法将数据库转换为 DKNF，因此它通常是最难实现的范式。

反规范化

阅读完以上关于规范化的内容以及如何进行数据拆分后，你可能在想

现在我的数据库中到处都是表格，我必须连接一堆表格才能从系统中获取简单的数据集！这会如何影响性能呢？

你可能是对的。在某些性能至关重要的场景中，可能需要对数据库/模式的某些部分进行反规范化，并使用更宽的表格，这些表格可能包含重复/冗余数据。

但是，此类决策最好留给经验丰富的 DBA，因为可能还有其他方法可以提高性能，例如物理设计、索引、查询优化等，这些都是 DBA 的专业领域。数据库反规范化，就像规范化一样，最好留给经验丰富的专业人士处理。

但是，如果你足够自信，想要精通数据库反规范化，那么阅读一本好的数据库教科书中关于数据库调优的章节将会有所帮助；我阅读的那本教科书只是重申了关于 Boyce-Codd 范式和 3NF 范式的先前课程，以及两者之间的选择，因此在试图实现无损连接和函数依赖性保留的规范化目标时，从 BCNF 到 3NF 已经存在一个反规范化步骤。这本书还简要介绍了使用更新、插入和删除 SQL 触发器来强制执行函数依赖性（由于反规范化而没有被强制执行），然后比较这些触发器的查询计划和执行成本，以及保留规范化的查询计划和执行成本，以及由此产生的对于某些查询必须进行连接的成本，后者通常需要创建索引来降低仅需要一小部分行数据的查询的连接成本。