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

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

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

1DatabasePrinciples2Chapter3TheRelationalDataModelRelationalModelFunctionalDependencies3Contents3.1BasicsoftheRelationalM

odel3.3FromE/RdiagramstoRelationalDesigns3.5FunctionalDependencies3.7DesignofRelationalDatabaseSchemasReadingGuide

Exercise4Thethingsyoushouldknow…ThebasicprincipleofrelationaldatabaseisproposedbyE.F.Coddin1970.ThefirstRDBMSproductionisSystemRThemostpopularRD

BMS•DB2,Oracle,Ingres,Sybase,Informix,…53.1BasicoftheRelationalModelTherelationalmodelgivesusasinglewaytorepresentdata:asatwo-dimensional

tablecalledarelation.6Attributes(属性)Attributesofarelationserveusasnamesforthecolumnsoftherelation.Usually,theattributesdescribesthemeaningofe

ntriesinthecolumnbelow.7Schemas（模式）Thenameofarelationandthesetofattributesforarelationiscalledtheschemaforthatrelation.Weshowthesc

hemafortherelationwiththerelationnamefollowedbyaparenthesizedlistofitsattributes.8Tuples（元组）Therowsofarelation,

otherthantheheaderrowcontainingtheattributes,arecalledtuple.Forexample:Relations,however,aresetsoftuples,anditisimpossibleforatupletoapp

earmorethanonceinagivenrelation.9Domains（域）Therelationalmodelrequiresthateachcomponentofeachtuplebeatomic

;thatis,itmustbeofsomeelementarytypesuchasintegersorstring.Eachattributesofarelationhasparticularelementarytype,thusdomainisdecided.10Eq

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

Arelationaboutmoviesisnotstatic;rather,relationschangeovertime.Itisnotcommonfortheschemaofarelationtochange

.Weshallcallasetoftuplesforagivenrelationaninstanceofthatrelation.12AnExampleofRelationInstanceRelation:Person(Name,Address,T

elephone)RelationInstance:NameAddressTelephoneBob123MainSt555-1234Bob128MainSt555-1235Pat123MainSt555-1235Harry456MainSt555-22

21Sally456MainSt555-2221Sally456MainSt555-2223Pat12StateSt555-123513More…Relation(Instance)=asetoftuplesDatabase=co

llectionofrelationsRelationschema=relationname+attributesExample:Movies(title,year,length,fileType)Databaseschema=asetofallrelationschemas

Movies(Title,Year,Length,FileType)Star(Name,Age)Studio(StudioName,Addr)14NameAddrTelN1A1T1N2A2T2N3A3T3N4T4N5T5T6T7NameAddrTe

lN1A1T1N1A1T2N1A1T3...N1A1T7N1A2T1N1A3T1N2A1T1TupleDomainComponentAttribute15IntegrityConstrainofRelationsEntityConstrainTheattributesbelongtokeyc

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

ThevalueofforeignkeycanonlybeNULLorsameaswhatisinS.User-defineConstrainUsersdefinetheconstrainsthemselves.16补充：关系的完整性

实体完整性参照完整性用户定义完整性实体完整性和参照完整性是关系模型必须满足的，被称作关系的不变性，由关系数据库系统自动支持★17实体完整性规则：若属性A是基本关系R的主属性，则属性A不能取空值说明：基本关系的主码中的任何属性都不能取空值，而不仅是主码整体

不能取空值依据：现实世界的实体是唯一可分的例：学生（学号，姓名，性别，专业号，年龄）课程（课程号，课程名，学分）选修（学号，课程号，成绩）18例１：学生实体与专业实体间的关系：学生（学号，姓名，性别，专业号，年龄）专业（专业号，

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

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

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

13.2FromE/RDiagramstoRelationFromEntitySetstoRelationSimplestapproach(notalwaysbest):converteachE.S.toarelation.Create

arelationofthesamenameandwiththesamesetofattributes.22Movies(title,year,length,filmType)Stars(name,address)Studios(name,add

ress)Example23FromE/RRelationshipstoRelationRelationshipsintheE/Rmodelarealsorepresentedbyrelations.TherelationforagivenrelationshipRhasthefollow

ingattributes;1.ForeachentitysetinvolvedinrelationshipR,wetakeitskeyattributesaspartoftheschemaoftherelationforR.2.Iftherelationshiphasattributes,the

nthesearealsoattributesofrelationR.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:C

onverttheE-Rdiagramyoudesignedinlastexerciseintoarelationaldatabaseschema.283.5FunctionalDependenciesDefinitionofFunctionalDepe

ndency(函数依赖)X->AisanassertionaboutarelationRthatwhenevertwotuplesofRagreeonalltheattributesofX,thentheymustalsoagreeontheattributeA.Say“X->Aholdsi

nR.”Convention•X,Y,Zrepresentsetsofattributes;•A,B,C,…representsingleattributes.29ExampleMovies(title,year,length,filmT

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

r->studioName30FD’sWithMultipleAttributesNoneedforFD’swithmultipleattributeonright.•Butsometimesforconvenientwecancombinethem.•Example

:title,year->lengthtitle,year->filmTypetitle,year->starNamebecometitle,year->length,filmType,starNameMultipleattr

ibuteonleftmaybeessential.•Example:title,year->length31TrivialDependenciesAfunctionaldependencyA1A2…An->

Bissaidtobetrivial(平凡)ifBisoneoftheA’s,otherwiseissaidtobenontrivial(非平凡).Example:SupposeFunctionalDependencie

stitle,year->titleisatrivialdependency.32Transitive(传递)FunctionalDependenciesSupposewehavearelationRwiththreeattribu

tesA,B,andC,theFDsA->BandB->CbothholdforR.ThenitiseasytoseethattheFDA->calsoholdsforR,SoCissaidtodependonAtransitively,viaB33KeysofR

elationsfromFD’sviewWesayasetofoneormoreattributes{A1,A2,…,An}isakeyforrelationif:1.Thoseattributesfunctionallydetermineallotherattributesoftherel

ation.Thatis,itisimpossiblefortwodistincttuplesofRtoagreeonallofA1,A2,…,An.2.Nopropersubsetof{A1,A2,…,An}functionally

determinesallotherattributesofR;i.e.,akeymustbeminimal34ExampleAttributes{title,year,starName}formakeyfortheMovierelati

onofFig.above.Sometimesarelationhasmorethanonekey.Ifso,itiscommontodesignoneofthekeysastheprimarykey.35SuperKeysSuper

keysatisfiesthefirstconditionofakey;However,asuperkeyneednotsatisfythesecondcondition;Example•Attributeset{title,year,starName}formake

yfortheMovie.•Anysupersetofthisattributeset,suchas{title,year,starName,length}isasuperkey.36RulesfordiscoveringKeysofRelationFirstrul

e:Iftherelationcomesfromanentitysetthenthekeyfortherelationisthekeyattributesofthisentityset.Secondrule:

IfarelationRiscomesfromarelationship,thenthemultiplicityoftherelationshipaffectsthekeyforR.Therearethreeca

ses:•Iftherelationshipismany-many,thenthekeysofbothconnectedentitysetsarethekeyattributesforR.•Iftherelationshipismany-onefromentitysetE1toe

ntitysetE2,thenthekeyattributesofE1arekeyattributesofR,butthoseofE2arenot.•Iftherelationshipisone-one,thenthekeyatt

ributesforeitheroftheconnectedentitysetsarekeyattributesofR.Thus,thereisnotauniquekeyforR.37Example•Owns:Itisama

ny-onerelationbetweenMoviestoStudios,Thus,thekeyfortherelationOwnsisthekeyattributestitleandyear,whichc

omefromthekeyforMovies.Owns(title,year,studioName)•Star-in:Itisamany-manyrelationshipbetweenMoviesandStars.Soalla

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

ytocramtoomuchintoasinglerelationarecalledanomalies.•Redundancy:Informationmayberepeatedunnecessarilyinseveral

tuples.•InsertionAnomalies:Tupleinsertionmaybefailedduetolacksomeotherinformationinthecurrentdatabase.•Del

etionAnomalies:Ifasetofvaluesbecomesempty,wemayloseotherinformationasasideeffect.•UpdateAnomalies:Wemaychangeinformationinonetuplebutle

avethesameinformationunchangedinanother.39Example:Goalofrelationalschemadesignistoavoidanomaliesandredundanc

y.40NormalFormArelationschemaissaidtobeinparticularnormalformifitsatisfiesacertainprescribedsetofconditions.•1NF,2NF,3NF,BCNFNormaliza

tionProcedureThesuccessivereductionofagivencollectionofrelationschematosomemoredesirableform.NFNFNFBCNF123411NF

ArelationRisin1NFifandonlyif,everytuplecontainsexactlyonevalueforeachattributes.•RelationsinRelationaldatabasealwaysin1

NF.•But,arelationschemaonlyin1NFisalsoalwaysundesirableforanumberofrelations.422NFArelationRisin2NFifandonl

yif:itisin1NFandeverynon-keyattributesisfullfunctionaldependencyontheprimarykey.FullFunctionalDependency•InrelationR,ifX->Y,andanysubs

etofX,X’-\>Y,sayYfullfunctionaldependencytoX,thatX-F>Y.•OtherwiseX-P>Y,YispartlyfunctiondependencytoX.Example:Ifinrela

tionR(A,B,C),existingfunctionaldependencies(A,B)->C,A-\>C,B-\>C,so(A,B)-F>CandRisin2NF433NFArelationRisin3NFif:itisin2NFandthereisno

transitivefunctionaldependencyexisted.Example:IfinrelationR(A,B,C),existingfunctionaldependenciesA->BandB->C,thenRisnot3NF.

44BCNFWesayarelationRisinBCNFif:wheneverX->AisanontrivialFDandXisasuperkey.•NontrivialmeansAisnotamemberofs

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

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

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

s{title,year}.IneachFD,theleftsideisasuperkey.TheseFD’sshowsMoviesisinBCNF47Decomposition(分解)intoBCNFThedec

ompositionstrategyistolookforanontrivialdependencyA1,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.We

shalldecomposetherelationintofollowingtwo:R1{title,year,length,filmType,studioName}R2{title,year,starName}49Example2Wesupposearel

ationS-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,Sd

ept->SlocRelationS-L-Cisonlyin1NF.PleasedecomposeitintoBCNF.50Step1:Decomposeitinto2NF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(Sdept,Sloc)52

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