数据库系统基础教程第四章答案

数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案数据库系统基础教程第四章答案编制仅供参考审核批准生效日期地址：电话：传真:邮编:SolutionsChapter4cweassumethataphoneandaddresscanonlybelongtoasinglecustomer(1-mrelationshiprepresentedbyarrowintocustomer).d)Indweassumethatanaddresscanonlybelongtoonecustomerandaphonecanexistatonlyoneaddress.Ifthemultiplicityofaboverelationshipswerem-to-n,theentitysetbecomesweakandthekeyssNoofcustomerswillbeneededaspartofthecompositekeyoftheentityset.Inc&d,weconvertattributesphonesandaddressestoentitysets.Sinceentitysetsoftenbecomerelationsinrelationaldesign,wemustconsidermoreefficientalternatives.Insteadofqueryingmultipletableswherekeyvaluesareduplicated,wecanalsomodifyattributes:(i)PhonesattributecanbeconvertedintoHomePhone,OfficePhoneandCellPhone.(ii)Amultivaluedattributesuchasaliascanbekeptasanattributewhereasinglecolumncanbeusedinrelationaldesign.concatenateallvalues.SQLallowsaquery"like'%Junius%'"tosearchthemultiplevaluesinacolumnalias. a)b)c)Therelationship"played"betweenTeamsandPlayersissimilartorelationship"plays"betweenTeamsandPlayers. TheinformationaboutchildrencanbeascertainedfrommotherOfandfatherOfrelationships.AttributessNoisrequiredsincenamesarenotunique.Professoronlyworksinatmostonedepartment.AcoursehasatmostoneTA.Acourseisonlytaughtbyoneprofessorandofferedbyonedepartment.Studentsandprofessorshavebeenassigneduniqueemailids.Acourseisuniquelyidentifiedbythecourseno,sectionno,andsemester.cs157-3spring09).thatforeachmovie,auniquestudioexiststhatproducesthemovie.Eachstariscontractedtoatmostonestudio.Butstarscouldbeunemployedatagiventime.Thusthefour-wayrelationshipinfigcanbeeasilyintoconvertedequivalentrelationships.TheowneraddressisrepeatedinAccSetsandAddressesentitysets.Simplicity:AccSetsdoesnotserveanyusefulpurposeandthedesigncanbemoresimplyrepresentedbycreatingmany-to-manyrelationshipbetweenCustomersandAccounts.Rightkindofelement:TheentitysetAddresseshasasingleattributeaddress.Acustomercannothavemorethanoneaddress.HenceaddressshouldbeanattributeofentitysetCustomers.Faithfulness:Customerscannotbeuniquelyidentifiedbytheirnames.InrealworldCustomerswouldhaveauniqueattributesuchasssNoorcustomerNoandPresidentscanbecombinedintooneentitysetStudioswithPresidentsbecominganattributeofStudiosunderfollowingcircumstances:1.ThePresidentsentitysetonlycontainsasimpleattributeviz.presidentName.AdditionalattributesspecifictoPresidentsmightjustifymakingPresidentsintoanentityset.Theentitysetsshouldhavesingleattribute.a)Stars:starNameb)Movies:movieNamec)Studios:studioName.Howeverthereexistsamany-to-manyrelationshipbetweenStudiosandContracts.Hence,inaddition,weneedmoreinformationaboutstudiosinvolved.Ifacontractalwaysinvolvestwostudios,twoattributessuchasproducingStudioandstarStudiocanreplacetheStudiosentityset.Ifacontactcanbeassociatedwithatmostfivestudios,itmaybepossibletoreplacetheStudiosentitysetbyfiveattributesviz.studio1,studio2,studio3,studio4,andstudio5.Alternately,acompositeattributecontainingconcatenationofallstudionamesinacontactcanbeconsidered.Aseparatorcharactersuchas"$"canbeused.SQLallowssearchingofsuchanattributeusingquerylike'%keyword%'AugmentationruleofFunctionalDependency,givenB->M(B=Baby,M=Mother)thenBND->M(N=Nurse,D=Doctor)Hencewecanjustputanarrowenteringmother.a)PutanarrowenteringentitysetMothersforthesimplestsolution(Asinfig.,whereamulti-wayrelationshipwasallowed,eventhoughMoviesalonecouldidentifytheStudio).However,wecandisplaymoreaccurateinformationwithbelowfigure.b)c)AgainfromAugmentationruleofFunctionalDependency,givenBM->DthenBMN->DThuswecanjustaddanarrowenteringDoctorstofig.Belowfigurerepresentsmoreaccurateinformationhowever.TransitivityandAugmentationrulesofFunctionalDependencyallowarrowenteringMothersfromBirths.However,anewrelationshipinbelowfigurerepresentsmoreaccurateinformation.c)Designflawsinabcabove1.Assuggestedabove,usingTransitivityandAugmentationrulesofFunctionalDependency,muchsimplerdesignispossible. Inbelowfigurethereexistsamany-to-onerelationshipbetweenBabiesandBirthsandanothermany-to-onerelationshipbetweenBirthsandMothers.Fromtransitivityofrelationships,thereisamany-to-onerelationshipbetweenBabiesandMothers.Henceababyhasauniquemotherwhileabirthcanallowmorethanonebaby.captaincannotexistwithoutateam.Howeveraplayercan(freeagent).Arecentlyformed(ordefunct)teamcanexistwithoutplayersorcolors.c)Childrencanexistwithoutmotherandfather(unknown).keysofbothE1andE2arerequiredforuniquelyidentifyingtuplesinRb)ThekeyofE1c)ThekeyofE2d)ThekeyofeitherE1orE2Case:Allentitysetshavearrowsgoingintothem.allrelationshipsare1-to-1AnyKiOtherwise:CombinationofallKi'swheretheredoesnotexistanarrowgoingfromRtoEi.gradeisnotpartofthekeyforenrollments.ThekeysofStudentsandCoursesbecomekeysoftheweakentitysetEnrollments.ItispossibletomakeassignmentnumberaweakkeyofEnrollmentsbutthisisnotgooddesign(redundancysincemultipleassignmentscorrespondtoacourse).AnewentitysetAssignmentiscreatedanditisalsoaweakentityset.HencethekeyattributesofAssignmentwillcomefromthestrongentitysetstowhichEnrollmentsisconnected.studentID,dept,andCourseNo. fortoCustandtoFltrelationshipsarenotrequiredsincetheweakentitysetBookingsalreadycontainsthekeysofCustomersandFlights.ischanged.SincetoCustisnolongeranidentifyingrelationship,SSNoisnolongerapartofBookingsrelation.Bookings(flightNo,flightDay,row,seat)ToCust(custSSNO,flightNo,flightDay,row,seat)TheaboverelationsaremergedintoBookings(flightNo,flightDay,row,seat,custSSNo)HowevercustSSNoisnolongerakeyofBookingsrelation.Itbecomesaforeignkeyinstead.Ships(name,yearLaunched)SisterOf(name,sisterName)onotherrelationshipsnotshowninERdiagram,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)weakrelationCourseshasthekeyfromDeptsalongwithnumber.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)FatherOfandMotherOfaremany-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)cannotbelongtobothmaleandfemalebranchoftheERdiagram.Moreoversinceanentitybelongstooneandonlyoneclasswhenusingobject-orientedapproach,noentitybelongstoPeoplerelation.AgainwecouldreplaceMotherOfandFatherOfrelationsbyaddingasattributestoPeopleMale,PeopleMaleFathers,PeopleFemale,andPeopleFemaleMothersrelations.(c)People(name,fatherName,motherName)ChildOf(personName,childName)entitysetresultsinonerelation.Thusboththeminimumandmaximumnumberofrelationsise.Therootrelationhasaattributesincludingkkeys.Thustheminimumnumberofattributesisa.Allotherrelationsincludethekkeysfromrootalongwiththeiraattributes.Thusthemaximumnumberofattributesisa+k.(b)Therelationforrootwillhaveaattributes.Therelationrepresentingthewholetreewillhavee*aattributes.Thenumberofrelationswilldependontheshapeofthetree.Atreeofeentitieswhereonlyonechildexists(sayleftchildonly)wouldhavetheminimumnumberofrelations.Thusbelowfigurewillonlycontain4subtreesthatcontainrootE1,E1E2,E1E2E3,andE1E2E3E4.Witheentitysets,minimumerelationsarepossible.Themaximumnumberofsubtreesresultwhenalltheentities(exceptroot)areatdepth1.Thusbelowfigurewillcontain8subtreesthatcontainrootE1,E1E2,E1E3,E1E4,E1E2E3,E1E3E4,E1E2E4,andE1E2E3E4.Witheentitysets,maximum2^(e-1)relationsarepossible.(c)Thenullsmethodalwaysresultsinonerelationandcontainsattributesfromalleentities.e*aattributes.Summarizingfora,b,andcabove;#Components#RelationsMinMaxMinMaxMethodstraight-E/Raaeeobject-orientedae*ae2^(e-1)nullse*ae*a11andFemalessubclassesarecomplete.MothersandFathersarepartial.Allsubclassesaredisjoint.converttheternaryrelationshipContractsintothreebinaryrelationshipsbetweenanewentitysetContractsandexistingentitysets.self-associationParentOfforentitysetpeoplehasmultiplicity0..2atparentroleend.InaLibrarydatabase,ifapatroncanloanatmost12books,themmultiplicityis0..12.ForaFullTimeStudentsentityset,arelationshipofmultiplicity5..*mustexistwithCourses(Astudentmusttakeatleast5coursestobeclassifiedFullTime."SSNo",name,addr,phone)Flights("number","day",aircraft)Bookings(row,seat,"custSSNo","FlightNumber","FlightDay")"title","year",length,genre)Studios("name",address)Presidents("cert#",name,address)Owns("movieTitle","movieYear",studioName)Runs("studioName",presCert#)b)Sincethesubclassesaredisjoint,ObjectOrientedApproachisused.Thehierarchyisnotcomplete.HencefourrelationsarerequiredMovies(title,year,length,genre)MurderMysteries(title,year,length,genre,weapon)Cartoons(title,year,length,genre)Cartoon-MurderMysteries(title,year,length,genre,weapon)Movies("title","year",length,genre)MurderMysteries("title","year",length,genre,weapon)Cartoons("title","year",length,genre)Cartoon-MurderMysteries("title","year",length,genre,weapon)c)Customers(ssNo,name,phone,address)Accounts(number,balance,type)Owns(custSSNo,accountNumber)Customers("ssNo",name,phone,address)Accounts("number",balance,type)Owns("custSSNo","accountNumber")d)Teams(name,captainName)Players(name,teamName)Fans(name,favoriteColor)Colors(colorname)ForDisplaysassociation,TeamColors(teamName,colorname)RootsFor(fanName,teamName)Admires(fanName,playerName)Teams("name",captainName)Players("name",teamName)Fans("name",favoriteColor)Colors("colorname")ForDisplaysassociation,TeamColors("teamName","colorname")RootsFor("fanName","teamName")Admires("fanName","playerName")e)People(ssNo,name,fatherSSNo,motherSSNo)People("ssNo",name,fatherssNo,motherssNo)f)Students(email,name)Courses(no,section,semester,professorEmail)Departments(name)Professors(email,name,worksDeptName)Takes(letterGrade,studentEmail,courseNo,courseSection,courseSemester)Students("email",name)Courses("no","section","semester",professorEmail)Departments("name")Professors("email",name,worksDeptName)Takes(letterGrade,"studentEmail","courseNo","courseSection","courseSemester")andeveryobjectisamemberofexactlyonesubclassatleaflevel.Wehavenineclassesattheleafofhierarchy.Henceweneedninerelations.b)Allobjectsonlybelongtoonesubclassanditsancestors.Hence,weneednotconsidereverypossiblesubtreebutratherthetotalnumberofnodesintree.Henceweneedthirteenrelations.c)Weneedallpossiblesubtrees.Hence218relationsarerequired.Customer(key(ssNo)){ attributeintegerssNo; attributestringname; attributestringaddr; attributestringphone; relationshipSet<Account>ownsAccts inverseAccount::ownedBy;};classAccount(key(number)){ attributeintegernumber; attributestringtype; attributerealbalance; relationshipSet<Customer>ownedBy inverseCustomer::ownsAccts;};classAccounttocontainrelationshipCustomerownedBy(noSet)b)AlsoremovesetinrelationshipownsAcctsofclassCustomer.c)ODLallowsacollectionofprimitivetypesaswellasstructures.ToclassCustomeraddfollowingattributesinplaceofsimpleattributesaddrandphone:Set<stringphone>Set<Structaddr{stringstreet,stringcity,stringstate}>d)ODLallowsstructuresandcollectionsrecursively.Set<Structaddr{stringstreet,stringcity,stringstate},Set<stringphone>>areallowedinODL.Hence,ColorsSetcanbecomeanattributeofTeams.classColors(key(colorname)){attributestringcolorname; relationshipSet<Fans>FavoredBy inverseFans::Favors; relationshipset<Teams>DisplayedBy inverseTeams::Displays; }; classTeams(key(name)){attributestringname; relationshipset<Colors>Displays inverseColors::DisplayedBy; relationshipset<Players>PlayedBy inversePlayers::Plays; relationshipPLayersCaptainedBy inversePlatyers::Captains; relationshipset<Fans>RootedBy inverseFans::Roots; }; classPlayers(key(name)){ attributestringname; relationshipSet<Teams>Plays inverseTeams::PlayedBy; relationshipTeamsCaptains inverseTeams::CaptainedBy; relationshipSet<Fans>AdmiredBy inverseFans::Admires; };classFans(key(name)){ attributestringname; relationshipColorsFavors inverseColors::FavoredBy; relationshipSet<Teams>RootedBy inverseTeams::Roots; relationshipSet<Players>Admires inversePlayers::AdmiredBy; }; Person{ attributestringname; relationshipPersonmotherOf inversePerson::childrenOfFemale; relationshipPersonfatherOf inversePerson::childrenOfMale; relationshipSet<Person>children inversePerson::parentsOf; relationshipSet<Person>childrenOfFemale inversePerson::motherOf; relationshipSet<Person>childrenOfMale inversePerson::fatherOf; relationshipSet<Person>parentsOf inversePerson::children;};structeducation{stringdegree,stringschool,stringdate}cannothaveduplication.HenceuseofSetsdoesnotmakeanydifferentascomparedtobags,lists,orarrays.Listswillallowfasteraccess/queriesduetothealreadysortednature.Departments(key(name)){attributestringname;relationshipCoursesoffersinverseCourses::offeredBy;};classCourses(key(number,offeredBy)){attributestringnumber;relationshipDepartmentsofferedByinverseDepartments::offers;};b)classLeagues(key(name)){attributename;relationshipTeamscontainsinverseTeams::belongs;};classTeams(key(name,belongs)){attributename,relationshipLeaguesbelongsinverseLeagues::contains;relationshipPlayersplayinversePlayers::plays;};classPlayers(key(number,plays)){attributenumber,relationshipTeamsplaysinverseTeams::play;};classStudents(keyemail){attributestringemail;attributestringname;relationshipCoursesisTAinverseCourses::TA;relationshipCoursesTakesinverseCourses::TakenBy;};classProfessors(keyemail){attributestringemail;attributestringname;relationshipDepartmentsWorksForinverseDepartment::Works;relationshipCoursesTeachesinverseCourses::TaughtBy;};classCourses(key(no,semester,section)){attributestringno;attributestringsemester;attributestringsection; relationshipStudentsTAinverseStudents::isTA;relationshipStudentsTakenByinverseStudents::Takes;relationshipProfessorsTaughtByinverseProfessors::Teaches;relationshipDepartmentsOfferedByinverseDepartments::Offer;};classDepartments(keyname){attributename;relationshipCoursesOfferinverseCourses::OfferedBy;relationshipProfessorsWorksinverseProfessors::WorksFor;};relationshipisitsowninversewhenforeveryattributepairintherelationship,theinversepairalsoexists.Arelationwithsucharelationshipiscalledsymmetricinsettheory..ArelationshipcalledSiblingOfinPersonrelationisitsowninverse.canremoveAddressesrelationsinceitsattributesareasubsetofrelationPhones.c)Fans(name,colors)RootedBy(fan_name,teamname)Admires(fan_name,playername)Players(name,teamname,is_captain)Teams(name)--removesubsetofteamcolorTeamcolors(name,colorname)Colors(colorname)d)classPerson{ attributestringname; relationshipPersonmotherOf inversePerson::childrenOfFemale; relationshipPersonfather