New Endor Airlines Database New Endor Airlines (NEA) is the premier aviation service in the small island nation of New Endor, located in the southwestern Pacific Ocean. For the 40 years since its inception, NEA has operated effectively with paper-based record keeping. Recently, New Endor has grown in popularity as a business and tourism destination. Due to increased demand for their services, NEA has decided to modernize their record keeping and automate much of their management activity. You have been contracted by NEA to design a new relational database that fits in with their current business practices. The database that you will design is focused on flight and aircraft management and is described in full here. Ticket billing, advertising and other business activities are handled in different databases that you will not be dealing with. NEA operates local and international flights. Being small and somewhat geographically isolated, all international flights from New Endor are classified as long-haul, and all local flights are classified as short-haul. Flights are identified by a unique 9-digit flight identifier. The last 3 digits of the flight identifier are referred to as the flight number. Passengers are given only the flight number (e.g., a ticket reads flight number NE370), and the airline ensures that no two flights with the same flight number are in the air simultaneously. Flight details that are recorded include the intended departure date and time, route information (including departure and arrival locations), passenger/ticket and staff details, and details about the aircraft being operated. Each aircraft is given a unique aircraft number and classified into one of two types according to which type of flight they are used for: short-haul or long-haul. For short-haul aircraft, NEA dedicates some cargo space to mail transport, so the dedicated mail cargo capacity is stored (though other details regarding mail will be handled in a different database). For long-haul aircraft, extra medical supplies are required, so they record the number of advanced medical packs and the number of defibrillators on board. Each aircraft belongs to an aircraft model, which is also retained in the database along with basic model information such as the unique model identifier, number of economy class seats, business class seats, first class seats, the cargo capacity, fuel capacity, length, and wingspan. The airline often purchases more than one of each aircraft model. Each flight travels along one strictly defined route. Each route has a unique route identifier, route description, departure location and arrival location. A location is defined as a local or international airport, having a unique 3 letter airport code (issued by the International Air Transport Association). Additional location information includes the country, address and contact details of the airport. There can be more than one route from/to any location, and a route can be reused for multiple different flights. Aside from choosing the route, flight planners must take fuel consumption, weather conditions and air traffic into account. So, when choosing the route, the planned average speed, average height, estimated journey duration (in minutes) and estimated fuel consumption must be specified for each flight.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 Page 3/6 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. 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).TASKS: 1. You are required to develop an EER model for the above problem description. The EER should contain all necessary information such as entities, attributes, primary keys, relationships (including specialization/union if any), cardinalities, and participation (including (min, max)). Any assumptions should also be stated clearly. If assumptions are made, it is important to make sure that the assumptions reflect possible real practice for a similar business and do not contradict with the problem description above. Assignment should be typed, not written/drawn by hand. Use any software to draw figures in your assignment. However, make sure to follow the notations introduced in this subject. [100%] Please note the following important points before you get started: . You are NOT required to transform your EER diagram into a set of tables for this part of the assignment. You are also NOT required to perform normalization of the user views provided in the appendices. The forms in the appendices should be used as a source of supplementary information for this assignment. For example, Appendix 1 may give you some clarity about what information is required to be stored for a customer/passenger (among other things).Appendix A: Passenger Flight Form New Endor Airlines PASSENGER TICKET FORM Welcome to New Endor Airlines. If you have flown with us before, then you do not need to fill out Section A (in which case, please skip to section B) Section A: Passenger Details First name: Last name: Home address: Postcode: Country: Date of birth: Email address: Phone: Passport number: Section B: Flight Details Please complete this section with assistance from your travel agent Flight number: Departure date: Departure airport: Arrival airport: Seat number (check availability): Class (tick one): Economy a Business o First class Checked luggage limit (tick one): 15kgo 30kg a 50kgo Meal choice (tick one): Standard (ST) a Vegan / gluten free (VG) a Please give the passenger number(s) of any people you are flying with Add another page if you are flying with more than 3 passengers Passenger number 1: Passenger number 2: Passenger number 3: Please describe any food allergies or medical conditions: Failure to report a serious medical condition prior to flying is illegal and may compromise your safety and the safety of others on board the flight Admin use only Passenger number: Ticket number: Identification provided? a Staff name: Date purchased: Staff signature:Appendix B: Flight Captain's Log 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: Staff number: Flight identifier: Flight number: Was the captain incapacitated during flight? Yes = No o First officer full name: Staff number: Date: Time (use 24hr format): Captain signature: First officer signature: Date and time of departure (use 24hr format): Date and time of arrival (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 a No a Give details of irregular events. Attach and sign extra page if necessary. Event 1 Date: Time: Details: Event 2 Date: Time: Details: Event 3 Date: Time Details: Event 4 Date: Time Details