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

Exercise3.1.1Answersforthisexercisemayvarybecauseofdifferentinterpretations.SomepossibleFDs:SocialSecuritynumbernameAreacodestateStreetaddress,city,statezipcodePossiblekeys:{SocialSecuritynumber,streetaddress,city,state,areacode,phonenumber}Needstreetaddress,city,statetouniquelydeterminelocation.Apersoncouldhavemultipleaddresses.Thesameistrueforphones.Thesedays,apersoncouldhavealandlineandacellularphoneExercise3.1.2AnswersforthisexercisemayvarybecauseofdifferentinterpretationsSomepossibleFDs:IDx-position,y-position,z-positionIDx-velocity,y-velocity,z-velocityx-position,y-position,z-positionIDPossiblekeys:{ID}{x-position,y-position,z-position}Thereasonwhythepositionswouldbeakeyisnotwomoleculescanoccupythesamepoint.Exercise3.1.3aThesuperkeysareanysubsetthatcontainsA1.Thus,thereare2(n-1)suchsubsets,sinceeachofthen-1attributesA2throughAnmayindependentlybechoseninorout.Exercise3.1.3bThesuperkeysareanysubsetthatcontainsA1orA2.Thereare2(n-1)suchsubsetswhenconsideringA1andthen-1attributesA2throughAn.Thereare2(n-2)suchsubsetswhenconsideringA2andthen-2attributesA3throughAn.WedonotcountA1inthesesubsetsbecausetheyarealreadycountedinthefirstgroupofsubsets.Thetotalnumberofsubsetsis2(n-1)+2(n-2).Exercise3.1.3cThesuperkeysareanysubsetthatcontains{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.3dThesuperkeysareanysubsetthatcontains{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.1aWecouldtryinferencerulestodeducenewdependenciesuntilwearesatisfiedwehavethemall.Amoresystematicwayistoconsidertheclosuresofall15nonemptysetsofattributes.Forthesingleattributeswehave{A}+=A,{B}+=B,{C}+=ACD,and{D}+=AD.Thus,theonlynewdependencywegetwithasingleattributeontheleftisCA.Nowconsiderpairsofattributes:{AB}+=ABCD,sowegetnewdependencyABD.{AC}+=ACD,andACDisnontrivial.{AD}+=AD,sonothingnew.{BC}+=ABCD,sowegetBCA,andBCD.{BD}+=ABCD,givingusBDAandBDC.{CD}+=ACD,givingCDA.Forthetriplesofattributes,{ACD}+=ACD,buttheclosuresoftheothersetsareeachABCD.Thus,wegetnewdependenciesABCD,ABDC,andBCDA.Since{ABCD}+=ABCD,wegetnonewdependencies.Thecollectionof11newdependenciesmentionedaboveare:CA,ABD,ACD,BCA,BCD,BDA,BDC,CDA,ABCD,ABDC,andBCDA.Exercise3.2.1bFromtheanalysisofclosuresabove,wefindthatAB,BC,andBDarekeys.AllothersetseitherdonothaveABCDastheclosureorcontainoneofthesethreesets.Exercise3.2.1cThesuperkeysareallthosethatcontainoneofthosethreekeys.Thatis,asuperkeythatisnotakeymustcontainBandmorethanoneofA,C,andD.Thus,the(proper)superkeysareABC,ABD,BCD,andABCD.Exercise3.2.2ai)Forthesingleattributeswehave{A}+=ABCD,{B}+=BCD,{C}+=C,and{D}+=D.Thus,thenewdependenciesareACandAD.Nowconsiderpairsofattributes:{AB}+=ABCD,{AC}+=ABCD,{AD}+=ABCD,{BC}+=BCD,{BD}+=BCD,{CD}+=CD.ThusthenewdependenciesareABC,ABD,ACB,ACD,ADB,ADC,BCDandBDC.Forthetriplesofattributes,{BCD}+=BCD,buttheclosuresoftheothersetsareeachABCD.Thus,wegetnewdependenciesABCD,ABDC,andACDB.Since{ABCD}+=ABCD,wegetnonewdependencies.Thecollectionof13newdependenciesmentionedaboveare:AC,AD,ABC,ABD,ACB,ACD,ADB,ADC,BCD,BDC,ABCD,ABDCandACDB.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.ThusthenewdependenciesareABD,ADC,BCAandCDB.Forthetriplesofattributes,alltheclosuresofthesetsareeachABCD.Thus,wegetnewdependenciesABCD,ABDC,ACDBandBCDA.Since{ABCD}+=ABCD,wegetnonewdependencies.Thecollectionof8newdependenciesmentionedaboveare:ABD,ADC,BCA,CDB,ABCD,ABDC,ACDBandBCDA.iii)Forthesingleattributeswehave{A}+=ABCD,{B}+=ABCD,{C}+=ABCD,and{D}+=ABCD.Thus,thenewdependenciesareAC,AD,BD,BA,CA,CB,DBandDC.Sinceallthesingleattributes’closuresareABCD,anysupersetofthesingleattributeswillalsoleadtoaclosureofABCD.Knowingthis,wecanenumeratetherestofthenewdependencies.Thecollectionof24newdependenciesmentionedaboveare:AC,AD,BD,BA,CA,CB,DB,DC,ABC,ABD,ACB,ACD,ADB,ADC,BCA,BCD,BDA,BDC,CDA,CDB,ABCD,ABDC,ACDBandBCDA.Exercise3.2.2bi)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.2ci)Thesuperkeysareallthoseset