数据库系统基础教程第四章答案.docx
- 文档编号:11810410
- 上传时间:2023-04-02
- 格式:DOCX
- 页数:42
- 大小:514.35KB
数据库系统基础教程第四章答案.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)
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 系统 基础教程 第四 答案