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

数据库模式设计及其规范化杨德仁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-RelationalModelInordertocomplywiththerelationalmodelitisnecessaryto1)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...ORDERCustomerNo:001964OrderNumber:00012345Name:MarkCampbellOrderDate:14-Feb-2002Address:1TheHouseLeytonstoneE119ZZProductProductUnitOrderLineNumberDescriptionPriceQuantityTotalT5060Hook5.00525.00PT42Bolt2.501020.50QZE48Spanner20.00120.00OrderTotal:65.50FirstNormalForm(1NF)Definition:Arelationisin1NFif,andonlyif,allitsunderlyingattributescontainatomicvaluesonly.RemoverepeatinggroupsintoanewrelationArepeatinggroupisshownbyapairofbracketswithinthe