E-R建模及关系表转换-例题习题-2014-submitted

一、ConsiderthefollowinginformationinanairportdatabaseAnairportisdescribedbyitsnameandthecitythattheairportlocatesatEachairplanehasanuniqueregistrationnumberandalsothedateofproductionasitsdescriptiveattributesEachairplanemodelisidentifiedbyamodelnumberandhasacapacityandaweightAtechnicianischaracterizedbyanuniquetechnician_id,hisnameandhisphonenumberEachairportaccommodatesanumberofairplanemodels,andanairplanemodelmayappearsinseveralairports;butsomeairportsarenotsuitableforaccommodaingseveralspecialplanemodels.Eachairplaneisofaspecificmodel,e.g.Boeing737;andforeachmodel,therearemorethanoneairplanebeingofit.foreachairport,therearesometechniciansworkingthere,andeachtechnicianmustworksatonlyoneairport.Eachtechnicianisresponsibleforoneormoreplanemodels,andeachplanemodelhasatleastonetechnicianresponsibleforitEachairplaneisperiodicallytestedbyanumberoftechnicianstoensurethattheairplaneisstillairworthy.Atechnicianmaytestseveralairplaneseachyear,andsometimesatechnicianhasnotaskfortestingtheairplanes.Itisrequiredthatwhenatechniciantestsanairplane,theairplanemodelthatheisresponsibleforisjustthemodelthattheairplanetestedisof.Theinformation,suchasthetestnumber,thetestingdate,thetestingresultandthetimespentonthetest,isneededtodescribeatestingoftheairplane(1)DesigntheE/RdiagramfortheairportdatabaseonthebasisoftheinformationmentionedaboveNote:theprimarykeyoftheentities,mappingcardinalityofeachrelationshipandparticipationofeachentitytotherelationshipshouldbedescribedinthediagram..(10points)(2)ConverttheE-Rdiagramtotheproperrelationalschema,andgivetheprimarykeyofeachrelationschemabyunderlines.(10points)Answers：airportmodeltechnicianairplaneaccommodateworktestbe-ofresponsiblea-namere-numcapacitym-numweightdatecityphone-numt-namet-idt-numresultt-datetime(2)(10points)实体airport归结为:airport(a-name,city);实体model归结为:model(m-num,weight,capacity);联系accommodate归结为:accommodate(a-name,m-num);实体airplane和联系be-of归结为：airplane(re-num,date,m-num);实体technician、联系work归结为：technician(t-id,t-name,phone-num,a-name)联系test归结为:test(t-id,re-num,t-num,t-date,time,result)联系responsible归结为:responsible(t-id,m-num)二、Auniversitystudentdatabaseneedstostoreinformationaboutstudents,professors,projects,anddepartments.Considerthefollowinginformation:EachstudenthasaSNo,aname,anage,andadegreeprogram(e.g.M.S.orPh.D.).EachprofessorhasaPNo,aname,anage,andaresearchspecialty.Eachprojecthasaprojectnumber,astartingdate,anendingdate,andabudget.Eachdepartmenthasadepartmentnumber,adepartmentname,andamainoffice.integrityconstraints:a.Astudentstudiesinone(andonlyone)departmentb.AProfessorworksinone(andonlyone)departmentc.Eachprojectmustbemanagedbyoneandonlyoneprofessor,andeachprofessormustmanageatleastoneproject.d.Eachprojectisworkedonbysomestudents,morethanonestudentcanparticipate(orworkon)thesameproject,andsomestudentsmayworkonnoprojects.e.Whenastudentworkonaproject,theprofessormanagingthisprojectmustsupervisethestudent’swork.Onestudentmayworkonseveralprojects,sohemayhaveseveralsupervisors.(1)DesignanddrawanE/Rdiagramforthisdatabasethatcapturestheinformationabove.Note:mappingcardinalityofeachrelationshipandparticipationofeachentitytotherelationshipshouldbedescribedinthediagram.(2)ConverttheE-Rdiagramtotheproperrelationalschema,andgivetheprimarykeyofeachrelationschemabyunderlines.Answers：(1)E-RdiagramisasfollowsstudentdepartmentsprojectprofessorstudyparticipatemanageworkssupervisorSNonameresearch-areasagedepartment-namedepartment-numbermain-officePNonameagedegree-programending-datestarting-dateproject-numberbudget(2)thereducedtablesareasfollows:(a)student(SNo,name,age,degree-program,department-number)Note:relationshipstudyisreducedtothistable(b)professor(PNo,name,age,research-area,department-number)Note:relationshipworksisreducedtothistable(c)department(department-number,depart-name,main-officer)(d)project(project-number,starting-date,ending-date,budget,PNo)Note:relationshipmanageisreducedtothistable(e)participate(SNo,project-number)(f)supervisor(SNo,PNo)solution2:(1)E-RdiagramisasfollowsstudentdepartmentsprojectprofessorstudyparticipatemanageworkssupervisorSNonameresearch-areasagedepartment-namedepartment-numbermain-officePNonameagedegree-programending-datestarting-dateproject-numberbudget(2)thereducedtablesareasfollows:(a)student(SNo,name,age,degree-program,department-number)Note:relationshipstudyisreducedtothistable(b)professor(PNo,name,age,research-area,department-number)Note:relationshipworksisreducedtothistable(c)department(department-number,depart-name,main-officer)(d)project(project-number,starting-date,ending-date,budget,PNo)Note:relationshipmanageisreducedtothistable(e)participate(SNo,project-number)(f)supervisor(SNo,Project-number,PNo)三、NotownRecordscompanyneedstostoreinformationaboutsongs,albums（专辑）andmusicianswhoperformonitsalbumsinadatabase.Considerthefollowinginformation:EachmusiciansthatrecordsatcompanyhasanId(whichisunique),aname,anaddress,andaphonenumber.EachinstrumentusedincompanyhasanameandanuniqueID.EachalbumrecordedontheNotownlabelhasatitle,a