EER Modelling Problem DescriPtion:

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 NE$370),$ 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, departure terminal, arrival terminal, 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. All aircraft must be serviced regularly, with an entry added to the aircraft's Service Log. The service log includes the date of service, a description of work done, and the cost of servicing. The frequency of servicing depends on the 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, wingspan, and the number of flight hours between services as recommended by the manufacturer. The airline often purchases more than one of each aircraft model.

Flights must be carefully planned prior to departure. 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 airpor 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.

CSE$4$DBF $(2024)-$ Assessment $2$ T$3$

Page $\frac{2}{4}$

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 $\text{'}E\text{'},$ business $\text{'}B\text{'}$ or first class $\text{'}F\text{'}),$ 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, an