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

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

關(guān)系名

U

組成該關(guān)系的屬性名集合

D

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

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

F

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

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

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

是完全參與每項(xiàng)loan

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

使用單獨(dú)的模式EM表示模式EM

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

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

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

Allattributesinatablemustbeatomic,andsolelydependantuponthefullyprimarykeyofthattable.THEKEY,THEWHOLEKEY,ANDNOTHINGBUTTHEKEY!（碼，完整的碼）這是規(guī)范化的標(biāo)準(zhǔn)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.把組合屬性分成單獨(dú)屬性2.把此單獨(dú)屬性變成另外的列！repeatinggroupsArepeatinggroupisanattribute(orsetofattributes)thatcanhavemorethanonevalueforaprimarykeyvalue.Example:Wehavethefollowingrelationthatcontainsstaffanddepartmentdetailsandalistoftelephonecontactnumbersforeachmemberofstaff.staffNo

jobdeptdnamecity contactnumberSL10Salesman10SalesStratford018111777,018111888,079311122

SA51Manager20AccountsBarking017111777DS40Clerk20AccountsBarkingOS45Clerk30OperationsBarking079311555RepeatingGroupsarenotallowedinarelationaldesign,sinceallattributeshavetobe‘a(chǎn)tomic’-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ù)依賴的相關(guān)定義設(shè)R(U)是一個屬性集U上的關(guān)系模式，X和Y是U的子集。數(shù)據(jù)依賴：通過一個關(guān)系中屬性間的相等與否體現(xiàn)出來的數(shù)據(jù)間的相互關(guān)系，其中最重要的是函數(shù)依賴和多值依賴。函數(shù)依賴：設(shè)X,Y是關(guān)系R的兩個屬性集合，當(dāng)任何時刻R中的任意兩個元組中的X屬性值相同時，則其Y屬性值也相同，則稱X函數(shù)決定Y或Y函數(shù)依賴于X。

平凡函數(shù)依賴：當(dāng)關(guān)系中屬性集合Y是屬性集合X的子集時，存在函數(shù)依賴X→Y，即一組屬性函數(shù)決定它的所有子集。非平凡函數(shù)依賴：當(dāng)關(guān)系中屬性集合Y不是屬性集合X的子集時，存在函數(shù)依賴X→Y。完全函數(shù)依賴：設(shè)X,Y是關(guān)系R的兩個屬性集合，X’是X的真子集，存在X→Y，但對每一個X’都有X’!→Y，則稱Y完全函數(shù)依賴于X。部分函數(shù)依賴：設(shè)X,Y是關(guān)系R的兩個屬性集合，存在X→Y，若X’是X的真子集，存在X’→Y，則稱Y部分函數(shù)依賴于X。傳遞函數(shù)依賴：設(shè)X,Y,Z是關(guān)系R中互不相同的屬性集合，存在X→Y(Y!→X),Y→Z，則稱Z傳遞函數(shù)依賴于X（經(jīng)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.機(jī)制：Removetransitivedependenciesintoanewrelation步驟Stepsfrom2NFto3NF:Removetheoffendingattributesthataretransitivelydependentonnon-keyattribute(s),andplacetheminanewrelation.Addtothisrelationacopyoftheattribute(s)whicharethedeterminantsoftheseoffendingattributes.Thesewillautomaticallybecometheprimarykeyofthisnewrelation.Namethenewentity(appendingthenumber3toindicate3NF)Renametheoriginalentity(endingwitha3toindicate3NF)Thirdnormalform(3NF)requiresthatdatastoredinatablebedependentonlyontheprimarykey,andnotonanyotherfieldinthetable.Thedatabasemustmeetalltherequirementsofthesecondnormalform.機(jī)制Anyfieldwhichisdependentnotonlyontheprimarykeybutalsoonanotherfieldismovedouttoaseparatetable.Boyce-CoddNormalFormBoyce-Coddnormalform(orBCNF)requiresthattherebenonon-trivialfunctionaldependenciesofattributesonsomethingotherthanasupersetofacandidatekey(calledasuperkey).多值依賴與4NF

多值依賴與4NF

連接依賴與5NFFifthnormalform(5NFandalsoPJ/NF)requiresthattherearenonon-trivialjoindependenciesthatdonotfollowfromthekeyconstraints.Atableissaidtobeinthe5NFifandonlyifitisin4NFandeveryjoindependencyinitisimpliedbythecandidatekeys.規(guī)范化示意圖

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