Each flight ticket is for only one customer and only one flight. When a customer buys...

Transcribed Image Text:

Each flight ticket is for only one customer and only one flight. When a customer buys a flight ticket, NEA assigns them a unique customer number and collects a number of details (see Appendix A) for flight management. The passenger's name, home address, home country, birth date, passport number (if held), email and phone number are kept on file. For the particular flight, NEA assigns a unique ticket number to the passenger and also records the passenger's checked luggage limit (in kilograms), seat number, flight class code (economy 'E', business 'B' or first class 'F'), as well as a short description of any food allergies or medical conditions that the passenger may have. The details of a ticket are only generated once the customer purchases it, and ticket availability is determined by the number of seats on the aircraft model. For each flight, passengers may choose either a regular in-flight meal or the alternative option which is both vegan and gluten free. Other ticket details such as invoices, advertising and pricing are handled on a separate database. Customer, ticket, and flight details are all kept on the database indefinitely after a flight. New Endor Airlines CAPTAIN'S LOG ENTRY IMPORTANT! To be completed by the FLIGHT CAPTAIN for each flight and witnessed by the FIRST OFFICER. If the captain is incapacitated during flight, then responsibility is passed in full to the first officer onwards from the time of the incapacitating event. Captain full name: _ Flight identifier: Was the captain incapacitated during flight? Yes No First officer full name:, Date: Captain signature: First officer signature: Date and time of departure (use 24hr format): Date and time of arrival (use 24hr format): Event 2 Date: Time (use 24hr format): See NEA Flight Operations Manual Section 17B for the definition of an irregular event. Did any irregular events occur during the journey? Yes No a Event 3 Date: Staff number: Flight number: Give details of irregular events. Attach and sign extra page if necessary. Event 1 Date: Time:_ Details: Staff number: Time: Time: Details: Details: V Event 4 Date: Time: Details: Each flight has at least two pilots on board: the flight captain and the first officer. The captain has ultimate responsibility for everything that happens during the flight. During each flight, the captain makes a single entry in the Captain's Log, recording the actual time and date of departure and arrival, as well as a short description of each irregular event that occurred during flight (see Appendix B). The First Officer (FO) is second in command and so, like the captain, the FO is distinguished from any additional pilots that may be on board. To keep a record of pilot activities, NEA assigns a short two letter 'activity code' for each additional pilot on board each flight (aside from the captain and the FO). This code categorizes the reason that the pilot was on board (e.g., *TR* signifies training). When a pilot is hired by NEA, they are given a staff number, and their total hours of prior flight experience are recorded, along with their name, address, email, phone number, passport number (as all NEA flight staff must have a passport), and a list of qualifications/licenses held. The other type of staff to be covered by the database are flight attendants. When hired by the NEA, flight attendants, like pilots, are issued with a staff number, and their name, address, email, phone number and passport number are recorded. It is possible that an NEA employee could be both a flight attendant and an airline pilot (it is not uncommon for NEA pilots in training to work as flight attendants between training flights). Question: a. Normalize the user views provided in Appendices A and B specification. Clearly show individual steps of UNF, INF, 2NF and 3NF for each of the user views. List the final tables. b. Discuss the differences between the result of the top-down approach from question 1 above and the result of the bottom-up approach (Detailed explanation). c. Provide some analysis (with examples) on why they are similar or different for this particular problem. Each flight ticket is for only one customer and only one flight. When a customer buys a flight ticket, NEA assigns them a unique customer number and collects a number of details (see Appendix A) for flight management. The passenger's name, home address, home country, birth date, passport number (if held), email and phone number are kept on file. For the particular flight, NEA assigns a unique ticket number to the passenger and also records the passenger's checked luggage limit (in kilograms), seat number, flight class code (economy 'E', business 'B' or first class 'F'), as well as a short description of any food allergies or medical conditions that the passenger may have. The details of a ticket are only generated once the customer purchases it, and ticket availability is determined by the number of seats on the aircraft model. For each flight, passengers may choose either a regular in-flight meal or the alternative option which is both vegan and gluten free. Other ticket details such as invoices, advertising and pricing are handled on a separate database. Customer, ticket, and flight details are all kept on the database indefinitely after a flight. New Endor Airlines CAPTAIN'S LOG ENTRY IMPORTANT! To be completed by the FLIGHT CAPTAIN for each flight and witnessed by the FIRST OFFICER. If the captain is incapacitated during flight, then responsibility is passed in full to the first officer onwards from the time of the incapacitating event. Captain full name: _ Flight identifier: Was the captain incapacitated during flight? Yes No First officer full name:, Date: Captain signature: First officer signature: Date and time of departure (use 24hr format): Date and time of arrival (use 24hr format): Event 2 Date: Time (use 24hr format): See NEA Flight Operations Manual Section 17B for the definition of an irregular event. Did any irregular events occur during the journey? Yes No a Event 3 Date: Staff number: Flight number: Give details of irregular events. Attach and sign extra page if necessary. Event 1 Date: Time:_ Details: Staff number: Time: Time: Details: Details: V Event 4 Date: Time: Details: Each flight has at least two pilots on board: the flight captain and the first officer. The captain has ultimate responsibility for everything that happens during the flight. During each flight, the captain makes a single entry in the Captain's Log, recording the actual time and date of departure and arrival, as well as a short description of each irregular event that occurred during flight (see Appendix B). The First Officer (FO) is second in command and so, like the captain, the FO is distinguished from any additional pilots that may be on board. To keep a record of pilot activities, NEA assigns a short two letter 'activity code' for each additional pilot on board each flight (aside from the captain and the FO). This code categorizes the reason that the pilot was on board (e.g., *TR* signifies training). When a pilot is hired by NEA, they are given a staff number, and their total hours of prior flight experience are recorded, along with their name, address, email, phone number, passport number (as all NEA flight staff must have a passport), and a list of qualifications/licenses held. The other type of staff to be covered by the database are flight attendants. When hired by the NEA, flight attendants, like pilots, are issued with a staff number, and their name, address, email, phone number and passport number are recorded. It is possible that an NEA employee could be both a flight attendant and an airline pilot (it is not uncommon for NEA pilots in training to work as flight attendants between training flights). Question: a. Normalize the user views provided in Appendices A and B specification. Clearly show individual steps of UNF, INF, 2NF and 3NF for each of the user views. List the final tables. b. Discuss the differences between the result of the top-down approach from question 1 above and the result of the bottom-up approach (Detailed explanation). c. Provide some analysis (with examples) on why they are similar or different for this particular problem.