2814ICT/7003ICT Data Management Assignment Trimester 1, 2020 The Airline Case Study Date / time due: Friday Week 11, 17:00 15 May 2020 This assignment contributes 30% to your final assessment. Instructions: The assignment should be done in groups of (maximum) 2 persons. Members in each group should contribute equally. Submit one report per group with your solutions to all the tasks. On the cover sheet include the following Contribution Agreement table (the contribution needs to add up to 100%):
2 Section I. Task Description An airline company needs a database to track its staff, airplanes, flights and passengers and so on. The detailed system specification is given in Section II. Task 1: Conceptual design (50 marks) Draw an (Enhanced) ER diagram for the conceptual design of the database. Make sure your diagram is visible in the submission. Using automatic ER generation tools is NOT acceptable. You may use the Chen’s notation, the UML notation, or the crow’s feet notation in drawing your EERD, but please do not use a mixture of them. If you use any non-standard notation, you must provide a legend. You can make your own reasonable assumptions if necessary. Clearly list your assumptions. Task 2: Logical design (25 marks) Map your (Enhanced) ER diagram to tables, using the rules in text or lecture slides. Clearly show the primary key and foreign keys. Task 3: Schema refinement and documentation (10 marks) 1. Explain whether your tables are (at least) in 3NF. If a table is not in 3NF, modify your ERD or do normalization. If you choose to use non-3NF tables, provide the reasons why. 2. Assign appropriate data types and lengths to all attributes using MySQL data types. Include necessary integrity constraints such as domain constraints, null value constraints, and other important integrity constraints. Document your final database schema using a form similar to the following: 3 Task 4: Implementation (15 marks) Write the MySQL SQL statements for the queries (1) to (8) in SECTION II – 8. While implementation of the tables and the queries is optional, you should make sure the queries are logically correct based on your final design in Task 3. 4 Section II. Terra Firma Airlines Case Study Terra Firma Airlines requires a system to schedule flights and to control passenger reservations. The system must be designed to satisfy the following requirements: 1. The airline has several types of planes of varying passenger capacities and flight ranges. There are one or more airplanes of each type in the airline’s fleet. Each airplane has a unique serial number. For each airplane, its serial number, type, manufacture date, and full service history (service date, place, and description) should be maintained. 2. The airline has pilots, flight attendants as well as other staff. For each member of staff it keeps data about his / her staff number (which is a unique identifier), the staff name, date of birth, home address, and date joined the airline. 3. For each staff member, an emergency contact person, including name, postal address, phone, email, and relationship with the staff, must be recorded. 4. Pilots are certified only on certain types of planes. Information on the types of planes certified for each pilot, as well as the date of certification, must be maintained. 5. The airline provides regular training to flight attendants. Records of all trainings received by each flight attendant must be recorded. Such records include the training program name, the start and finish dates, and a brief description about the content. 6. A flight, which is identified by a unique flight number, has an original city, a destination city but no stop-overs. The scheduled departure time and arrival time must be maintained. In addition to all of the above information, an actual scheduled flight has a date, an airplane, a captain, a co-pilot and up to 8 flight attendants. The captain and co-pilot must both be pilots certified for the assigned type of airplane, and we assume that a pilot can work as captain on one scheduled flight, and as co-pilot on another. In addition, the actual departure time, actual arrival time, and a description (which describes incidents such as cancellation due to weather conditions, or delays due to mechanical problems) need to be recorded. A scheduled flight is uniquely identified by the combination of a flight number and date. 7. The airline needs an online booking system that people can use to search for available flights and purchase tickets. For each ticket sold, the system must record the ticket number (which is an identifier of the ticket), date purchased, payment type (e.g., credit card, paypal, bank transfer), the flight number, flight date, ticket type (e.g., promotion-fare, flexi-fare, premium-fare), ticket price as well as details of the passenger: ID type and ID number (a valid ID can be a passport or a driver’s license), first name, last name, sex, address, contact phone, and email address. The booking system must ensure that the same passenger is not booked on the same scheduled flight more than once. 8. The system must be able to make the following queries (These requirements need to be reflected in Tasks 1 and 2 in Section I): (1) For each airplane (given the serial number), list the type code, type description, 5 capacity and flight range, manufacture date, purchase date, and the next service date. (2) For each pilot (given the staff number), list the pilot name, the type code and description of each of the types of planes on which the pilot is certified. (3) List the flight number, date and airplane serial number for all flights on which the pilot is currently scheduled using the pilot staff number. (4) For each city served, list the city name, the state, and a description of the airport (we assume each city has only one airport). (5) For each city (given the city name), produce a list of the flights scheduled to departure within the next 24 hours. The list should include the flight number, the time of departure in ascending order of departure time. (6) Given the flight number, list the origination city, destination city as well as the departure and/or arrival times at these cities. (7) For a scheduled flight, list the captain, the co-pilot, the flight attendants, and the airplane serial number. (8) For each scheduled flight (given the flight number and date), prepare a customer call list, which includes the ID type, ID number, full name, date of birth, address and contact phone number of the passengers booked on the flight. (9) Given a date, an origination city and a destination city, list all scheduled flights and the number of available seats on each flight. (10) For a given passenger (identified by ID type and ID number), list the flights he or she has been booked on, including the departure/arrival city and time. (11) For a staff member, list the details of the emergency contact person. (12) List all airplanes by the serial number and type code, and if an airplane is scheduled to fly today, also list the flight number. (13) For each flight attendant, find the details of training he/she received. (14) For each airplane (given the serial number), list its scheduled flights (departure and arrival city as well as time) today. 6 Section III. Checklist Task 1: • The EERD contain all necessary entities, relationships, attributes so that all the queries can be answered. • Correct entity/relationship types (strong or weak) are used. • Each strong entity has a PK • Each relationship has the two types of constraints • Correct class-subclass relationships are identified • There are no redundant entities, relationships or attributes (In particular, no foreign key attributes are present in the entities Please do not confuse ER diagram with schema diagram, and confuse entities with tables. Task 2: • You have mapped your EERD to tables correctly using the appropriate rules. • You have marked/listed all primary key and foreign key constraints. Please do not just copy your entities as the tables…usually you need more tables than entities because you may need to map some relationships into tables, and multi-valued attributes into tables. Please do not combined this task with task 3. Task 3: • All your tables are in 3NF (and otherwise convincing reasons are provided to justify the use of non-3NF tables). • An appropriate data type and length for each attribute. • Other constraints such as null value constraints and domain constraints are present, and important business rules are also identified. Task 4: • The SQL commands are logically correct against your final tables in Task 3. Submission: Submit an electronic copy of your assignment online, where the filename should be of the format StudentNumber1StudentNumber2.pdf (for 2-person group), or StudentNumber.pdf (for 1 person group) Each group member should sign the declaration form on the cover sheet.