er建模及关系表转换例题习题submitted讲课稿.docx

er建模及关系表转换例题习题submitted讲课稿.docx

《er建模及关系表转换例题习题submitted讲课稿.docx》由会员分享，可在线阅读，更多相关《er建模及关系表转换例题习题submitted讲课稿.docx（9页珍藏版）》请在冰豆网上搜索。

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