SolutionstoExercises文档格式.docx
《SolutionstoExercises文档格式.docx》由会员分享,可在线阅读,更多相关《SolutionstoExercises文档格式.docx(32页珍藏版)》请在冰豆网上搜索。
1.TheAddressesentitysetisnothingbutasingleaddress,sowewouldprefertomakeaddressanattributeofCustomers.Werethebanktorecordseveraladdressesforacustomer,thenitmightmakesensetohaveanAddressesentitysetandmakeLives-atamany-manyrelationship.
2.TheAcct-Setsentitysetisuseless.Eachcustomerhasauniqueaccountsetcontaininghisorheraccounts.However,relatingcustomersdirectlytotheiraccountsinamany-manyrelationshipconveysthesameinformationandeliminatestheaccount-setconceptaltogether.
Exercise2.2.2:
Underwhatcircumstances(regardingtheunseenattributesofStudiosandPresidents)wouldyourecommendcombiningthetwoentitysetsandrelationshipinFig.2.3intoasingleentitysetandattributes?
WhenthereisexactlyoneStudiosandexactlyonePresident,wecancombinethetwoentitysetsintoasingleentitysetandthepresidentcanbeoneattributeofentitysetStudios.
Exercise2.3.1:
ForyourE/Rdiagramsof:
a)Exercise2.1.1.
(i)Selectandspecifykeys,and(ii)Indicateappropriatereferentialintegrityconstraints.P.53
KeysssNoandnumberareappropriateforCustomersandAccounts,respectively.Also,wethinkitdoesnotmakesenseforanaccounttoberelatedtozerocustomers,soweshouldroundtheedgeconnectingOwnstoCustomers.Itdoesnotseeminappropriatetohaveacustomerwith0accounts;
theymightbeaborrower,forexample,soweputnoconstraintontheconnectionfromOwnstoAccounts.HereistheE/Rdiagram,showingunderlinedkeysandthenumerocityconstraint.
Exercise2.4.1:
Onewaytorepresentstudentsandthegradestheygetincoursesistouseentitysetscorrespondingtostudents,tocourses,andto“enrollments”,Enrollmententitiesforma“connecting”entitysetbetweenstudentsandcoursesandcanbeusedtorepresentnotonlythefactthatastudentistakingacertaincourse,butthegradeofthestudentinthecourse.DrawanE/Rdiagramforthissituation,indicatingweakentitysetsandthekeysfortheentitysets.Isthegradepartofthekeyforenrollment?
P.58
HereistheE/Rdiagram.WehaveomittedattributesotherthanourchoiceforthekeyattributesofStudentsandCourses.Alsoomittedarenamesfortherelationships.
AttributegradeisnotpartofthekeyforEnrollments.ThekeyforEnrollementsisstudIDfromStudentsanddeptandnumberfromCourses.
Chapter3TheRelationalDataModel
Exercise3.1.1:
InFig.3.3areinstancesoftworelationsthatmightconstitutepartofabankingdatabase.Indicatethefollowing:
P.64
a)Theattributeofeachrelation.
Accounts:
acctNo,type,balance
Customers:
firstName,lastName,idNo,account
b)Thetuplesofeachrelation.
Accounts:
(12345,savings,12000),(23456,checking,1000),(34567,savings,25)
(Robbie,Banks,901-222,12345),(Lena,Hand,805-333,12345),(Lena,Hand,805-333,23456)
c)Thecomponentsofonetuplefromeachrelations.
Thefirstofthethreetupleshasthreecomponents12345,savings,and12000forattributesacctNo,type,andbalanceofrelationAccounts.
ThefirstofthethreetupleshasfourcomponentsRobbie,Banks,901-222,and12345forattributesfirstName,lastName,idNo,andaccountofrelationCustomers.
d)Therelationschemaforeachrelation.
TherelationschemaforAccounts:
Accounts(acctNo,type,balance)
TherelationschemaforCustomers:
Customers(firstName,lastName,idNo,account)
e)Thedatabaseschema.
ThedatabaseschemaisAccounts(acctNo,type,balance),andCustomers(firstName,lastName,idNo,account)
f)Asuitabledomainforeachattribute.
ForAccounts:
acctNostring;
typestring;
balancereal
ForCustomers:
firstNamestring;
lastNamestring;
idNostring;
accountstring
g)Anotherequivalentwaytopresenteachrelation.
ForAccounts:
acctNo
Type
Balance
12345
Savings
12000
34567
savings
25
23456
checking
1000
firstName
lastName
idNo
account
Robbie
Banks
901-222
Lena
Hand
805-333
Exercise3.1.2:
Howmanydifferentways(consideringordersoftuplesandattributes)aretheretorepresentarelationinstanceifthatinstancehasthreeattributesandthreetuples.
tuples:
3!
=6
columns:
numberofpresentationis6*6=36
Exercise3.2.1:
ConverttheE/RdiagramofFig.3.11toarelationaldatabaseschema.P.75
Customers(ssNo,name,addr,phone)
Flights(number,day,aircraft)
Bookings(ssNo,number,day,row,seat)
Beingaweakentityset,Bookings'
relationhasthekeysforCustomersandFlightsandBookings'
ownattributes.
Exercise3.2.3:
TheE/RdiagramofFig.3.12representsships.Shipsaresaidtobesistersiftheyweredesignedfromthesameplans.Convertthisdiagramtoarelationaldatabaseschema.
Ships(name,yearLaunched)
SisterOf(name,sister_name)
Exercise3.3.1:
convertthediagramofFig.3.14torelationaldatabaseschema,usingeachofthefollowingapproaches:
P.80
a)Thestraight-E/Rmethod.
b)Theobject-orientedmethod.
c)Thenullsmethod.
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.4.2:
Considerarelationrepresentingthepresentpositionofmoleculesinaclosedcontainer.TheattributesareanIDforthemolecule,thex,y,andzcoordinatesofthemolecule,anditsvelocityinthex,y,andzdimensions.WhatFD’swouldyouexpecttohold?
Whatarethekeys?
P.89
SurelyIDisakeybyitself.However,wethinkthattheattributesx,y,andztogetherformanotherkey.Thereasonisthatatnotimecantwomoleculesoccupythesamepoint.
ID→xyz
ID→vx,vy,vz
xyz→vx,vy,vz
Exercise3.4.4:
InyourdatabaseschemaconstructedforExercise3.2.1,indicatethekeysyouwouldexpectforeachrelation.
Thekeyattributesareindicatedbycapitalizationintheschemabelow:
Customers(SSNO,name,address,phone)
Flights(NUMBER,DAY,aircraft)
Bookings(SSNO,NUMBER,DAY,row,seat)
Exercise3.5.1:
considerarelationwithschemaR(A,B,C,D)andFD’sAB→C,C→D,andD→A.P.100
a)WhatareallthenontrivialFD’sthatfollowfromthegivenFD’s?
YoushouldrestrictyourselftoFD’swithsingleattributesontherightside.
b)WhatareallthekeysofR?
c)WhatareallthesuperkeyforRthatarenotkeys?
For(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->
For(b),Fromtheanalysisofclosuresabove,wefindthatAB,BC,andBDarekeys.AllothersetseitherdonothaveABCDastheclosureorcontainoneofthesethreesets.
For(c),Thesuperkeysareallthosethatcontainoneofthosethre