数据库设计Step by Step (10)——范式化

引言:前文(数据库设计Step by Step
(9)——ER-to-SQL转化)讨论了如何把ER图转化为关联表结构。本文将介绍数据库范式并讨论哪边范式化候选表。我们先来拘禁一下此时地处数据库生命周期中的岗位(如下图所示)。

社会保险 1

前方几乎篇博文中我们详细的讨论了ER建模的主意。精心设计的ER模型将扶持我们一直获得范式化的申或独自待稍许修改就为范式化的阐明,设计、绘制ER图的首要也反映于此间。概念数据建模(ER建模)从同开始就是影响的指引着咱走向范式化的多寡库表结构。

正文的议论将起来为第一范式,止于BCNF范式。在切切实实数据库设计着,一般用上的范式化目标是第三要么BCNF范式,更胜级别之范式更多的凡理论价值,本文也未将涉嫌。

 

社会保险 2

范式基础

关系数据库中的表有时会面对性能、一致性与可维护性方面的题材。举例来说,把所有数据库被的多寡都定义在一如既往摆放表中将导致大量冗余的数量,低效的询问与翻新性能,对某些数据的勾将促成来因此数码的丢等。如图1所显示,products,
salespersons, customers, orders都存储于平摆设称吧Sales的表中。

product_name order_no cust_name cust_addr credit date sales_name
vacuum cleaner 1435 Dave Austin 6 2010-03-01 Carl
computer 2730 Qiang Plymouth 10 2011-04-15 Ted
refrigerator 2460 Mike Ann Arbor 8 2010-09-12 Dick
DVD player 519 Peter Detroit 3 2010-12-05 Fred
radio 1986 Charles Chicago 7 2011-05-10 Richard
CD player 1817 Eric Mumbai 8 2010-08-03 Paul
vacuum cleaner 1865 Charles Chicago 7 2010-10-01 Carl
vacuum cleaner 1885 Betsy Detroit 8 2009-04-19 Carl
refrigerator 1943 Dave Austin 6 2011-01-04 Dick
television 2315 Sakti East Lansing 6 2011-03-15 Fred

(图1 Sales表)

在当时张表中,某些产品与客户信息是冗余的,浪费了仓储空间。某些查询如“上个月哪客户预订了吸尘器”需要找整张表。当要改客户Dave的地址需要更新该表的大都久记下。最后去客户Qiang的订单(2730)将促成该客户姓名、地址、信用级别信息的遗失,因为该客户只有唯一这个订单。

比方我们通过有些办法将该大表拆分成基本上独小表,从而打消上述这些题材设数据库更为便捷与保险,范式化就是为着达成这无异于靶。范式化是凭经分析说明中各属性之间的相互依赖,并将大表映射为多单小表的经过。

 

率先范式(1NF)

概念:当且特当一张表的保有列只包含原子值时,即表中每行中之诸一个列只出一个价值,则该表符合第一范式。

祈求1蒙受之Sales表的各级一样履行、每一样排列被只有原子值,故Sales表满足第一范式。

为重新好之敞亮第一范式,我们讨论一下域、属性、列里的歧异。

域是某属性所有可能价值的成团,但一样个域可能为用于多独属性上。举例来说,人名的地段包含有或的全名集合,在图1的申中唯独用于cust_name或sales_name属性。每一样列代表一个属性,有些情况下表示不同属性的大都独列有同等的域,这并无会见违反第一范式,因为表中的价仍是原子的。

单独称第一范式的表常会遇上数据重复、更新性能及创新一致性等题材。为了更好的了解这些题目,我们不能不定义键的定义。

超键是一个或多单特性的汇,其能够支援我们唯一确定一长条记下。若构成超键属性列的子集仍为一个超键,但该子集少了其它一个属性都用使该不再是一个超键,则该属性列子集称为候选键。主键是打同张表的候选键集合中擅自选择出的,作为该表的一个目。

作一个例证,图2中表的具备属性组成一个超键。

report_no editor dept_no dept_name dept_addr author_id author_name author_addr
4216 woolf 15 design argus1 53 mantei cs-tor
4216 woolf 15 design argus1 44 bolton mathrev
4216 woolf 15 design argus1 71 koenig mathrev
5789 koenig 27 analysis argus2 26 fry folkstone
5789 koenig 27 analysis argus2 38 umar prise
5789 koenig 27 analysis argus2 71 koenig mathrev

(图2 Report表)

于事关模型中莫允发生还的尽,因此一个斐然的超键是表明底兼具列(属性)的结。假设表中列一个部门的地址(dept_addr)都相同,则除dept_addr之外的属性仍然是一个超键。对另属性作类似之要,逐步缩小属性之结合。我们发现属性组合report_no,
author_id能唯一确定表中的旁属性,即凡是一个超键。同时report_no或author_id中的即兴一个且心有余而力不足唯一确定表中的如出一辙履行,故属于性组合report_no,
author_id是一个候选键。由于它是该表的绝无仅有候选键,它们啊是该表的主键。

一样摆表能有差不多个候选键。举例来说,在祈求2挨,若有增大列author_ssn(SSN:社会保险号),属性组合report_no,
author_ssn也克唯一确定表中的其余属性。因此属性组合(report_no,
author_id)和(report_no,
author_ssn)都是候选键,可以任选其一作为主键。

 

次范式(2NF)

为了解释第二与再胜似级别范式。我们要引入函数依赖的定义。一个还是多个属于性值能唯一确定一个要多单其他属性值称为函数依赖。给得某表(R),一组属性(B)函数依赖让任何一样组属性(A),即在随意时刻每个A值就及唯一的B值相关联。这无异套数依赖用A
–> B代表。以图2着之表为例,表report的函数依赖如下:

report:  report_no –> editor, dept_no

                dept_no –> dept_name, dept_addr

                author_id –> author_name, author_addr

概念:一摆设表满足第二范式(2NF)的条件是当且仅当该表满足第一范式且每个非键属性完全靠让主键。当一个性出现在函数依赖式的右端,且函数依赖式的左端为表的主键或可由于主键传递派生出之属于性组,则该属性完全依赖让主键。

report表中一个传递函数依赖之例子:

report_no –> dept_no

dept_no –> dept_name

为我们会派生出函数依赖(report_no –>
dept_name),即dept_name传递依赖让report_no。

接轨我们的例证,图2中表的复合键(report_no,
author_id)是绝无仅有的候选键,即为表的主键。该表存在一个FD(dept_no –>
dept_name,
dept_addr),其左端没有主键的别样部分。该表的别样两个FD(report_no
–> editor, dept_no和author_id –> author_name,
author_addr)的左端包含主键的一模一样片可无是普。故report表的旁一样漫漫FD都未饱第二范式的格。

合计一下只有满足第一范式的report表的缺陷。report_no,
editor和dept_no对该Report的各个一样员author都得更,故当Report的editor需要转移时,多长达记下得一起修改。这便是所谓的更新异常(update
anomaly),冗余的更新会降低性能。当没管持有符合条件的笔录并创新时,还会招致数的匪相同。若要于说明中投入一号新的author,只有以该author参与了某Report的行文才能够插入该author的记录,这就是是所谓的插入异常(insert
anomaly)。最后,若有平等张Report无效了,所有与该Report相关联的记录须同步去。这可能引致author信息之丢(与该Report相关联的author_id,
author_name, author_addr也于删了)。这等同入作用为称呼删除异常(delete
anomaly),使数码丧失了完整性。

上述这些弱点而由此把单纯满足第一范式的表转化为多布置满足第二范式的表来克服。在保留原函数依赖以及语义关系的前提下,把Report表映射为老三摆放小表(report1,
report2, report3),其中蕴含的数量而图3所展示。

Report 1

report_no editor dept_no dept_name dept_addr
4216 woolf 15 design argus 1
5789 koenig 27 analysis argus 2

Report 2

author_id author_name author_addr
53 mantei cs-tor
44 bolton mathrev
71 koenig mathrev
26 fry folkstone
38 umar prise
71 koenig mathrev

Report 3

report_no author_id
4216 53
4216 44
4216 71
5789 26
5789 38
5789 71

(图3  2NF表)

这些满足第二范式表的函数依赖也:

report1: report_no –> editor, dept_no

                 dept_no –> dept_name, dept_addr

report2: author_id –> author_name, author_addr

report3: report_no, author_id为候选键,无函数依赖

现行咱们已获取了三摆设满足第二范式的申,消除了第一范式表存在的最糟糕的题目。第一、editor,
dept_no, dept_name,
dept_addr不再需要也各一样员author重复。第二、更改一号editor只待创新report1的一模一样条记下。第三、删除report不再会导致author信息丢失的副作用。

俺们好小心到当下三摆放满足第二范式的说明可以直接由ER图转化得到。ER图中之Author、Report实体和中间的“多对大多”关系而依据上亦然篇博文(数据库设计Step
by Step
(9)——ER-to-SQL转化)的平整不行当然的转账为老三张表。

 

其三范式(3NF)

其次范式相对于第一范式已经生矣巨大的上进,但由有传递依赖(transitive
dependency),满足第二范式的表仍会在数据操作十分(anomaly)。当一摆表中留存传递依赖,其象征该表中描述了少于个单身的真情。每个事实对应于一修函数依赖,函数依赖之左手各不相同。举例来说,删除一个report,其蕴藉删除report1和report3表明中的对应记录(如图3所展示),该去动作的副作用是dept_no,
dept_name,
dept_addr信息也叫删了。如果把表report1映射为带有列report_no, editor,
dept_no的表report11以及寓列dept_no, dept_name,
dept_addr的表report12(如图4所显示),我们不怕能够免上述问题。

Report11

report_no editor dept_no
4216 woolf 15
5789 koenig 27

Report12

dept_no dept_name dept_addr
15 design argus 1
27 analysis argus 2

Report 2

author_id author_name author_addr
53 mantei cs-tor
44 bolton mathrev
71 koenig mathrev
26 fry folkstone
38 umar prise
71 koenig mathrev

Report 3

report_no author_id
4216 53
4216 44
4216 71
5789 26
5789 38
5789 71

(图4  3NF表)

概念:一摆表满足第三范式(3NF)当且仅当那每个非平凡函数依赖X –>
A,其中X和A可也简易或复合性,必须满足以下简单只尺码之一。1. X乎超键 或
2.
A啊某候选键的分子。若A也某候选键的积极分子,则A被称作主属性。注:平凡函数依赖之样式呢YZ
–> Z。

于上述例子中通过将report1映射为report11跟report12,消除了传递依赖report_no
–> dept_no –> dept_name,
dept_addr,我们得了使图4所出示之老三范式表及函数依赖:

report11: repot_no –> editor, dept_no

report12: dept_no –> dept_name, dept_addr

report2:   author_id –> author_name, author_addr

report3:   report_no, author_id为候选键(无函数依赖)

 

Boyce-Codd范式(BCNF)

其三范式消除了多数的十分,也是商数据库设计被达成的极其广的正儿八经。剩下的异常情况可由此Boyce-Codd范式(BCNF)或重复胜级别范式来解。BCNF范式可看成加强之老三范式。

概念:一张表R满足Boyce-Codd范式(BCNF),若那各一样修未寻常函数依赖X –>
A中X为超键。

BCNF范式是比较第三范式更强级别的范式因为其错过除了第三范式中之老二种规格(允许函数依赖右侧为主属性),即表的各级一样修函数依赖的左手必须也超键。每一样布置满足BCNF范式的说明而满足第三范式、第二范式和第一范式。

以下的例证展示了相同布置满足第三范式但非饱BCNF范式的发明。这样的表和那些只满足于逊色范式的说明一样有删除异常。

断言1:一个小组里的各个一样号称职工就出于同样各负责人来治本。一个小组或者有多员领导。

emp_name, team_name –> leader_name

断言2:每一样位负责人单独会与一个组的田间管理。

leader_name –> team_name

emp_name team_name leader_name
Sutton Hawks Wei
Sutton Condors Bachmann
Niven Hawks Wei
Niven Eagles Makowski
Wilson Eagles DeSmith

(图5  team表)

team表满足第三范式,具有复合候选键emp_name, team_name

team表有如下删除异常:若Sutton离开了Condors组,Bachmann为Condors组的首长及时无异消息用遗失。

破除这同一删除异常最简易的章程是依据两修断言创建两摆表,通过简单张表中冗余的音信来解删除异常。这同说是无害的并维持了独具原先的函数依赖,但立刻降低了履新性能,并欲再多囤积空间。为了避免删除异常,这样做是值得的。

流动:无损分解是恃将同张表说为有限摆放小表后,通过对少布置小表进行natural
join得到的阐发和原始表相同,不见面产生其它多余行。

 

社会保险 3

数据库范式化示例

社会保险 4(图6 
employee数据库ER图)

本案例基于图6遭受的ER模型和以下相关函数依赖。一般而言,函数依赖而经分析ER图及工作更推得。

  1. emp_id, start_date –> job_title, end_date
  2. emp_id –> emp_name, phone_no, office_no, proj_no,
    proj_name, dept_no
  3. phone_no –> office_no
  4. proj_no –> proj_name, proj_start_date, proj_end_date
  5. dept_no –> dept_name, mgr_id
  6. mgr_id –> dept_no

咱俩的靶子是计划性至少能够上第三范式(3NF)的关系数据库表结构,并尽可能减少表的数码。

若果以函数依赖1交6放大入一摆放表,并安装复合主键:emp_id,
start_date,那么我们违了第三范式,因为函数依赖2到6底等式左侧不是超键。因此,我们要将函数依赖1起另外的函数依赖中分离出去。如果以函数依赖2顶6进行统一,我们用得到许多传递依赖。故函数依赖2、3、4、5亟须分到不同之表中。我们还来设想函数依赖5同6是否会于不背第三范式的前提下开展联合。因为mgr_id和dept_no是相互依赖的,这点儿只属性在说明中还是超键,所以可以统一。

经过合理的映射函数依赖1及6,我们能赢得如下表:

emp_hist:      emp_id, start_date –> job_title, end_date

employee:    emp_id –> emp_name, phone_no, proj_no,
dept_no

phone:            phone_no –> office_no

project:           proj_no –> proj_name, proj_start_date,
proj_end_date

department: dept_no –> dept_name, mgr_id

                           mgr_id –> dept_no

及时等同解决方案包含了独具函数依赖。满足第三范式和BCNF范式,同时该方案创造了起码数量的阐发。

 

范式化从ER图得到的候选表

当数据库生命周期中,对表的范式化是透过分析表的函数依赖就的。这些函数依赖包括:从需求分析着收获的函数依赖;从ER图中得的函数依赖;从直觉中取的函数依赖。

主函数依赖代表了实体键之间的凭。次函数依赖代表实体内数据元素中的赖。一般的话,主函数依赖而打ER图中获取,次函数依赖而起需要分析中收获。表1著了每种基本ER构件所能博取的主函数依赖。

关系的度(Degree) 关系的连通数(Connectivity) 主函数依赖
二元或二元回归 “一对一”
“一对多”
“多对多”
2个:键(“一”侧) –> 键(“一”侧)
1个:键(“多”侧) –> 键(“一”侧)
无(由两侧键组成的组合键)
三元 “一对一对一”
“一对一对多”
“一对多对多”
“多对多对多”
3个:键(“一”),键(“一”) –> 键(“一”)
2个:键(“一”),键(“多”) –> 键(“一”)
1个:键(“多”),键(“多”) –> 键(“一”)
无(有三侧键组成的组合键)
泛化

每个候选表一般会发生差不多个主函数依赖与次函数依赖,这决定了即说明底范式化程度。对每个表动各种技术如果其上要求原则中求的范式化程度,在范式化过程被而保证数据完整性,即范式化后得到的表应包含本候选表的装有函数依赖。精心设计的概念数据模型通常会获基本已经范式化的阐明,后期的范式化处理不会见怪艰苦,所以概念数据建模非常关键。

 

社会保险 5

重中之重内容回顾

  1. 不善的申结构设计将促成表数据的创新非常(update
    anomaly)、插入异常(insert anomaly)、删除异常(delete anomaly)

2.
范式化通过解除冗余数据,来缓解数据库有的一致性、完整性和可维护性等地方的题材。

  1. 每当事实上数据库设计被,范式化的靶子一般是达到第三范式或BCNF范式。

  2. 精心设计的定义数据模型(ER模型)能辅助我们收获范式化的说明。

数据库范式化参考资料

  1. Database
    Normalization(http://en.wikipedia.org/wiki/Database_normalization)

  2. 3 Normal Forms Database
    Tutorial(http://www.phlonx.com/resources/nf3/)

发表评论

电子邮件地址不会被公开。 必填项已用*标注