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

前文数据库设计Step by Step (9)——ER-to-SQL转化讨论了如何把ER图转化为关系表结构。本文将介绍数据库范式并讨论如何范式化候选表。我们先来看一下此刻处在数据库生命周期中的位置如下图所示。前几篇博文中我们详细的讨论了ER建模的方法。精心设计的ER模型将帮助我们直接得到范式化的表或只需稍许修改即为范式化的表设计、绘制ER图的重要性也体现在这里。概念数据建模ER建模从一开始就潜移默化的引导着我们走向范式化的数据库表结构。本文的讨论将始于第一范式止于BCNF范式。在现实数据库设计中一般需达到的范式化目标是第三或BCNF范式更高级别的范式更多的是理论价值本文也不将涉及。范式基础关系数据库中的表有时会面对性能、一致性和可维护性方面的问题。举例来说把整个数据库中的数据都定义在一张表中将导致大量冗余的数据低效的查询和更新性能对某些数据的删除将造成有用数据的丢失等。如图1所示products, salespersons, customers, orders都存储在一张名为Sales的表中。product_nameorder_nocust_namecust_addrcreditdatesales_namevacuum cleaner1435DaveAustin62010-03-01Carlcomputer2730QiangPlymouth102011-04-15Tedrefrigerator2460MikeAnn Arbor82010-09-12DickDVD player519PeterDetroit32010-12-05Fredradio1986CharlesChicago72011-05-10RichardCD player1817EricMumbai82010-08-03Paulvacuum cleaner1865CharlesChicago72010-10-01Carlvacuum cleaner1885BetsyDetroit82009-04-19Carlrefrigerator1943DaveAustin62011-01-04Dicktelevision2315SaktiEast Lansing62011-03-15Fred图1 Sales表在这张表中某些产品和客户信息是冗余的浪费了存储空间。某些查询如“上个月哪些客户订购了吸尘器”需要搜索整张表。当要修改客户Dave的地址需要更新该表的多条记录。最后删除客户Qiang的订单2730将造成该客户姓名、地址、信用级别信息的丢失因为该客户只有唯一这个订单。如果我们通过一些方法把该大表拆分成多个小表从而消除上述这些问题使数据库更为高效和可靠范式化就是为了达到这一目标。范式化是指通过分析表中各属性之间的互相依赖并把大表映射为多个小表的过程。第一范式1NF定义当且仅当一张表的所有列只包含原子值时即表中每行中的每一个列只有一个值则该表符合第一范式。图1中的Sales表的每一行、每一列中只有原子值故Sales表满足第一范式。为了更好的理解第一范式我们讨论一下域、属性、列之间的差异。域是某属性所有可能值的集合但同一个域可能被用于多个属性上。举例来说人名的域包含所有可能的姓名集合在图1的表中可用于cust_name或sales_name属性。每一列代表一个属性有些情况下代表不同属性的多个列具有相同的域这并不会违反第一范式因为表中的值仍是原子的。仅符合第一范式的表常会遇到数据重复、更新性能以及更新一致性等问题。为了更好的理解这些问题我们必须定义键的概念。超键是一个或多个属性的集合其能帮助我们唯一确定一条记录。若组成超键属性列的子集仍为一个超键但该子集少了任何一个属性都将使其不再是一个超键则该属性列子集称为候选键。主键是从一张表的候选键集合中任意挑选出的作为该表的一个索引。作为一个例子图2中表的所有属性组成一个超键。report_noeditordept_nodept_namedept_addrauthor_idauthor_nameauthor_addr4216woolf15designargus153manteics-tor4216woolf15designargus144boltonmathrev4216woolf15designargus171koenigmathrev5789koenig27analysisargus226fryfolkstone5789koenig27analysisargus238umarprise5789koenig27analysisargus271koenigmathrev图2 Report表在关系模型中不允许有重复的行因此一个明显的超键是表的所有列属性的组合。假设表中每一个部门的地址dept_addr都相同则除dept_addr之外的属性仍然是一个超键。对其他属性作类似的假设逐步缩小属性的组合。我们发现属性组合report_no, author_id能唯一确定表中的其他属性即是一个超键。同时report_no或author_id中的任意一个都无法唯一确定表中的一行故属性组合report_no, author_id是一个候选键。由于它们是该表的唯一候选键它们也是该表的主键。一张表能有多个候选键。举例来说在图2中若有附加列author_ssnSSN社会保险号属性组合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_nodept_no – dept_name, dept_addrauthor_id – author_name, author_addr定义一张表满足第二范式2NF的条件是当且仅当该表满足第一范式且每个非键属性完全依赖于主键。当一个属性出现在函数依赖式的右端且函数依赖式的左端为表的主键或可由主键传递派生出的属性组则该属性完全依赖于主键。report表中一个传递函数依赖的例子report_no – dept_nodept_no – dept_name因为我们能派生出函数依赖report_no – dept_name即dept_name传递依赖于report_no。继续我们的例子图2中表的复合键report_no, author_id是唯一的候选键即为表的主键。该表存在一个FDdept_no – dept_name, dept_addr其左端没有主键的任何组成部分。该表的另两个FDreport_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 1report_noeditordept_nodept_namedept_addr4216woolf15designargus 15789koenig27analysisargus 2Report 2author_idauthor_nameauthor_addr53manteics-tor44boltonmathrev71koenigmathrev26fryfolkstone38umarprise71koenigmathrevReport 3report_noauthor_id421653421644421671578926578938578971图3 2NF表这些满足第二范式表的函数依赖为report1: report_no – editor, dept_nodept_no – dept_name, dept_addrreport2: author_id – author_name, author_addrreport3: 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所示我们就能消除上述问题。Report11report_noeditordept_no4216woolf155789koenig27Report12dept_nodept_namedept_addr15designargus 127analysisargus 2Report 2author_idauthor_nameauthor_addr53manteics-tor44boltonmathrev71koenigmathrev26fryfolkstone38umarprise71koenigmathrevReport 3report_noauthor_id421653421644421671578926578938578971