er建模及关系表转换例题习题submitted讲课稿.docx
《er建模及关系表转换例题习题submitted讲课稿.docx》由会员分享,可在线阅读,更多相关《er建模及关系表转换例题习题submitted讲课稿.docx(9页珍藏版)》请在冰豆网上搜索。
![er建模及关系表转换例题习题submitted讲课稿.docx](https://file1.bdocx.com/fileroot1/2023-6/21/4a68d8fd-2f39-45d5-9d74-9cdb03313238/4a68d8fd-2f39-45d5-9d74-9cdb033132381.gif)
er建模及关系表转换例题习题submitted讲课稿
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/Rdiagramfortheairportdatabaseonthebasisoftheinformationmentionedabove
Note:
theprimarykeyoftheentities,mappingcardinalityofeachrelationshipandparticipationofeachentitytotherelationshipshouldbedescribedinthediagram..(10points)
(2)ConverttheE-Rdiagramtotheproperrelationalschema,andgivetheprimarykeyofeachrelationschemabyunderlines.(10points)
Answers:
(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)department
b.AProfessorworksinone(andonlyone)department
c.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-Rdiagramisasfollows
(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-Rdiagramisasfollows
(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,acopyrightdate,aformat,andanalbumidentifier.
●EachsongrecordedatNotownhasatitleandanauthor,andeachsongcanbeidentifiedbyitstitle.
●Anmusicianmayplayseveralinstruments,andaninstrumentsmaybeplayedbyseveralmusicians.
●Eachalbumhasanumberofsongsonit,andeachsongbelongstoonlyonealbum.
●Eachsongisperformedbyoneormoremusicians,andamusicianmayperformanumberofsongs.
●Eachalbumhasexactlyonemusicianwhoactsasitsproducer.Amusicianmayproduceseveralalbums,butsomemusiciansmayhavenoalbums.
(1)DesigntheE/Rdiagramforhospitaldatabaseonbasisoftheinformationmentionedabove.(10points)
Note:
mappingcardinalityofeachrelationshipandparticipationofeachentitytotherelationshipshouldbedescribedinthediagram.
(2)ConverttheE-Rdiagramtotheproperrelationalschemas,andgivetheprimarykeysofeachrelationschemasbyunderlines.(10points)
四、Ahospitaldatabaseneedstostoreinformationaboutdoctors,patients,sickroom(病房),anddepartments(科室).Considerthefollowinginformation
●Eachdoctorhasdescriptiveattributesofidentifiernumber,name,age,andtechnicaltitle).
●Eachpatienthasdescriptiveattributesofthenumberofmedicalrecords(病历),name,age,andsex
●Eachsickroomhasdescriptiveattributesofthenumberofsickroom,theaddress
●Eachdepartmenthasdescriptiveattributesofname,address,telephone-number
●Integrityconstraints:
f.Eachdoctormustbelongtoone(andonlyone)department;andforeachdepartment,therearemorethanonedoctorsbelongingtoit.
g.Eachpatientistakencareofbyoneandonlyoneresponsibledoctor;adoctormayberesponsiblefornopatients,oronlyonepatients,ormorethanonepatients
h.Eachpatientlivesinoneandonlyonesickroom;asickroommaycontainmorethanonepatients
i.Eachsickroomcanbemanagedbymorethanonedepartment;butforsomedepartments,therearenosickroomsmanagedbythem,whileforotherdepartments,therearemorethanonemanagedsickroom.
(1)DesigntheE/Rdiagramforhospitaldatabaseonbasisoftheinformationmentionedabove.(10points)
Note:
mappingcardinalityofeachrelationshipandparticipationofeachentitytotherelationshipshouldbedescribedinthediagram.
(2)ConverttheE-Rdiagramtotheproperrelationalschema,andgivetheprimarykeyofeachrelationschemabyunderlines.(10points)
五、(20points)Apharmaciesdatabaseneedstostoretheinformationaboutthepharmaceuticalcompanies(制药公司)andothers.Therelevantinformationisasfollows:
●Eachpharmaceuticalcompanyisidentifiedbyitsnameandhasaphonenumber.
●Eachpharmacy(药房)isidentifiedbyitsnameandhasanaddress,andseveralphonenumbers.
●Foreachdrug,thetradenameandformulamustberecorded,andthetradenameidentifiesadruguniquely.
●Eachdrugisdevelopedbyonlyonegivenpharmaceuticalcompany,andonepharmaceuticalcompanycandevelopseveraldrugsandperhapsdevelopsnodugnow.
●Eachpharmacysellsseveraldrugsandhasapriceforeach.Adrugcouldbesoldbyseveralpharmacies,andthepricemayvaryfromonepharmacytoanother.
●Pharmaceuticalcompaniesmakecontractswithpharmacies,butsomepharmaceuticalcompaniesmayhavenocontract.Apharmaceuticalcompanycansigncontractswithseveralpharmacies,andapharmacycanalsoenterintocontractswithseveralpharmaceuticalcompanies.Eachcontractisdescribedbyastartdate,anenddateandthecontentofthecontract.
(1)DesigntheE/Rdiagramforhospitaldatabaseonbasisoftheinformationmentionedabove.(10points)
Note:
themappingcardinalityofeachrelationshipandparticipationofeachentitytotherelationshipshouldbedescribedinthediagram.
(2)ConverttheE-Rdiagramtotheproperrelationalschemas,andgivetheprimarykeyofeachrelationschemabyunderlines.(10points)
六、(8Points)Aschoolisgoingtoarrangeasportsdayforthestudents.Adatabasetokeeptrackofparticipantsandactivitiesduringthesportsdayistobecreated.Considerthefollowinginformation:
(1).Participatingpersons.Eachpersonhasanumberandaname.Thepersonisidentifiedbythenumber.
(2).Teams.Eachteamhasanumberandaname.Theteamisidentifiedbythenumber.
(3).Ateamconsistsofseveralpersons,andapersoncanbeamemberofseveralteams.Ateammusthaveatleastoneperson,andapersonmaynotparticip