課程資源數(shù)據(jù)庫系統(tǒng)復(fù)習(xí)課件chreview

Chapter6

DatabaseDesign

(Review)2023/3/14DatabasePrinciples&Programming2ReviewofE-RModel(1)E-RModelEntityAttributeIdentifier,Descriptor,CompositeAttribute,Multi-ValuedAttributeRelationshipCard(E,R)=(x,y)single-valued/multi-valuedparticipationmandatory/optionalparticipation

One-to-One,Many-to-Many,Many-to-OneExamplesofE-Rdiagrams(1)ExamplesofE-Rdiagrams(2)2023/3/14DatabasePrinciples&Programming5ReviewofE-RModel(2)TransformE-RModeltoRelationsRule1:EntityRule2:Multi-valuedAttributeRule3:N-NRelationshipsRule4.N-1RelationshipsRule5:1-1Relationships:OptionalononesideRule6:1-1Relationships:Mandatoryonbothsides2023/3/14DatabasePrinciples&Programming6Rule3:N-NRelationshipsEmployees(eid,straddr,city,……)Projects(prid,proj_name,due_date)works_on(eid,prid,percent)EmployeesProjectsworks_onpercent(1,N)(0,N)2023/3/14DatabasePrinciples&Programming7Rule4.N-1RelationshipsInstructors(insid,lname,……)Course_sections(secid,insid,course,…)insidsecid2023/3/14DatabasePrinciples&Programming8Rule5:1-1Relationships:OptionalononesidePerson(ssn,name,birthdate)DrivingLicense(DLno,validdate,class,ssn)PersonDrivingLicensehavingDLno(0,1)(1,1)classssnnamebirthdatevaliddate2023/3/14DatabasePrinciples&Programming9Rule6:1-1Relationships:MandatoryonbothsidesStudent(ssn,name,scid,department,class)StudentStudentCardhavingscid(1,1)(1,1)classssnnamedepartment2023/3/14DatabasePrinciples&Programming10ReviewofE-RModel(3)CardinalityofAttributes(x,y)Employeeseidemp_addresshobbiesstaddresscitystatezipcode(1,1)(0,1)(1,N)(1,1)(1,1)(1,1)(1,1)2023/3/14DatabasePrinciples&Programming11ReviewofE-RModel(4)WeakEntities2023/3/14DatabasePrinciples&Programming12ReviewofE-RModel(5)GeneralizationHierarchies6.4CaseStudy6.4.1圖書借閱管理6.4.2籃球聯(lián)賽信息管理6.4.3聊天論壇管理6.4.4郵件信息管理2023/3/14DatabasePrinciples&Programming146.5Normalization(規(guī)范化):PreliminariesARunningExampleDef.6.5.1UpdateAnomalyDef.6.5.2DeleteAnomaly,InsertAnomalyNormalForm(NF，范式)FunctionalDependency(FD)A→B(AfunctionallydeterminesB,orBisfunctionallydependentonA)Foranyrowsr1andr2inT,ifr1(A)=r2(A)thenr1(B)=r2(B).2023/3/14DatabasePrinciples&Programming15A→BB→AX1X2X3X4AY1Y2Y3Y4B（一對一）B→AX1X2X3AY1Y2Y3Y4B（一對多）(none)X1X2X3AY1Y2Y3B（多對多）2023/3/14DatabasePrinciples&Programming166.6FunctionalDependenciesArmstrong’sAxiomsR1(自反規(guī)則)：IfYX，thenX→YR2(傳遞規(guī)則)：IfX→YandY→Z，thenX→ZR3(增廣規(guī)則)：IfX→Y,thenXZ→YZR4(合并規(guī)則)：IfX→YandX→Z，thenX→YZR5(分解規(guī)則)：IfX→YZ，thenX→YandX→ZR6(偽傳遞規(guī)則)：IfX→YandWY→Z，thenXW→ZR7(聚積規(guī)則)：IfX→YZandZ→W，thenX→YZW2023/3/14DatabasePrinciples&Programming17Contentoflast函數(shù)依賴ClosureofaSetofFDs(函數(shù)依賴集的閉包)FDSetCover(函數(shù)依賴集的覆蓋)EquivalenceoftwosetsofFDS(函數(shù)依賴集的等價)MinimalCover(最小覆蓋)ClosureofaSetofAttributes(屬性集的閉包)模式分解Losslessposition(無損性分解)FDPreserved(依賴保持)Superkey&KeyPrimeAttribute(主屬性)Non-PrimeAttribute(非主屬性)2023/3/14DatabasePrinciples&Programming18Contentoflast三個范式的定義BCNF,3NF,2NF四個算法Algorithm6.6.12:屬性集閉包的計算Algorithm6.6.13:最小覆蓋的計算AlgorithmtoFindCandidateKeyAlgorithm6.8.8:AnAlgorithmtoAchieveWell-Behaved3NFposition2023/3/14DatabasePrinciples&Programming196.7LosslesspositionsDef.6.7.1positionofTable tableT==>tables{T1,T2,...,Tk}Losslessposition(無損性分解)Lossless-Joinposition(無損聯(lián)接分解)2023/3/14DatabasePrinciples&Programming20ContentofnextFDPreserved(依賴保持)設(shè)關(guān)系T上的函數(shù)依賴集為F，分解后的子關(guān)系Ti上的函數(shù)依賴集為Fi，則：分解滿足依賴保持性的定義如下：2023/3/14DatabasePrinciples&Programming21ContentofnextSuperkeyorXisasuperkeyofT

iff

XHead(T)XisasuperkeyofT

iff

X+F=Head(T)PRIMEATTRIBUTE(主屬性)NON-PRIMEATTRIBUTE(非主屬性)AlgorithmtoFindCandidateKey2023/3/14DatabasePrinciples&Programming226.8NormalFormsDef.6.8.4BCNFT∈BCNFiffforanyXAinF+

{if(AisasingleattributenotinX)thenXmustbeasuperkeyforT;}2023/3/14DatabasePrinciples&Programming236.8NormalFormsDef.6.8.63NFT∈3NFiffforanyXAinF+

{if(Aisasinglenon-primeattributenotinX)thenXmustbeasuperkeyforT;}2023/3/14DatabasePrinciples&Programming246.8NormalFormsDef.6.8.62NFT∈2NFiffforanyXAinF+

{if(Aisasinglenon-primeattributenotinX)thenXisnotproperlycontainedinanykeyofT;}2023/3/14DatabasePrinciples&Programming25BCNF和3NF定義的對比BCNFforanyFDXAinF+thatliesinT(allattributesofXandAinT),ifAisasingleattributenotinX,thenXmustbeasuperkeyforT3NFforanyFDXAimpliedbyFthatliesinT,ifAisasinglenon-primeattributenotinX,thenXmustbeasuperkeyforT.2023/3/14DatabasePrinciples&Programming263NF和2NF定義的對比3NFforanyXAimpliedbyFthatliesinT,ifAisasinglenon-primeattributenotinX,thenXmustbeasuperkeyforT.2NFforanyXAimpliedbyFthatliesinT,ifAisasinglenon-primeattributenotinX,thenXisnotproperlycontainedinanykeyofT.2023/3/14DatabasePrinciples&Programming27algorithm6.6.12ClosureofasetofAttributesInput:Xisasetofattributes,FisasetofFDs

X+

:=

X;

repeat{oldX+:=X+;foreachYZinF{

ifYX+thenX+:=X+Z;}

}until(

oldX+

=

X+)

return

X+2023/3/14DatabasePrinciples&Programming28step1:FromthesetFofFDs,wecreateanequivalentsetHofFDs,withonlysingleattributesontherightside.step3:FromthesetHofFDs,successivelyreplaceindividualFDswithFDsthathaveasmallernumberofattributesontheleft-handside,aslongastheresultdoesnotchangeH+.step2:FromthesetHofFDs,successivelyremoveindividualFDsthatareinessentialinH.step4:FromtheremainingsetofFDs,gatherallFDswithequalleft-handsidesandusetheunionruletocreateanequivalentsetofFDsMwhereallleft-handsidesareunique.6.6.13：最小覆蓋計算2023/3/14DatabasePrinciples&Programming29AnAlgorithmtoFindCandidateKeyGivenatableTwithasetFofFDssetK:=Head(T);foreachattributeAinK

{compute(K-A)F+

;if(K-A)F+containsalltheattributesinTthen

{setK:=K

-{A};}}returnK;關(guān)鍵字的計算（優(yōu)化）設(shè)有關(guān)系模式R，Head(R)是關(guān)系R的屬性集合，F(xiàn)是關(guān)系R上的最小函數(shù)依賴集。根據(jù)F中的函數(shù)依賴，可以將屬性集合Head(R)劃分為以下的三個子集：只在函數(shù)依賴的左邊出現(xiàn)過的屬性的集合UL

（包括沒有出現(xiàn)在任何函數(shù)依賴中的屬性）只在函數(shù)依賴的右邊出現(xiàn)過的屬性的集合U