数据库系统基础教程第四章答案.docx
《数据库系统基础教程第四章答案.docx》由会员分享，可在线阅读，更多相关《数据库系统基础教程第四章答案.docx（42页珍藏版）》请在冰豆网上搜索。
数据库系统基础教程第四章答案
数据库系统基础教程第四章答案
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）
c）
4.4.4
a）
b）
4.5.1
Customers（SSNo,name,addr,phone）
Flights（number,day,aircraft）
Bookings（custSSNo,flightNo,flightDay,row,seat）
RelationsfortoCustandtoFltrelationshipsarenotrequiredsincetheweakentitysetBookingsalreadycontainsthekeysofCustomersandFlights.
4.5.2
（a）
（b）
Schemaischanged.SincetoCustisnolongeranidentifyingrelationship,SSNoisnolongerapartofBookingsrelation.
Bookings（flightNo,flightDay,row,seat）
ToCust（custSSNO,flightNo,flightDay,row,seat）
Theaboverelationsaremergedinto
Bookings（flightNo,flightDay,row,seat,custSSNo）
HowevercustSSNoisnolongerakeyofBookingsrelation.Itbecomesaforeignkeyinstead.
4.5.3
Ships（name,yearLaunched）
SisterOf（name,sisterName）
4.5.4
（a）
Stars（name,addr）
Studios（name,addr）
Movies（title,year,length,genre）
Contracts（starName,movieTitle,movieYear,studioName,salary）
DependingonotherrelationshipsnotshowninERdiagram,studioNamemaynotberequiredasakeyofContracts（ornotevenrequiredasanattributeofContracts）.
（b）
Students（studentID）
Courses（dept,courseNo）
Enrollments（studentID,dept,courseNo,grade）
（c）
Departments（name）
Courses（deptName,number）
（d）
Leagues（name）
Teams（leagueName,teamName）
Players（leagueName,teamName,playerName）
4.6.1
TheweakrelationCourseshasthekeyfromDeptsalongwithnumber.HencethereisnorelationforGivenByrelationship.
（a）
Depts（name,chair）
Courses（number,deptName,room）
LabCourses（number,deptName,allocation）
（b）LabCourseshasalltheattributesofCourses.
Depts（name,chair）
Courses（number,deptName,room）
LabCourses（number,deptName,room,allocation）
（c）CoursesandLabCoursesarecombinedintoonerelation.
Depts（name,chair）
Courses（number,deptName,room,allocation）
4.6.2
（a）
Person（name,address）
ChildOf（personName,personAddress,childName,childAddress）
Child（name,address,fatherName,fatherAddress,motherName,motherAddresss）
Father（name,address,wifeName,wifeAddresss）
Mother（name,address）
SinceFatherOfandMotherOfaremany-onerelationshipsfromChild,thereisnoneedforaseparaterelationforthem.Similarlytheone-onerelationshipMarriedcanbeincludedinFather（orMother）.ChildOfisamany-manyrelationshipandneedsaseparaterelation.
HowevertheChildOfrelationisnotrequiredsincetherelationshipcanbededucedfromFatherOfandMotherOfrelationshipscontainedinChildrelation.
（b）
ApersoncannotbebothMotherandFather.
Person（name,address）
PersonChild（name,address）
PersonChildFather（name,address）
PersonChildMother（name,address）
PersonFather（name,address）
PersonMother（name,address）
ChildOf（personName,personAddress,childName,childAddress）
FatherOf（childName,childAddress,fatherName,fatherAddress）
MotherOf（childName,childAddress,motherName,motherAddress）
Married（husbandName,husbandAddress,wifeName,wifeAddress）
Themany-manyChildOfrelationshipagainrequiresarelation.
Anentitybelongstooneandonlyoneclasswhenusingobject-orientedapproach.Hence,themany-onerelationsMotherOfandFatherOfcouldbeaddedasattributestoPersonChild,PersonChildFather,andPersonChildMotherrelations.
SimilarlytheMarriedrelationcanbeaddedasattributestoPersonChildMotherandPersonMother（orthecorrespondingfatherrelations）.
（c）ForthePersonrelationatleastoneofhusbandandwifeattributeswillbenull.
Person（personName,personAddress,fatherName,fatherAddress,motherName,motherAddresss,wifeName,wifeAddresss,husbandName,husbandAddress）
ChildOf（personName,personAddress,childName,childAddress）
升级会员 