数据库模式设计及其规范化

数据库模式设计及其规范化杨德仁2011年11月15日西部CA数据库设计的两个途径自底向上从报表开始报表是一个视图或复杂的关系模式需要规范化如何规范化？自上而下（“详细设计”阶段）从类或对象开始联系在何体现？E-R图扩展的E-R图（ISA关系）实体和联系需要转换转化规则在何？大纲数据库模式的定义数据库模型设计（E-R图）从E-R模型向关系模式转换数据库设计的规范化(bottom-up)数据冗余和数据异常基于码和数据依赖的规范化数据依赖函数依赖多值依赖投影依赖，……规范化的结果：一系列小表查询优化：反规范化数据库模式的定义数据库数据库由多个关系（表）构成，表达系统的信息。关系模式与关系实例码（key，键）三种模型概念模型（E-R模型）逻辑模型（关系模式）物理模型（存储结构）：索引，存储，化简访问瓶颈关系模式和关系实例关系的描述称作关系模式，包括关系名、关系中的属性名、属性向域的映象、属性间的数据依赖关系等关系模式可以形式化地表示为：R（U，D，dom，F） R

关系名

U

组成该关系的属性名集合

D

属性组U中属性所来自的域

dom属性向域的映象集合，直接说明为属性的类型、长度等

F

属性间的数据依赖关系集合某一时刻对应某个关系模式的内容(元组的集合)称作关系实例，简称为关系关系模式是型，是稳定的；关系是某时刻的值，是不断变化的，是一种快照，如同我们夜间仰望星空 ！元组的语义表示实体、实体之间的联系关系模式的码按组成简单码组合码全码（所有属性都是码的组成）按使用情况主码（在用）候选码（够条件，备用）按包含关系码超码（最小的超码为候选码）按关联关系主码外码关系模式的完整性三种完整性实体完整性唯一，不能重复；不能为空，若属性A是关系R的主属性，则A不能取空值。参照完整性体现实体之间的联系用户自己定义的完整性（约束机制）如属性取值，或其它业务规则，如何实现？系统的支持性实体完整性和参照完整性由系统自动支持系统应提供定义和检验用户定义的完整性的机制实现机制：模式定义，约束（取值范围），触发器（业务规则）等数据库模型设计从概念模模型(E-R图)开始基于E-R联系实体强实体与弱实体（如还款项）联系按参与联系的实体数目分类二元联系与多元联系按基数分类（针对二元联系）1:1，1:m(m:1)，m:n工具Powerdesigner，ERWIN等数据库模型设计矩形表示强实体集菱形表示联系集直线将属性和实体集/联系集、实体集和联系集联系在一起椭圆表示属性，三种特殊属性：双椭圆表示多值属性虚线椭圆表示派生属性复合属性：属性上的属性：姓名=姓+名；地址=区域+街道+号码下划线表示主码属性数据库模型设计强实体集：有主码的实体集弱实体集：单独不能存在或意义不完整的实体没有足够的属性以形成主码；如，payment的payment_number（还款序列号，部分码）针对贷款，不同贷款payment_number可以相同

弱实体集，与另一个称为标识实体集（属主实体集）的实体集关联才有意义弱实体集与其标识实体集相关联的联系成为标识性联系标识性联系是从弱实体集到标识实体集的多对一联系，并且弱实体集全部参与联系弱实体集的分辨符(或称部分码)是用于区分某个弱实体集的所有实体的属性集合（如还款序列号）弱实体集的主码由标识实体集的主码加上弱实体集的分辨符构成数据库模型设计弱实体集用双矩形表示，标识性联系使用双菱形表示使用虚下划线标识弱实体集的分辨符（部分码）payment_number

：payment实体集的分辨符payment主码：(loan_number,payment_number)数据库模型设计联系可以有属性分类二元联系多元联系在实体集和联系集之间，用箭头或线段表示基数约束：有向直线()指向“一”方，无向直线(—)表示“多”。基数约束三种：1:n,n:1,m:n数据库模型设计实体集在联系集中的参与完全参与(用双线表达)：实体集中的每个实体参与联系集中的至少一个联系如，borrower中的loan

是完全参与每项loan

通过borrower必须有一个客户与之相关联角色实体在联系中的作用称为实体的角色同一个实体集在一个联系集中参与的次数大于1时，则每次参与具有不同的角色（自环联系集）在E-R图中，通过对连接菱形和矩形的直线做标签来显示角色角色标签是可选的，用于澄清联系的语义E-R模型转化为关系模式参考论文：ER模型转换为关系模式的实用规则（潘文林）E-R图所表达的数据库，可用关系模式集合表示，一般而言(要看具体情况)：对于每个实体集和联系集，都有唯一的关系模式与之对应，关系模式名即为相应的实体集和联系集的名称每一关系模式有多个属性，属性有其唯一名称E-R模型转化为关系模式属性转换规则一般属性，照搬派生属性，要去掉为复合属性的每个子属性创建单独的属性。实体集E的多值属性M

使用单独的模式EM表示模式EM

含与E的主码相应的属性，以及与多值属性M相应的属性将多值属性的每一个值映射为模式EM的一个元组此处多值属性不同于后面的多值依赖！E-R模型转化为关系模式实体转换规则强实体集转换为具有同样属性的关系模式弱实体集转换为包含自身属性、它所依赖的强实体集的主码属性主外码约束：外码约束保证表示弱实体的每个元组都有一个表示相应强实体的元组与之对应。

E-R模型转化为关系模式联系转换规则冗余模式：连接弱实体集和相应强实体集的联系集是多对一的，且没有属性；弱实体集的主码包括了强实体集的主码。其对应模式是冗余的,不用转化。联系集（m:n）转换为关系模式时，包含自身属性、各个参与实体集的主码属性；码为各实体码的组合。模式的合并，1:n,1:1联系集对应的关系模式可与实体集对应的关系模式合并。合并后模式的主码是其模式中融入了联系集模式的那个实体集的主码。多对一：将完全参与联系的实体集与联系集合并。一般是将该联系与n端实体所对应的关系模式合并。合并时需要在n端实体的关系模式的属性中加入1端实体的码和联系本身的属性。如果“多”端是部分参与，会引起空值一对一：将联系与任意一端实体所对应的关系模式合并，需要在该关系模式的属性中加入另一个实体的码和联系本身的属性。具有相同码的关系模式要合并数据库设计的规范化（Normalization）ThemaingoalofDatabaseNormalizationistorestructure(重构)thelogicaldatamodelofadatabaseto:Eliminateredundancy:Toavoidredundancybystoringeach‘fact’withinthedatabaseonlyonce.Organizedataefficiently:Toputdataintoaformthatconformstorelationalprinciples(e.g.,singlevaluedattributes,eachrelationrepresentsoneentity)-norepeatinggroups.Toputthedataintoaformthatismoreabletoaccuratelyaccommodatechange.Reducethepotentialfordataanomalies:Toavoidcertainupdating‘anomalies’.Tofacilitatetheenforcementofdataconstraints.规范化的原则与目的规范化遵从概念单一化即“一事一地”的原则：一个关系只描述一个实体或者实体间的联系。若多于一个实体，就把它“分离”出来。规范化实质上是概念的单一化一个关系表示一个“实体”。规范化的目的就是使结构合理，消除存储异常，使数据冗余尽量小，便于插入、删除和更新。数据库设计的规范化（Normalization）Redundantdataiswherewehavestoredthesame‘information’morethanonce.i.e.,theredundantdatacouldberemovedwithoutthelossofinformation.Dataanomaliesareinconsistenciesinthedatastoredinadatabaseasaresultofanoperationsuchasupdate,insertion,and/ordeletion（三种数据异常）Suchinconsistenciesmayarisewhenhaveaparticularrecordstoredinmultiplelocationsandnotallofthecopiesareupdated.Wecanpreventsuchanomaliesbyimplementing7differentlevelofnormalizationcalledNormalForms(NF)范式Normalization-RelationalModelInordertocomplywiththerelationalmodelitisnecessaryto1)removerepeatinggroups

2)avoidredundancyanddataanomaliesbyremoving：partialfunctionaldependencies.transitivefunctionaldependencies.多值依赖，投影依赖等RelationalDatabaseDesign:

Allattributesinatablemustbeatomic,andsolelydependantuponthefullyprimarykeyofthattable.THEKEY,THEWHOLEKEY,ANDNOTHINGBUTTHEKEY!（码，完整的码）这是规范化的标准UnnormalizedNormalForm(UNF)Definition:Arelationisun-normalizedwhenithasnothadanynormalisationrulesappliedtoit,anditsuffersfromvariousanomalies.Thisonlytendstooccurwheretherelationhasbeendesignedusinga‘bottom-upapproach’.

i.e.,thecapturingofattributestoa‘UniversalRelation’fromascreenlayout,manualreport,manualdocument,etc...FirstNormalForm(1NF)Definition:Arelationisin1NFif,andonlyif,allitsunderlyingattributescontainatomicvaluesonly.RemoverepeatinggroupsintoanewrelationArepeatinggroupisshownbyapairofbracketswithintherelationalschema.ORDER(order-no,order-date,cust-no,cust-name,cust-add,(prod-no,prod-desc,unit-price,ord-qty,line-total)*,order-total))StepsfromUNFto1NF:Removetherepeatinggroup(andanynestedrepeatedgroupsitmaycontain)andcreateanewrelationtocontainit.AddtothisrelationacopyofthePKoftherelationimmediatelyenclosingit.Namethenewentity(appendingthenumber1toindicate1NF)DeterminethePKofthenewentityRepeatstepsuntilnomorerepeatinggroups.1st

NormalForm（TheRequirements）Therequirementstosatisfythe1stNF:Eachtablehasaprimarykey(主码):minimalsetofattributeswhichcanuniquelyidentifyarecordThevaluesineachcolumnofatableareatomic(Nomulti-valueattributesallowed).Therearenorepeatinggroups:twocolumnsdonotstoresimilarinformationinthesametable.1.把组合属性分成单独属性2.把此单独属性变成另外的列！repeatinggroupsArepeatinggroupisanattribute(orsetofattributes)thatcanhavemorethanonevalueforaprimarykeyvalue.Example:Wehavethefollowingrelationthatcontainsstaffanddepartmentdetailsandalistoftelephonecontactnumbersforeachmemberofstaff.staffNo

jobdeptdnamecity contactnumberSL10Salesman10SalesStratford018111777,018111888,079311122

SA51Manager20AccountsBarking017111777DS40Clerk20AccountsBarkingOS45Clerk30OperationsBarking079311555RepeatingGroupsarenotallowedinarelationaldesign,sinceallattributeshavetobe‘atomic’-i.e.,therecanonlybeonevaluepercellinatable!FunctionalDependency（函数依赖）FunctionalDependencyFormalDefinition:

AttributeBisfunctionallydependantuponattributeA(oracollectionofattributes)ifavalueofAdeterminesasinglevalueofattributeBatanyonetime（即不是偶然的）.FormalNotation:

ABThisshouldbereadas‘AdeterminesB’or‘BisfunctionallydependantonA’.AiscalledthedeterminantandBiscalledtheobjectofthedeterminant.staffNojobdeptdname

SL10Salesman10SalesSA51Manager20AccountsDS40Clerk20AccountsOS45Clerk30OperationsExample:staffNo

jobstaffNodeptstaffNo

dnamedeptdname

FunctionalDependenciesFunctionalDependencyCompoundDeterminants:Ifmorethanoneattributeisnecessarytodetermineanotherattributeinanentity,thensuchadeterminantistermedacompositedeterminant.FullFunctionalDependency:Onlyofrelevancewithcompositedeterminants.Thisisthesituationwhenitisnecessarytousealltheattributesofthecompositedeterminanttoidentifyitsobjectuniquely.order#line#qtyprice

A00100110200A00200120400A00200220800A00400115300Example:(Order#,line#)qty(Order#,line#)price

FullFunctionalDependenciesFunctionalDependencyPartialFunctionalDependency:Thisisthesituationthatexistsifitisnecessarytoonlyuseasubsetoftheattributesofthecompositedeterminanttoidentifyitsobjectuniquely.student#unit#roomgrade9900100A01TH22429900010A01TH224149901011A02JS07539900001A01TH22416Example:(student#,unit#)grade

FullFunctionalDependenciesunit#room

PartialFunctionalDependenciesRepetitionofdata!函数依赖的相关定义设R(U)是一个属性集U上的关系模式，X和Y是U的子集。数据依赖：通过一个关系中属性间的相等与否体现出来的数据间的相互关系，其中最重要的是函数依赖和多值依赖。函数依赖：设X,Y是关系R的两个属性集合，当任何时刻R中的任意两个元组中的X属性值相同时，则其Y属性值也相同，则称X函数决定Y或Y函数依赖于X。

平凡函数依赖：当关系中属性集合Y是属性集合X的子集时，存在函数依赖X→Y，即一组属性函数决定它的所有子集。非平凡函数依赖：当关系中属性集合Y不是属性集合X的子集时，存在函数依赖X→Y。完全函数依赖：设X,Y是关系R的两个属性集合，X’是X的真子集，存在X→Y，但对每一个X’都有X’!→Y，则称Y完全函数依赖于X。部分函数依赖：设X,Y是关系R的两个属性集合，存在X→Y，若X’是X的真子集，存在X’→Y，则称Y部分函数依赖于X。传递函数依赖：设X,Y,Z是关系R中互不相同的属性集合，存在X→Y(Y!→X),Y→Z，则称Z传递函数依赖于X（经Y）。2nd

NormalFormSecondnormalform(2NF)Ifatablehasacompositekey,allattributesmustberelatedtothewholekey（即要消除部分函数依赖）:Thedatabasemustmeetalltherequirementsofthefirstnormalform.Datawhichisredundantlyduplicatedacrossmultiplerowsofatableismovedouttoaseparatetable.TransitiveDependencyand3rdNFDefinition:Atransitivedependencyexistswhenthereisanintermediatefunctionaldependency.FormalNotation:IfAB

and

BC,thenitcanbestatedthatthefollowingtransitivedependencyexists:ABCstaffNojobdeptdname

SL10Salesman10SalesSA51Manager20AccountsDS40Clerk20AccountsOS45Clerk30OperationsExample:staffNodeptdeptdnamestaffNo

dept

dname

TransitiveDependenciesRepetitionofdata!TransitiveDependencyand3rdNFDefinition:Arelationisin3NFif,andonlyif,itisin2NFandeverynon-keyattributeisnon-transitivelydependentontheprimarykey.机制：Removetransitivedependenciesintoanewrelation步骤Stepsfrom2NFto3NF:Removetheoffendingattributesthataretransitivelydependentonnon-keyattribute(s),andplacetheminanewrelation.Addtothisrelationacopyoftheattribute(s)whicharethedeterminantsoftheseoffendingattributes.Thesewillautomaticallybecometheprimarykeyofthisnewrelation.Namethenewentity(appendingthenumber3toindicate3NF)Renametheoriginalentity(endingwitha3toindicate3NF)Thirdnormalform(3NF)requiresthatdatastoredinatablebedependentonlyontheprimarykey,andnotonanyotherfieldinthetable.Thedatabasemustmeetalltherequirementsofthesecondnormalform.机制Anyfieldwhichisdependentnotonlyontheprimarykeybutalsoonanotherfieldismovedouttoaseparatetable.Boyce-CoddNormalFormBoyce-Coddnormalform(orBCNF)requiresthattherebenonon-trivialfunctionaldependenciesofattributesonsomethingotherthanasupersetofacandidatekey(calledasuperkey).多值依赖与4NF

多值依赖与4NF

连接依赖与5NFFifthnormalform(5NFandalsoPJ/NF)requiresthattherearenonon-trivialjoindependenciesthatdonotfollowfromthekeyconstraints.Atableissaidtobeinthe5NFifandonlyifitisin4NFandeveryjoindependencyinitisimpliedbythecandidatekeys.规范化示意图

规范化就是对原关系进行投影，消除决定属性不是候选键的任何函数依赖。分以下几步完成：对1NF关系进行投影，消除原关系中非主属性对键的部分函数依赖，将1NF关系转换成若干个2N