e-r建模及关系表转换-例题习题

1、ConsiderthefollowinginformationinanairportdatabaseAnairportisdescribedbyitsnameandthecitythattheairportIocatesatEachairpIanehasanuniqueregistrationnumberandaIsothedateofproductionasitsdescriptiveattributesEachairplanemodeIisidentifiedbyamodeInumberandhasacapacityandaweightAtechnicianischaracterizedb

2、yanuniquetechnician_id,hisnameandhisphonenumberEachairportaccommodatesanumberofairpIanemodeIs,andanairpIanemodeImayappearsinseveraIairports;butsomeairportsarenotsuitabIeforaccommodaingseveraIspecialpIanemodeIs.EachairpIaneisofaspecificmodeI,e.g.Boeing737；andforeachmodeI,therearemorethanoneairplanebe

3、ingofit.foreachairport,therearesometechniciansworkingthere,andeachtechnicianmustworksatonlyoneairport.EachtechnicianisresporisibleforoneormorepIanemodeIs,andeachplanemodelhasatleastonetechnicianresponsibleforitEachairpIaneisperiodicallytestedbyanumberoftechnicianstoensurethattheairplaneisstillairwor

4、thy.AtechnicianmaytestseveraIairplaneseachyear,andsometimesatechnicianhasnotaskfortestingtheairplanes?ItisrequiredthatwhenatechniciantestsanairpIane,theairplanemodeIthatheisresponsibleforisjustthemodeIthattheairpIanetestedisof.Theinformation,suchasthetestnumber,thetestingdate,thetestingresultandthet

5、imespentonthetest,isneededtodescribeatestingoftheairplane(1) DesigntheE/RdiagramfortheairportdatabaseonthebasisoftheinformationmentionedaboveNote:theprimarykeyoftheentities,mappingcardinalityofeachrelationshipandparticipationofeachentitytotherelationshipshouldbedescribedinthediagram.(10points)(2) Co

6、nverttheE-Rdiagramtotheproperrelationalschema,andgivetheprimarykeyofeachrelationschemabyunderlines.(10points)Answers：ui-namey(weight)：*<TQcapacily)< m-num J)airport<5acc o m inodatemodelwork<ponsib>be-of J> technician(t-nuin)JZ;、一testaiiplane(bnamc)CresulDCtimejdate)X (re-nun】)(pho

7、nejiuni)-dale)(10points)实体airport归结为：airport(a-name,city);实体model归结为：model(m-num,weight,capacity);联系accommodate归结为：accominodate(a-name,nvnum);实体airplane和联系be?of归结为：airplane(re-num,date,m-num);实体technician>联系work归结为：technician(上t-name,phone-num,a-name)联系test归结为：test(t-id,re-num,t-num,t-date,time,r

8、esult)联系responsible归结为：responsible(Md,nvnum)二、Auniversitystudentdatabaseneedstostoreinformationaboutstudents八professors八projects八anddepartments.Considerthefollowinginformation:?EachstudenthasaSNo,aname,anage,andadegreeprogram(e.g.M.S.orPh.D.).?EachprofessorhasaPNo,aname,anage,andaresearchspecialty.?

9、Eachprojecthasaprojectnumber,astartingdate,anendingdate,andabudget.?Eachdepartmenthasadepartmentnumber,adepartmentname,andamainoffice.?integrityconstraints:a. Astudentstudiesinone(andonlyone)departmentb. AProfessorworksinone(andonlyone)departmentc?Eachprojectmustbemanagedbyoneandonlyoneprofessor,and

10、eachprofessormustmanageatleastoneproject.d. Eachprojectisworkedonbysomestudents,morethanonestudentcanparticipate(orworkon)thesameproject,andsomestudentsmayworkonnoprojects.e. Whenastudentworkonaproject,theprofessormanagingthisprojectmustsupervisethestudentswork.Onestudentmayworkonseveralprojects,soh

11、emayhaveseveralsupervisors?(1) DesignanddrawanE/Rdiagramforthisdatabasethatcapturestheinformationabove?Note:mappingcardinalityofeachrelationshipandparticipationofeachentitytotherelationshipshouldbedescribedinthediagram.(2) ConverttheE-Rdiagramtotheproperrelationalschema,andgivetheprimarykeyofeachrel

12、ationschemabyunderlines?Answers：E-RdiagramisasfollowsUKiinej/degree，、Vperanz"departtnehrx<-numberJ(SNo/studentsludy>idepartinent/main、V_-office_x<garticipat><&jpcrvisoj>><Lworks(budget)projectsjnanaee7/project-number/'、fndin宜、X.J-dtUc)AtarimgV-dateJ:professor<

13、name><rescarch;><areasJ(2) thereducedtablesareasfollows:department-number)(a) student(SNo,name,age,degree-program,Note:relationshipstudyisreducedtothistable(b) professor(PNo,name,age,research-area,department-number)Note:relationshipworksisreducedtothistable(c)department(departdepart-name

14、,main-officer)(d) project(project?number,starting-date,ending-date,budget,PNo)Note:relationshipmanageisreducedtothistable(e) participate(SNo,project-number)supervisor(SNo,PNo)solution2:(1) E-RdiagramisasfollowsstudentparticipatedJbudset；epartmen?name1studyupervisoi/departments<-numberJtdepartment

15、sX*13O-tmain-officeworks、PMprojectCBS)I k J-vendingJ-datU_一一_一hstartinej,-cate"dprofessoriresearc；1areas(2) thereducedtablesareasfollows:(a) student(SNo,name,age,degree-program,department-niunber)Note:relationshipstudyisreducedtothistable(b) professor(PNo,name,age,research-area,department-numbe

16、rNote: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,PioiecJnumber,PNo)三.NotovvnRecordscompa

17、nyneedstostoreinformationaboutsongs,albums(专辑)andmusicianswhoperformonitsalbumsinadatabase.Considerthefollowinginformation:?EachmusiciansthatrecordsatcompanyhasanId(whichisunique),aname,anaddress,andaphonenumber.?EachinstrumentusedincompanyhasanameandanuniqueID.?EachalbumrecordedontheNotownlabelhasa

18、title,acopyrightdate,aformat,andanalbumidentifier.?EachsongrecordedatNotownhasatitleandanauthor,andeachsongcanbeidentifiedbyitstitle.?Anmusicianmayplayseveralinstruments,andaninstrumentsmaybeplayedbyseveralmusicians?Eachalbumhasanumberofsongsonit,andeachsongbelongstoonlyonealbum.?Eachsongisperformed

19、byoneormoremusicians,andamusicianmayperformanumberofsongs?Eachalbumhasexactlyonemusicianwhoactsasitsproducer.Amusicianmayproduceseveralalbums,butsomemusiciansmayhavenoalbums.(1) DesigntheE/Rdiagramforhospitaldatabaseonbasisoftheinformationmentionedabove.(10points)Note:mappingcardinalityofeachrelatio

20、nshipandparticipationofeachentitytotherelationshipshouldbedescribedinthediagram.(2) ConverttheE-Rdiagramtotheproperrelationalschemas,andgivetheprimarykeysofeachrelationschemasbyunderlines.(10points)四、Ahospitaldatabaseneedstostoreinformationaboutdoctors,patients,sickroom(病房)，anddepartments(科室).Consid

21、erthefollowinginformation?Eachdoctorhasdescriptiveattributesofidentifiernumber,name,age,andtechnicaltitle).?Eachpatienthasdescriptiveattributesofthenumberofmedicalrecords(病历)，name,age,andsex?Eachsickroomhasdescriptiveattributesofthenumberofsickroom,theaddress?Eachdepartmenthasdescriptiveattributesof

22、name,address,telephone-number?Integrityconstraints:a. Eachdoctormustbelongtoone(andonlyone)department;andforeachdepartment,therearemorethanonedoctorsbelongingtoit.b. Eachpatientistakencareofbyoneandonlyoneresponsibledoctor;adoctormayberesponsiblefornopatients,oronlyonepatients,ormorethanonepatientsc

23、. Eachpatientlivesinoneandonlyonesickroom;asickroommaycontainmorethanonepatientsd?Eachsickroomcanbemanagedbymorethanonedepartment;butforsomedepartments,therearenosickroomsmanagedbythem,whileforotherdepartments,therearemorethanonemanagedsickroom?(1) DesigntheE/Rdiagramforhospitaldatabaseonbasisofthei

24、nformationmentionedabove?(10points)Note:mappingcardinalityofeachrelationshipandparticipationofeachentitytotherelationshipshouldbedescribedinthediagram?(2) ConverttheE-Rdiagramtotheproperrelationalschema,andgivetheprimarykeyofeachrelationschemabyunderlines.(10points)五.(20points)Apharmaciesdatabasenee

25、dstostoretheinformationaboutthepharmaceuticalcompanies(帝l药公司)andothers.Therelevantinformationisasfollows:?Eachpharmaceuticalcompanyisidentifiedbyitsnameandhasaphonenumber.?Eachpharmacy(药房)isidentifiedbyitsnameandhasanaddress,andseveralphonenumbers?Foreachdrug?thetradenameandformulamustberecorded,and

26、thetradenameidentifiesadruguniquely.?Eachdrugisdevelopedbyonlyonegivenpharmaceuticalcompany,andonepharmaceuticalcompanycandevelopseveraldrugsandperhapsdevelopsnodugnow.?Eachpharmacysellsseveraldnjgsandhasapriceforeach?Adrugcouldbesoldbyseveralpharmacies,andthepricemayvaryfromonepharmacytoanother.?Ph

27、armaceuticalcompaniesmakecontractswithpharmacies,butsomepharmaceuticalcompaniesmayhavenocontract.Apharmaceuticalcompanycansigncontractswithseveralpharmacies,andapharmacycanalsoenterintocontractswithseveralpharmaceuticalcompanies.Eachcontractisdescribedbyastartdate,anenddateandthecontentofthecontract

28、?(1) DesigntheE/Rdiagramforhospitaldatabaseonbasisoftheinformationmentionedabove?(10points)Note:themappingcardinalityofeachrelationshipandparticipationofeachentitytotherelationshipshouldbedescribedinthediagram.(2) ConverttheE-Rdiagramtotheproperrelationalschemas,andgivetheprimarykeyofeachrelationschemabyunderlines?(10points)六、(8Points)Aschoolisgoingtoarrangeasportsdayforthestudents.Adatabasetokeeptrackofparticipantsandactivitiesduringthesportsdayistobecreated.