规范化
规范化逻辑数据库设计包括把数据组织成多个表。规范化通过减少冗余来改善性能。冗余能导致:
n 不一致——当同一事实被存放多份时容易发生错误。
n 更新异常——插入,修改和删除数据可以导致不一致。
当一个表在更新或删除时,很可以忽略对其他关系作相应的修改。
正规化有众多的益处。这些包含快速排序和创建索引,每个表有很少的索引,很少的NULL值增加了数据库的紧凑性。然而,随着规范化的深入,数量和复杂性也增加了。如果数量的增加在于表的增加,数据库的性能可能恶化。规范化帮助简化表的结构。应用程序的性能是直接与数据库设计相关的。粗劣的设计会妨碍系统的性能。数据库的逻辑设计忙以理想的数据库为基础的。
为了完成一个良好的数据库设计必须遵守一些规则:
n 每个表都有标识符。
n 每个表交为实体的单个类型存储数据。
n 在表中应避免NULL值列。
n 在表中应避免值或列的重复。
范式
规范化能使表满足一定的约束条件,并达到某一个范式。范式保证在数据库里不包含任何类型的冗余和不一致。范式是具有最小冗余度的表结构。现在已经定主了一些范式。最重要的,使用最广泛的是非曲直:
n 第一范式(1NF)
n 第二范式(2NF)
n 第三范式(3NF)
n Boyce-Codd范式(BCNF)
第一范式(1NF)
当表中每个单元包含用仅含一个值时,这个表叫作第一范式(1NF)。
考虑下列表Project.
Profect
Ecode | Dept | ProjCode | Hours |
E101 | Systems | P27 P51 P20 | 90 101 60 |
E305 | Sales | P27 P22 | 109 98 |
E508 | Admin | P51 P27 | NULL 72 |
表中的数据没有规范化因为ProjCode和Hours单元里有一个以上值。
在Project表里应用第一范式的定义,你得到下表
Profect
Ecode | Dept | ProjCode | Hours |
E101 | Systems | P27 | 90 |
E101 | Systems | P51 | 101 |
E101 | Systems | P20 | 60 |
E305 | Sales | P27 | 109 |
E305 | Sales | P22 | 98 |
E508 | Admin | P51 | NULL |
E508 | Admin | P27 | 72 |
函数相关性
规范化理论是以函数相关性的基本的概念为基础。首先,让我们了解函数相关性的概念。
给定一个关系(你可以回想起表也称关系)R,如果R中A的每个值都与B的某一确定值对应,则属性A函数依赖于属性B。
换句话说,属性A函数依赖于属性B是仅当B的每个值,都有A的某一确定值对应。属性B被称为决定因素。
考虑下表Employee:
Employee
Code | Name | City |
E1 | Mac | Delhi |
E2 | Sandra | CA |
E3 | Henry | France |
给定了Code 值,就有唯一确定的Name值。例如,对于Code E1有唯一确定的Name值,Mac。因此,Name函数依赖Code。同样地,对于每个Code 值,都有唯一确定的 City值。因此,属性City函数依赖属性Code。属性Code是决定因素。你也能说Code决定City和Name。
第二范式(2 NF)
一个表称为2 NF是当它是1NF,并且记录中的所有非关键字属性都不部分依赖于关键字。
考虑Project表
Project |
ECode |
ProjCode |
Dept |
Hours |
这表有下列记录
Ecode | ProjCode | Dept | Hours |
E101 | P27 | Systems | 90 |
E305 | P27 | Finance | 10 |
E508 | P51 | Admin | NULL |
E101 | P51 | Systems | 101 |
E101 | P20 | Systems | 60 |
E508 | P27 | Admin | 72 |
这情形能导致下列列问题:
n 插入
在被指定项目之前,部门的职员不能记录在案
n 更新
对于个个职员来说,其代码和部门被重复多次。因此,如果一个职员转到另一个部门,这将修改Employee表的每条记录。任何忽略都将导致不一致。
n 删除
如果职员完成工作项目,职员的记录将被删除。职员所属部门的信息也将丢失。
这里主关键字是复合的(ECode+ProjCode)
表满足1NF的定义。你现在需要检查它是否满足2NF。
在表中,对于每个ECode的值,有不止一个Hours.值。例如,ECode为E101,有三个Hours值:90,101和60。因此,Hours并不函数依赖于Ecode。同样地,对于每个ProjCode值,有不止一个Hours.值。例如,ProjCode为P27,有三个Hours值,90,10和72。然而,对于每个ECode和ProjCode的组合值,都有唯一确定的Hours值。因此,Hours是函数依靠于组合键,ECode+ProjCode.
现在,你必须检查Dept.是否也函数依赖于复合主关键字,ECode+ProjCode,对于每个ECode值,都有唯一确定的Dept值,例如,对于ECode,101,有唯一确定值,系统部门。因此Dept函数依赖于ECode,然而,对于每个ProjCode值,有不止一个Dept值。例如,对于ProjCode P27,有二个Dept值,系统和财务。因此,Dept并不函数依赖于ProjCode。Dept,因此,函数依赖于关键字的部分(ECode)而不是全部关键字(ECode+ProjCode)。因此,表Prodect不在2NF。属于2NF的表,非关键字属性必须是完全而不是部分依赖于关键字。
转换表为2NF的准则
n 查找和去除只函数依赖于关键字的部分而不是全部的属性。把他们放置于不同的表内。
n 重组剩余的属性。
转换表Project为2NF,你必须去除那些不完全的函数依赖于主关键字的属性,把它和依赖于的属性置于同一张表内。在上述例子,既然Dept不完全函数依赖于主关键字ECode+ProjCode,你把Dept和ECode一起放在一张独立的称EmployeeDept的表内。
现在,表Project将包含ECode,ProjCode和Hours.
EmployeeDept Project
ECode | Dept |
E101 | Systems |
E305 | Sales |
E508 | Admin |
ECode | ProjCode | Hours |
E101 | P27 | 90 |
E101 | P51 | 101 |
E101 | P20 | 60 |
E305 | P27 | 10 |
E508 | P51 | NULL |
E508 | P27 | 72 |
第三范式(3NF)
关系被称为3NF是当这是2NF并且每个非关键字属性仅函数数依赖于主关键字。
考虑表格Employee
ECode | Dept | DeptHead |
E101 | Systems | E901 |
E305 | Finance | E906 |
E402 | Sales | E906 |
E508 | Admin | E908 |
E607 | Finance | E909 |
E608 | Finance | E909 |
这种依赖会带来的问题是
n 插入
不能插入一个新的没有任何雇员的部门的部门负责人。这是因为主关键字未知。
n 更新
对于一个部门来说,部门负责人的(DeptHead)代码被多次重复,如果一个部门负责人调换了部门,必须对相应的表进行修改以保持一致性。
n 删除
如果职员的记录被删除,关于部门 负责人的信息了将被删除。因此,将引起信息的丢失。
你必须检查表是否是3NF。既然在表里每个单元有单个值,表是在1NF。
在Employee表里主关键字是Ecode。对于Ecode的每个值,都有唯一确定的Dept值。因此,属性Dept函数依赖于主关键字,Ecode。同样的,对于每个Ecode值,都有唯一确定的DeptHead值。因此,DeptHead函数依赖于主关键字Ecode。因此,所有属性都全部函数依赖于主关键字,Ecode因此表是在2NF。
然而,属性EeptHead。也依赖属性Dept。根据3NF,所有非关键字属性仅函数依赖于主关键字。这表不是3NF因为DeptHead函数依赖于Dept,它不是主关键字。
把表转换为3NF的准则
n 查找和去除函数依赖于不是主关键字的非关键字属性,把他们放于另一个表内。
n 重组剩余的属性
转换表Employee到3NF,你必须去除列DeptHead因为它不是仅函数依赖的于主关键字Ecode并把它和其依赖的属性Dept放于另一个称Department的表内。
Employee Department
Ecode | Dept |
E101 | Systems |
E305 | Finance |
E402 | Sales |
E508 | Admin |
E607 | Finance |
E608 | Finance |
Dept | DeptHead |
Systems | E901 |
Sales | E906 |
Admin | E908 |
Finance | E909 |
Boyce-Codd范式
原来定义的3NF在某些情形是不充分的,它不满足下列表:
n 有多个候选关键字
n 候选关键字是组合而成的
n 多个候选关键字之间重叠(至少有一个共同属性)
因此,就引出了一个新的范式—Boyce-Codd范式。你必须了解,在没有上述在个条件下,可以做到第三范式为止。这时3NF跟Boyce-Codd范式一样。
关系是Boyce-Codd范式(BCNF)当且仅当每个决定因素都是候选关键字。
考虑下列Project表。
Project
ECode | Name | ProjCode | Hours |
E1 | Veronica | P2 | 48 |
E2 | Anthony | P5 | 100 |
E3 | Mac | P6 | 15 |
E4 | Susan | P3 | 250 |
E4 | Susan | P5 | 75 |
E1 | veronica | P5 | 40 |
这表有冗余。如果职员的名字改变,将对表的每条记录都作修改,否则就不一致。
ECode+ProjCode是主关键字。你将注意到Name+ProjCode也能被选为主关键字。因而,是候选关键字
n Hours是函数依赖于ECode+ProjCode.
n Hours也是函数依赖于Name+ProjCode.
n Name 是函数依赖于Ecode.
n Ecode 是函数依赖于Name.
你将注意到该表:
n 多个候选关键字,是ECode+ProjCode和Name+ProjCode.
n 候选关键字是组合的。
n 候选关键字重叠因为属性ProjCode公用。
这是Boyce-Codd范式的个案。这是在第三范式。唯一非关键字项是Hours,全部依赖于关键字,ECode+ProjCode和Name+ProjCode。
ECode和Name是决定因素因为他们互相依赖。然而,他们不是候选关键了。根据BCNF,决定因素必须是候选关键字。
转换表到BCNF的准则
n 查找和去除重叠的候选关键字。把候选关键字的部分和它函数依赖的属性放在一个不同的表内。
n 重组剩余的项为一个表格。
因此,去除Name和Ecode并把他们放置于不同表内。你将想到下列表
Employee Project
ECode | Name |
E1 | Veronica |
E2 | Anthony |
E3 | Mac |
E4 | Susan |
E4 | Susan |
E1 | Veronica |
ECode | ProjCode | Hours |
E1 | P2 | 48 |
E2 | P5 | 100 |
E3 | P6 | 15 |
E4 | P2 | 250 |
E4 | P5 | 75 |
E1 | P5 | 40 |
规范化的最后结果是一组相关的表,这些表组成数据库。规范化的优点已列举了许多。然而,有时,为了获得简单输出,你不得不连接多个表。这影响查询的性能。在这种情况,由增加额外的列或额外的表来适当引入冗余是明智的。
为了改善性能而故意引入冗余,这种过程称为非规范化。
例如,考虑下列表,一个放置产品的详情和另一个放置订单。
Orders Products
OrderNo | Productld | Qty |
101 | P1 | 2 |
102 | P3 | 1 |
103 | P1 | 1 |
104 | P2 | 3 |
105 | P2 | 2 |
Productld | Desc | Cost |
P1 | XXX | 20 |
P2 | YYY | 10 |
P3 | ZZZ | 12 |
如果你必须计算每个订单的总销售,应该是产品总销费再加上下班10%的锐,计算总销售的查询如下:
SELECT SUM((cost*qty)+(0.10*cost*qty)) FROM Orders JOIN Products ON Orders.ProductId=Products.ProductId
如果有成千上万条记录,服务器将花时间处理查询和返回结果因为有连接和计算加入。因此,为了加速查询的处理,你可以把每个订单的销费和税存储在一起:
Orders
OrderNo | Productld | Qty | ProdutCost | Tax | OrderCost |
101 | P1 | 2 | 40 | 4 | 44 |
102 | P3 | 1 | 12 | 1.2 | 13.2 |
103 | P1 | 1 | 20 | 2 | 22 |
104 | P2 | 3 | 30 | 3 | 33 |
105 | P2 | 2 | 20 | 2 | 22 |
现在,为了查询销售总额,你必须作出简单查询:
SELECT SUM(OrderCost)FROM Orders
这个表结构已简化了查询并加速了查询的处理。由存储额外的列,你在表中引入了冗余但是提高了查询性能。
查规范化显然将取决于性能和数据完整性之间的平衡。非规范化也增加磁盘空间的使用。
小结
n 数据库的逻辑设计是优化关系数据库的核心
n 规范化逻辑数据库设计包括把数据分成一个以上的表
n 规范化减少冗余,从而改善性能
n 范式保证在数据库里不包含任何类型的冗余和不一致。
n 广泛使用的范式是:
l 第一范式(1NF)
l 第二范式(2NF)
l 第三范式(3NF)
l Boyce-Codd范式(BCNF)
n 非规范化引入数据的冗余。
n 非规范化提高查询的性能。