数据库系统基础教程第四章答案Word格式文档下载.doc
《数据库系统基础教程第四章答案Word格式文档下载.doc》由会员分享,可在线阅读,更多相关《数据库系统基础教程第四章答案Word格式文档下载.doc(45页珍藏版)》请在冰点文库上搜索。
(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
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.
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.Fromtransitivityofrelationships,thereisamany-to-onerelationshipbetweenBabiesandMothers.Henceababyhasauniquemotherwhileabirthcanallowmorethanonebaby.
4.3.1
Acaptaincannotexistwithoutateam.Howeveraplayercan(freeagent).Arecentlyformed(ordefunct)teamcanexistwithoutplayersorcolors.
Childrencanexistwithoutmotherandfather(unknown).
4.3.2
ThekeysofbothE1andE2arerequiredforuniquelyidentifyingtuplesinR
ThekeyofE1
ThekeyofE2
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
4.4.4
4.5.1
Customers(SSNo,name,addr,phone)
Flights(number,day,aircraft)
Bookings(custSSNo,flightNo,flightDay,row,seat)
RelationsfortoCustandtoFltrelationshipsarenotrequiredsincetheweakentitysetBookingsalreadycontainsthekeysofCustomersandFlights.
4.5.2
(a)
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
Stars(name,addr)
Studios(name,addr)
Movies(title,year,length,genre)
Contracts(starName,movieTitle,movieYear,studioName,salary)
DependingonotherrelationshipsnotshowninERdiagram,studioNamemaynotberequiredasakeyofContracts(ornotevenrequiredasanattributeofContracts).
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.
LabCourses(number,deptName,room,allocation)
(c)CoursesandLabCoursesarecombinedintoonerelation.
Courses(number,deptName,room,allocation)
4.6.2
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.
ApersoncannotbebothMotherandFather.
PersonChild(name,address)
PersonChildFather(name,address)
PersonChildMother(name,address)
PersonFather(nam