ER图应用举例.docx

ER图应用举例.docx

《ER图应用举例.docx》由会员分享，可在线阅读，更多相关《ER图应用举例.docx（6页珍藏版）》请在冰点文库上搜索。

ER图应用举例

Acompanydatabaseneedstostoreinformationaboutemployees（identifiedbyssn,withsalaryandphoneasattributes）,departments（identifiedbydno,withdnameandbudgetasattributes）,andchildrenofemployees（withnameandageasattributes）.Employeesworkindepartments;eachdepartmentismanagedbyanemployee;achildmustbeidentifieduniquelybynamewhentheparent（whoisanemployee;assumethatonlyoneparentworksforthecompany）isknown.Wearenotinterestedininformationaboutachildoncetheparentleavesthecompany.

DrawanERdiagramthatcapturesthisinformation.

2Considerthefollowinginformationaboutauniversitydatabase:

⏹ProfessorshaveanSSN,aname,anage,arank,andaresearchspecialty.

⏹Projectshaveaprojectnumber,asponsorname（e.g.,NSF）,astartingdate,anendingdate,andabudget.

⏹GraduatestudentshaveanSSN,aname,anage,andadegreeprogram（e.g.,M.S.orPh.D.）.

⏹Eachprojectismanagedbyoneprofessor（knownastheproject’sprincipalinves-tigator）.

⏹Eachprojectisworkedonbyoneormoreprofessors（knownastheproject’sco-investigators）.

⏹Professorscanmanageand/orworkonmultipleprojects.

⏹Eachprojectisworkedonbyoneormoregraduatestudents（knownastheproject’sresearchassistants）.

⏹Whengraduatestudentsworkonaproject,aprofessormustsupervisetheirworkontheproject.Graduatestudentscanworkonmultipleprojects,inwhichcase

⏹theywillhavea（potentiallydifferent）supervisorforeachone.

⏹Departmentshaveadepartmentnumber,adepartmentname,andamainoffice.

⏹Departmentshaveaprofessor（knownasthechairman）whorunsthedepartment.

⏹Professorsworkinoneormoredepartments,andforeachdepartmentthattheyworkin,atimepercentageisassociatedwiththeirjob.

⏹Graduatestudentshaveonemajordepartmentinwhichtheyareworkingontheirdegree.

⏹Eachgraduatestudenthasanother,moreseniorgraduatestudent（knownasastudentadvisor）whoadviseshimorheronwhatcoursestotake.

⏹DesignanddrawanERdiagramthatcapturestheinformationabouttheuniversity.

⏹UseonlythebasicERmodelhere;thatis,entities,relationships,andattributes.Besuretoindicateanykeyandparticipationconstraints.

⏹

⏹

⏹

⏹3NotownRecordshasdecidedtostoreinformationaboutmusicianswhoperformonitsalbums（aswellasothercompanydata）inadatabase.Notown纪录决定存储关于在它的册页执行的音乐家的信息（并且其他公司数据）在数据库。

Thecompanyhaswiselychosentohireyouasadatabasedesigner（atyourusualconsultingfeeof$2500/day）.公司明智地选择雇用您作为数据库设计员（在$2500/day您的通常咨询的费）。

⏹EachmusicianthatrecordsatNotownhasanSSN,aname,anaddress,andaphonenumber.记录在Notown的每位音乐家有SSN、一个名字、一个地址和一个电话号码。

Poorlypaidmusiciansoftensharethesameaddress,andnoaddresshasmorethanonephone.恶劣受雇用的音乐家经常分享同一个地址，并且地址没有超过一个电话。

⏹EachinstrumentusedinsongsrecordedatNotownhasauniqueidentificationnumber,aname（e.g.,guitar,synthesizer,flute）andamusicalkey（e.g.,C,B-flat,E-flat）.用于歌的每台仪器被录音在Notown有一个独特的identifi正离子数字，一把命名（即，吉他、合成器，fl犹特语）和一把音乐钥匙（即，C，B-flat，E-flat）。

⏹EachalbumrecordedontheNotownlabelhasauniqueidentificationnumber,atitle,acopyrightdate,aformat（e.g.,CDorMC）,andanalbumidentifier.在Notown标签记录的每个册页有一个独特的identifi正离子数字、一个标题、一个版权日期、一个格式（即，CD或MC）和一册页identifi唔。

⏹EachsongrecordedatNotownhasatitleandanauthor.每歌被录音在Notown有一个标题和一位作者。

⏹Eachmusicianmayplayseveralinstruments,andagiveninstrumentmaybeplayedbyseveralmusicians.每位音乐家也许弹奏几台仪器，并且一台特定仪器也许由几位音乐家弹奏。

⏹Eachalbumhasanumberofsongsonit,butnosongmayappearonmorethanonealbum.每个册页有一定数量的歌曲对此，但歌曲在超过一个册页可能不发表。

⏹Eachsongisperformedbyoneormoremusicians,andamusicianmayperformanumberofsongs.每首歌曲由一个或更多音乐家执行，并且音乐家也许执行一定数量的歌曲

⏹Eachalbumhasexactlyonemusicianwhoactsasitsproducer.Amusicianmayproduceseveralalbums,ofcourse.每个册页确切地有作为它的生产商的一位音乐家。

当然音乐家也许生产几个册页。

⏹DesignaconceptualschemaforNotownanddrawanERdiagramforyourschema.设计一个概念框图为Notown并且得出一张ER图为您的图解。

⏹4ThePrescriptions-R-Xchainofpharmacieshasofferedtogiveyouafreelifetimesupplyofmedicineifyoudesignitsdatabase.Giventherisingcostofhealthcare,youagree.Here’stheinformationthatyougather:

药房链子有off的处方RXered给您医学自由终身供应，如果您设计它的数据库。

假使医疗保健的上涨成本，您同意。

这您收集的信息：

⏹PatientsareidentifiedbyanSSN,andtheirnames,addresses,andagesmustberecorded.

⏹DoctorsareidentifiedbyanSSN.Foreachdoctor,thename,specialty,andyearsofexperiencemustberecorded.

⏹Eachpharmaceuticalcompanyisidentifiedbynameandhasaphonenumber.

⏹患者是identifi编辑由SSN，并且必须记录他们的名字、地址和年龄。

医生是identifi编辑由SSN。

为每位医生，必须记录名字、专业和岁月经验。

每家制药公司名义上是identifi编辑并且有电话号码。

⏹Foreachdrug,thetradenameandformulamustberecorded.Eachdrugissoldbyagivenpharmaceuticalcompany,andthetradenameidentifiesadruguniquelyfromamongtheproductsofthatcompany.Ifapharmaceuticalcompanyisdeleted,youneednotkeeptrackofitsproductsanylonger.为每种药物，必须记录商标和惯例。

每种药物由一家特定制药公司和商标identifiES独特地卖药物从在那家公司之中产品。

如果一家制药公司被删除，您不需要其中任一长期记录它的产品。

⏹Eachpharmacyhasaname,address,andphonenumber.

⏹Everypatienthasaprimaryphysician.Everydoctorhasatleastonepatient.

每家药房有一个名字、地址和电话号码。

每名患者有一位主要医师。

每位医生有至少一名患者。

⏹Eachpharmacysellsseveraldrugsandhasapriceforeach.Adrugcouldbesoldatseveralpharmacies,andthepricecouldvaryfromonepharmacytoanother.每家药房卖几种药物并且有一个价格为中的每一。

药物可能被卖在几家药房，并且价格可能从一家药房变化到另一个。

⏹Doctorsprescribedrugsforpatients.Adoctorcouldprescribeoneormoredrugsforseveralpatients,andapatientcouldobtainprescriptionsfromseveraldoctors.医生为患者规定药品。

医生可能为几名患者规定一个或更多药品，并且患者可能获得处方从几位医生。

⏹Eachprescriptionhasadateandaquantityassociatedwithit.Youcanassumethat,ifadoctorprescribesthesamedrugforthesamepatientmorethanonce,onlythelastsuchprescriptionneedstobestored.每张处方有一个日期和一个数量与它交往。

您能假设，如果医生为同一名患者更比一次规定同一药品，只有为时这样处方需要被存放。

⏹Pharmaceuticalcompanieshavelong-termcontractswithpharmacies.Apharmaceuticalcompanycancontractwithseveralpharmacies,andapharmacycancontractwithseveralpharmaceuticalcompanies.Foreachcontract,youhavetostoreastartdate,anenddate,andthetextofthecontract.制药公司有同药房的长期合同。

一家制药公司能收缩与几家药房，并且药房可能收缩与几家制药公司。

为每个合同，您必须存放起始日期、末端日期和合同的文本。

⏹Pharmaciesappointasupervisorforeachcontract.Theremustalwaysbeasupervisorforeachcontract,butthecontractsupervisorcanchangeoverthelifetimeofthecontract.药房任命一位监督员为每个合同。

必须总有一位监督员为每个合同，但合同监督员能改变在合同的终身。

⏹.DrawanERdiagramthatcapturestheprecedinginformation.IdentifyanyconstraintsnotcapturedbytheERdiagram.得出获取在先的信息的ER图。

辨认ER图没夺取的所有限制。

5ComputerSciencesDepartmentfrequentfliershavebeencomplainingtoDaneCountyAirportofficialsaboutthepoororganizationattheairport.Asaresult,theofficialsdecidedthatallinformationrelatedtotheairportshouldbeorganizedusingaDBMS,andyouhavebeenhiredtodesignthedatabase.Yourfirsttaskis

⏹toorganizetheinformationaboutalltheairplanesstationedandmaintainedattheairport.Therelevantinformationisasfollows:

⏹Everyairplanehasaregistrationnumber,andeachairplaneisofaspecificmodel.

⏹Theairportaccommodatesanumberofairplanemodels,andeachmodelisidentifiedbyamodelnumber（e.g.,DC-10）andhasacapacityandaweight.

⏹Anumberoftechniciansworkattheairport.Youneedtostorethename,SSN,address,phonenumber,andsalaryofeachtechnician.

⏹Eachtechnicianisanexpertononeormoreplanemodel（s）,andhisorherexpertisemayoverlapwiththatofothertechnicians.Thisinformationabouttechniciansmustalsoberecorded.

⏹Trafficcontrollersmusthaveanannualmedicalexamination.Foreachtrafficcontroller,youmuststorethedateofthemostrecentexam.

⏹Allairportemployees（includingtechnicians）belongtoaunion.Youmuststoretheunionmembershipnumberofeachemployee.Youcanassumethateachemployeeisuniquelyidentifiedbyasocialsecuritynumber.

⏹Theairporthasanumberofteststhatareusedperiodicallytoensurethatairplanesarestillairworthy.EachtesthasaFederalAviationAdministration（FAA）testnumber,aname,andamaximumpossiblescore.

⏹TheFAArequirestheairporttokeeptrackofeachtimeagivenairplaneistestedbyagiventechnicianusingagiventest.Foreachtestingevent,theinformationneededisthedate,thenumberofhoursthetechnicianspentdoingthetest,andthescoretheairplanereceivedonthetest.

⏹1.DrawanERdiagramfortheairportdatabase.Besuretoindicatethevariousattributesofeachentityandrelationshipset;alsospecifythekeyandparticipationconstraintsforeachrelationshipset.Specifyanynecessaryoverlapandcoveringconstraintsaswell（inEnglish）.