【文档说明】数据库原理-英文课件Chapter3--The-Relational-Data-Model.ppt，共(52)页，671.547 KB，由小橙橙上传

转载请保留链接：https://www.ichengzhen.cn/view-92517.html

以下为本文档部分文字说明：

1DatabasePrinciples2Chapter3TheRelationalDataModelRelationalModelFunctionalDependencies3Contents3.1BasicsoftheRelationalModel3.3FromE/Rdi

agramstoRelationalDesigns3.5FunctionalDependencies3.7DesignofRelationalDatabaseSchemasReadingGuideExercise4Thethi

ngsyoushouldknow…ThebasicprincipleofrelationaldatabaseisproposedbyE.F.Coddin1970.ThefirstRDBMSproductionisSystemRThemostpopul

arRDBMS•DB2,Oracle,Ingres,Sybase,Informix,…53.1BasicoftheRelationalModelTherelationalmodelgivesusasinglewaytorepresentdata:asatwo-dimensionaltabl

ecalledarelation.6Attributes(属性)Attributesofarelationserveusasnamesforthecolumnsoftherelation.Usually,theattributesdescribesthemeaningofen

triesinthecolumnbelow.7Schemas（模式）Thenameofarelationandthesetofattributesforarelationiscalledtheschemaforthatrelation.Weshowth

eschemafortherelationwiththerelationnamefollowedbyaparenthesizedlistofitsattributes.8Tuples（元组）Therowsofarelation,otherthantheheaderrowcontaini

ngtheattributes,arecalledtuple.Forexample:Relations,however,aresetsoftuples,anditisimpossibleforatupletoappearmorethanoncei

nagivenrelation.9Domains（域）Therelationalmodelrequiresthateachcomponentofeachtuplebeatomic;thatis,itmustb

eofsomeelementarytypesuchasintegersorstring.Eachattributesofarelationhasparticularelementarytype,thusdomainisdecided.10EquivalentRepresenta

tionsofaRelationTheattributesoftherelationcanbereorderedwithoutchangetherelation.11RelationInstance（关系实例）Arelati

onaboutmoviesisnotstatic;rather,relationschangeovertime.Itisnotcommonfortheschemaofarelationtochange.Weshallcallasetoftuplesforagi

venrelationaninstanceofthatrelation.12AnExampleofRelationInstanceRelation:Person(Name,Address,Telepho

ne)RelationInstance:NameAddressTelephoneBob123MainSt555-1234Bob128MainSt555-1235Pat123MainSt555-1235Harry456MainSt

555-2221Sally456MainSt555-2221Sally456MainSt555-2223Pat12StateSt555-123513More…Relation(Instance)=asetoft

uplesDatabase=collectionofrelationsRelationschema=relationname+attributesExample:Movies(title,year,length,fi

leType)Databaseschema=asetofallrelationschemasMovies(Title,Year,Length,FileType)Star(Name,Age)Studio(Stud

ioName,Addr)14NameAddrTelN1A1T1N2A2T2N3A3T3N4T4N5T5T6T7NameAddrTelN1A1T1N1A1T2N1A1T3...N1A1T7N1A2T1N1A3T1N

2A1T1TupleDomainComponentAttribute15IntegrityConstrainofRelationsEntityConstrainTheattributesbelongtokeycannotbesetasNULL.ReferenceCons

trainForeignKey:annon-keyattributeAinRisakeyinS,thentheAiscalledaforeignkeyofR.ThevalueofforeignkeycanonlybeNULLorsameaswhatisinS.User-d

efineConstrainUsersdefinetheconstrainsthemselves.16补充：关系的完整性实体完整性参照完整性用户定义完整性实体完整性和参照完整性是关系模型必须满足的，被称作关系的不变性，由关系数据库系统自动支持★17实体完整性规则：

若属性A是基本关系R的主属性，则属性A不能取空值说明：基本关系的主码中的任何属性都不能取空值，而不仅是主码整体不能取空值依据：现实世界的实体是唯一可分的例：学生（学号，姓名，性别，专业号，年龄）课程（课程号，课程名，学分）选

修（学号，课程号，成绩）18例１：学生实体与专业实体间的关系：学生（学号，姓名，性别，专业号，年龄）专业（专业号，专业名）关系参照图外码参照关系被参照关系例２：学生，课程，学生与课程之间的多对多联系：学生（学号，姓名，性别，专业号，年龄）课程（课程号

，课程名，学分）选修（学号，课程号，成绩）关系参照图被参照关系参照关系学生关系专业关系专业号学生关系选修关系课程关系学号课程号参照完整性主码？外码？19参照完整性定义：外码设F是参照关系R的一个或一组属性，但不是R的码，若F与被参照关系S的主码相对应，则称F是R的外码（详细定义见教材P54

）规则：参照关系R中每个元组在外码F上的值必须为：•或者取空值（F的每个属性值均为空值）•或者等于S中某个元组的主码值例３：学生（学号，姓名，性别，专业号，年龄，班长）参照关系被参照关系外码20用户定义完

整性用户定义的、具体应用中的数据必须满足的约束条件成绩：0－100之间身份证、身份证和生日对应关系213.2FromE/RDiagramstoRelationFromEntitySetstoRelationSimplestapproach(notalwaysbest):

converteachE.S.toarelation.Createarelationofthesamenameandwiththesamesetofattributes.22Movies(title,year,length,filmType)Stars(name,address)Stu

dios(name,address)Example23FromE/RRelationshipstoRelationRelationshipsintheE/Rmodelarealsorepresen

tedbyrelations.TherelationforagivenrelationshipRhasthefollowingattributes;1.ForeachentitysetinvolvedinrelationshipR,wetakeitsk

eyattributesaspartoftheschemaoftherelationforR.2.Iftherelationshiphasattributes,thenthesearealsoattributesofrelationR.24Example:Owns(title,year,s

tudioname)Stars-in(title,year,starName)25E-R图向关系模型的转换原则一个实体转换为一个关系模式，实体的属性就是关系的属性，实体的码就是关系的码对实体间的联系一个1:1联系可以转换为一个独立的关系模式，也可以与任意对

应的关系模式合并一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并一个m:n联系转换为一个关系模式三个或三个以上实体间的一个多元联系可以转换为一个关系模式具有相同码的关系模式可以合并补充：26如公司部

门管理系统的E-R图及其转换成的关系模式部门项目职工电话包括承担n1n1办公室号，面积项目号，预算费参与mn办公室包含1n包括n1部门号，预算费，领导人职工号电话号码，说明职工号，姓名，办公电话分担任务部门(部门号，部门预算费，领导人职工号)职工(职工号，姓名，办公电话，部门号)办公

室(办公室号，面积，部门号)项目(项目号，项目预算费，部门号)电话(电话号码，说明，办公室号)项目承担情况(职工号，项目号，分担任务)27ExerciseRequired:ConverttheE-Rdiagramyoudesignedinlastexerciseintoare

lationaldatabaseschema.283.5FunctionalDependenciesDefinitionofFunctionalDependency(函数依赖)X->AisanassertionaboutarelationRthatw

henevertwotuplesofRagreeonalltheattributesofX,thentheymustalsoagreeontheattributeA.Say“X->AholdsinR.”Conven

tion•X,Y,Zrepresentsetsofattributes;•A,B,C,…representsingleattributes.29ExampleMovies(title,year,length,filmT

ype,studioName,starname)Wecanassertthethreedependencies:1.title,year->length2.title,year->filmType3.title,year->stu

dioName30FD’sWithMultipleAttributesNoneedforFD’swithmultipleattributeonright.•Butsometimesforconvenientwecancombinethem.•Example:title,year->length

title,year->filmTypetitle,year->starNamebecometitle,year->length,filmType,starNameMultipleattributeonleftmaybeessential

.•Example:title,year->length31TrivialDependenciesAfunctionaldependencyA1A2…An->Bissaidtobetrivial(平凡)ifBisoneoftheA’s

,otherwiseissaidtobenontrivial(非平凡).Example:SupposeFunctionalDependenciestitle,year->titleisatrivialdependency.32Transitive(传递)FunctionalDepende

nciesSupposewehavearelationRwiththreeattributesA,B,andC,theFDsA->BandB->CbothholdforR.ThenitiseasytoseethattheFDA->calsoholdsforR,SoCissa

idtodependonAtransitively,viaB33KeysofRelationsfromFD’sviewWesayasetofoneormoreattributes{A1,A2,…,An}isakeyf

orrelationif:1.Thoseattributesfunctionallydetermineallotherattributesoftherelation.Thatis,itisimpossiblefortwodistincttuplesofRtoagreeonall

ofA1,A2,…,An.2.Nopropersubsetof{A1,A2,…,An}functionallydeterminesallotherattributesofR;i.e.,akeymustbeminimal34ExampleAttributes{title,year,starNa

me}formakeyfortheMovierelationofFig.above.Sometimesarelationhasmorethanonekey.Ifso,itiscommontodesignoneofthekeysastheprimarykey.

35SuperKeysSuperkeysatisfiesthefirstconditionofakey;However,asuperkeyneednotsatisfythesecondcondition;Example•At

tributeset{title,year,starName}formakeyfortheMovie.•Anysupersetofthisattributeset,suchas{title,year,starName,length}isasuperkey.36Rulesfordiscov

eringKeysofRelationFirstrule:Iftherelationcomesfromanentitysetthenthekeyfortherelationisthekeyattributesofthi

sentityset.Secondrule:IfarelationRiscomesfromarelationship,thenthemultiplicityoftherelationshipaffectsthekeyforR.Therearethreecases

:•Iftherelationshipismany-many,thenthekeysofbothconnectedentitysetsarethekeyattributesforR.•Iftherelationshipismany-one

fromentitysetE1toentitysetE2,thenthekeyattributesofE1arekeyattributesofR,butthoseofE2arenot.•Ifthere

lationshipisone-one,thenthekeyattributesforeitheroftheconnectedentitysetsarekeyattributesofR.Thus,thereisnotauniquekeyforR.37Example•Owns:Itisamany-

onerelationbetweenMoviestoStudios,Thus,thekeyfortherelationOwnsisthekeyattributestitleandyear,whichcomefromthekeyforMovies.Owns(ti

tle,year,studioName)•Star-in:Itisamany-manyrelationshipbetweenMoviesandStars.Soallattributesoftheresultingrelat

ionarekeyattributesStars-in(title,year,starName)383.7DesignofRelationalDatabaseSchemaAnomalies(异常)

Problemoccurwhenwetrytocramtoomuchintoasinglerelationarecalledanomalies.•Redundancy:Informationmayberepeatedunnecessarilyinseveraltuples.•InsertionA

nomalies:Tupleinsertionmaybefailedduetolacksomeotherinformationinthecurrentdatabase.•DeletionAnomalies:Ifas

etofvaluesbecomesempty,wemayloseotherinformationasasideeffect.•UpdateAnomalies:Wemaychangeinformationinonetuplebu

tleavethesameinformationunchangedinanother.39Example:Goalofrelationalschemadesignistoavoidanomaliesandredundancy.40NormalFormArelationschem

aissaidtobeinparticularnormalformifitsatisfiesacertainprescribedsetofconditions.•1NF,2NF,3NF,BCNFNormalizationProcedureThesuccessiveredu

ctionofagivencollectionofrelationschematosomemoredesirableform.NFNFNFBCNF123411NFArelationRisin

1NFifandonlyif,everytuplecontainsexactlyonevalueforeachattributes.•RelationsinRelationaldatabasealwaysin1NF.•But,arelationschemaonlyin1

NFisalsoalwaysundesirableforanumberofrelations.422NFArelationRisin2NFifandonlyif:itisin1NFandeverynon-keyattributesisfullfunctionaldependencyonthep

rimarykey.FullFunctionalDependency•InrelationR,ifX->Y,andanysubsetofX,X’-\>Y,sayYfullfunctionaldependencytoX,thatX-F>Y.•OtherwiseX-P>Y,Yisp

artlyfunctiondependencytoX.Example:IfinrelationR(A,B,C),existingfunctionaldependencies(A,B)->C,A-\>C,B-\>C,so(A,B)-F>CandRisin2N

F433NFArelationRisin3NFif:itisin2NFandthereisnotransitivefunctionaldependencyexisted.Example:Ifinrelati

onR(A,B,C),existingfunctionaldependenciesA->BandB->C,thenRisnot3NF.44BCNFWesayarelationRisinBCNFif:wheneverX->AisanontrivialFDandXis

asuperkey.•NontrivialmeansAisnotamemberofsetX.•Superkeyisanysupersetofakey(notnecessarilyapropersuperset).45Example1Movies(title,year,lengt

h,filmType,studioName,starName)FD:title,year->length,filmType,studioNameTheonlykeyis{title,year,starName}IneachFD,theleftsideisnotasuperkey.TheseFD

’sshowsMoviesisnotinBCNF.46Example2Movies(title,year,length,filmType,studioName)FD’s:title,year->length,filmType,studioNameTheonlykeyis{title,yea

r}.IneachFD,theleftsideisasuperkey.TheseFD’sshowsMoviesisinBCNF47Decomposition(分解)intoBCNFThedecompositionstrategyistolookforanontr

ivialdependencyA1,A2,…,An->B1,B2,…,BmthatviolatesBCNF;i.e.,{A1,A2,…,An}isnotasuperkey,asaheuristic,weshallgenerallyaddtot

herightsideasmanyattributesasarefunctionallydeterminedby{A1,A2,…,An}.48Example1Thekeyis(title,year,satrName),butthefollowingFDexist:{title,year->len

gth,filmType,studioName}Thus,therelationisaBCNFviolation.Weshalldecomposetherelationintofollowingtwo:R1{title,year,length,filmType,studioName}

R2{title,year,starName}49Example2WesupposearelationS-L-C(Sno,Sdept,Sloc,Cno,G)Onlykeyofthisrelationis(Sno,Cno)FD’s:(Sno,Cno)-F>G,Sno->Sdept

,(Sno,Cno)-p>SdeptSno->Sloc,(Sno,Cno)-p>Sloc,Sdept->SlocRelationS-L-Cisonlyin1NF.PleasedecomposeitintoBCNF.50Step1:Decomposeitinto2

NFSC(Sno,Cno,G)S-L(Sno,Sdept,Sloc)Step2:Decomposeitinto3NFNow,SCisin3NF,butS-Lonlyin2NF.SoweneedtoDecomposeS-Linto3NFSC(Sno,Cno,G)S-D(Sno

,Sdept)D-L(Sdept,Sloc)51Step3:DecomposeitintoBCNFNow,allofrelationsSC,S-D,D-LareinBCNF.ThefinalresultisSC(Sno,Cno,G)S-D(Sno,Sdept)D-L(Sdep

t,Sloc)52ReadingGuideAFirstCourseinDatabaseSystems:Required:3.1and3.3Recommended:3.5and3.7数据库系统概论推荐：第

五章，第六章