学计算机要懂数值转化,重学大学计算机教程数据库的规范化[转]|阿哥博客|技术博客|技术论坛|阿哥网络|阿哥科技|阿哥IT|IT星球|阿哥星球...

2020年5月24日

关系模型满足的确定约束条件称为范式&＃xff0c;根据满足约束条件的级别不同&＃xff0c;范式由低到高分为 1NF(第一范式)、2NF(第二范式)、3NF(第三范式)、BCNF(BC 范式)、4NF(第四范式)等。不同的级别范式性质不同。

把一个低一级的关系模型分解为高一级关系模型的过程&＃xff0c;称为关系模型的规范化。关系模型分解必须遵守两个准则。

(1)无损连接性&＃xff1a;信息不失真(不增减信息)。

(2)函数依赖保持性&＃xff1a;不破坏属性间存在的依赖关系。

规范化的基本思想是逐步消除不合适的函数依赖&＃xff0c;使数据库中的各个关系模型达到某种程度的分离。规范化解决的主要是单个实体的质量问题&＃xff0c;是对于问题域中原始数据展现的正规化处理。

规范化理论给出了判断关系模型优劣的理论标准&＃xff0c;帮助预测模式可能出现的问题&＃xff0c;是数据库逻辑设计的指南和工具&＃xff0c;具体有&＃xff1a;

(1)用数据依赖的概念分析和表示各数据项之间的关系。

(2)消除 E-R 图中的冗余联系。

1&＃xff0e;函数依赖

通俗地说&＃xff0c;就像自变量 x 确定之后&＃xff0c;相应的函数值 f(x)也就唯一确定了一样&＃xff0c;函数依赖是衡量和调整数据规范化的最基础的理论依据。

例如&＃xff0c;记录职工信息的结构如下&＃xff1a; 职工工号(EMP_NO)

职工姓名(EMP_NMAE) 所在部门(DEPT)。

则说 EMP_NO 函数决定 EMP_NMAE 和 DEPT&＃xff0c;或者说 EMP_NMAE&＃xff0c;DEPT 函数依赖于 EMP_NO&＃xff0c;记为&＃xff1a;EMP_NO→EMP_NMAE&＃xff0c;EMP_NO→DEPT。

关系R中的一个属性或一组属性 K&＃xff0c;如果给定一个 K 则唯一决定 U 中的一个元组&＃xff0c;也就是 U 函数完全依赖于 K&＃xff0c;就称 K 为 R 的码。一个关系可能有多个码&＃xff0c;选中其中一个作为主码。

包含在任一码中的属性称为主属性&＃xff0c;不包含在任何码中的属性称为非主属性。

关系 R 中的属性或属性组 X 不是 R 的码&＃xff0c;但 X 是另一个关系模型的码&＃xff0c;称 X 是 R

的外码。

主码和外码是一种表示关系间关联的重要手段。数据库设计中一个重要的任务就是要找到问题域中正确的关联关系&＃xff0c;孤立的关系模型很难描述清楚业务逻辑。

2&＃xff0e;第一范式

1NF 是最低的规范化要求。如果关系R 中所有属性的值域都是简单域&＃xff0c;其元素(即属性)不可再分&＃xff0c;是属性项而不是属性组&＃xff0c;那么关系模型 R 是第一范式的&＃xff0c;记作 RÎ1NF。这一限制是关系的基本性质&＃xff0c;所以任何关系都必须满足第一范式。第一范式是在实际数据库设计中必须先达到的&＃xff0c;通常称为数据元素的结构化。

经过处理后&＃xff0c;就可以以省、市为条件进行查询和统计了。

满足 1NF 的关系模型会有许多重复值&＃xff0c;并且增加了修改其数据时引起疏漏的可能性。为了消除这种数据冗余和避免更新数据的遗漏&＃xff0c;需要更加规范的 2NF。

数据库表中的字段都是单一属性的&＃xff0c;不可再分。这个单一属性由基本类型构成&＃xff0c;包括整型、实数、字符型、逻辑型、日期型等。很显然&＃xff0c;在当前的任何关系数据库管理系统(DBMS)中&＃xff0c;傻瓜也不可能做出不符合第一范式的数据库&＃xff0c;因为这些DBMS不允许你把数据库表的一列再分成二列或多列。因此&＃xff0c;你想在现有的DBMS中设计出不符合第一范式的数据库都是不可能的。

3&＃xff0e;第二范式

如果一个关系 R 属于 1NF&＃xff0c;且所有的非主属性都完全依赖于主属性&＃xff0c;则称之为第二范式&＃xff0c;记作 RÎ2NF。

为了说明问题&＃xff0c;现举一个例子来说明&＃xff1a;

有一个获得专业技术证书的人员情况登记表结构为&＃xff1a;

省份、姓名、证书名称、证书编号、核准项目、发证部门、发证日期、有效期。

这个结构符合 1NF&＃xff0c;其中“证书名称”和“证书编号”是主码&＃xff0c;但是因为“发证部门” 只完全依赖于“证书名称”&＃xff0c;即只依赖于主关键字的一部分(即部分依赖)&＃xff0c;所以它不符合 2NF&＃xff0c;这样首先存在数据冗余&＃xff0c;因为证书种类可能不多。其次&＃xff0c;在更改发证部门时&＃xff0c;如果漏改了某一记录&＃xff0c;存在数据不一致。再次&＃xff0c;如果获得某种证书的职工全部跳槽了&＃xff0c;那么这个发证部门的信息就可能丢失了&＃xff0c;即这种关系不允许存在某种证书没有获得者的情况。

可以用分解的方法消除部分依赖的情况&＃xff0c;而使关系达到 2NF 的标准。方法是&＃xff0c;从现有关系中分解出新的关系表&＃xff0c;使每个表中所有的非关键字都完全依赖于各自的主关键字。可以分解成两个表(省份、姓名、证书名称、证书编号、核准项目、发证日期、有效期)和(证书名称、发证部门)&＃xff0c;这样就完全符合 2NF 了。

如果关系模型R为第一范式&＃xff0c;并且R中的每一个非主属性完全函数依赖于R的某个候选键&＃xff0c;则称R为第二范式模式(如果A是关系模式R的候选键的一个属性&＃xff0c;则称A是R的主属性&＃xff0c;否则称A是R的非主属性)。

所谓完全依赖是指不能存在仅依赖主关键字一部分的属性.

例如&＃xff0c;在选课关系表(学号&＃xff0c;课程号&＃xff0c;成绩&＃xff0c;学分)&＃xff0c;关键字为组合关键字(学号&＃xff0c;课程号)&＃xff0c;但由于非主属性学分仅依赖于课程号&＃xff0c;对关键字(学号&＃xff0c;课程号)只是部分依赖&＃xff0c;而不是完全依赖&＃xff0c;因此此种方式会导致数据冗余以及更新异常等问题&＃xff0c;解决办法是将其分为两个关系模式&＃xff1a;学生表(学号&＃xff0c;课程号&＃xff0c;分数)和课程表(课程号&＃xff0c;学分)&＃xff0c;新关系通过学生表中的外关键字课程号联系&＃xff0c;在需要时进行连接。

4&＃xff0e;第三范式

如果一个关系 R 属于 2NF&＃xff0c;且每个非主属性不传递依赖于主属性&＃xff0c;这种关系是 3NF&＃xff0c; 记作 RÎ3NF。第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

&＃xff0c;指的是如果存在”A → B → C”的决定关系&＃xff0c;则C传递函数依赖于A。因此&＃xff0c;满足第三范式的数据库表应该不存在如下依赖关系&＃xff1a;

关键字段 → 非关键字段x → 非关键字段y

从 2NF 中消除传递依赖&＃xff0c;就是3NF。例如&＃xff0c;有一个表(职工姓名&＃xff0c;工资级别&＃xff0c;工资额)&＃xff0c;其中职工姓名是关键字&＃xff0c;此关系符合2NF&＃xff0c;但是因为工资级别决定工资额&＃xff0c;也就是说非主属性“工资额”传递依赖于主属性“职工姓名”&＃xff0c;它不符合3NF&＃xff0c;同样可以使用投影分解的办法分解成两个表&＃xff1a;(职工姓名&＃xff0c;工资级别)&＃xff0c;(工资级别&＃xff0c;工资额)。

以学生表(学号&＃xff0c;姓名&＃xff0c;课程号&＃xff0c;成绩)为例&＃xff0c;其中学生姓名无重名&＃xff0c;所以该表有两个候选码(学号&＃xff0c;课程号)和(姓名&＃xff0c;课程号)&＃xff0c;故存在函数依赖&＃xff1a;学号——>姓名&＃xff0c;(学号&＃xff0c;课程号)——>成绩&＃xff0c;唯一的非主属性成绩对码不存在部分依赖&＃xff0c;也不存在传递依赖&＃xff0c;所以属性属于第三范式。

5&＃xff0e;BC 范 式

一般满足 3NF 的关系模型已能消除冗余和各种异常现象&＃xff0c;获得比较满意的效果&＃xff0c;但无

论2NF 还是 3NF 都没有涉及主属性间的函数依赖&＃xff0c;所以有时仍会引起一些问题。由此引入BC 范式(由 Boyeet 和Codd 提出)。通常认为BCNF 是第三范式的改进。

它构建在第三范式的基础上&＃xff0c;如果关系模型R是第一范式&＃xff0c;且每个属性都不传递依赖于R的候选键&＃xff0c;那么称R为BCNF的模式。

当一个关系模型 R BCNF&＃xff0c;则在函数依赖范畴里&＃xff0c;就认为已彻底实现了分离&＃xff0c;消除了 插入、删除的异常。

假设仓库管理关系表(仓库号&＃xff0c;存储物品号&＃xff0c;管理员号&＃xff0c;数量)&＃xff0c;满足一个管理员只在一个仓库工作&＃xff1b;一个仓库可以存储多种物品&＃xff0c;则存在如下关系&＃xff1a;

(仓库号&＃xff0c;存储物品号)——>(管理员号&＃xff0c;数量)

(管理员号&＃xff0c;存储物品号)——>(仓库号&＃xff0c;数量)

所以&＃xff0c;(仓库号&＃xff0c;存储物品号)和(管理员号&＃xff0c;存储物品号)都是仓库管理关系表的候选码&＃xff0c;表中唯一非关键字段为数量&＃xff0c;它是符合第三范式的。但是&＃xff0c;由于存在如下决定关系&＃xff1a;

(仓库号)——>(管理员号)

(管理员号)——>(仓库号)

即存在关键字段决定关键字段的情况&＃xff0c;因此其不符合BCNF。把仓库管理关系表分解为两个关系表仓库管理表(仓库号&＃xff0c;管理员号)和仓库表(仓库号&＃xff0c;存储物品号&＃xff0c;数量)&＃xff0c;这样这个数据库表是符合BCNF的&＃xff0c;并消除了删除异常、插入异常和更新异常。

4NF(第四范式)

设R是一个关系模型&＃xff0c;D是R上的多值依赖集合。如果D中存在凡多值依赖X->Y时&＃xff0c;X必是R的超键&＃xff0c;那么称R是第四范式的模式。

例如&＃xff0c;职工表(职工编号&＃xff0c;职工孩子姓名&＃xff0c;职工选修课程)&＃xff0c;在这个表中&＃xff0c;同一个职工可能会有多个职工孩子姓名&＃xff0c;同样&＃xff0c;同一个职工也可能会有多个职工选修课程&＃xff0c;即这里存在着多值事实&＃xff0c;不符合第四范式。如果要符合第四范式&＃xff0c;只需要将上表分为两个表&＃xff0c;使它们只有一个多值事实&＃xff0c;例如职工表一(职工编号&＃xff0c;职工孩子姓名)&＃xff0c;职工表二(职工编号&＃xff0c;职工选修课程)&＃xff0c;两个表都只有一个多值事实&＃xff0c;所以符合第四范式。

综合 1NF、2NF 和 3NF、BCNF 的内涵可概括如下&＃xff1a;

(1)非主属性完全函数依赖于码(2NF的要求)&＃xff1b;

(2)非主属性不传递依赖于任何一个候选码(3NF 的要求)&＃xff1b;

(3)主属性对不含它的码完全函数依赖(BCNF 的要求)&＃xff1b;

(4)没有属性完全函数依赖于一组非主属性(BCNF 的要求)。

反规范化

数据库中的数据规范化的优点是减少了数据冗余&＃xff0c;节约了存储空间&＃xff0c;相应逻辑和物理的I/O 次数减少&＃xff0c;同时加快了增、删、改的速度&＃xff0c;但是对完全规范的数据库查询&＃xff0c;通常需要更多的连接操作&＃xff0c;从而影响查询速度。因此&＃xff0c;有时为了提高某些查询或应用的性能而破坏规范规则&＃xff0c;即反规范化(非规范化处理)。

常见的反规范化技术包括&＃xff1a;

(1)增加冗余列

增加冗余列是指在多个表中具有相同的列&＃xff0c;它常用来在查询时避免连接操作。例如&＃xff1a;以规范化设计的理念&＃xff0c;学生成绩表中不需要字段“姓名”&＃xff0c;因为“姓名”字段可以通过学号查询到&＃xff0c;但在反规范化设计中&＃xff0c;会将“姓名”字段加入表中。这样查询一个学生的成绩时&＃xff0c;不需要与学生表进行连接操作&＃xff0c;便可得到对应的“姓名”。

(2)增加派生列

增加派生列指增加的列可以通过表中其他数据计算生成。它的作用是在查询时减少计算量&＃xff0c;从而加快查询速度。例如&＃xff1a;订单表中&＃xff0c;有商品号、商品单价、采购数量&＃xff0c;我们需要订单总价时&＃xff0c;可以通过计算得到总价&＃xff0c;所以规范化设计的理念是无须在订单表中设计“订单总价”字段。但反规范化则不这样考虑&＃xff0c;由于订单总价在每次查询都需要计算&＃xff0c;这样会占用系统大量资源&＃xff0c;所以在此表中增加派生列“订单总价”以提高查询效率。

(3)重新组表

重新组表指如果许多用户需要查看两个表连接出来的结果数据&＃xff0c;则把这两个表重新组成一个表来减少连接而提高性能。

(4)分割表

有时对表做分割可以提高性能。表分割有两种方式。

水平分割&＃xff1a;根据一列或多列数据的值把数据行放到两个独立的表中。水平分割通常在下面的情况下使用。

情况 1&＃xff1a;表很大&＃xff0c;分割后可以降低在查询时需要读的数据和索引的页数&＃xff0c;同时也降低了索引的层数&＃xff0c;提高查询效率。

情况 2&＃xff1a;表中的数据本来就有独立性&＃xff0c;例如表中分别记录各个地区的数据或不同时期的数据&＃xff0c;特别是有些数据常用&＃xff0c;而另外一些数据不常用。

情况 3&＃xff1a;需要把数据存放到多个介质上。

垂直分割&＃xff1a;把主码和一些列放到一个表&＃xff0c;然后把主码和另外的列放到另一个表中。如果一个表中某些列常用&＃xff0c;而另外一些列不常用&＃xff0c;则可以采用垂直分割&＃xff0c;另外垂直分割可以使得数据行变小&＃xff0c;一个数据页就能存放更多的数据&＃xff0c;在查询时就会减少 I/O 次数。其缺点是需要管理冗余列&＃xff0c;查询所有数据需要连接操作。