E-R Schema Design
Due to the recent frequent aviation accidents, the human resource (HR) department head for an airline called iFLY decided to found an aviation academy under their company in order to secure the supply of outstanding pilots. The HR manager has hired you to set up a database to help him to keep track of each student pilot's performance. The first part of your project will require capturing his design requirements and producing an E-R diagram representing a suitable conceptual schema for the database.
Part 1.
Here is a transcript from your first interview with the HR department's manager:
"We have to deal with student pilots and airplanes. Students have a unique student id, a name, and a year that students are in. Airplanes have a unique airplane id, model, a cruising range, and expected training hours to get a certificate for flying the airplane model. We have only one training-purpose airplane for each model. Each student may learn one or more airplanes. Similarly, each airplane may be learned by one or more students. Also, each student must learn at least one airplane. However, there could be some airplanes which are not learned by any students"
Design an E-R schema to represent the required information and express your design in the form of an E-R diagram. Be sure that your design captures all of implications of the manager's description, including:
(a) [20pts] All of the relevant entities and their attributes (including keys).
(b) [10pts] All of the relevant relationships and any associated attributes.
(c) [10pts] Appropriate cardinality (a.k.a. key) constraints for the relationships. (d) [10pts] Appropriate participation constraints for the relationships.
Part 2.
After you presented the HR manager an E-R diagram produced from the first interview transcript, the HR manager recognized that he had to provide more complete information to you in the first interview instead of having expected you to come up with an complete E-R diagram that captures all he needs. Now, he gives more complete information as follows:
"In addition to the information that I provided in the first interview, we want to record each student's flying hours for each airplane as flight records. This makes sure that each student fulfills the expected training hours for each airplane so that they can get a certificate for flying the airplane. More specifically, each flight record includes a flight begin date&time and a flight hours for that specific flight. A given student will never fly a given airplane more than once on any given day. Furthermore, we want to capture the following concept correctly. A student is able to learn an airplane without flying the airplane yet. Also, flight begin date&time in each flight record must be a valid real date&time. In other words, we don't want to have any dummy value or dummy records in the flight records. This is very critical."
Extend your E-R Diagram in order to reflect the above requirements. Again, be sure that your design captures all of implications of the manager's description, including:
(a) [20pts] All of the relevant entities and their attributes (including keys).
(b) [10pts] All of the relevant relationships and any associated attributes.
(c) [10pts] Appropriate cardinality (a.k.a. key) constraints for the relationships. (d) [10pts] Appropriate participation constraints for the relationships.