【数据库系统概念】E-R建模及关系表转换

 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

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

   

   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

1. 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:

1.  A student studies in one (and only one) department
2.  A Professor works in one (and only one) department
3. Each project must be managed by one and only one professor, and each professor must manage at least one project.
4.  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.
5.  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

1. 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.)
2. Convert the E-R diagram to the proper relational schema, and give the primary key of each relation schema by underlines.

2.2 Answer

1. 
   

2. 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)

转载于:https://juejin.im/post/5cb025b0f265da036b4a5399