版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請(qǐng)進(jìn)行舉報(bào)或認(rèn)領(lǐng)
文檔簡(jiǎn)介
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)實(shí)體airport歸結(jié)為:airport(a-name,city);實(shí)體model歸結(jié)為:model(m-num,weight,capacity);聯(lián)系accommodate歸結(jié)為:accominodate(a-name,nvnum);實(shí)體airplane和聯(lián)系be?of歸結(jié)為:airplane(re-num,date,m-num);實(shí)體technician>聯(lián)系work歸結(jié)為:technician(上t-name,phone-num,a-name)聯(lián)系test歸結(jié)為:test(t-id,re-num,t-num,t-date,time,r
8、esult)聯(lián)系responsible歸結(jié)為: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.
溫馨提示
- 1. 本站所有資源如無(wú)特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請(qǐng)下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請(qǐng)聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
- 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁(yè)內(nèi)容里面會(huì)有圖紙預(yù)覽,若沒有圖紙預(yù)覽就沒有圖紙。
- 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
- 5. 人人文庫(kù)網(wǎng)僅提供信息存儲(chǔ)空間,僅對(duì)用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對(duì)用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對(duì)任何下載內(nèi)容負(fù)責(zé)。
- 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請(qǐng)與我們聯(lián)系,我們立即糾正。
- 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶因使用這些下載資源對(duì)自己和他人造成任何形式的傷害或損失。
最新文檔
- 2024年肇慶客運(yùn)資格證考試題目
- 2024年客運(yùn)資格證應(yīng)用能力考試題庫(kù)及答案
- 2024年沈陽(yáng)駕駛員客運(yùn)資格證模擬考試題答案解析
- 2024年北京客運(yùn)考試應(yīng)用能力試題及答案解析
- 2024年泰安客運(yùn)從業(yè)資格證培訓(xùn)資料
- 中學(xué)學(xué)生寒假社會(huì)實(shí)踐方案
- 2024年感恩節(jié)微信營(yíng)銷方案
- 沙灘主題婚禮策劃方案
- 餐飲營(yíng)銷目標(biāo)策劃的設(shè)計(jì)方案
- 商業(yè)零售公司年終總結(jié)表彰會(huì)策劃方案策劃方案
- 第五章膜過濾法
- 部編版七年級(jí)上冊(cè)語(yǔ)文第一單元默寫練習(xí)題
- 收房交付通知書范本
- 《風(fēng)景談》PPT課件完美版
- 四川省廣元市三年(2024-2023-2024)中考語(yǔ)文試卷分類匯編語(yǔ)言運(yùn)用(含答案解析)
- 初三物理熱機(jī)效率練習(xí)題及參考答案
- 重慶市2023年高職分類考試招生信息及通用技術(shù)試題及答案(重慶市春招考試)
- 遼寧省沈陽(yáng)市和平區(qū)2023-2024學(xué)年三上數(shù)學(xué)期末學(xué)業(yè)質(zhì)量監(jiān)測(cè)模擬試題含答案
- 甘蔗種植項(xiàng)目可行性研究報(bào)告
- 一年級(jí)傳統(tǒng)文化2小兒語(yǔ)課件
- 鋼結(jié)構(gòu)焊接工藝評(píng)定樣本
評(píng)論
0/150
提交評(píng)論