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 attributes
- 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 airports 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.
- 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 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.1 Question
- Design the E/R diagram for the airport database on the basis of the information mentioned above
2.Convert the E-R diagram to the proper relational schema, and give the primary key of each relation schema by underlines.
1.2 Answers
- E-R
2.
- 实体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);
- 多对一合并 :
模式 be of(re-num , m-num) ,模式 airplane(re-num , date)
与airplane合并后的模式属性为 :包含两个模式所有属性的并集:
airplane(re-num, date, m-num),
合并后的主码为融入关系的那个实体集的主码,即re-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)
2. A university student database needs to store information about students, professors, projects, and departments. Consider the following information:
- l Each student has an 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 student studies in one (and only one) department
- A Professor works in one (and only one) department
- Each project must be managed by one and only one professor, and each professor must manage at least one project.
- 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.
- When a student work on a project, the professor managing this project must supervise the student’s work. One student may work on several projects, so he may have several supervisors.
2.1 Question
- Design and draw an E/R diagram for this database that captures the information above .(mapping cardinality of each relationship and participation of each entity to the relationship should be described in the diagram.)
- Convert the E-R diagram to the proper relational schema, and give the primary key of each relation schema by underlines.
2.2 Answer
the reduced tables are as follows:
- student (SNo, name, age, degree-program, department-number)
Note: relationship study is reduced to this table
原 student(SNO,age,name,degree-program)
study(SNO,department-number)
多对一关系合并后,关系study 并入新的关系模式student,属性为原来两者属性的并集,主码为student的主码:
student (SNo, name, age, degree-program, department-number)
- professor (PNo, name, age, research-area, department-number)
Note: relationship works is reduced to this table
- department (department-number, depart-name, main-officer)
- project(project-number, starting-date, ending-date, budget, PNo)
- Note: relationship manage is reduced to this table
- participate (SNo, project-number)
- supervisor (SNo, PNo)