资料库正规化

2013-03-19_091950

这是MySQL 5实力养成暨评量里的1-32.‘可以消除表格部份依赖关系的是下列哪个正规化?

答案:(B) 第二正规化

[adsense][/adsense]

已经上过资料库的课很多次了,可是关于正规化的问题还是无法解决真是愚蠢。所以我们参考维基的说明再好好看一遍吧。

资料库正规化

资料库正规化,又称资料库或资料库正规化、标准化,是资料库设计中的一系列原理和技术,以减少资料库中资料冗余,增进资料的一致性。关联模型的发明者埃德加·科德最早提出这一概念,并于1970年代初定义了第一正规化、第二正规化和第三正规化的概念,还与Raymond F. Boyce于1974年共同定义了第三正规化的改进范式——BC正规化。
除外还包括针对多值依赖的第四正规化,连线依赖的第五正规化,DK正规化和第六正规化。
现在资料库设计最多满足3NF,普遍认为范式过高,虽然具有对资料关联更好的约束性,但也导致资料关联表增加而令资料库IO更易繁忙,原来交由资料库处理的关联约束现更多在资料库使用程式中完成。

第一正规化

第一正规化(1NF,中国大陆译作第一范式)是资料库正规化中所使用的一种正规形式。第一正规化是为了要排除重复群的出现,所采用的方法是要求资料库的每个栏位都只能存放单一值,而且每笔记录都要能利用一个唯一的主键来加以识别。

不符合第一正规化的情况

重复群

重复群通常会出现在会计帐上,每一笔记录可能有不定个数的值。举例来说:

交易
顾客 日期 数量
Pete Monday 19.00-28.20
Pete Wednesday -84.00
Sarah Friday 100.00150.00-40.00

‘数量’ 就是所谓的重复群了,而在这种情况下这份资料就不符合第一正规化。想要消除重复群的话,只要把每笔记录都转化为单一记录即可:

交易
交易 ID 顾客 日期 数量
1 Pete Monday 19.00
2 Pete Monday -28.20
3 Pete Wednesday -84.00
4 Sarah Friday 100.00
5 Sarah Friday 150.00
6 Sarah Friday -40.00

缺乏唯一识别码

一样是在交易这个例子中,同一天同一个人买了同样的数量,这样的交易做了两次:

交易
顾客 日期 数量
Pete Monday 19.00
Pete Monday 19.00

如上所示,这两笔交易可以说是一模一样,也就是说如果只靠这些资料我们没有办法分辨这两笔记录。我们之所以说它不符合第一正规化,是因为上面这样的表示法欠缺一个唯一识别码,可以是一个栏位,也可以是一组栏位,而且可以保证在这个资料中唯一识别码不会重复出现。要将它正规化到符合第一正规化的原则只需要加入一个唯一识别码即可:

交易
交易 ID 顾客 日期 数量
1 Pete Monday 19.00
2 Pete Monday 19.00

关联式资料库里的第一正规化

大多数的 RDBMS (关联式资料库) 允许使用者在定义资料表的时候不去指定主键,不过这么一来这种资料表就不符合第一正规化了。

从某个角度看来,不允许重复群的出现是关联式资料库表示资讯的方法,RDBMS 里资料表每一笔记录的每一个栏位都只能有一个值。举例来说,如果定义了一个叫做 Favorite Number 的整数栏位,每一笔记录的 Favorite Number 这个栏位都只会是一个整数 (或是无);这也就是说,如果设定了主键的话,理论上不可能会有任何关联式资料库的资料表会违反第一正规化的原则。

不过就算是在这种情况下,还是可以设计出在骨子里违反第一正规化的资料表。最简单的方法就是把多个有意义的值编码过后存进一个栏位里,然后在资料表中用很多栏位来表达同一个事实。

单一栏位中有多个有意义的值

在单一栏位中存放多个值是违反第一正规化的做法,下面这个就是很好的例子,它把多个值用逗号分开来表示:

挑食列表
不喜欢的食物
Jim Liver, Goat’s cheese
Alice Broccoli
Norman Pheasant, Liver, Peas

以这样的设计看来,想要知道有什么人不喜欢某样特定的东西是很不容易的。不过可以把这个资料表转化成下面这种符合第一正规化的型式:

挑食列表
不喜欢的食物
Jim Liver
Jim Goat’s cheese
Alice Broccoli
Norman Pheasant
Norman Liver
Norman Peas

用很多栏位来表达同一个事实

在同一个资料表里用多个栏位来表达同一个事情也是违反第一正规化的:

个人资料
喜欢的颜色 不喜欢的食物 (1) 不喜欢的食物 (2) 不喜欢的食物 (3)
Jim Green Liver Goat’s cheese
Alice Fuchsia Broccoli
Norman Blue Pheasant Liver Peas
Emily Yellow

就算我们能确定每个人不喜欢吃的食物最多不会超过三样,这还是一个很糟的设计。举例来说,我们想要知道所有不喜欢同一种食物的人的组合的话,这就不是件容易的事,因为食物有可能出现在任何一个栏位,也就是说每一次的查询都要去检查 9 (3 x 3) 组不同的栏位组合。

第二正规化

第二正规化(2NF,中国大陆译作第二范式)是资料库正规化中所使用的一种正规形式。它的规则是要求资料表里的所有资料都要和该资料表的主键有完全相依关系;如果有哪些资料只和主键的一部份有关的话,就得把它们独立出来变成另一个资料表。如果一个资料表的主键只有单一一个栏位的话,它就一定符合第二正规化。

一个资料表符合第二正规化若且唯若

  • 它符合第一正规化
  • 所有非主键的栏位都一定和主键有关

范例

有一个资料表记录了设备元件的资讯,如下所示:

元件来源
元件 ID (主键) 供应商 ID (主键) 供应商名称 价格 供应商住址
65 2 Stylized Parts 59.99 VA
73 2 Stylized Parts 20.00 VA
65 1 ACME Industries 69.99 CA

这个资料表的每个值都是单一值,所以它符合第一正规化。因为同一个元件有可能由不同的供应商提供,所以得把元件 ID 和供应商 ID 合在一起组成一个主键。

主键和价格之间的关系很正确:同一个元件在不同供应商有可能会有不同的报价,所以价格确实和主键完全相关(完全依赖)。

另一方面,供应商的名称和住址就只和供应商 ID 有关(部分依赖),这不符合第二正规化的原则。仔细看就会发现 “Stylized Parts” 这个名称和 “VA” 这个住址重复出现了两次;要是它改名了或是被其他公司并购了怎么办?这时候最好把这些资料存到第二个资料表中:

供应商
供应商 ID (主键) 名称 住址
1 ACME Industries CA
2 Stylized Parts VA

这么一来,原本的 “元件来源” 资料表就得要做相对应的更动:

元件来源
元件 ID (主键) 供应商 ID (主键) 价格
65 2 59.99
73 2 20.00
65 1 69.99

检查资料表里的每个栏位,确认它们是不是都和主键完全相关,这样才能知道这个资料表是不是符合第二正规化;如果不是的话,就把那些不完全相关的栏位移到独立的资料表里。接下来的步骤是要确保所有不是键的栏位都和彼此没有相依关系,这就叫做第三正规化

第三正规化

第三正规化(3NF,中国大陆译作第三范式)是资料库正规化中所使用的一种正规形式,用来检验是否所有非键属性都只和候选键有相关性,也就是说所有非键属性互相之间应该是无关的。

第三正规化和第二正规化不同的地方在于,在第三正规化里,所有的非键属性都必须和每个候选键有直接相关。如果再对第三正规化做进一步加强就成了BC正规化,它所强调的重点就在于 “资料间的关系是奠基在键上、以整个键为考量、而且除了键之外不考虑其他因素”。

正规定义

  • {R} 表一个关系;
  • F 表维持 R 所需的一组功能相依性
  • X 表 R 属性的子集合
  • A 表 R 的一个属性

如果对于 X \to A 这种型式的功能相依性而言,下列叙述任一为真的话,则可以称 R 符合第三正规化:

任何一个具有部份相依性或是转移相依性的关系都违反了第三正规化。

范例

以下面这个定义机械元件的关系为例:

机械元件
元件编号
(主键)
制造商名称 制造商位址
1000 Toyota Park Avenue
1001 Mitsubishi Lincoln Street
1002 Toyota Park Avenue

本例中制造商位址很明显地不该被列在这个关系里面,因为和元件本身比起来,制造商位址应该和制造商比较有关系;正确的做法应该是把独立成为一个新的资料表:

制造商
制造商名称
(主键)
制造商位址
Toyota Park Avenue
Mitsubishi Lincoln Street

然后把原本的资料表改成这样:

机械元件
元件编号
(主键)
制造商名称
1000 Toyota
1001 Mitsubishi
1002 Toyota

先前那个资料表的问题在于每提到一次制造商名称就要多存一次它的位址,而这就不符合第三正规化的原则。

下面提供了另一个例子:

订单 (Order)
订单编号 (Order Number)
(主键)
客户名称 (Customer Name) 单价 (Unit Price) 数量 (Quantity) 小计 (Total)
1000 David $35.00 3 $105.00
1001 Jim $25.00 2 $50.00
1002 Bob $25.00 3 $75.00

在本例中,非主键栏位完全依赖于主键订单编号,也就是说唯一的订单编号能导出唯一非主键栏位值,符合第二正规化。第三正规化要求非主键栏位之间不能有依赖关系,显然本例中小计依赖于非主键栏位单价和数量,不符合第三正规化。小计不应该放在这个资料表里面,只要把单价乘上数量就可以得到小计了;如果想要符合第三正规化的话,就把小计拿掉吧 (不过在做查询的时候,本来用 “SELECT Orders.Total FROM Order” 就要改成用 “SELECT UnitPrice * Quantity FROM Order” 了)。

订单 (Order)
订单编号 (Order Number)
(主键)
客户名称 (Customer Name) 单价 (Unit Price) 数量 (Quantity)
1000 David $35.00 3
1001 Jim $25.00 2
1002 Bob $25.00 3

BC正规化

Boyce-Codd范式(Boyce-Codd normal form,BCNF),是资料库正规化中所使用的一种正规形式。是在第三正规化的基础上加上更严格约束,每个BCNF关联是第三正规化的子集,有从属关联。它的定义是:

如果对于关联模式R中存在的任意一个非平凡函式依赖X->A,都满足X是R的一个候选键,那么关联模式R就属于BCNF,。

BCNF与第三正规化的不同之处在于,第三正规化允许A是主属性(第三正规化中不存在非主属性被另一个非主属性决定),而在BCNF中,任何属性(包括非主属性和主属性)都不能被非主属性所决定。

范例

关联模式R:

Property_id#(主键) County_name Lot# Area

其中依赖关联如下: Property_id#->{County_name,Lot#,Area}; {County_name,Lot#}->{Property_id#,Area}; Area->County_name; 很明显最后一个依赖违反了BC正规化的要求,Area不是关联模式R的主键,而依赖于它的County_name是能够决定其他属性的主属性。故应当规范化为:

Property_id#(主键) County_name Lot#
Area(主键) County_name