Reference no: EM133672531

Assignment

AIMS AND OBJECTIVES:

to represent a problem description given in natural language as an (Enhanced) Entity- Relationship model

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.

NEA operates both local and international flights. All flights are identified by a unique 9-digit flight identifier. In addition to the identifier, flight details that are recorded include the intended departure date and time, the planned average speed, average height, estimated journey duration (in minutes), estimated fuel consumption, and actual departure and arrival times.

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.
Each aircraft used by NEA is given a unique aircraft number. Each aircraft belongs to an aircraft model, which is also retained in the database along with the information that are relevant to each model 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 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 several details 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 each ticket NEA assigns a unique ticket number and 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 (which you are not required to deal with here). Customer, ticket, and flight details are all kept on the database indefinitely after a flight.

For each employee NEA keeps their personal information on record. The information includes staff number, name, address, email, phone number and passport number. All employees are categorized into two groups, i.e., pilots and flight attendants. However, 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). Additionally, for each pilot their total hours of prior flight experience and a list of qualifications/licenses held are also recorded.

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. 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). For each flight, the details about the participating flight attendants are also recorded.

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.

Note: The diagram must not be drawn by hand. Use any software to draw figures in your assignment. However, make sure to follow the notations introduced in this subject. You are NOT required to transform your EER diagram into a set of tables for this assignment.