社会保险数据库设计Step by Step (10)——范式化

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

2011-09-04 17:42 by DBFocus, 3549
visits, 收藏, 编辑

引言:前文(数据库设计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/)

发表评论

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