数据库归一化,减少了冗余

2020年12月28日17:17:40 发表评论 43 次浏览

本文概述

数据库规范化

规范化是关系数据建模的基本概念之一。在里面关系数据库模型, 好的数据库设计的特点是冗余最少。其原因是冗余数据导致语义异常, 从而使自动数据处理和数据库维护变得困难。规范化是消除关系数据库中冗余的一种策略。我们将向你展示如何实现数据库标准格式。

内容

  1. 什么是数据库规范化?定义
  2. 数据库规范化:有关如何重新配置​​数据库的示例
    1. 第一范式(1NF)
    2. 第二范式(2NF)
    3. 第三范式(3NF)
  3. 其他范式
    1. 博伊斯·科德范式(3.5NF)
    2. 第四范式(4NF)
    3. 第五范式(5NF)
  4. 规范化的优缺点

什么是数据库规范化?定义

规范化是一种方法数据库设计用于关系数据库避免冗余。

关系数据库模型是计算机化数据管理中使用最广泛的概念。在关系数据库中, 信息存储为表中的记录通过按键相关。数据记录包含多个值范围, 这些值范围使用表列分配给特定属性。

下表显示了虚拟办公设备供应商的已存储发票数据。 John Public已为其公司订购了10台显示器, 12个鼠标垫和1张办公椅。 Jane Doe的订单包括2台笔记本电脑和2副耳机。

什么是数据库规范化?定义

在在线商店数据库中, 发票数据被分配给属性发票编号("发票编号"), 日期, 客户, 客户编号("客户编号"), 地址, 发票项目编号("发票项目")。编号), 产品, 产品编号("产品编号"), 数量("编号")和价格。表上的每一行代表一个数据记录。这种数据集称为tuple.

上面显示的数据库部分是数据库设计不佳的示例。乍看之下, 表格显然显示出许多冗余。此外, "客户"和"地址"列中的值包含多值数据。这称为非规范化数据库。换句话说, 它不遵循数据库规范化规则。

非规范化数据库的主要缺点是由于冗余值而增加了内存需求。此外, 包含多值数据的属性很难读取, 而且彼此之间很难相互关联。

例子:上面列出的数据库部分中的两个客户都位于缅因州的斯普林菲尔德。但是, 由于没有分离出此信息, 因此, 来自同一位置的客户不容易过滤数据库。

为避免重复值和多值值范围, 三个顺序数据库正常形式已经为关系数据库模型开发了。

数据库标准格式是已定义的目标状态。已为每种常规形式定义了特殊要求, 如果要达到此目标状态, 则必须满足特殊要求。如果满足相应标准格式的所有要求, 则数据库与第一, 第二或第三标准格式完全对应。

数据库规范化:有关如何重新配置​​数据库的示例

为了说明将关系数据库转换为第一, 第二和第三范式, 我们以上表中的数据为例, 逐步介绍关系数据库规范化的各个阶段。

第一范式(1NF)

关系数据库中的表满足以下条件时, 它符合第一范式(1NF):

  • 所有数据都是原子的
  • 所有表列均包含相同的值

考虑数据集原子如果每个信息项都分配给一个单独的数据字段。

在下表的帐单数据中, 所有非原子性或不包含等效数据的值范围都以红色突出显示。

第一范式(1NF)示例第1部分

如突出显示的单元格所示, 示例表中的数据不能满足第一范式合规性的任何要求。

应该执行以下过程来规范这些部分:

  1. 将所有多值数据划分为单独的列
  2. 检查每列中的值是否相似

要将示例表中的数据记录转换为原子形式, 必须将"客户"和"地址"字段划分为更具体的名字和姓氏属性, 以及街道地址, 城市, 州和邮政编码。

当前在价格列中同时列出了美元和美分。确定一种货币格式以创建相似的值范围。

第一范式(1NF)示例第2部分

结果是一个符合第一个范式的表格不会导致有效的处理由于双精度值。然后建议将表转换为第二范式以消除冗余。

第二范式(2NF)

符合第二范式的表格除以下内容外, 还必须满足第一范式的所有要求:

  • 每个非键属性都必须具有完整功能, 具体取决于主键

在引言中, 关系数据库被定义为通过键相互关联的各个表的系统。

在关系数据库中使用键来唯一地标识数据记录(元组)。可以唯一地命名数据库表各行的键称为超级键。这样的键可以表示单个列的值或几个列的组合值。

在给定的示例中, 可能的超级键是由发票号码("发票编号"), 客户编号("客户编号")和发票项目编号("发票项目编号")属性, 如下表中突出显示。

第二范式(2NF)示例第1部分

例如, 由发票编号, 客户编号和发票项目编号组成的键, 其值为{124, 12, 1}, 可以清楚地指定代表Jane Doe笔记本电脑购买的数据记录:

第二范式(2NF)示例第2部分

但是, 并不是唯一标识需要选定超级键中的所有信息。发票编号和发票项目编号的组合(即超级键的子集)足以识别单个数据记录。具有最少数量属性的此类键称为关键候选人or备用键.

第二范式(2NF)示例第3部分

通常, 每个表选择一个关键候选项代表该表。为此, 顺序编号是理想的。这样的钥匙叫做首要的关键并指定数据记录的顺序。

像任何其他候选密钥一样, 主密钥可以是一个部分的密钥, 也可以是一个复合密钥(如在给定的示例中)。样本表使用一个复合主键, 该主键由发票编号和发票项目编号组成。

要将数据库表转换为第二范式, 你不仅需要确定主键和所有非键属性, 还需要确定它们之间的关系。按着这些次序:

  1. 检查所有非键属性是否在功能上完全取决于主键。仅当需要所有主键属性来唯一标识非键属性时, 才存在这种依赖性。这也意味着, 如果满足第一范式的所有先决条件, 则具有单一主键的表将自动对应于第二范式。
  2. 将不完全依赖完整主键的所有非键属性移到单独的表。

仔细查看示例表, 请注意第二范式的先决条件未满足因为日期列仅取决于发票编号("发票编号"), 而不取决于发票项目编号("发票编号")。名字, 姓氏, 街道地址, 城市, 州和邮政编码也相同。

为了将数据表转换为第二范式, 所有完全依赖于发票号的属性已移至称为"发票"的单独表中。

第二范式(2NF)示例第4部分

带有数据余额的表已命名为"发票项目"。

第二范式(2NF)示例第5部分

标准化后, 在两个表中都可以找到发票编号("发票号")并将它们链接在一起。该属性用作"发票"表中的主键, 但它用作外键在"发票项目"表中, 并且也是该表的组合主键的一部分。

现在, 示例数据符合第二范式。但是, 尚不可能完全消除冗余。那么归一化的目标通常是第三范式。

第三范式(3NF)

如果要将表转换为第三范式, 则必须满足第一范式和第二范式的所有先决条件以及以下条件:

  • 任何非关键属性都不能过渡地依赖关键候选者

当非关键属性依赖于另一个非关键属性, 从而间接依赖于其关键候选者时, 就会发生传递依赖。

给定的数据库模板在多个地方违反了第三范式的条件:

第三范式(3NF)示例第1部分

在"发票"表中, 第一个和最后一个名称, 街道地址, 城市, 州和邮政编码, 不仅取决于主键(发票编号), 还取决于客户编号。

在"发票项目"表中, 产品和价格属性不仅取决于从发票编号和发票项目编号派生的主键, 还取决于产品编号。此特定条件也违反了第三范式。

第三范式(3NF)示例第2部分

删除所有非关键属性之间的依赖关系, 相关属性已移至单独的表, 并通过外键相互链接。这将产生四个规范化的表:"发票", "客户", "发票项目"和"产品"。

"发票"表的主键是顺序的发票号。每个发票编号都分配了一个发票日期和一个客户编号。

第三范式(3NF)示例第3部分

有关每个客户的更多详细信息存储在"客户"表中。 "发票"和"客户"表通过客户编号链接。在"客户"表中用作主键, 在"发票"表中用作外键。

第三范式(3NF)示例第4部分

"发票项目"表是示例数据库中的中央表, 其中包含有关哪些产品应显示在哪个发票上以及订购了多少项目的信息。 "发票项目"表上的顺序主键是从发票编号和发票项目编号派生的。相应产品仅作为充当外键的产品编号列出, 并将"发票项目"表与"产品"表链接。

第三范式(3NF)示例第5部分

最后, "产品"表包含有关各个产品的详细信息, 例如产品说明和价格。主键是产品序列号。

第三范式(3NF)示例第6部分

在该示例中, 将两个表拆分为四个表似乎不太有效。实际上, 只有两个客户的数据中的冗余并不重要。但是想象一下你想一贯地在关系数据库中处理数十万条客户或产品记录没有矛盾。通常只有与第三范式相对应的数据库公式才有可能这样做。

即使数据库规范化需要更多的编程工作, 但3NF(第三种范式)通常被视为关系数据库公式的标准, 并且仅在特殊情况下才有所不同。例如, 符合第三范式的数据库有时会被归一化为第二范式。这是因为对于非常大的数据库, 跨多个表的联接非常耗时。非规范化减少表的数量以及查询时间。

其他范式

实际上, 归一化通常以第三范式结束。以下常规格式引用具有特殊条件的数据库模式, 然后仅在特殊情况下使用。

博伊斯·科德范式(3.5NF)

博伊斯·科德的范式是收紧第三范式。对于3NF:

  • 任何非关键属性都不能过渡地依赖关键候选者

但是, 以Boyce Codd正常形式:

  • 除非属性是琐碎的依赖关系, 否则任何属性都不能过渡性地依赖关键候选对象

Boyce Codd范式仅与具有几个复合关键候选人其中键重叠, 即一个属性和同一个属性是两个候选键的子集。

符合第三范式且没有多个候选关键字的数据库表会自动代表Boyce Codd范式。

下表显示了两个关键候选项, 每个候选项均由两个属性组成。

  • 供应商编号和产品编号
  • 供应商和产品编号

两个键都可以识别每个单独的数据记录。唯一的非关键属性是数字。由于数字属性不是传递依赖对于任何关键候选者, 该表均符合3NF标准。

另一方面, 它不符合博伊斯·科德(Boyce Codd)的标准格式, 因为供应商编号(" V号")之间存在依赖性。和供应商("供应商")属性。供应商编号属性可传递地依赖于结合了供应商和产品编号的关键候选者。相反, 供应商属性是由结合了供应商编号(" V编号")和产品编号("产品编号")的关键候选值产生的。

Boyce Codd范式(3.5NF)第1部分

通过将输出表分为" Number"表和" Vendors"表, 可以避免传递依赖性, 从而消除了重叠的候选键。

Boyce Codd范式(3.5NF)第2部分

博伊斯·考德(Boyce Codd)范式通过重叠关键候选者来识别多次列出的关键属性, 从而避免了冗余。在上面的示例中, 转换为3.5NF可防止在vendor列中重复值。

博伊斯·科德(Boyce Codd)普通形式(3.5NF)第3部分

第四范式(4NF)

如果除以下各项外, 还满足Boyce Codd常规格式的要求, 则数据库表符合第四种常规格式:

  • 除非琐碎, 否则没有多值依赖项

一种多值依赖如果两个不相关的属性依赖于同一属性, 则总是存在, 如下例所示:

下表显示了每个客户已订购了哪些产品, 以及必须将其交付到哪些邮政编码。

第四范式(4NF)第1部分

例如, 客户编号为234的客户订购了商品1-0023-D和2-0023-D, 这些商品将以邮政编码12345交付到其地址。对于客户567, 商品1-0023-D, 3- 0023-D, 4-0023-D和5-0023-D将交付给邮政编码56789。

只能使用由所有三个属性(客户编号, 产品编号和邮政编码)产生的超级键来标识数据记录。由于没有非关键属性, 因此数据库符合3NF规范。此外, 由于不存在非平凡的传递依赖关系, 因此它也符合3.5NF。但是, 存在多值依赖关系:产品编号属性和邮政编码属性都依赖于客户编号属性, 但彼此不相关。

这种数据库设计的缺点是, 每次将新产品添加到客户记录中时, 也都必须添加邮政编码, 这将导致冗余数据。

可以通过将表转换为4NF来消除这些冗余。为此, 你必须以没有或没有仅琐碎的多值依赖。这是可能的, 因为产品编号和邮政编码完全无关。

第四范式(4NF)第2部分

如示例所示, 第四个范式消除了由多值依赖关系引起的冗余, 在这种情况下, 特别是在邮政编码列中。

第五范式(5NF)

如果数据库表除满足以下条件外, 还满足第四种标准格式的条件, 则它符合第五种标准格式:

  • 该表无法进一步拆分而不会丢失信息

下面是一个示例, 演示了一家公司经营基于TYPO3的网站和Magento网络商店的情况。软件项目由三名员工负责:Mary Smith, George Miller和Joe Davis, 每人都有不同的资格。

该表显示了哪个员工的资格适用于哪个软件项目的要求。

第五范式(5NF)第1部分

玛丽·史密斯(Mary Smith)在Magento项目上使用了她的PHP和SQL知识, 并在TYPO3网站上使用了SQL和JavaScript。乔治·米勒(George Miller)还使用Magento的PHP和TYPO3的JavaScript。 Joe Davis仅参与TYPO3项目, 是PHP的唯一程序员。该表还显示Magento需要PHP和SQL知识, 而TYPO3项目则需要PHP, SQL和JavaScript知识。

该表只有一个由所有三个属性组成的键, 这意味着它至少符合3NF和Boyce Codd范式。由于这三个属性之间没有依赖关系, 因此该表也符合第四范式。

要检查该表是否还符合5NF, 请将输出表"项目部署的员工资格"分成三个表:"项目部署", "员工资格"和"项目要求"。

"项目部署"表显示了哪个员工参与哪个软件项目。

第五范式(5NF)第2部分

"员工资格"表显示了哪位员工精通哪种编程或数据库语言。

第五范式(5NF)第3部分

"项目要求"表指出了哪个项目需要哪个编程资格。

第五范式(5NF)第4部分

乍一看, 将数据库分区分解后, 它看起来更加清晰。但是规范化期间创建的表是否具有与初始表相同的信息内容?

跨所有三个表的联接数据库查询都可以找到答案。结果令人惊讶。

第五范式(5NF)第5部分

重建输出表, 你可以假设参与该项目的每个员工都将使用其每项资格, 但前提是各个项目都需要这些资格。但是, 这样做的话, Joe Davis在TYPO3项目的PHP编程中独自工作的信息已丢失。这意味着输出表不能在没有信息丢失的情况下进行分解, 使其符合第五种标准格式。

实际上, 你很少会遇到满足4NF要求但不符合第五种范式的数据库公式。但是, 5NF对于其中新的消息从现有数据获得。

在该示例中, Mary Smith和George Miller都精通PHP, 他们将来也可以为TYPO3项目做出贡献。公司可以使用此信息来提高该项目中的软件开发效率。

规范化的优缺点

规范化的目的是减少重复值的实例。通过将数据库转移到列出的标准格式之一, 目标架构可从冗余少比源模式。规范化还使数据库维护更加容易。

另一方面, 数据库规范化总是涉及将属性存储在单独的表中。这可能需要集成外键, 这可能导致关键的裁员。但是, 主要缺点是, 在规范化数据库中, 逻辑上相关的数据不再存储在一起。一种必须加入合并已拆分为不同表的数据。

复杂的信息可以通过使用联接的数据库查询来过滤掉。但是, 连接的实现比简单查询要复杂得多。如果使用大量数据库表进行联接, 则也将花费更多时间。

一盏木

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: