数据库系统基础教程第三章答案.docx
- 文档编号:28634062
- 上传时间:2023-07-19
- 格式:DOCX
- 页数:36
- 大小:29.39KB
数据库系统基础教程第三章答案.docx
《数据库系统基础教程第三章答案.docx》由会员分享,可在线阅读,更多相关《数据库系统基础教程第三章答案.docx(36页珍藏版)》请在冰豆网上搜索。
数据库系统基础教程第三章答案
Exercise3.1.1
Answersforthisexercisemayvarybecauseofdifferentinterpretations.
SomepossibleFDs:
SocialSecuritynumber✍name
Areacode✍state
Streetaddress,city,state✍zipcode
Possiblekeys:
{SocialSecuritynumber,streetaddress,city,state,areacode,phonenumber}
Needstreetaddress,city,statetouniquelydeterminelocation.Apersoncouldhavemultipleaddresses.Thesameistrueforphones.Thesedays,apersoncouldhavealandlineandacellularphone
Exercise3.1.2
Answersforthisexercisemayvarybecauseofdifferentinterpretations
SomepossibleFDs:
ID✍x-position,y-position,z-position
ID✍x-velocity,y-velocity,z-velocity
x-position,y-position,z-position✍ID
Possiblekeys:
{ID}
{x-position,y-position,z-position}
Thereasonwhythepositionswouldbeakeyisnotwomoleculescanoccupythesamepoint.
ThesuperkeysareanysubsetthatcontainsA1.Thus,thereare2(n-1)suchsubsets,sinceeachofthen-1attributesA2throughAnmayindependentlybechoseninorout.
ThesuperkeysareanysubsetthatcontainsA1orA2.Thereare2(n-1)suchsubsetswhenconsideringA1andthen-1attributesA2throughAn.Thereare2(n-2)suchsubsetswhenconsideringA2andthen-2attributesA3throughAn.WedonotcountA1inthesesubsetsbecausetheyarealreadycountedinthefirstgroupofsubsets.Thetotalnumberofsubsetsis2(n-1)+2(n-2).
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).
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).
Wecouldtryinferencerulestodeducenewdependenciesuntilwearesatisfiedwehavethemall.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.
Fromtheanalysisofclosuresabove,wefindthatAB,BC,andBDarekeys.AllothersetseitherdonothaveABCDastheclosureorcontainoneofthesethreesets.
Thesuperkeysareallthosethatcontainoneofthosethreekeys.Thatis,asuperkeythatisnotakeymustcontainBandmorethanoneofA,C,andD.Thus,the(proper)superkeysareABC,ABD,BCD,andABCD.
i)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.
SinceA1A2…AnCcontainsA1A2…An,thentheclosureofA1A2…AnCcontainsB.ThusitfollowsthatA1A2…AnC✍B.
1A2…AnC✍B.Usingtheconceptoftrivialdependencies,wecanshowthatA1A2…AnC✍C.ThusA1A2…AnC✍BC.
FromA1A2…AnE1E2…Ej,weknowthattheclosurecontainsB1B2…BmbecauseoftheFDA1A2…An✍B1B2…Bm.TheB1B2…BmandtheE1E2…EjcombinetoformtheC1C2…Ck.ThustheclosureofA1A2…AnE1E2…EjcontainsDaswell.Thus,A1A2…AnE1E2…Ej✍D.
FromA1A2…AnC1C2…Ck,weknowthattheclosurecontainsB1B2…BmbecauseoftheFDA1A2…An✍B1B2…Bm.TheC1C2…CkalsotellusthattheclosureofA1A2…AnC1C2…CkcontainsD1D2…Dj.Thus,A1A2…AnC1C2…Ck✍B1B2…BkD1D2…Dj.
IfattributeArepresentedSocialSecurityNumberandBrepresentedaperson’sname,thenwewouldassumeA✍BbutB✍AwouldnotbevalidbecausetheremaybemanypeoplewiththesamenameanddifferentSocialSecurityNumbers.
LetattributeArepresentSocialSecurityNumber,BrepresentgenderandCrepresentname.SurelySocialSecurityNumberandgendercanuniquelyidentifyaperson’sname(i.e.AB✍C).ASocialSecurityNumbercanalsouniquelyidentifyaperson’sname(i.e.A✍C).However,genderdoesnotuniquelydetermineaname(i.e.B✍Cisnotvalid).
LetattributeArepresentlatitudeandBrepresentlongitude.Together,bothattributescanuniquelydetermineC,apointontheworldmap(i.e.AB✍C).However,neitherAnorBcanuniquelyidentifyapoint(i.e.A✍CandB✍Carenotvalid).
Exercise3.2.5
GivenarelationwithattributesA1A2…An,wearetoldthattherearenofunctionaldependenciesoftheformB1B2…Bn-1✍CwhereB1B2…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…Cm✍A1A2…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,itmustbethat(X+)+=X+orelseX+isnottheclosureofX.
Ifallsetsofattributesareclosed,thentherecannotbeanynontrivialfunctionaldependencies.SupposeA1A2...An✍Bisanontrivialdependency.Then{A1A2...An}+containsBandthusA1A2...Anisnotclosed.
Iftheonlyclosedsetsare?
and{A,B,C,D},thenthefollowingFDshold:
A✍BA✍CA✍D
B✍AB✍CB✍D
C✍AC✍BC✍D
D✍AD✍BD✍C
AB✍CAB✍D
AC✍BAC✍D
AD✍BAD✍C
BC✍ABC✍D
BD✍ABD✍C
CD✍ACD✍B
ABC✍D
ABD✍C
ACD✍B
BCD✍A
Iftheonlyclosedsetsare?
{A,B}and{A,B,C,D},thenthefollowingFDshold:
A✍B
B✍A
C✍AC✍BC✍D
D✍AD✍BD✍C
AC✍BAC✍D
AD✍BAD✍C
BC✍ABC✍D
BD✍ABD✍C
CD✍ACD✍B
ABC✍D
ABD✍C
ACD✍B
BCD✍A
Exercise3.2.9
WecanthinkofthisproblemasasituationwheretheattributesA,B,Crepresentcitiesandthefunctionaldependenciesrepresentonewaypathsbetweenthecities.Theminimalbasesaretheminimalnumberofpathwaysthatareneededtoconnectthecities.Wedonotwanttocreateanotherroadwayifthetwocitiesarealreadyconnected.
Thesystematicwaytodothiswouldbetocheckallpossible
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 系统 基础教程 第三 答案