《E-R建模及关系表格转换习题-20102887.pdf》由会员分享，可在线阅读，更多相关《E-R建模及关系表格转换习题-20102887.pdf（8页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、 页脚 一、Consider the following information in an airport database An airport is described by its name and the city that the airport locates at Each airplane has a registration number and the date of production as its descriptive attributes Each airplane model is identified by a model number and has a
2、capacity and a weight A technician is characterized by technician_id,his name and his phone number Each airport accommodates a number of airplane models,and an airplane model may appears in several airports Each airplane is of a specific model,e.g.Boeing 737;and for each model,there may be more than
3、 one airplane being of it A number of technicians work at the airport,but each technician works at only one airport.Each technician is responsible for one or more plane models,and one plane model has one or more technicians responsible for it Each airplane is periodically tested by a number of techn
4、icians to ensure that the airplane is still airworthy.A technician may test several airplanes each year.页脚 It is required that when a technician tests an airplane,the airplane model that he is responsible for is just the model that the airplane tested is of.The information,such as test number,the te
5、sting date,the testing result and the time spent on the test,is needed to describe a testing of the airplane(1)Design the E/R diagram for the airport database on the basis of the information mentioned above Note:the key of the entities,mapping cardinality of each relationship and participation of ea
6、ch entity to the relationship should be described in the diagram.(10 points)(2)Convert the E-R diagram to the proper relational schema,and give the primary key of each relation schema by underlines.(10 points)Answers：页脚 airportmodeltechnicianairplaneaccommodateworktestbe-ofresponsiblea-namere-numcap
7、acitym-numweightdatecityphone-numt-namet-idt-numresultt-datetime 或：airportmodeltechnicianairplaneaccommodateworktestbe-ofresponsiblea-namere-numcapacitym-numweightdatecityphone-numt-namet-idt-numresultt-datetime (2)(10 points)页脚 实体 airport 归结为:airport(a-name,city);实体 model 归结为:model(m-num,weight,cap
8、acity);联 系accommodate归 结 为:accommodate(a-name,m-num);实体 airplane 和联系 be-of 归结为：airplane(re-num,date,m-num);实体 technician、联系 work、联系 test 归结为：technician(t-id,t-name,phone-num,a-name)test(t-id,re-num,t-num,t-date,time,result)联系 responsible 归结为 responsible(t-id,m-num)如果 technician 部分参与到联系 test 中，则如下图所示
9、：airportmodeltechnicianairplaneaccommodateworktestbe-ofresponsiblea-namere-numcapacitym-numweightdatecityphone-numt-namet-idt-numresultt-datetime 关系表转换：实体 airport 归结为:airport(a-name,city);页脚 实体 model 归结为:model(m-num,weight,capacity);联 系accommodate归 结 为:accommodate(a-name,m-num);实体 airplane 和联系 be-of
10、 归结为：airplane(re-num,date,m-num);实体 technician、联系 work 归结为：technician(t-id,t-name,phone-num,a-name)联系 responsible 归结为:responsible(t-id,m-num)联系 test 归结为:test(t-id,re-num,t-num,t-date,time,result)二、Notown Records company needs to store information about songs,albums（专辑）and musicians who perform on it
11、s albums in a database.Consider the following information:Each musicians that records at company has an Id(which is unique),a name,an address,and a phone number.Each instrument used in company has a name and an ID,ID is unique.Each album recorded on the Notown label has a title,a 页脚 copyright date,a
12、 format,and an album identifier.Each song recorded at Notown has a title and an author,and each song can be identified by title.Each musician may play several instruments,and a given instruments may be played by several musicians.Each album has a number of songs on it,but no song may appear on more
13、than one album.Each song is performed by one or more musicians,and a musician may perform a number of songs.Each album has exactly one musician who acts as its producer.A musician may produce several albums,of course.(1)Design the E/R diagram for hospital database on basis of the information mention
14、ed above.(10 points)Note:mapping cardinality of each relationship and participation of each entity to the relationship should be described in the diagram.(2)Convert the E-R diagram to the proper relational schemas,and give the primary keys of each relation schemas by underlines.(10 points)Answers：(1
15、)页脚 M_id musician play addresname instrument I_id I_name album produce perform a_identifier title format Copyright_date song author have Phone_number 页脚 全参与和部分参与可有不同答案。(2)musician(m_id,name,address,phone_number)instrument(I_id,I_name)play(m_id,I_id)song(s_title,author,a_identifier)perform(m_id,s_title)album(a_identifier,title,copyright_date,format,M_id)