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).