数据库系统基础教程第四章答案Word格式.docx

数据库系统基础教程第四章答案Word格式.docx

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

Incweassumethataphoneandaddresscanonlybelongtoasinglecustomer（1-mrelationshiprepresentedbyarrowintocustomer）、

d）

Indweassumethatanaddresscanonlybelongtoonecustomerandaphonecanexistatonlyoneaddress、

Ifthemultiplicityofaboverelationshipswerem-to-n,theentitysetbeesweakandthekeyssNoofcustomerswillbeneededaspartofthepositekeyoftheentityset、

Inc&

d,weconvertattributesphonesandaddressestoentitysets、Sinceentitysetsoftenbeerelationsinrelationaldesign,

wemustconsidermoreefficientalternatives、

Insteadofqueryingmultipletableswherekeyvaluesareduplicated,wecanalsomodifyattributes:

（i）PhonesattributecanbeconvertedintoHomePhone,OfficePhoneandCellPhone、

（ii）Amultivaluedattributesuchasaliascanbekeptasanattributewhereasinglecolumncanbeusedinrelationaldesigni、e、concatenateallvalues、SQLallowsaquery"

like'

%Junius%'

"

tosearchthemultiplevaluesinacolumnalias、

4、1、3

4、1、4

Therelationship"

played"

betweenTeamsandPlayersissimilartorelationship"

plays"

betweenTeamsandPlayers、

4、1、5

4、1、6TheinformationaboutchildrencanbeascertainedfrommotherOfandfatherOfrelationships、AttributessNoisrequiredsincenamesarenotunique、

4、1、7

4、1、8

（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

StudiosandPresidentscanbebinedintooneentitysetStudioswithPresidentsbeinganattributeofStudiosunderfollowingcircumstances:

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,apositeattributecontainingconcatenationofallstudionamesinacontactcanbeconsidered、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、

AgainfromAugmentationruleofFunctionalDependency,

BM->

D

then

BMN->

D

ThuswecanjustaddanarrowenteringDoctorstofig4、15、Belowfigurerepresentsmoreaccurateinformationhowever、

4、2、6

b）TransitivityandAugmentationrulesofFunctionalDependencyallowarrowenteringMothersfromBirths、However,anewrelationshipinbelowfigurerepresentsmoreaccurateinformation、

Designflawsinabcabove1、Assuggestedabove,usingTransitivityandAugmentationrulesofFunctionalDependency,muchsimplerdesignispossible、

4、2、7

Inbelowfigurethereexistsamany-to-onerelationshipbetweenBabiesandBirthsandanothermany-to-onerelationshipbetweenBirthsandMothers、Fromtransit