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

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

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

1DatabasePrinciples2Chapter3TheRelationalDataModelRelationalModelFunctionalDependencies3Contents3

.1BasicsoftheRelationalModel3.3FromE/RdiagramstoRelationalDesigns3.5FunctionalDependencies3.7DesignofRelationalDatabaseSchemasReadingG

uideExercise4Thethingsyoushouldknow…ThebasicprincipleofrelationaldatabaseisproposedbyE.F.Coddin1970.ThefirstRDBMSpr

oductionisSystemRThemostpopularRDBMS•DB2,Oracle,Ingres,Sybase,Informix,…53.1BasicoftheRelationalModelTherelat

ionalmodelgivesusasinglewaytorepresentdata:asatwo-dimensionaltablecalledarelation.6Attributes(属性)Attributesofar

elationserveusasnamesforthecolumnsoftherelation.Usually,theattributesdescribesthemeaningofentriesinthecolumnbelow.7Schemas（模式）T

henameofarelationandthesetofattributesforarelationiscalledtheschemaforthatrelation.Weshowtheschemafortherela

tionwiththerelationnamefollowedbyaparenthesizedlistofitsattributes.8Tuples（元组）Therowsofarelation,otherthantheheaderrowcontai

ningtheattributes,arecalledtuple.Forexample:Relations,however,aresetsoftuples,anditisimpossibleforatupletoappearmorethanoncein

agivenrelation.9Domains（域）Therelationalmodelrequiresthateachcomponentofeachtuplebeatomic;thatis,itmustbeofsomeele

mentarytypesuchasintegersorstring.Eachattributesofarelationhasparticularelementarytype,thusdomainisdecided.10EquivalentRepresentati

onsofaRelationTheattributesoftherelationcanbereorderedwithoutchangetherelation.11RelationInstance（关系实例）Arelationaboutmoviesisnotstatic;rather,re

lationschangeovertime.Itisnotcommonfortheschemaofarelationtochange.Weshallcallasetoftuplesforagivenrelationaninstanceofthatrelati

on.12AnExampleofRelationInstanceRelation:Person(Name,Address,Telephone)RelationInstance:NameAddressTelephoneBob123MainSt555-1234Bob128MainSt555-1235P

at123MainSt555-1235Harry456MainSt555-2221Sally456MainSt555-2221Sally456MainSt555-2223Pat12StateSt555-123513More…

Relation(Instance)=asetoftuplesDatabase=collectionofrelationsRelationschema=relationname+attributesExample

:Movies(title,year,length,fileType)Databaseschema=asetofallrelationschemasMovies(Title,Year,Length,FileType

)Star(Name,Age)Studio(StudioName,Addr)14NameAddrTelN1A1T1N2A2T2N3A3T3N4T4N5T5T6T7NameAddrTelN1A1T1N1A1T2N1A1T

3...N1A1T7N1A2T1N1A3T1N2A1T1TupleDomainComponentAttribute15IntegrityConstrainofRelationsEntityConstrainTheattributesbelo

ngtokeycannotbesetasNULL.ReferenceConstrainForeignKey:annon-keyattributeAinRisakeyinS,thentheAiscalledafo

reignkeyofR.ThevalueofforeignkeycanonlybeNULLorsameaswhatisinS.User-defineConstrainUsersdefinetheconstrainst

hemselves.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.to

arelation.Createarelationofthesamenameandwiththesamesetofattributes.22Movies(title,year,length,filmType)Stars(name,address)Studios(

name,address)Example23FromE/RRelationshipstoRelationRelationshipsintheE/Rmodelarealsorepresentedbyrelations.TherelationforagivenrelationshipRhasth

efollowingattributes;1.ForeachentitysetinvolvedinrelationshipR,wetakeitskeyattributesaspartoftheschemaoftherelationforR.2.Iftherelationsh

iphasattributes,thenthesearealsoattributesofrelationR.24Example:Owns(title,year,studioname)Stars-in(ti

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

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

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

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

onverttheE-Rdiagramyoudesignedinlastexerciseintoarelationaldatabaseschema.283.5FunctionalDependenciesDefinitionofFunc

tionalDependency(函数依赖)X->AisanassertionaboutarelationRthatwhenevertwotuplesofRagreeonalltheattributesofX,thentheymustalsoagreeont

heattributeA.Say“X->AholdsinR.”Convention•X,Y,Zrepresentsetsofattributes;•A,B,C,…representsingleattributes.29Exa

mpleMovies(title,year,length,filmType,studioName,starname)Wecanassertthethreedependencies:1.title,year->length2.title,year->filmTy

pe3.title,year->studioName30FD’sWithMultipleAttributesNoneedforFD’swithmultipleattributeonright.•But

sometimesforconvenientwecancombinethem.•Example:title,year->lengthtitle,year->filmTypetitle,year->starNamebecometitle

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

1A2…An->Bissaidtobetrivial(平凡)ifBisoneoftheA’s,otherwiseissaidtobenontrivial(非平凡).Example:SupposeFunctionalDependenciestitle,yea

r->titleisatrivialdependency.32Transitive(传递)FunctionalDependenciesSupposewehavearelationRwiththreeattributes

A,B,andC,theFDsA->BandB->CbothholdforR.ThenitiseasytoseethattheFDA->calsoholdsforR,SoCissaidtodependonAtransitively,v

iaB33KeysofRelationsfromFD’sviewWesayasetofoneormoreattributes{A1,A2,…,An}isakeyforrelationif:1.Thoseattributesfunctional

lydetermineallotherattributesoftherelation.Thatis,itisimpossiblefortwodistincttuplesofRtoagreeonallofA1,A2

,…,An.2.Nopropersubsetof{A1,A2,…,An}functionallydeterminesallotherattributesofR;i.e.,akeymustbeminimal34Exam

pleAttributes{title,year,starName}formakeyfortheMovierelationofFig.above.Sometimesarelationhasmorethanonekey.Ifso

,itiscommontodesignoneofthekeysastheprimarykey.35SuperKeysSuperkeysatisfiesthefirstconditionofakey;However,asuperke

yneednotsatisfythesecondcondition;Example•Attributeset{title,year,starName}formakeyfortheMovie.•Anysupersetofthisat

tributeset,suchas{title,year,starName,length}isasuperkey.36RulesfordiscoveringKeysofRelationFirstrule:Iftherelationcomesfromanentitysetthenthekey

fortherelationisthekeyattributesofthisentityset.Secondrule:IfarelationRiscomesfromarelationship,thenthemultiplicityoftherelationshipaffectsthe

keyforR.Therearethreecases:•Iftherelationshipismany-many,thenthekeysofbothconnectedentitysetsarethekeyattributesforR.•

Iftherelationshipismany-onefromentitysetE1toentitysetE2,thenthekeyattributesofE1arekeyattributesofR,butthoseofE2arenot.•Iftherelationshipi

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

onbetweenMoviestoStudios,Thus,thekeyfortherelationOwnsisthekeyattributestitleandyear,whichcomefromthekeyforMovies.Owns(

title,year,studioName)•Star-in:Itisamany-manyrelationshipbetweenMoviesandStars.Soallattributesoftheresultingrelation

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

oomuchintoasinglerelationarecalledanomalies.•Redundancy:Informationmayberepeatedunnecessarilyinseveraltuple

s.•InsertionAnomalies:Tupleinsertionmaybefailedduetolacksomeotherinformationinthecurrentdatabase.•DeletionAnomalies:Ifasetofv

aluesbecomesempty,wemayloseotherinformationasasideeffect.•UpdateAnomalies:Wemaychangeinformationinonetuplebutleavethesameinformationunch

angedinanother.39Example:Goalofrelationalschemadesignistoavoidanomaliesandredundancy.40NormalFormArelationsch

emaissaidtobeinparticularnormalformifitsatisfiesacertainprescribedsetofconditions.•1NF,2NF,3NF,BCNFNormalizationProce

dureThesuccessivereductionofagivencollectionofrelationschematosomemoredesirableform.NFNFNFBCNF123411NFArelationRisin1NFifandonlyif,everytup

lecontainsexactlyonevalueforeachattributes.•RelationsinRelationaldatabasealwaysin1NF.•But,arelationschemaonlyin1NFisalsoalwaysundesirab

leforanumberofrelations.422NFArelationRisin2NFifandonlyif:itisin1NFandeverynon-keyattributesisfullfunctionaldependencyontheprimarykey.Ful

lFunctionalDependency•InrelationR,ifX->Y,andanysubsetofX,X’-\>Y,sayYfullfunctionaldependencytoX,thatX-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.Ex

ample:IfinrelationR(A,B,C),existingfunctionaldependenciesA->BandB->C,thenRisnot3NF.44BCNFWesayarela

tionRisinBCNFif:wheneverX->AisanontrivialFDandXisasuperkey.•NontrivialmeansAisnotamemberofsetX.•Superkeyisanysupersetofak

ey(notnecessarilyapropersuperset).45Example1Movies(title,year,length,filmType,studioName,starName)FD:title,year->l

ength,filmType,studioNameTheonlykeyis{title,year,starName}IneachFD,theleftsideisnotasuperkey.TheseFD’sshowsMoviesisno

tinBCNF.46Example2Movies(title,year,length,filmType,studioName)FD’s:title,year->length,filmType,studioNameTheonlykeyis{title,year}.IneachFD,the

leftsideisasuperkey.TheseFD’sshowsMoviesisinBCNF47Decomposition(分解)intoBCNFThedecompositionstrategyistolookfor

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

ributesasarefunctionallydeterminedby{A1,A2,…,An}.48Example1Thekeyis(title,year,satrName),butthefollow

ingFDexist:{title,year->length,filmType,studioName}Thus,therelationisaBCNFviolation.Weshalldecomposetherelationintofollowingtwo:R1{title,year,lengt

h,filmType,studioName}R2{title,year,starName}49Example2WesupposearelationS-L-C(Sno,Sdept,Sloc,Cno,G)Onlykeyofthisrelation

is(Sno,Cno)FD’s:(Sno,Cno)-F>G,Sno->Sdept,(Sno,Cno)-p>SdeptSno->Sloc,(Sno,Cno)-p>Sloc,Sdept->SlocRelationS-L-Ci

sonlyin1NF.PleasedecomposeitintoBCNF.50Step1:Decomposeitinto2NFSC(Sno,Cno,G)S-L(Sno,Sdept,Sloc)Step2:Decomposeitinto3NFNow,SCisi

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

mposeitintoBCNFNow,allofrelationsSC,S-D,D-LareinBCNF.ThefinalresultisSC(Sno,Cno,G)S-D(Sno,Sdept)

D-L(Sdept,Sloc)52ReadingGuideAFirstCourseinDatabaseSystems:Required:3.1and3.3Recommended:3.5and3.7数据库系统概论推荐：第五章，第六章