SolutionstoExercises.docx

SolutionstoExercises.docx

《SolutionstoExercises.docx》由会员分享，可在线阅读，更多相关《SolutionstoExercises.docx（32页珍藏版）》请在冰豆网上搜索。

SolutionstoExercises

《IntroductiontoDatabaseSystem》

SolutionstoExercises

LiPing

Chapter2TheEntity-RelationshipDataModel

Exercise2.1.1:

Letusdesignadatabaseforabank,includinginformationaboutcustomersandtheiraccounts.Informationaboutacustomerincludestheirname,address,phone,andSocialSecuritynumber.Accountshavenumbers,types（e.g.,savings,checking）andbalances.Wealsoneedtorecordthecustomer（s）whoownanaccount.DrawtheE/Rdiagramforthisdatabase.Besuretoincludearrowswhereappropriate,toindicatethemultiplicityofarelationship.P.36

Solution:

Exercise2.1.6:

Supposewewishtokeepagenealogy.Weshallhaveoneentityset,People.Theinformationwewishtorecordaboutpersonsincludestheirname（anattribute）andthefollowingrelationships:

mother,father,andchildren.GivenanE/RdiagraminvolvingthePeopleentitysetandalltherelationshipsinwhichitisinvolved.Includerelationshipsformother,father,andchildren.Donotforgettoindicateroleswhenanentitysetisusedmorethanonceinarelationship.

Solution:

Exercise2.2.1:

InFig.2.14isanE/Rdiagramforabankdatabaseinvolvingcustomersandaccounts.Sincecustomersmayhaveseveralaccounts,andaccountsmaybeheldjointbyseveralcustomers,weassociatewitheachcustomeran“accountset”,andaccountsaremembersofoneormoreaccountsets.Assumingthemeaningofthevariousrelationshipsandattributesareasexpectedgiventheirnames,criticizethedesign.Whatdesignrulesareviolated?

Why?

Whatmodificationswouldyousuggest?

P.44

Solution:

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

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

Exercise2.2.2:

Underwhatcircumstances（regardingtheunseenattributesofStudiosandPresidents）wouldyourecommendcombiningthetwoentitysetsandrelationshipinFig.2.3intoasingleentitysetandattributes?

Solution:

WhenthereisexactlyoneStudiosandexactlyonePresident,wecancombinethetwoentitysetsintoasingleentitysetandthepresidentcanbeoneattributeofentitysetStudios.

Exercise2.3.1:

ForyourE/Rdiagramsof:

a）Exercise2.1.1.

（i）Selectandspecifykeys,and（ii）Indicateappropriatereferentialintegrityconstraints.P.53

Solution:

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

Solution:

HereistheE/Rdiagram.WehaveomittedattributesotherthanourchoiceforthekeyattributesofStudentsandCourses.Alsoomittedarenamesfortherelationships.

AttributegradeisnotpartofthekeyforEnrollments.ThekeyforEnrollementsisstudIDfromStudentsanddeptandnumberfromCourses.

Chapter3TheRelationalDataModel

Exercise3.1.1:

InFig.3.3areinstancesoftworelationsthatmightconstitutepartofabankingdatabase.Indicatethefollowing:

P.64

Solution:

a）Theattributeofeachrelation.

Accounts:

acctNo,type,balance

Customers:

firstName,lastName,idNo,account

b）Thetuplesofeachrelation.

Accounts:

（12345,savings,12000）,（23456,checking,1000）,（34567,savings,25）

Customers:

（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

ForCustomers:

firstName

lastName

idNo

account

Robbie

Banks

901-222

12345

Lena

Hand

805-333

23456

Lena

Hand

805-333

12345

Exercise3.1.2:

Howmanydifferentways（consideringordersoftuplesandattributes）aretheretorepresentarelationinstanceifthatinstancehasthreeattributesandthreetuples.

Solution:

tuples:

3!

=6

columns:

3!

=6

numberofpresentationis6*6=36

Exercise3.2.1:

ConverttheE/RdiagramofFig.3.11toarelationaldatabaseschema.P.75

Solution:

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.

Solution:

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.

Solution:

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.4.2:

Considerarelationrepresentingthepresentpositionofmoleculesinaclosedcontainer.TheattributesareanIDforthemolecule,thex,y,andzcoordinatesofthemolecule,anditsvelocityinthex,y,andzdimensions.WhatFD’swouldyouexpecttohold?

Whatarethekeys?

P.89

Solution:

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

ID→xyz

ID→vx,vy,vz

xyz→vx,vy,vz

Exercise3.4.4:

InyourdatabaseschemaconstructedforExercise3.2.1,indicatethekeysyouwouldexpectforeachrelation.

Solution:

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?

Solution:

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

For（b）,Fromtheanalysisofclosuresabove,wefindthatAB,BC,andBDarekeys.AllothersetseitherdonothaveABCDastheclosureorcontainoneofthesethreesets.

For（c）,Thesuperkeysareallthosethatcontainoneofthosethre