E-R建模及关系表转换-例题习题-2014-submitted精品文档一、 Consider the following information in an airport databasel An airport is described by its name and the city that the airport locates atl Each airplane has an unique registration number and also the date of production as its descriptive attributesl Each airplane model is identified by a model number and has a capacity and a weightl A technician is characterized by an unique technician_id, his name and his phone number l Each airport accommodates a number of airplane models, and an airplane model may appears in several airports; but some airports are not suitable for accommodaing several special plane models.l Each airplane is of a specific model, e.g. Boeing 737; and for each model, there are more than one airplane being of it. l for each airport, there are some technicians working there, and each technician must works at only one airport.l Each technician is responsible for one or more plane models, and each plane model has at least one technician responsible for itl 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 sometimes 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 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 primary key of the entities, mapping cardinality of each relationship and participation of each 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：(2) (10 points)实体airport归结为: airport(a-name, city); 实体model归结为: model(m-num, weight, capacity); 联系accommodate归结为: 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 store information about students, professors, projects, and departments. Consider the following information: l Each student has a SNo, a name, an age, and a degree program (e.g. M.S. or Ph.D.).l Each professor has a PNo, a name, an age, and a research specialty.l Each project has a project number, a starting date, an ending date, and a budget.l Each department has a department number, a department name, and a main office.l 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 professor, 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 supervise 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 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.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: 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: relationship 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) 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