数据库设计Step

数据库设计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表)

在那张表中,某些产品和客户新闻是冗余的,浪费了蕴藏空间。某些查询如“上个月什么客户定购了吸尘器”须求摸索整张表。当要修改客户戴夫的地址需求立异该表的多条记下。最后删除客户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组,巴赫(Bach)mann为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/

发表评论

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