数据库系统基础教程答案.docx

数据库系统基础教程答案.docx

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

数据库系统基础教程答案

Solutions

Chapter4

4.1.1

4.1.2

a）

b）

c）

Incweassumethataphoneandaddresscanonlybelongtoasinglecustomer（1-mrelationshiprepresentedbyarrowintocustomer）.

d）

Indweassumethatanaddresscanonlybelongtoonecustomerandaphonecanexistatonlyoneaddress.

Ifthemultiplicityofaboverelationshipswerem-to-n,theentitysetbecomesweakandthekeyssNoofcustomerswillbeneededaspartofthecompositekeyoftheentityset.

Inc&d,weconvertattributesphonesandaddressestoentitysets.Sinceentitysetsoftenbecomerelationsinrelationaldesign,

wemustconsidermoreefficientalternatives.

Insteadofqueryingmultipletableswherekeyvaluesareduplicated,wecanalsomodifyattributes:

（i）PhonesattributecanbeconvertedintoHomePhone,OfficePhoneandCellPhone.

（ii）Amultivaluedattributesuchasaliascanbekeptasanattributewhereasinglecolumncanbeusedinrelationaldesigni.e.concatenateallvalues.SQLallowsaquery"like'%Junius%'"tosearchthemultiplevaluesinacolumnalias.

4.1.3

4.1.4

a）

b）

c）

Therelationship"played"betweenTeamsandPlayersissimilartorelationship"plays"betweenTeamsandPlayers.

4.1.5

4.1.6TheinformationaboutchildrencanbeascertainedfrommotherOfandfatherOfrelationships.AttributessNoisrequiredsincenamesarenotunique.

4.1.7

4.1.8

a）

（b）

4.1.9

Assumptions

AProfessoronlyworksinatmostonedepartment.

AcoursehasatmostoneTA.

Acourseisonlytaughtbyoneprofessorandofferedbyonedepartment.

Studentsandprofessorshavebeenassigneduniqueemailids.

Acourseisuniquelyidentifiedbythecourseno,sectionno,andsemester（e.g.cs157-3spring09）.

4.1.10

Giventhatforeachmovie,auniquestudioexiststhatproducesthemovie.Eachstariscontractedtoatmostonestudio.

Butstarscouldbeunemployedatagiventime.Thusthefour-wayrelationshipinfig4.6canbeeasilyintoconvertedequivalentrelationships.

4.2.1

Redundancy:

TheowneraddressisrepeatedinAccSetsandAddressesentitysets.

Simplicity:

AccSetsdoesnotserveanyusefulpurposeandthedesigncanbemoresimplyrepresentedbycreatingmany-to-manyrelationshipbetweenCustomersandAccounts.

Rightkindofelement:

TheentitysetAddresseshasasingleattributeaddress.Acustomercannothavemorethanoneaddress.

HenceaddressshouldbeanattributeofentitysetCustomers.

Faithfulness:

Customerscannotbeuniquelyidentifiedbytheirnames.InrealworldCustomerswouldhaveauniqueattributesuchasssNoorcustomerNo

4.2.2

StudiosandPresidentscanbecombinedintooneentitysetStudioswithPresidentsbecominganattributeofStudiosunderfollowingcircumstances:

1.ThePresidentsentitysetonlycontainsasimpleattributeviz.presidentName.AdditionalattributesspecifictoPresidentsmightjustifymakingPresidentsintoanentityset.

4.2.3

4.2.4Theentitysetsshouldhavesingleattribute.

a）Stars:

starName

b）Movies:

movieName

c）Studios:

studioName.Howeverthereexistsamany-to-manyrelationshipbetweenStudiosandContracts.Hence,inaddition,weneedmoreinformationaboutstudiosinvolved.Ifacontractalwaysinvolvestwostudios,twoattributessuchasproducingStudioandstarStudiocanreplacethe

Studiosentityset.Ifacontactcanbeassociatedwithatmostfivestudios,itmaybepossibletoreplacetheStudiosentitysetbyfiveattributesviz.studio1,studio2,studio3,studio4,andstudio5.Alternately,acompositeattributecontainingconcatenationofallstudionamesinacontactcanbeconsidered.Aseparatorcharactersuchas"$"canbeused.SQLallowssearchingofsuchanattributeusingquerylike'%keyword%'

4.2.5

FromAugmentationruleofFunctionalDependency,

given

B->M（B=Baby,M=Mother）

then

BND->M（N=Nurse,D=Doctor）

Hencewecanjustputanarrowenteringmother.

a）PutanarrowenteringentitysetMothersforthesimplestsolution（Asinfig.4.4,whereamulti-wayrelationshipwasallowed,eventhoughMoviesalonecouldidentifytheStudio）.However,wecandisplaymoreaccurateinformationwithbelowfigure.

b）

c）

AgainfromAugmentationruleofFunctionalDependency,

given

BM->D

then

BMN->D

ThuswecanjustaddanarrowenteringDoctorstofig4.15.Belowfigurerepresentsmoreaccurateinformationhowever.

4.2.6

a）

b）TransitivityandAugmentationrulesofFunctionalDependencyallowarrowenteringMothersfromBirths.However,anewrelationshipinbelowfigurerepresentsmoreaccurateinformation.

c）

Designflawsinabcabove1.Assuggestedabove,usingTransitivityandAugmentationrulesofFunctionalDependency,muchsimplerdesignispossible.

4.2.7

Inbelowfigurethereexistsamany-to-onerelationshipbetweenBabiesandBirthsandanothermany-to-onerelationshipbetweenBirthsandMothers.Fromtransitivityofrelationships,thereisamany-to-onerelationshipbetweenBabiesandMothers.Henceababyhasauniquemotherwhileabirthcanallowmorethanonebaby.

4.3.1

a）

b）

Acaptaincannotexistwithoutateam.Howeveraplayercan（freeagent）.Arecentlyformed（ordefunct）teamcanexistwithoutplayersorcolors.

c）

Childrencanexistwithoutmotherandfather（unknown）.

4.3.2

a）

ThekeysofbothE1andE2arerequiredforuniquelyidentifyingtuplesinR

b）

ThekeyofE1

c）

ThekeyofE2

d）

ThekeyofeitherE1orE2

4.3.3

SpecialCase:

Allentitysetshavearrowsgoingintothemi.e.allrelationshipsare1-to-1

AnyKi

Otherwise:

CombinationofallKi'swheretheredoesnotexistanarrowgoingfromRtoEi.

4.4.1

No,gradeisnotpartofthekeyforenrollments.ThekeysofStudentsandCoursesbecomekeysoftheweakentitysetEnrollments.

4.4.2

ItispossibletomakeassignmentnumberaweakkeyofEnrollmentsbutthisisnotgooddesign（redundancysincemultipleassignmentscorrespondtoacourse）.AnewentitysetAssignmentiscreatedanditisalsoaweakentityset.HencethekeyattributesofAssignmentwillcomefromthestrongentitysetstowhichEnrollmentsisconnectedi.e.studentID,dept,andCourseNo.

4.4.3

a）

b）