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

1、er建模及关系表转换例题习题submitted讲课稿E-R建模及关系表转换-例题习题-2014-submitted一、 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 an unique registration number and also the date of production as its descriptive attrib

2、utes Each airplane model is identified by a model number and has a capacity and a weight A technician is characterized by an unique 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; but some airp

3、orts are not suitable for accommodaing several special plane models. Each airplane is of a specific model, e.g. Boeing 737; and for each model, there are more than one airplane being of it. for each airport, there are some technicians working there, and each technician must works at only one airport

4、. Each technician is responsible for one or more plane models, and each plane model has at least one technician responsible for it Each airplane is periodically tested by a number of technicians to ensure that the airplane is still airworthy. A technician may test several airplanes each year, and so

5、metimes a technician has no task for testing the airplanes.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 the test number, the testing date, the testing result and the t

6、ime 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 primary key of the entities, mapping cardinality of each relationship and participation of each entity to the relationsh

7、ip 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：(2) (10 points)实体airport归结为: airport(a-name, city); 实体model归结为: model(m-num, weight, capacity); 联系accommo

8、date归结为: 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)二、 A university student database needs to st

9、ore information about students, professors, projects, and departments. Consider the following information: Each student has a SNo, a name, an age, and a degree program (e.g. M.S. or Ph.D.). Each professor has a PNo, a name, an age, and a research specialty. Each project has a project number, a start

10、ing date, an ending date, and a budget. Each department has a department number, a department name, and a main office. integrity constraints:a. A student studies in one (and only one) departmentb. A Professor works in one (and only one) departmentc. Each project must be managed by one and only one p

11、rofessor, and each professor must manage at least one project. d. Each project is worked on by some students, more than one student can participate(or work on) the same project, and some students may work on no projects.e. When a student work on a project, the professor managing this project must su

12、pervise the students work. One student may work on several projects, so he may have several supervisors. (1) Design and draw an E/R diagram for this database that captures the information above .Note: mapping cardinality of each relationship and participation of each entity to the relationship shoul

13、d be described in the diagram.(2) Convert the E-R diagram to the proper relational schema, and give the primary key of each relation schema by underlines.Answers： (1) E-R diagram is as follows(2) the reduced tables are as follows: (a) student (SNo, name, age, degree-program, department-number)Note:

14、relationship study is reduced to this table(b) professor (PNo, name, age, research-area, department-number)Note: relationship works is reduced to this table(c) department (department-number, depart-name, main-officer)(d) project(project-number, starting-date, ending-date, budget, PNo)Note: relations

15、hip manage is reduced to this table(e) participate (SNo, project-number)(f) supervisor (SNo, PNo)solution2: (1) E-R diagram is as follows(2) the reduced tables are as follows: (a) student (SNo, name, age, degree-program, department-number)Note: relationship study is reduced to this table(b) professo

16、r (PNo, name, age, research-area, department-number)Note: relationship works is reduced to this table(c) department (department-number, depart-name, main-officer)(d) project(project-number, starting-date, ending-date, budget, PNo)Note: relationship manage is reduced to this table(e) participate (SNo

17、, project-number)(f) supervisor (SNo, Project-number, PNo)三、 Notown Records company needs to store information about songs, albums（专辑） and musicians who perform on its albums in a database. Consider the following information: Each musicians that records at company has an Id (which is unique), a name

18、 , an address, and a phone number. Each instrument used in company has a name and an unique ID. Each album recorded on the Notown label has a title, a copyright date, a format, and an album identifier. Each song recorded at Notown has a title and an author, and each song can be identified by its tit

19、le. An musician may play several instruments, and an instruments may be played by several musicians. Each album has a number of songs on it, and each song belongs to only one album. Each song is performed by one or more musicians, and a musician may perform a number of songs. Each album has exactly

20、one musician who acts as its producer. A musician may produce several albums, but some musicians may have no albums. (1) Design the E/R diagram for hospital database on basis of the information mentioned above .(10 points)Note: mapping cardinality of each relationship and participation of each entit

21、y 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) 四、 A hospital database needs to store information about doctors, patients, sickroom (病房), and departmen

22、ts (科室). Consider the following information Each doctor has descriptive attributes of identifier number, name, age, and technical title). Each patient has descriptive attributes of the number of medical records(病历) , name, age, and sex Each sickroom has descriptive attributes of the number of sickro

23、om, the address Each department has descriptive attributes of name, address, telephone-number Integrity constraints:f. Each doctor must belong to one (and only one) department; and for each department, there are more than one doctors belonging to it. g. Each patient is taken care of by one and only

24、one responsible doctor; a doctor may be responsible for no patients, or only one patients, or more than one patientsh. Each patient lives in one and only one sickroom; a sickroom may contain more than one patientsi. Each sickroom can be managed by more than one department; but for some departments,

25、there are no sickrooms managed by them, while for other departments, there are more than one managed sickroom. (1) Design the E/R diagram for hospital database on basis of the information mentioned above .(10 points)Note: mapping cardinality of each relationship and participation of each entity to t

26、he relationship should be described in the diagram.(2) Convert the E-R diagram to the proper relational schema, and give the primary key of each relation schema by underlines. (10 points)五、 (20 points) A pharmacies database needs to store the information about the pharmaceutical companies（制药公司）and o

27、thers. The relevant information is as follows: Each pharmaceutical company is identified by its name and has a phone number. Each pharmacy (药房) is identified by its name and has an address, and several phone numbers. For each drug, the trade name and formula must be recorded, and the trade name iden

28、tifies a drug uniquely. Each drug is developed by only one given pharmaceutical company, and one pharmaceutical company can develop several drugs and perhaps develops no dug now. Each pharmacy sells several drugs and has a price for each. A drug could be sold by several pharmacies, and the price may

29、 vary from one pharmacy to another. Pharmaceutical companies make contracts with pharmacies, but some pharmaceutical companies may have no contract. A pharmaceutical company can sign contracts with several pharmacies, and a pharmacy can also enter into contracts with several pharmaceutical companies

30、. Each contract is described by a start date, an end date and the content of the contract. (1) Design the E/R diagram for hospital database on basis of the information mentioned above. (10 points)Note: the mapping cardinality of each relationship and participation of each entity to the relationship

31、should be described in the diagram.(2) Convert the E-R diagram to the proper relational schemas, and give the primary key of each relation schema by underlines. (10 points)六、 (8 Points)A school is going to arrange a sports day for the students. A database to keep track of participants and activities

32、 during the sports day is to be created. Consider the following information: (1). Participating persons. Each person has a number and a name. The person is identified by the number. (2). Teams. Each team has a number and a name. The team is identified by the number. (3). A team consists of several persons, and a person can be a member of several teams.A team must have at least one person, and a person may not particip