Develop a conceptual data model reflecting the following requirements: • Identification of the relations (entity types). • Identification of relationship types and their participation and cardinality constraints. • Identification of attributes and association of attributes with entity or relationship types. • Determination of candidate and primary key attributes of entity types. • Determination of specialization/generalization and categorization relationships, whenever it is appropriate. • Enhanced Entity-Relationship (EER) diagram to reflect the requirements. Develop an airport information system to organize all information about all the airplanes stationed and maintained at the airport in the XYZ county. You need to specify the assumptions that are used in your design, if there is any. The relevant information is as follows: • Every airplane has a registration number, and each airplane is of a specific model. • The airport accommodates a number of airplane models, and each model is identified by a model number (e.g., DC-10) and has a capacity and a weight. • A number of technicians work at the airport. You need to store the name, SSN, address, phone number, and salary of each technician. • Each technician is an expert on one or more plane model(s), and his or her expertise may overlap with that of other technicians. This information about technicians must also be recorded. • Traffic controllers must have an annual medical examination. For each traffic controller, you must store the date of the most recent exam. • All airport employees (including technicians) belong to a union. You must store the union membership number of each employee. You can assume that each employee is uniquely identified by a social security number. • The airport has a number of tests that are used periodically to ensure that airplanes are still airworthy. Each test has a Federal Aviation Administration (FAA) test number, a name, and a maximum possible score. • The FAA requires the airport to keep track of each time a given airplane is tested by a given technician using a given test. For each testing event, the information needed is the date, the number of hours the technician spent doing the test, and the score the airplane received on the test.
Develop a conceptual data model reflecting the following requirements:
• Identification of the relations (entity types).
• Identification of relationship types and their participation and cardinality constraints.
• Identification of attributes and association of attributes with entity or relationship types.
• Determination of candidate and primary key attributes of entity types.
• Determination of specialization/generalization and categorization relationships, whenever it is appropriate.
• Enhanced Entity-Relationship (EER) diagram to reflect the requirements.
Develop an airport
and maintained at the airport in the XYZ county. You need to specify the assumptions that are
used in your design, if there is any. The relevant information is as follows:
• Every airplane has a registration number, and each airplane is of a specific model.
• The airport accommodates a number of airplane models, and each model is identified by a
model number (e.g., DC-10) and has a capacity and a weight.
• A number of technicians work at the airport. You need to store the name, SSN, address,
phone number, and salary of each technician.
• Each technician is an expert on one or more plane model(s), and his or her expertise may
overlap with that of other technicians. This information about technicians must also be
recorded.
• Traffic controllers must have an annual medical examination. For each traffic controller, you
must store the date of the most recent exam.
• All airport employees (including technicians) belong to a union. You must store the union
membership number of each employee. You can assume that each employee is uniquely
identified by a social security number.
• The airport has a number of tests that are used periodically to ensure that airplanes are still
airworthy. Each test has a Federal Aviation Administration (FAA) test number, a name,
and a maximum possible score.
• The FAA requires the airport to keep track of each time a given airplane is tested by a given
technician using a given test. For each testing event, the information needed is the date, the
number of hours the technician spent doing the test, and the score the airplane received on
the test.
Sample Queries
1. Insert a new technician into the
2. Delete an existing airplane from the database.
3. Update the expertise of an existing technician.
4. List the details of the technician whose salary is greater than the average of the salary of all
technicians.
5. List all the model numbers that a given technician has the expertise, along with their capacity
and weight information.
6. List the total number of technicians who are experts in each model.
7. List the details (test number, test name, maximum score, etc.) of the FAA tests for a given
airplane, sorted by the maximum scores.
8. List the most recent annual medical examination and his/her union membership number for
each traffic controller.
9. List the total number of tests done by each technician for a given airplane.
10. List the name of the technician, the registration number of the airplane, and the FAA
number of those tests done between September 2021 and December 2021, sorted by the FAA
numbers.
Trending now
This is a popular solution!
Step by step
Solved in 5 steps with 1 images
