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

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

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

1DatabasePrinciples2Chapter3TheRelationalDataModelRelationalModelFunctionalDependencies3Contents3.1BasicsoftheRelationalModel3.3Fr

omE/RdiagramstoRelationalDesigns3.5FunctionalDependencies3.7DesignofRelationalDatabaseSchemasReadingGuideExercise4Thethingsyoushouldknow…

ThebasicprincipleofrelationaldatabaseisproposedbyE.F.Coddin1970.ThefirstRDBMSproductionisSystemRThemostpopu

larRDBMS•DB2,Oracle,Ingres,Sybase,Informix,…53.1BasicoftheRelationalModelTherelationalmodelgivesusasinglewaytorepres

entdata:asatwo-dimensionaltablecalledarelation.6Attributes(属性)Attributesofarelationserveusasnamesforthecol

umnsoftherelation.Usually,theattributesdescribesthemeaningofentriesinthecolumnbelow.7Schemas（模式）Thenameofarelationandthesetofattributesforare

lationiscalledtheschemaforthatrelation.Weshowtheschemafortherelationwiththerelationnamefollowedbyaparenthesizedlistofitsattrib

utes.8Tuples（元组）Therowsofarelation,otherthantheheaderrowcontainingtheattributes,arecalledtuple.Forexample:Relations,however,a

resetsoftuples,anditisimpossibleforatupletoappearmorethanonceinagivenrelation.9Domains（域）Therelationalmodelrequiresthateachcomponentofeacht

uplebeatomic;thatis,itmustbeofsomeelementarytypesuchasintegersorstring.Eachattributesofarelationhasparticularelementarytype,thusdomain

isdecided.10EquivalentRepresentationsofaRelationTheattributesoftherelationcanbereorderedwithoutchangetherelation.11RelationInstance（关系实例）Arel

ationaboutmoviesisnotstatic;rather,relationschangeovertime.Itisnotcommonfortheschemaofarelationtochange.Weshallcallasetoftuplesforagivenrelation

aninstanceofthatrelation.12AnExampleofRelationInstanceRelation:Person(Name,Address,Telephone)RelationInstance:

NameAddressTelephoneBob123MainSt555-1234Bob128MainSt555-1235Pat123MainSt555-1235Harry456MainSt555-2221Sally456Main

St555-2221Sally456MainSt555-2223Pat12StateSt555-123513More…Relation(Instance)=asetoftuplesDatabase=collectionofrelationsRelationschema=rel

ationname+attributesExample:Movies(title,year,length,fileType)Databaseschema=asetofallrelationschemasMovies(Title,Year,Length,FileType)Star(N

ame,Age)Studio(StudioName,Addr)14NameAddrTelN1A1T1N2A2T2N3A3T3N4T4N5T5T6T7NameAddrTelN1A1T1N1A1T2N1A1T3...N1

A1T7N1A2T1N1A3T1N2A1T1TupleDomainComponentAttribute15IntegrityConstrainofRelationsEntityConstrainTheattrib

utesbelongtokeycannotbesetasNULL.ReferenceConstrainForeignKey:annon-keyattributeAinRisakeyinS,thentheAiscalledaf

oreignkeyofR.ThevalueofforeignkeycanonlybeNULLorsameaswhatisinS.User-defineConstrainUsersdefinetheconstrainsthemselves.16

补充：关系的完整性实体完整性参照完整性用户定义完整性实体完整性和参照完整性是关系模型必须满足的，被称作关系的不变性，由关系数据库系统自动支持★17实体完整性规则：若属性A是基本关系R的主属性，则属性

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

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

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

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

中的数据必须满足的约束条件成绩：0－100之间身份证、身份证和生日对应关系213.2FromE/RDiagramstoRelationFromEntitySetstoRelationSimplestapproach(notalwaysbest):converteachE.S.toarel

ation.Createarelationofthesamenameandwiththesamesetofattributes.22Movies(title,year,length,filmType)Stars(

name,address)Studios(name,address)Example23FromE/RRelationshipstoRelationRelationshipsintheE/Rmodelarealsorepresentedbyrelations.Therelatio

nforagivenrelationshipRhasthefollowingattributes;1.ForeachentitysetinvolvedinrelationshipR,wetakeitskeyattributes

aspartoftheschemaoftherelationforR.2.Iftherelationshiphasattributes,thenthesearealsoattributesofrelationR.24Example:O

wns(title,year,studioname)Stars-in(title,year,starName)25E-R图向关系模型的转换原则一个实体转换为一个关系模式，实体的属性就是关系的属性，实

体的码就是关系的码对实体间的联系一个1:1联系可以转换为一个独立的关系模式，也可以与任意对应的关系模式合并一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并一个m:n联系转换为一个关系模式三个或三个以上实体间的一个多元联系可以

转换为一个关系模式具有相同码的关系模式可以合并补充：26如公司部门管理系统的E-R图及其转换成的关系模式部门项目职工电话包括承担n1n1办公室号，面积项目号，预算费参与mn办公室包含1n包括n1部门号，预算费，领导人职工号电话号码，说明职工号，姓名，办公电

话分担任务部门(部门号，部门预算费，领导人职工号)职工(职工号，姓名，办公电话，部门号)办公室(办公室号，面积，部门号)项目(项目号，项目预算费，部门号)电话(电话号码，说明，办公室号)项目承担情况(职工号

，项目号，分担任务)27ExerciseRequired:ConverttheE-Rdiagramyoudesignedinlastexerciseintoarelationaldatabaseschema.283.5FunctionalDependenci

esDefinitionofFunctionalDependency(函数依赖)X->AisanassertionaboutarelationRthatwhenevertwotuplesofRagree

onalltheattributesofX,thentheymustalsoagreeontheattributeA.Say“X->AholdsinR.”Convention•X,Y,Zrepresentsets

ofattributes;•A,B,C,…representsingleattributes.29ExampleMovies(title,year,length,filmType,studioName,starname)Wecanassertthethreedependencies:1.

title,year->length2.title,year->filmType3.title,year->studioName30FD’sWithMultipleAttributesNoneedforFD’swith

multipleattributeonright.•Butsometimesforconvenientwecancombinethem.•Example:title,year->lengthtitle,year->filmTypetitle,year->st

arNamebecometitle,year->length,filmType,starNameMultipleattributeonleftmaybeessential.•Example:title,year->length31TrivialDependenciesAfunct

ionaldependencyA1A2…An->Bissaidtobetrivial(平凡)ifBisoneoftheA’s,otherwiseissaidtobenontrivial(非平凡).Example:SupposeFuncti

onalDependenciestitle,year->titleisatrivialdependency.32Transitive(传递)FunctionalDependenciesSupposewehavea

relationRwiththreeattributesA,B,andC,theFDsA->BandB->CbothholdforR.ThenitiseasytoseethattheFDA->calsoholdsfor

R,SoCissaidtodependonAtransitively,viaB33KeysofRelationsfromFD’sviewWesayasetofoneormoreattributes{A1,A2,…,An}isakeyforrelationif:1.Thoseattr

ibutesfunctionallydetermineallotherattributesoftherelation.Thatis,itisimpossiblefortwodistincttuplesofRtoagreeonallofA1,A2,…,A

n.2.Nopropersubsetof{A1,A2,…,An}functionallydeterminesallotherattributesofR;i.e.,akeymustbeminimal34ExampleA

ttributes{title,year,starName}formakeyfortheMovierelationofFig.above.Sometimesarelationhasmorethanonekey.Ifso,itiscommont

odesignoneofthekeysastheprimarykey.35SuperKeysSuperkeysatisfiesthefirstconditionofakey;However,asuperkeyneednotsatisfythesecondcondition;Example•

Attributeset{title,year,starName}formakeyfortheMovie.•Anysupersetofthisattributeset,suchas{title,year,starName,l

ength}isasuperkey.36RulesfordiscoveringKeysofRelationFirstrule:Iftherelationcomesfromanentitysetthenthekeyfortherelationistheke

yattributesofthisentityset.Secondrule:IfarelationRiscomesfromarelationship,thenthemultiplicityoftherelationshipaffectsthek

eyforR.Therearethreecases:•Iftherelationshipismany-many,thenthekeysofbothconnectedentitysetsarethekeyattributesforR.•Ift

herelationshipismany-onefromentitysetE1toentitysetE2,thenthekeyattributesofE1arekeyattributesofR,butthoseofE2arenot.•Iftherelationshipisone-

one,thenthekeyattributesforeitheroftheconnectedentitysetsarekeyattributesofR.Thus,thereisnotauniquekeyforR.37Example•Owns:Itisaman

y-onerelationbetweenMoviestoStudios,Thus,thekeyfortherelationOwnsisthekeyattributestitleandyear,whichcomefromthekeyforMo

vies.Owns(title,year,studioName)•Star-in:Itisamany-manyrelationshipbetweenMoviesandStars.Soallattributesoftheresultingrelationarekey

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

intoasinglerelationarecalledanomalies.•Redundancy:Informationmayberepeatedunnecessarilyinseveraltuples.•InsertionAn

omalies:Tupleinsertionmaybefailedduetolacksomeotherinformationinthecurrentdatabase.•DeletionAnomalies:Ifasetofva

luesbecomesempty,wemayloseotherinformationasasideeffect.•UpdateAnomalies:Wemaychangeinformationinonetuplebutleavethesame

informationunchangedinanother.39Example:Goalofrelationalschemadesignistoavoidanomaliesandredundancy.40No

rmalFormArelationschemaissaidtobeinparticularnormalformifitsatisfiesacertainprescribedsetofconditions.•1N

F,2NF,3NF,BCNFNormalizationProcedureThesuccessivereductionofagivencollectionofrelationschematosomemoredesirableform.NFNFNFBCNF

123411NFArelationRisin1NFifandonlyif,everytuplecontainsexactlyonevalueforeachattributes.•RelationsinRelationaldatabasealwaysin1NF.•But,arelation

schemaonlyin1NFisalsoalwaysundesirableforanumberofrelations.422NFArelationRisin2NFifandonlyif:itisin1NFandeverynon-keyattri

butesisfullfunctionaldependencyontheprimarykey.FullFunctionalDependency•InrelationR,ifX->Y,andanysubsetofX,X’-\>Y,sayYfullfunctio

naldependencytoX,thatX-F>Y.•OtherwiseX-P>Y,YispartlyfunctiondependencytoX.Example:IfinrelationR(A,B,C),existingfun

ctionaldependencies(A,B)->C,A-\>C,B-\>C,so(A,B)-F>CandRisin2NF433NFArelationRisin3NFif:itisin2NFandthereisnotransitivefu

nctionaldependencyexisted.Example:IfinrelationR(A,B,C),existingfunctionaldependenciesA->BandB->C,thenRisnot3NF.44BCNFWesayarelati

onRisinBCNFif:wheneverX->AisanontrivialFDandXisasuperkey.•NontrivialmeansAisnotamemberofsetX.•Superkeyisanys

upersetofakey(notnecessarilyapropersuperset).45Example1Movies(title,year,length,filmType,studioName,starName)FD:title

,year->length,filmType,studioNameTheonlykeyis{title,year,starName}IneachFD,theleftsideisnotasuperkey.TheseFD’sshowsM

oviesisnotinBCNF.46Example2Movies(title,year,length,filmType,studioName)FD’s:title,year->length,filmT

ype,studioNameTheonlykeyis{title,year}.IneachFD,theleftsideisasuperkey.TheseFD’sshowsMoviesisinBCNF47Decomposition(分解)intoBC

NFThedecompositionstrategyistolookforanontrivialdependencyA1,A2,…,An->B1,B2,…,BmthatviolatesBCNF;i.e.,{A1,A2,…,An}isnotasuperkey,asaheuristi

c,weshallgenerallyaddtotherightsideasmanyattributesasarefunctionallydeterminedby{A1,A2,…,An}.48Example1Thekeyis(t

itle,year,satrName),butthefollowingFDexist:{title,year->length,filmType,studioName}Thus,therelationisaBCNFviolation

.Weshalldecomposetherelationintofollowingtwo:R1{title,year,length,filmType,studioName}R2{title,year,starName}49Example2Wesuppos

earelationS-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:Decomposeitinto2NFSC(Sn

o,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(Sn

o,Sdept)D-L(Sdept,Sloc)52ReadingGuideAFirstCourseinDatabaseSystems:Required:3.1and3.3Recommended:3.5

and3.7数据库系统概论推荐：第五章，第六章