Identify entities, attributes $,$ primary keys, relationships, cardinalities and participations, specialisations along with both completeness and disjointedness constraints and notate a union

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