数据库系统基础教程第三章答案解析.docx

数据库系统基础教程第三章答案解析.docx

《数据库系统基础教程第三章答案解析.docx》由会员分享，可在线阅读，更多相关《数据库系统基础教程第三章答案解析.docx（38页珍藏版）》请在冰豆网上搜索。

数据库系统基础教程第三章答案解析

Exercise3.1.1

Answersforthisexercisemayvarybecauseofdifferentinterpretations.

SomepossibleFDs:

SocialSecuritynumbername

Areacodestate

Streetaddress,city,statezipcode

Possiblekeys:

{SocialSecuritynumber,streetaddress,city,state,areacode,phonenumber}

Needstreetaddress,city,statetouniquelydeterminelocation.Apersoncouldhavemultipleaddresses.Thesameistrueforphones.Thesedays,apersoncouldhavealandlineandacellularphone

Exercise3.1.2

Answersforthisexercisemayvarybecauseofdifferentinterpretations

SomepossibleFDs:

IDx-position,y-position,z-position

IDx-velocity,y-velocity,z-velocity

x-position,y-position,z-positionID

Possiblekeys:

{ID}

{x-position,y-position,z-position}

Thereasonwhythepositionswouldbeakeyisnotwomoleculescanoccupythesamepoint.

Exercise3.1.3a

ThesuperkeysareanysubsetthatcontainsA1.Thus,thereare2（n-1）suchsubsets,sinceeachofthen-1attributesA2throughAnmayindependentlybechoseninorout.

Exercise3.1.3b

ThesuperkeysareanysubsetthatcontainsA1orA2.Thereare2（n-1）suchsubsetswhenconsideringA1andthen-1attributesA2throughAn.Thereare2（n-2）suchsubsetswhenconsideringA2andthen-2attributesA3throughAn.WedonotcountA1inthesesubsetsbecausetheyarealreadycountedinthefirstgroupofsubsets.Thetotalnumberofsubsetsis2（n-1）+2（n-2）.

Exercise3.1.3c

Thesuperkeysareanysubsetthatcontains{A1,A2}or{A3,A4}.Thereare2（n-2）suchsubsetswhenconsidering{A1,A2}andthen-2attributesA3throughAn.Thereare2（n-2）–2（n-4）suchsubsetswhenconsidering{A3,A4}andattributesA5throughAnalongwiththeindividualattributesA1andA2.Wegetthe2（n-4）termbecausewehavetodiscardthesubsetsthatcontainthekey{A1,A2}toavoiddoublecounting.Thetotalnumberofsubsetsis2（n-2）+2（n-2）–2（n-4）.

Exercise3.1.3d

Thesuperkeysareanysubsetthatcontains{A1,A2}or{A1,A3}.Thereare2（n-2）suchsubsetswhenconsidering{A1,A2}andthen-2attributesA3throughAn.Thereare2（n-3）suchsubsetswhenconsidering{A1,A3}andthen-3attributesA4throughAnWedonotcountA2inthesesubsetsbecausetheyarealreadycountedinthefirstgroupofsubsets.Thetotalnumberofsubsetsis2（n-2）+2（n-3）.

Exercise3.2.1a

Wecouldtryinferencerulestodeducenewdependenciesuntilwearesatisfiedwehavethemall.Amoresystematicwayistoconsidertheclosuresofall15nonemptysetsofattributes.

Forthesingleattributeswehave{A}+=A,{B}+=B,{C}+=ACD,and{D}+=AD.Thus,theonlynewdependencywegetwithasingleattributeontheleftisCA.

Nowconsiderpairsofattributes:

{AB}+=ABCD,sowegetnewdependencyABD.{AC}+=ACD,andACDisnontrivial.{AD}+=AD,sonothingnew.{BC}+=ABCD,sowegetBCA,andBCD.{BD}+=ABCD,givingusBDAandBDC.{CD}+=ACD,givingCDA.

Forthetriplesofattributes,{ACD}+=ACD,buttheclosuresoftheothersetsareeachABCD.Thus,wegetnewdependenciesABCD,ABDC,andBCDA.

Since{ABCD}+=ABCD,wegetnonewdependencies.

Thecollectionof11newdependenciesmentionedaboveare:

CA,ABD,ACD,BCA,BCD,BDA,BDC,CDA,ABCD,ABDC,andBCDA.

Exercise3.2.1b

Fromtheanalysisofclosuresabove,wefindthatAB,BC,andBDarekeys.AllothersetseitherdonothaveABCDastheclosureorcontainoneofthesethreesets.

Exercise3.2.1c

Thesuperkeysareallthosethatcontainoneofthosethreekeys.Thatis,asuperkeythatisnotakeymustcontainBandmorethanoneofA,C,andD.Thus,the（proper）superkeysareABC,ABD,BCD,andABCD.

Exercise3.2.2a

i）Forthesingleattributeswehave{A}+=ABCD,{B}+=BCD,{C}+=C,and{D}+=D.Thus,thenewdependenciesareACandAD.

Nowconsiderpairsofattributes:

{AB}+=ABCD,{AC}+=ABCD,{AD}+=ABCD,{BC}+=BCD,{BD}+=BCD,{CD}+=CD.ThusthenewdependenciesareABC,ABD,ACB,ACD,ADB,ADC,BCDandBDC.

Forthetriplesofattributes,{BCD}+=BCD,buttheclosuresoftheothersetsareeachABCD.Thus,wegetnewdependenciesABCD,ABDC,andACDB.

Since{ABCD}+=ABCD,wegetnonewdependencies.

Thecollectionof13newdependenciesmentionedaboveare:

AC,AD,ABC,ABD,ACB,ACD,ADB,ADC,BCD,BDC,ABCD,ABDCandACDB.

ii）Forthesingleattributeswehave{A}+=A,{B}+=B,{C}+=C,and{D}+=D.Thus,therearenonewdependencies.

Nowconsiderpairsofattributes:

{AB}+=ABCD,{AC}+=AC,{AD}+=ABCD,{BC}+=ABCD,{BD}+=BD,{CD}+=ABCD.ThusthenewdependenciesareABD,ADC,BCAandCDB.

Forthetriplesofattributes,alltheclosuresofthesetsareeachABCD.Thus,wegetnewdependenciesABCD,ABDC,ACDBandBCDA.

Since{ABCD}+=ABCD,wegetnonewdependencies.

Thecollectionof8newdependenciesmentionedaboveare:

ABD,ADC,BCA,CDB,ABCD,ABDC,ACDBandBCDA.

iii）Forthesingleattributeswehave{A}+=ABCD,{B}+=ABCD,{C}+=ABCD,and{D}+=ABCD.Thus,thenewdependenciesareAC,AD,BD,BA,CA,CB,DBandDC.

Sinceallthesingleattributes’closuresareABCD,anysupersetofthesingleattributeswillalsoleadtoaclosureofABCD.Knowingthis,wecanenumeratetherestofthenewdependencies.

Thecollectionof24newdependenciesmentionedaboveare:

AC,AD,BD,BA,CA,CB,DB,DC,ABC,ABD,ACB,ACD,ADB,ADC,BCA,BCD,BDA,BDC,CDA,CDB,ABCD,ABDC,ACDBandBCDA.

Exercise3.2.2b

i）Fromtheanalysisofclosuresin3.2.2a（i）,wefindthattheonlykeyisA.AllothersetseitherdonothaveABCDastheclosureorcontainA.

ii）Fromtheanalysisofclosures3.2.2a（ii）,wefindthatAB,AD,BC,andCDarekeys.AllothersetseitherdonothaveABCDastheclosureorcontainoneofthesefoursets.

iii）Fromtheanalysisofclosures3.2.2a（iii）,wefindthatA,B,CandDarekeys.AllothersetseitherdonothaveABCDastheclosureorcontainoneofthesefoursets.

Exercise3.2.2c

i）Thesuperkeysareallthosesetsthatcontainoneofthekeysin3.2.2b（i）.ThesuperkeysareAB,AC,AD,ABC,ABD,ACD,BCDandABCD.

ii）Thesuperkeysareallthosesetsthatcontainoneofthekeysin3.2.2b（ii）.ThesuperkeysareABC,ABD,ACD,BCDandABCD.

iii）Thesuperkeysareallthosesetsthatcontainoneofthekeysin3.2.2b（iii）.ThesuperkeysareAB,AC,AD,BC,BD,CD,ABC,ABD,ACD,BCDandABCD.

Exercise3.2.3a

SinceA1A2…AnCcontainsA1A2…An,thentheclosureofA1A2…AnCcontainsB.ThusitfollowsthatA1A2…AnCB.

Exercise3.2.3b

From3.2.3a,weknowthatA1A2…AnCB.Usingtheconceptoftrivialdependencies,wecanshowthatA1A2…AnCC.ThusA1A2…AnCBC.

Exercise3.2.3c

FromA1A2…AnE1E2…Ej,weknowthattheclosurecontainsB1B2…BmbecauseoftheFDA1A2…AnB1B2…Bm.TheB1B2…BmandtheE1E2…EjcombinetoformtheC1C2…Ck.ThustheclosureofA1A2…AnE1E2…EjcontainsDaswell.Thus,A1A2…AnE1E2…EjD.

Exercise3.2.3d

FromA1A2…AnC1C2…Ck,weknowthattheclosurecontainsB1B2…BmbecauseoftheFDA1A2…AnB1B2…Bm.TheC1C2…CkalsotellusthattheclosureofA1A2…AnC1C2…CkcontainsD1D2…Dj.Thus,A1A2…AnC1C2…CkB1B2…BkD1D2…Dj.

Exercise3.2.4a

IfattributeArepresentedSocialSecurityNumberandBrepresentedaperson’sname,thenwewouldassumeABbutBAwouldnotbevalidbecausetheremaybemanypeoplewiththesamenameanddifferentSocialSecurityNumbers.

Exercise3.2.4b

LetattributeArepresentSocialSecurityNumber,BrepresentgenderandCrepresentname.SurelySocialSecurityNumberandgendercanuniquelyidentifyaperson’sname（i.e.ABC）.ASocialSecurityNumbercanalsouniquelyidentifyaperson’sname（i.e.AC）.However,genderdoesnotuniquelydetermineaname（i.e.BCisnotvalid）.

Exercise3.2.4c

LetattributeArepresentlatitudeandBrepresentlongitude.Together,bothattributescanuniquelydetermineC,apointontheworldmap（i.e.ABC）.However,neitherAnorBcanuniquelyidentifyapoint（i.e.ACandBCarenotvalid）.

Exercise3.2.5

GivenarelationwithattributesA1A2…An,wearetoldthattherearenofunctionaldependenciesoftheformB1B2…Bn-1CwhereB1B2…Bn-1isn-1oftheattributesfromA1A2…AnandCistheremainingattributefromA1A2…An.Inthiscase,thesetB1B2…Bn-1andanysubsetdonotfunctionallydetermineC.ThustheonlyfunctionaldependenciesthatwecanmakeareoneswhereCisonboththeleftandrighthandsides.AllofthesefunctionaldependencieswouldbetrivialandthustherelationhasnonontrivialFD’s.

Exercise3.2.6

Let’sprovethisbyusingthecontrapositive.WewishtoshowthatifX+isnotasubsetofY+,thenitmustbethatXisnotasubsetofY.

IfX+isnotasubsetofY+,theremustbeattributesA1A2…AninX+thatarenotinY+.IfanyoftheseattributeswereoriginallyinX,thenwearedonebecauseYdoesnotcontainanyoftheA1A2…An.However,iftheA1A2…Anwereaddedbytheclosure,thenwemustexaminethecasefurther.AssumethattherewassomeFDC1C2…CmA1A2…AjwhereA1A2…AjissomesubsetofA1A2…An.ItmustbethenthatC1C2…CmorsomesubsetofC1C2…CmisinX.However,theattributesC1C2…CmcannotbeinYbecauseweassumedthatattributesA1A2…AnareonlyinX+andarenotinY+.Thus,XisnotasubsetofY.

Byprovingthecontrapositive,wehavealsoprovedifX⊆Y,thenX+⊆Y+.

Exercise3.2.7

ThealgorithmtofindX+isoutlinedonpg.76.Usingthatalgorithm,wecanprovethat

（X+）+=X+.Wewilldothisbyusingaproofbycontradiction.

Supposethat（X+）+≠X+.Thenfor（X+）+,itmustbethatsomeFDallowedadditionalattributestobeaddedtotheoriginalsetX+.Forexample,X+AwhereAissomeattributenotinX+.However,ifthiswerethecase,thenX+wouldnotbetheclosureofX.TheclosureofXwouldhavetoincludeAaswell.ThiscontradictsthefactthatweweregiventheclosureofX,X+.Therefore,i