数据库系统基础教程第二版课后习题答案Word下载.docx

数据库系统基础教程第二版课后习题答案Word下载.docx

《数据库系统基础教程第二版课后习题答案Word下载.docx》由会员分享，可在线阅读，更多相关《数据库系统基础教程第二版课后习题答案Word下载.docx（56页珍藏版）》请在冰豆网上搜索。

KeysssNoandnumberareappropriateforCustomersandAccounts,respectively.Also,wethinkitdoesnotmakesenseforanaccounttoberelatedtozerocustomers,soweshouldroundtheedgeconnectingOwnstoCustomers.Itdoesnotseeminappropriatetohaveacustomerwith0accounts;

theymightbeaborrower,forexample,soweputnoconstraintontheconnectionfromOwnstoAccounts.HereistheTheE/RDiagram，

showingunderlinedkeysandthenumerocityconstraint.

Exercise2.3.2（b）

IfRismany-onefromE1toE2,thentwotuples（e1,e2）and（f1,f2）oftherelationshipsetforRmustbethesameiftheyagreeonthekeyattributesforE1.Toseewhy,surelye1andf1arethesame.BecauseRismany-onefromE1toE2,e2andf2mustalsobethesame.Thus,thepairsarethesame.

SolutionsforSection2.4

Exercise2.4.1

HereistheTheE/RDiagram.

WehaveomittedattributesotherthanourchoiceforthekeyattributesofStudentsandCourses.Alsoomittedarenamesfortherelationships.AttributegradeisnotpartofthekeyforEnrollments.ThekeyforEnrollementsisstudIDfromStudentsanddeptandnumberfromCourses.

Exercise2.4.4b

HereistheTheE/RDiagramAgain,wehaveomittedrelationshipnamesandattributesotherthanourchoiceforthekeyattributes.ThekeyforLeaguesisitsownname;

thisentitysetisnotweak.ThekeyforTeamsisitsownnameplusthenameoftheleagueofwhichtheteamisapart,e.g.,（Rangers,MLB）or（Rangers,NHL）.ThekeyforPlayersconsistsoftheplayer'

snumberandthekeyfortheteamonwhichheorsheplays.Sincethelatterkeyisitselfapairconsistingofteamandleaguenames,thekeyforplayersisthetriple（number,teamName,leagueName）.e.g.,JeffGarciais（5,49ers,NFL）.

SolutionsforChapter3

SolutionsforSection3.1

Exercise3.1.2（a）

Wecanorderthethreetuplesinanyof3!

=6ways.Also,thecolumnscanbeorderedinanyof3!

=6ways.Thus,thenumberofpresentationsis6*6=36.

SolutionsforSection3.2

Exercise3.2.1

Customers（ssNo,name,address,phone）

Flights（number,day,aircraft）

Bookings（ssNo,number,day,row,seat）

Beingaweakentityset,Bookings'

relationhasthekeysforCustomersandFlightsandBookings'

ownattributes.

NoticethattherelationsobtainedfromthetoCustandtoFltrelationshipsareunnecessary.Theyare:

toCust（ssNo,ssNo1,number,day）

toFlt（ssNo,number,day,number1,day1）

Thatis,fortoCust,thekeyofCustomersispairedwiththekeyforBookings.SincebothincludessNo,thisattributeisrepeatedwithtwodifferentnames,ssNoandssNo1.AsimilarsituationexistsfortoFlt.

Exercise3.2.3

Ships（name,yearLaunched）

SisterOf（name,sisterName）

SolutionsforSection3.3

Exercise3.3.1

SinceCoursesisweak,itskeyisnumberandthenameofitsdepartment.WedonothavearelationforGivenBy.Inpart（a）,thereisarelationforCoursesandarelationforLabCoursesthathasonlythekeyandthecomputer-allocationattribute.Itlookslike:

Depts（name,chair）

Courses（number,deptName,room）

LabCourses（number,deptName,allocation）

Forpart（b）,LabCoursesgetsalltheattributesofCourses,as:

LabCourses（number,deptName,room,allocation）

Andfor（c）,CoursesandLabCoursesarecombined,as:

Courses（number,deptName,room,allocation）

Exercise3.3.4（a）

Thereisonerelationforeachentityset,sothenumberofrelationsise.Therelationfortherootentitysethasaattributes,whiletheotherrelations,whichmustincludethekeyattributes,havea+kattributes.

SolutionsforSection3.4

Exercise3.4.2

SurelyIDisakeybyitself.However,wethinkthattheattributesx,y,andztogetherformanotherkey.Thereasonisthatatnotimecantwomoleculesoccupythesamepoint.

Exercise3.4.4

Thekeyattributesareindicatedbycapitalizationintheschemabelow:

Customers（SSNO,name,address,phone）

Flights（NUMBER,DAY,aircraft）

Bookings（SSNO,NUMBER,DAY,row,seat）

Exercise3.4.6（a）

ThesuperkeysareanysubsetthatcontainsA1.Thus,thereare2^{n-1}suchsubsets,sinceeachofthen-1attributesA2throughAnmayindependentlybechoseninorout.

SolutionsforSection3.5

Exercise3.5.1（a）

Wecouldtryinferencerulestodeducenewdependenciesuntilwearesatisfiedwehavethemall.Amoresystematicwayistoconsidertheclosuresofall15nonemptysetsofattributes.

ForthesingleattributeswehaveA+=A,B+=B,C+=ACD,andD+=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->

Forthetriplesofattributes,ACD+=ACD,buttheclosuresoftheothersetsareeachABCD.Thus,wegetnewdependenciesABC->

D,ABD->

C,andBCD->

SinceABCD+=ABCD,wegetnonewdependencies.

Thecollectionof11newdependenciesmentionedaboveis:

C->

A,AB->

D,AC->

D,BC->

A,BC->

D,BD->

A,BD->

C,CD->

A,ABC->

Exercise3.5.1（b）

Fromtheanalysisofclosuresabove,wefindthatAB,BC,andBDarekeys.AllothersetseitherdonothaveABCDastheclosureorcontainoneofthesethreesets.

Exercise3.5.1（c）

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

Exercise3.5.3（a）

WemustcomputetheclosureofA1A2...AnC.SinceA1A2...An->

Bisadependency,surelyBisinthisset,provingA1A2...AnC->

B.

Exercise3.5.4（a）

Considertherelation

A

B

2

1

ThisrelationsatisfiesA->

BbutdoesnotsatisfyB->

Exercise3.5.8（a）

Ifallsetsofattributesareclosed,thentherecannotbeanynontrivialfunctionaldependencies.ForsupposeA1A2...An->

Bisanontrivialdependency.ThenA1A2...An+containsBandthusA1A2...Anisnotclosed.

Exercise3.5.10（a）

Weneedtocomputetheclosuresofallsubsetsof{ABC},althoughthereisnoneedtothinkabouttheemptysetorthesetofallthreeattributes.Herearethecalculationsfortheremainingsixsets:

A+=A

B+=B

C+=ACE

AB+=ABCDE

AC+=ACE

BC+=ABCDE

WeignoreDandE,soabasisfortheresultingfunctionaldependenciesforABCare:

AandAB->

C.NotethatBC->

Aistrue,butfollowslogicallyfromC->

A,andthereforemaybeomittedfromourlist.

SolutionsforSection3.6

Exercise3.6.1（a）

InthesolutiontoExercise3.5.1wefoundthatthereare14nontrivialdependencies,includingthethreegivenonesand11deriveddependencies.Theseare:

A,C->

D,D->

D,AB->

C,AC->

WealsolearnedthatthethreekeyswereAB,BC,andBD.Thus,anydependencyabovethatdoesnothaveoneofthesepairsontheleftisaBCNFviolation.Theseare:

A,AC->

D,andCD->

OnechoiceistodecomposeusingC->

D.ThatgivesusABCandCDasdecomposedrelations.CDissurelyinBCNF,sinceanytwo-attributerelationis.ABCisnotinBCNF,sinceABandBCareitsonlykeys,butC->

AisadependencythatholdsinABCDandthereforeholdsinABC.WemustfurtherdecomposeABCintoACandBC.Thus,thethreerelationsofthedecompositionareAC,BC,andCD.

SinceallattributesareinatleastonekeyofABCD,thatrelationisalreadyin3NF,andnodecompositionisnecessary.

Exercise3.6.1（b）

（Revised1/19/02）TheonlykeyisAB.Thus,B->

CandB->

DarebothBCNFviolations.ThederivedFD'

sBD->

CandBC->

DarealsoBCNFviolations.However,anyothernontrivial,derivedFDwillhaveAandBontheleft,andthereforewillcontainakey.

OnepossibleBCNFdecompositionisABandBCD.Itisobtainedstartingwithanyofthefourviolationsmentionedabove.ABistheonlykeyforAB,andBistheonlykeyforBCD.

SincethereisonlyonekeyforABCD,the3NFviolationsarethesame,andsoisthedecomposition.

SolutionsforSection3.7

Exercise3.7.1

SinceA->

->

B,andallthetupleshavethesamevalueforattributeA,wecanpairtheB-valuefromanytuplewiththevalueoftheremainingattributeCfromanyothertuple.Thus,weknowthatRmusthaveatleasttheninetuplesoftheform（a,b,c）,wherebisanyofb1,b2,orb3,andcisanyofc1,c2,orc3.Thatis,wecanderive,usingthedefinitionofamultivalueddependency,thateachofthetuples（a,b1,c2）,（a,b1,c3）,（a,b2,c1）,（a,b2,c3）,（a,b3,c1）,and（a,b3,c2）arealsoinR.

Exercise3.7.2（a）

First,peoplehaveuniqueSocialSecuritynumbersanduniquebirthdates.Thus,weexpectthefunctionaldependenciesssNo->

nameandssNo->

birthdatehold.Thesameappliestochildren,soweexpectchildSSNo->

childnameandchildSSNo-