New Endor Airlines Database New Endor Airlines (NEA) is the premier aviation service in the small...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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: Date of birth: Country: Email address: Phone: Passport number:, Section B: Flight Details Please complete this section with assistance from your travel agent Flight number: Departure airport: Arrival airport: Departure date: Seat number (check availability): Class (tick one): Economy Business First class Checked luggage limit (tick one): 15kg 30kg 50kg Meal choice (tick one): Standard (ST) Vegan/ gluten free (VG) 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? Staff name: Date purchased: Staff signature: 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: Date of birth: Country: Email address: Phone: Passport number:, Section B: Flight Details Please complete this section with assistance from your travel agent Flight number: Departure airport: Arrival airport: Departure date: Seat number (check availability): Class (tick one): Economy Business First class Checked luggage limit (tick one): 15kg 30kg 50kg Meal choice (tick one): Standard (ST) Vegan/ gluten free (VG) 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? Staff name: Date purchased: Staff signature:
Expert Answer:
Related Book For
Modern Advanced Accounting In Canada
ISBN: 9781259066481
7th Edition
Authors: Hilton Murray, Herauf Darrell
Posted Date:
Students also viewed these databases questions
-
Managing Scope Changes Case Study Scope changes on a project can occur regardless of how well the project is planned or executed. Scope changes can be the result of something that was omitted during...
-
Read the case study "Southwest Airlines," found in Part 2 of your textbook. Review the "Guide to Case Analysis" found on pp. CA1 - CA11 of your textbook. (This guide follows the last case in the...
-
A flat and infinitely large sheet with uniform charge density moves with constant speed in a direction parallel to its surface. Confirm the differential form of Poyntings theorem at every point not...
-
Refer to Example 9.7. Does the data suggest that the standard deviation of the strength distribution for fused specimens is smaller than that for not-fused specimens? Carry out a test at significance...
-
Many people have asked the question "Isn't office politics just for incompetents?" What is your answer to this question?
-
A Pepsi promotion encouraged consumers to collect Pepsi points and redeem them for merchandise. If they did not have quite enough points for the prize they wanted, they could buy additional points...
-
A number of graphs displaying cost behavior patterns are shown below. The vertical axis on each graph represents total cost, and the horizontal axis represents level of activity (volume). Required:...
-
5. A compact car has a maximum acceleration of 4.5 m/s when it only carries the driver. The driver and the car combined, have a total mass of 1250 kg. What is the maximum acceleration after picking...
-
Macarrys Bicycle Company makes and sells high-quality bicycles, primarily to larger North American bicycle retail outlets and to some wholesalers for smaller retail shops. They have several models,...
-
Brmhults Juice, based in Sweden, produces chilled fruit and vegetable juices. A manager at Brmhults is considering purchasing a new machine to bottle 16-fluid-ounce (473-milliliter) bottles of 100%...
-
Why is it important that an experts report include details of data sources used?
-
Define related parties.
-
What will an auditor consider when evaluating test results?
-
Is a Critical Chain schedule more useful to people planning and managing tasks, or to people actually performing the tasks? Explain.
-
What does the extent of audit testing refer to?
-
In January 2019, Olivia took over an old factory with the intention of renovating it and operating it as a trampoline park. Olivia entered into two contracts; One with William to do all of the...
-
Doorharmony Company makes doorbells. It has a weighted- average cost of capital of 5% and total assets of $ 5,900,000. Doorharmony has current liabilities of $ 750,000. Its operating income for the...
-
Ashton Inc. acquired a 40% interest in Villa Corp. at a bargain price because Villa had suffered significant losses in past years. Ashton's cost was $200,000. In the first year after acquisition,...
-
Describe the difference in accounting under ASPE versus IFRSs for the elimination of unrealized profits in ending inventory on a downstream transaction between an investor and an associate.
-
At the end of the year, the parent's investment account had an equity method balance of $120,000. At this time, its 75%-owned subsidiary had shareholders' equity totaling $125,000. How much was the...
-
Use the Akaike information or Schwartz criteria to select variables for a predictive model.
-
Identify influential observations in a multiple regression model.
-
Explain what is meant by (a) an omitted variable and (b) an irrelevant variable. Explain the consequences of omitted and irrelevant variables for the properties of the least squares estimator.
Study smarter with the SolutionInn App