數(shù)據庫模式設計及其規(guī)范化

數(shù)據庫模式設計及其規(guī)范化楊德仁2011年11月15日西部CA數(shù)據庫設計的兩個途徑自底向上從報表開始報表是一個視圖或復雜的關系模式需要規(guī)范化如何規(guī)范化？自上而下（“詳細設計”階段）從類或對象開始聯(lián)系在何體現(xiàn)？E-R圖擴展的E-R圖（ISA關系）實體和聯(lián)系需要轉換轉化規(guī)則在何？大綱數(shù)據庫模式的定義數(shù)據庫模型設計（E-R圖）從E-R模型向關系模式轉換數(shù)據庫設計的規(guī)范化(bottom-up)數(shù)據冗余和數(shù)據異?；诖a和數(shù)據依賴的規(guī)范化數(shù)據依賴函數(shù)依賴多值依賴投影依賴，……規(guī)范化的結果：一系列小表查詢優(yōu)化：反規(guī)范化數(shù)據庫模式的定義數(shù)據庫數(shù)據庫由多個關系（表）構成，表達系統(tǒng)的信息。關系模式與關系實例碼（key，鍵）三種模型概念模型（E-R模型）邏輯模型（關系模式）物理模型（存儲結構）：索引，存儲，化簡訪問瓶頸關系模式和關系實例關系的描述稱作關系模式，包括關系名、關系中的屬性名、屬性向域的映象、屬性間的數(shù)據依賴關系等關系模式可以形式化地表示為：R（U，D，dom，F(xiàn)） R

關系名

U

組成該關系的屬性名集合

D

屬性組U中屬性所來自的域

dom屬性向域的映象集合，直接說明為屬性的類型、長度等

F

屬性間的數(shù)據依賴關系集合某一時刻對應某個關系模式的內容(元組的集合)稱作關系實例，簡稱為關系關系模式是型，是穩(wěn)定的；關系是某時刻的值，是不斷變化的，是一種快照，如同我們夜間仰望星空 ！元組的語義表示實體、實體之間的聯(lián)系關系模式的碼按組成簡單碼組合碼全碼（所有屬性都是碼的組成）按使用情況主碼（在用）候選碼（夠條件，備用）按包含關系碼超碼（最小的超碼為候選碼）按關聯(lián)關系主碼外碼關系模式的完整性三種完整性實體完整性唯一，不能重復；不能為空，若屬性A是關系R的主屬性，則A不能取空值。參照完整性體現(xiàn)實體之間的聯(lián)系用戶自己定義的完整性（約束機制）如屬性取值，或其它業(yè)務規(guī)則，如何實現(xiàn)？系統(tǒng)的支持性實體完整性和參照完整性由系統(tǒng)自動支持系統(tǒng)應提供定義和檢驗用戶定義的完整性的機制實現(xiàn)機制：模式定義，約束（取值范圍），觸發(fā)器（業(yè)務規(guī)則）等數(shù)據庫模型設計從概念模模型(E-R圖)開始基于E-R聯(lián)系實體強實體與弱實體（如還款項）聯(lián)系按參與聯(lián)系的實體數(shù)目分類二元聯(lián)系與多元聯(lián)系按基數(shù)分類（針對二元聯(lián)系）1:1，1:m(m:1)，m:n工具Powerdesigner，ERWIN等數(shù)據庫模型設計矩形表示強實體集菱形表示聯(lián)系集直線將屬性和實體集/聯(lián)系集、實體集和聯(lián)系集聯(lián)系在一起橢圓表示屬性，三種特殊屬性：雙橢圓表示多值屬性虛線橢圓表示派生屬性復合屬性：屬性上的屬性：姓名=姓+名；地址=區(qū)域+街道+號碼下劃線表示主碼屬性數(shù)據庫模型設計強實體集：有主碼的實體集弱實體集：單獨不能存在或意義不完整的實體沒有足夠的屬性以形成主碼；如，payment的payment_number（還款序列號，部分碼）針對貸款，不同貸款payment_number可以相同

弱實體集，與另一個稱為標識實體集（屬主實體集）的實體集關聯(lián)才有意義弱實體集與其標識實體集相關聯(lián)的聯(lián)系成為標識性聯(lián)系標識性聯(lián)系是從弱實體集到標識實體集的多對一聯(lián)系，并且弱實體集全部參與聯(lián)系弱實體集的分辨符(或稱部分碼)是用于區(qū)分某個弱實體集的所有實體的屬性集合（如還款序列號）弱實體集的主碼由標識實體集的主碼加上弱實體集的分辨符構成數(shù)據庫模型設計弱實體集用雙矩形表示，標識性聯(lián)系使用雙菱形表示使用虛下劃線標識弱實體集的分辨符（部分碼）payment_number

：payment實體集的分辨符payment主碼：(loan_number,payment_number)數(shù)據庫模型設計聯(lián)系可以有屬性分類二元聯(lián)系多元聯(lián)系在實體集和聯(lián)系集之間，用箭頭或線段表示基數(shù)約束：有向直線()指向“一”方，無向直線(—)表示“多”?；鶖?shù)約束三種：1:n,n:1,m:n數(shù)據庫模型設計實體集在聯(lián)系集中的參與完全參與(用雙線表達)：實體集中的每個實體參與聯(lián)系集中的至少一個聯(lián)系如，borrower中的loan

是完全參與每項loan

通過borrower必須有一個客戶與之相關聯(lián)角色實體在聯(lián)系中的作用稱為實體的角色同一個實體集在一個聯(lián)系集中參與的次數(shù)大于1時，則每次參與具有不同的角色（自環(huán)聯(lián)系集）在E-R圖中，通過對連接菱形和矩形的直線做標簽來顯示角色角色標簽是可選的，用于澄清聯(lián)系的語義E-R模型轉化為關系模式參考論文：ER模型轉換為關系模式的實用規(guī)則（潘文林）E-R圖所表達的數(shù)據庫，可用關系模式集合表示，一般而言(要看具體情況)：對于每個實體集和聯(lián)系集，都有唯一的關系模式與之對應，關系模式名即為相應的實體集和聯(lián)系集的名稱每一關系模式有多個屬性，屬性有其唯一名稱E-R模型轉化為關系模式屬性轉換規(guī)則一般屬性，照搬派生屬性，要去掉為復合屬性的每個子屬性創(chuàng)建單獨的屬性。實體集E的多值屬性M

使用單獨的模式EM表示模式EM

含與E的主碼相應的屬性，以及與多值屬性M相應的屬性將多值屬性的每一個值映射為模式EM的一個元組此處多值屬性不同于后面的多值依賴！E-R模型轉化為關系模式實體轉換規(guī)則強實體集轉換為具有同樣屬性的關系模式弱實體集轉換為包含自身屬性、它所依賴的強實體集的主碼屬性主外碼約束：外碼約束保證表示弱實體的每個元組都有一個表示相應強實體的元組與之對應。

E-R模型轉化為關系模式聯(lián)系轉換規(guī)則冗余模式：連接弱實體集和相應強實體集的聯(lián)系集是多對一的，且沒有屬性；弱實體集的主碼包括了強實體集的主碼。其對應模式是冗余的,不用轉化。聯(lián)系集（m:n）轉換為關系模式時，包含自身屬性、各個參與實體集的主碼屬性；碼為各實體碼的組合。模式的合并，1:n,1:1聯(lián)系集對應的關系模式可與實體集對應的關系模式合并。合并后模式的主碼是其模式中融入了聯(lián)系集模式的那個實體集的主碼。多對一：將完全參與聯(lián)系的實體集與聯(lián)系集合并。一般是將該聯(lián)系與n端實體所對應的關系模式合并。合并時需要在n端實體的關系模式的屬性中加入1端實體的碼和聯(lián)系本身的屬性。如果“多”端是部分參與，會引起空值一對一：將聯(lián)系與任意一端實體所對應的關系模式合并，需要在該關系模式的屬性中加入另一個實體的碼和聯(lián)系本身的屬性。具有相同碼的關系模式要合并數(shù)據庫設計的規(guī)范化（Normalization）ThemaingoalofDatabaseNormalizationistorestructure(重構)thelogicaldatamodelofadatabaseto:Eliminateredundancy:Toavoidredundancybystoringeach‘fact’withinthedatabaseonlyonce.Organizedataefficiently:Toputdataintoaformthatconformstorelationalprinciples(e.g.,singlevaluedattributes,eachrelationrepresentsoneentity)-norepeatinggroups.Toputthedataintoaformthatismoreabletoaccuratelyaccommodatechange.Reducethepotentialfordataanomalies:Toavoidcertainupdating‘anomalies’.Tofacilitatetheenforcementofdataconstraints.規(guī)范化的原則與目的規(guī)范化遵從概念單一化即“一事一地”的原則：一個關系只描述一個實體或者實體間的聯(lián)系。若多于一個實體，就把它“分離”出來。規(guī)范化實質上是概念的單一化一個關系表示一個“實體”。規(guī)范化的目的就是使結構合理，消除存儲異常，使數(shù)據冗余盡量小，便于插入、刪除和更新。數(shù)據庫設計的規(guī)范化（Normalization）Redundantdataiswherewehavestoredthesame‘information’morethanonce.i.e.,theredundantdatacouldberemovedwithoutthelossofinformation.Dataanomaliesareinconsistenciesinthedatastoredinadatabaseasaresultofanoperationsuchasupdate,insertion,and/ordeletion（三種數(shù)據異常）Suchinconsistenciesmayarisewhenhaveaparticularrecordstoredinmultiplelocationsandnotallofthecopiesareupdated.Wecanpreventsuchanomaliesbyimplementing7differentlevelofnormalizationcalledNormalForms(NF)范式Normalization-RelationalModelInordertocomplywiththerelationalmodelitisnecessaryto1)removerepeatinggroups

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

Allattributesinatablemustbeatomic,andsolelydependantuponthefullyprimarykeyofthattable.THEKEY,THEWHOLEKEY,ANDNOTHINGBUTTHEKEY!（碼，完整的碼）這是規(guī)范化的標準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（函數(shù)依賴）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!函數(shù)依賴的相關定義設R(U)是一個屬性集U上的關系模式，X和Y是U的子集。數(shù)據依賴：通過一個關系中屬性間的相等與否體現(xiàn)出來的數(shù)據間的相互關系，其中最重要的是函數(shù)依賴和多值依賴。函數(shù)依賴：設X,Y是關系R的兩個屬性集合，當任何時刻R中的任意兩個元組中的X屬性值相同時，則其Y屬性值也相同，則稱X函數(shù)決定Y或Y函數(shù)依賴于X。

平凡函數(shù)依賴：當關系中屬性集合Y是屬性集合X的子集時，存在函數(shù)依賴X→Y，即一組屬性函數(shù)決定它的所有子集。非平凡函數(shù)依賴：當關系中屬性集合Y不是屬性集合X的子集時，存在函數(shù)依賴X→Y。完全函數(shù)依賴：設X,Y是關系R的兩個屬性集合，X’是X的真子集，存在X→Y，但對每一個X’都有X’!→Y，則稱Y完全函數(shù)依賴于X。部分函數(shù)依賴：設X,Y是關系R的兩個屬性集合，存在X→Y，若X’是X的真子集，存在X’→Y，則稱Y部分函數(shù)依賴于X。傳遞函數(shù)依賴：設X,Y,Z是關系R中互不相同的屬性集合，存在X→Y(Y!→X),Y→Z，則稱Z傳遞函數(shù)依賴于X（經Y）。2nd

NormalFormSecondnormalform(2NF)Ifatablehasacompositekey,allattributesmustberelatedtothewholekey（即要消除部分函數(shù)依賴）: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.規(guī)范化示意圖

規(guī)范化就是對原關系進行投影，消除決定屬性不是候選鍵的任何函數(shù)依賴。分以下幾步完成：對1NF關系進行投影，消除原關系中非主屬性對鍵的部分函數(shù)依賴，將1NF關系轉換成若干個2N