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

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

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

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

romE/RdiagramstoRelationalDesigns3.5FunctionalDependencies3.7DesignofRelationalDatabaseSchemasReadingGuideExer

cise4Thethingsyoushouldknow…ThebasicprincipleofrelationaldatabaseisproposedbyE.F.Coddin1970.ThefirstRDBMSproductionisSystemRThemostpo

pularRDBMS•DB2,Oracle,Ingres,Sybase,Informix,…53.1BasicoftheRelationalModelTherelationalmodelgivesusasinglewayt

orepresentdata:asatwo-dimensionaltablecalledarelation.6Attributes(属性)Attributesofarelationserveusasnamesforthecolumnsoftherelation.Us

ually,theattributesdescribesthemeaningofentriesinthecolumnbelow.7Schemas（模式）Thenameofarelationandthesetofat

tributesforarelationiscalledtheschemaforthatrelation.Weshowtheschemafortherelationwiththerelationnamefollowedbyaparenthesizedlistofit

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

,anditisimpossibleforatupletoappearmorethanonceinagivenrelation.9Domains（域）Therelationalmodelrequiresthateachcomponentofeachtupleb

eatomic;thatis,itmustbeofsomeelementarytypesuchasintegersorstring.Eachattributesofarelationhasparticularelemen

tarytype,thusdomainisdecided.10EquivalentRepresentationsofaRelationTheattributesoftherelationcanbereorderedw

ithoutchangetherelation.11RelationInstance（关系实例）Arelationaboutmoviesisnotstatic;rather,relationschangeovertime.Itisno

tcommonfortheschemaofarelationtochange.Weshallcallasetoftuplesforagivenrelationaninstanceofthatrelation.12AnExampleofR

elationInstanceRelation:Person(Name,Address,Telephone)RelationInstance:NameAddressTelephoneBob123MainSt555-1234Bob128MainSt555-1235Pat

123MainSt555-1235Harry456MainSt555-2221Sally456MainSt555-2221Sally456MainSt555-2223Pat12StateSt555-123513More…

Relation(Instance)=asetoftuplesDatabase=collectionofrelationsRelationschema=relationname+attributesExample:Movies(title,year,le

ngth,fileType)Databaseschema=asetofallrelationschemasMovies(Title,Year,Length,FileType)Star(Name,Age)Studio(StudioName,Addr)14Nam

eAddrTelN1A1T1N2A2T2N3A3T3N4T4N5T5T6T7NameAddrTelN1A1T1N1A1T2N1A1T3...N1A1T7N1A2T1N1A3T1N2A1T1TupleDomainComp

onentAttribute15IntegrityConstrainofRelationsEntityConstrainTheattributesbelongtokeycannotbesetasNULL

.ReferenceConstrainForeignKey:annon-keyattributeAinRisakeyinS,thentheAiscalledaforeignkeyofR.Thevalueoffo

reignkeycanonlybeNULLorsameaswhatisinS.User-defineConstrainUsersdefinetheconstrainsthemselves.16补充：关系的完整性实体完整性参照完整性用户定义完整性实体完整性和参照完整性

是关系模型必须满足的，被称作关系的不变性，由关系数据库系统自动支持★17实体完整性规则：若属性A是基本关系R的主属性，则属性A不能取空值说明：基本关系的主码中的任何属性都不能取空值，而不仅是主码整体不能取空值依据：现实世界的实体是唯一可分的

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

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

9参照完整性定义：外码设F是参照关系R的一个或一组属性，但不是R的码，若F与被参照关系S的主码相对应，则称F是R的外码（详细定义见教材P54）规则：参照关系R中每个元组在外码F上的值必须为：•或者取空值（F的每个属性值均为空值）•或者等于

S中某个元组的主码值例３：学生（学号，姓名，性别，专业号，年龄，班长）参照关系被参照关系外码20用户定义完整性用户定义的、具体应用中的数据必须满足的约束条件成绩：0－100之间身份证、身份证和生日对应关系213.2FromE/RDi

agramstoRelationFromEntitySetstoRelationSimplestapproach(notalwaysbest):converteachE.S.toarelation.C

reatearelationofthesamenameandwiththesamesetofattributes.22Movies(title,year,length,filmType)Stars(name,address)Studios(name,address)Ex

ample23FromE/RRelationshipstoRelationRelationshipsintheE/Rmodelarealsorepresentedbyrelations.TherelationforagivenrelationshipRhasth

efollowingattributes;1.ForeachentitysetinvolvedinrelationshipR,wetakeitskeyattributesaspartoftheschemaoftherelationforR.2.I

ftherelationshiphasattributes,thenthesearealsoattributesofrelationR.24Example:Owns(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-Rdiagramyoudesignedinlastexerciseintoarelational

databaseschema.283.5FunctionalDependenciesDefinitionofFunctionalDependency(函数依赖)X->Aisanassertionabouta

relationRthatwhenevertwotuplesofRagreeonalltheattributesofX,thentheymustalsoagreeontheattributeA.Say“X->AholdsinR.”Co

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

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

>studioName30FD’sWithMultipleAttributesNoneedforFD’swithmultipleattributeonright.•Butsometimesforconvenientwecancombinethem.•Exa

mple:title,year->lengthtitle,year->filmTypetitle,year->starNamebecometitle,year->length,filmType,starNameMultipleattributeonleftmaybeessential.•Exam

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

benontrivial(非平凡).Example:SupposeFunctionalDependenciestitle,year->titleisatrivialdependency.32Transitive

(传递)FunctionalDependenciesSupposewehavearelationRwiththreeattributesA,B,andC,theFDsA->BandB->CbothholdforR.Thenitiseas

ytoseethattheFDA->calsoholdsforR,SoCissaidtodependonAtransitively,viaB33KeysofRelationsfromFD’sviewWes

ayasetofoneormoreattributes{A1,A2,…,An}isakeyforrelationif:1.Thoseattributesfunctionallydetermineallotherattributesoftherelation.That

is,itisimpossiblefortwodistincttuplesofRtoagreeonallofA1,A2,…,An.2.Nopropersubsetof{A1,A2,…,An}functiona

llydeterminesallotherattributesofR;i.e.,akeymustbeminimal34ExampleAttributes{title,year,starName}formakeyfortheMovierelationofFig.above.Some

timesarelationhasmorethanonekey.Ifso,itiscommontodesignoneofthekeysastheprimarykey.35SuperKeysSuperkeysatisfiesthe

firstconditionofakey;However,asuperkeyneednotsatisfythesecondcondition;Example•Attributeset{title,year,starName}formakeyfor

theMovie.•Anysupersetofthisattributeset,suchas{title,year,starName,length}isasuperkey.36RulesfordiscoveringKeysofRelationFirstrule:Ift

herelationcomesfromanentitysetthenthekeyfortherelationisthekeyattributesofthisentityset.Secondrule:IfarelationRiscomesfromarelationship,then

themultiplicityoftherelationshipaffectsthekeyforR.Therearethreecases:•Iftherelationshipismany-many,thenthek

eysofbothconnectedentitysetsarethekeyattributesforR.•Iftherelationshipismany-onefromentitysetE1toentitysetE2,thenthekeyattributesofE1arekeyattribute

sofR,butthoseofE2arenot.•Iftherelationshipisone-one,thenthekeyattributesforeitheroftheconnectedentitysetsarekeyattributes

ofR.Thus,thereisnotauniquekeyforR.37Example•Owns:Itisamany-onerelationbetweenMoviestoStudios,Thus,thekeyfortherelat

ionOwnsisthekeyattributestitleandyear,whichcomefromthekeyforMovies.Owns(title,year,studioName)•Star-in:Itisamany-man

yrelationshipbetweenMoviesandStars.SoallattributesoftheresultingrelationarekeyattributesStars-in(title,year,starName)383.7DesignofRe

lationalDatabaseSchemaAnomalies(异常)Problemoccurwhenwetrytocramtoomuchintoasinglerelationarecalledanomalies.•Redundancy:Info

rmationmayberepeatedunnecessarilyinseveraltuples.•InsertionAnomalies:Tupleinsertionmaybefailedduetolacksomeotherinformationinthecurrentdatab

ase.•DeletionAnomalies:Ifasetofvaluesbecomesempty,wemayloseotherinformationasasideeffect.•UpdateAnomalies:Wemaychangeinformationinonetuplebut

leavethesameinformationunchangedinanother.39Example:Goalofrelationalschemadesignistoavoidanomaliesandredundancy.40NormalForm

Arelationschemaissaidtobeinparticularnormalformifitsatisfiesacertainprescribedsetofconditions.•1NF,2NF,3NF,BCNFNormalization

ProcedureThesuccessivereductionofagivencollectionofrelationschematosomemoredesirableform.NFNFNFBCNF123411NFArelationRisin1NFifand

onlyif,everytuplecontainsexactlyonevalueforeachattributes.•RelationsinRelationaldatabasealwaysin1NF.•But,arelationschemaonlyin1

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

cyontheprimarykey.FullFunctionalDependency•InrelationR,ifX->Y,andanysubsetofX,X’-\>Y,sayYfullfunctionaldependencytoX,t

hatX-F>Y.•OtherwiseX-P>Y,YispartlyfunctiondependencytoX.Example:IfinrelationR(A,B,C),existingfunctionaldependencies(A,B)->C,A-\>C,B-\>C,so(A,B)-F>

CandRisin2NF433NFArelationRisin3NFif:itisin2NFandthereisnotransitivefunctionaldependencyexisted.Example:IfinrelationR(A,B,C),exis

tingfunctionaldependenciesA->BandB->C,thenRisnot3NF.44BCNFWesayarelationRisinBCNFif:wheneverX->AisanontrivialFDandXisasuperkey.•Nontrivial

meansAisnotamemberofsetX.•Superkeyisanysupersetofakey(notnecessarilyapropersuperset).45Example1Movies(title,year,length,filmT

ype,studioName,starName)FD:title,year->length,filmType,studioNameTheonlykeyis{title,year,starName}IneachFD,theleftsidei

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

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

FThedecompositionstrategyistolookforanontrivialdependencyA1,A2,…,An->B1,B2,…,BmthatviolatesBCNF;i.e.,{A1,A2,…,

An}isnotasuperkey,asaheuristic,weshallgenerallyaddtotherightsideasmanyattributesasarefunctionallydeterminedby{A1,A2,…,An}.48Example

1Thekeyis(title,year,satrName),butthefollowingFDexist:{title,year->length,filmType,studioName}Thus,therelationisaBCNFviolation.Weshal

ldecomposetherelationintofollowingtwo: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,Sde

pt->SlocRelationS-L-Cisonlyin1NF.PleasedecomposeitintoBCNF.50Step1:Decomposeitinto2NFSC(Sno,Cno,G)S-L(Sno,Sdept,Sloc)Step2:D

ecomposeitinto3NFNow,SCisin3NF,butS-Lonlyin2NF.SoweneedtoDecomposeS-Linto3NFSC(Sno,Cno,G)S-D(Sno,Sdept)D-L(Sdept,Sloc)51Step3:Decomposeitinto

BCNFNow,allofrelationsSC,S-D,D-LareinBCNF.ThefinalresultisSC(Sno,Cno,G)S-D(Sno,Sdept)D-L(Sdept,Sloc)52ReadingGuideAFirstCourseinDatabaseSy

stems:Required:3.1and3.3Recommended:3.5and3.7数据库系统概论推荐：第五章，第六章