Airplane maintenance shop needs to keep track of each activity they perform and do queries when needed. An airplane must have a complete maintenance history with them all the time. And the airplane must be inspected at a regular interval and keep a record of finding during the inspection. Inspector is responsible of any issue that may come up due to improper inspection.
The shop needs to keep track of who did and the inspection.
The following information must be kept in the database.
 Airplane details including make, model weight, capacity.
 Technician information including name, address, telephone, email, department
 Inspectors’ information including name, address, telephone, email, department, qualifications
 Qualifications can be any of the certification related to airplane inspection. You can assume the list of attributes for the qualification relation
 Airplane must have a maintenance history which include the line items that were performed during the maintenance.
 There should be a relation to keep track of who did the inspection and when.
 For each maintenance, there will be set of technicians assigned. We need to retrieve later who was in the team of each maintenance for a given airplane.
 There can be several departments in the maintenance shop. Assume several departments on your own.
 We need to keep track of the airplane manufacturing company details too. This includes name, contact information
 Each airplane model will have a maintenance procedure that is specific to each airplane model. We need to retrieve the procedure of each airplane model too. (Procedure can be a set of actions that we need to perform. It can be a single document)
1. Design the above system using Entity-Relationship model.
2. Convert the E-R model into relations
a. Each of the entity must have its own relations and relationships may or may not
have a dedicated relation.
b. You must clearly define the primary keys of each relation.
3. Normalize the resulting relational schema resulted in section 2.

4. Write relational algebra expressions for the following queries
a. To retrieve the technicians assigned to each maintenance activity
b. To retrieve the name, email of the inspectors for a given airplane