数据库系统基础教程第二版课后习题答案.docx
- 文档编号:7190216
- 上传时间:2023-01-21
- 格式:DOCX
- 页数:56
- 大小:57.21KB
数据库系统基础教程第二版课后习题答案.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->childnameandchi
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 系统 基础教程 第二 课后 习题 答案