完整word版数据库系统基础教程第四章答案.docx
《完整word版数据库系统基础教程第四章答案.docx》由会员分享，可在线阅读，更多相关《完整word版数据库系统基础教程第四章答案.docx（46页珍藏版）》请在冰点文库上搜索。
完整word版数据库系统基础教程第四章答案
Solutions
Chapter4
4.1.1
4.1.2
b）
c）
Incweassumethataphoneandaddresscanonlybelongtoasinglecustomer（1-
d）
Indweassumethatanaddresscanonlybelongtoonecustomerandaphonecanexistatonlyoneaddress.
Ifthemultiplicityofaboverelationshipswerem-to-n,theentitysetbecomesweakandthekeyssNoofcustomerswillbeneededaspartofthecompositekeyoftheentityset.
Inc&d,weconvertattributesphonesandaddressestoentitysets.Sinceentitysetsoftenbecomerelationsinrelationaldesign,wemustconsidermoreefficientalternatives.
Insteadofqueryingmultipletableswherekeyvaluesareduplicated,wecanalsomodifyattributes:
（i）PhonesattributecanbeconvertedintoHomePhone,OfficePhoneandCellPhone.
（ii）Amultivaluedattributesuchasaliascanbekeptasanattributewhereasinglecolumncanbeusedinrelationaldesigni.e.concatenateallvalues.SQL
4.1.3
4.1.4
a）
b）
1>:
i.-I
Tmains
OoLujt曰
Pla.yor3
uid^r
c）
Therelationship"played"betweenTeamsandPlayersissimilartorelationship"plays"betweenTeamsandPlayers.
4.1.5
I■
acxnnxzres
4.1.6TheinformationaboutchildrencanbeascertainedfrommotherOfandfatherOfrelationships.AttributessNoisrequiredsincenamesarenotunique.
People
4.1.7
4.1.8
a）
（b）
Family
name
Student;a
4.1.9
Assumptions
AProfessoronlyworksinatmostonedepartment.
AcoursehasatmostoneTA.
Acourseisonlytaughtbyoneprofessorandofferedbyonedepartment.Studentsandprofessorshavebeenassigneduniqueemailids.
Acourseisuniquelyidentifiedbythecourseno,sectionno,andsemester（e.gCS157-3spring09）.
■
—
Professors
4.1.10
Giventhatforeachmovie,auniquestudioexiststhatproducesthemovie.Eachstariscontractedtoatmostonestudio.
Butstarscouldbeunemployedatagiventime.Thusthefour-wayrelationshipinfig4.6canbeeasilyintoconvertedequivalentrelationships.
421
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,twoattributessuch
asproducingStudioandstarStudiocanreplacethe
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）
Mothers
c）
AgainfromAugmentationruleofFunctionalDependency,
given
BM->D
then
BMN->D
ThuswecanjustaddanarrowenteringDoctorstofig4.15.Belowfigure
426
a）
Births
B日bi*
Mothers
Nurses
b）TransitivityandAugmentationrulesofFunctionalDependencyallowarrowenteringMothersfromBirths.However,anewrelationshipinbelowfigure
Designflawsinabcabove1.Assuggestedabove,usingTransitivityandAugmentationrulesofFunctionalDependency,muchsimplerdesignispossible.
427
Inbelowfigurethereexistsamany-to-onerelationshipbetweenBabiesandBirthsandanothermany-to-onerelationshipbetweenBirthsandMothers.Fromtransitivityofrelationships,thereisamany-to-onerelationshipbetween
BabiesandMothers.Henceababyhasauniquemotherwhileabirthcanallow
b）
Acaptaincannotexistwithoutateam.Howeveraplayercan（freeagent）.Arecentlyformed（ordefunet）teamcanexistwithoutplayersorcolors.
432
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.ThekeysofStudentsand
442
ItispossibletomakeassignmentnumberaweakkeyofEnrollmentsbutthisisnotgooddesign（redundancysincemultipleassignmentscorrespondtoacourse）.AnewentitysetAssignmentiscreatedanditisalsoaweakentityset.HencethekeyattributesofAssignmentwillcomefromthestrongentitysetstowhich
Enrollmentsisconnectedi.e.studentID,dept,andCourseNo.
443
a）
1
Ba.b
d
ies
Mothers
Doctd3
b）
c）
444
b）
4.5.1
Customers（SSNo,name,addr,phone）
Flights（number,day,aircraft）
Bookings（custSSNo,flightNo,flightDay,row,seat）
RelationsfortoCustandtoFltrelationshipsarenotrequiredsincetheweakentitysetBookingsalreadycontainsthekeysofCustomersandFlights.
4.5.2
⑻
（b）
Schemaischanged.SincetoCustisnoIongeranidentifyingrelationship,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（pers
升级会员 