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

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

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

数据库系统基础教程第二版课后习题答案

DatabaseSystems:

TheCompleteBook

SolutionsforChapter2

SolutionsforSection2.1

Exercise2.1.1

TheE/RDiagram.

Exercise2.1.8（a）

TheE/RDiagram

Kobvxybz

SolutionsforSection2.2

Exercise2.2.1

TheAddressesentitysetisnothingbutasingleaddress,sowewouldprefertomakeaddressanattributeofCustomers.Werethebanktorecordseveraladdressesforacustomer,thenitmightmakesensetohaveanAddressesentitysetandmakeLives-atamany-manyrelationship.

TheAcct-Setsentitysetisuseless.Eachcustomerhasauniqueaccountsetcontaininghisorheraccounts.However,relatingcustomersdirectlytotheiraccountsinamany-manyrelationshipconveysthesameinformationandeliminatestheaccount-setconceptaltogether.

SolutionsforSection2.3

Exercise2.3.1（a）

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）.

DatabaseSystems:

TheCompleteBook

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:

Depts（name,chair）

Courses（number,deptName,room）

LabCourses（number,deptName,room,allocation）

Andfor（c）,CoursesandLabCoursesarecombined,as:

Depts（name,chair）

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->A.

Forthetriplesofattributes,ACD+=ACD,buttheclosuresoftheothersetsareeachABCD.Thus,wegetnewdependenciesABC->D,ABD->C,andBCD->A.

SinceABCD+=ABCD,wegetnonewdependencies.

Thecollectionof11newdependenciesmentionedaboveis:

C->A,AB->D,AC->D,BC->A,BC->D,BD->A,BD->C,CD->A,ABC->D,ABD->C,andBCD->A.

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

0

2

1

2

ThisrelationsatisfiesA->BbutdoesnotsatisfyB->A.

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:

C->AandAB->C.NotethatBC->Aistrue,butfollowslogicallyfromC->A,andthereforemaybeomittedfromourlist.

SolutionsforSection3.6

Exercise3.6.1（a）

InthesolutiontoExercise3.5.1wefoundthatthereare14nontrivialdependencies,includingthethreegivenonesand11deriveddependencies.Theseare:

C->A,C->D,D->A,AB->D,AB->C,AC->D,BC->A,BC->D,BD->A,BD->C,CD->A,ABC->D,ABD->C,andBCD->A.

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

C->A,C->D,D->A,AC->D,andCD->A.

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-