Transcribed TextTranscribed Text

1 The airline case study Section I. Task Description An airline company needs a database to track its staff, airplanes, flights and passengers and so on. The detailed system specification is given in Section II. You can make your own reasonable assumptions if necessary. Your tasks are: Task 1: Conceptual design Draw an EER or ER diagram for the conceptual design of the database. List all of your assumptions. Task 2: ERD to table translation Map your EERD or ERD to tables, using the rules in text or lecture slides. Clearly show the primary key, foreign keys, and alternate keys. Task 3: Schema refinement and documentation Check your tables are at least in 3NF. If not, modify your ERD or do normalization. If you choose to use non-3NF tables, provide the reasons why. Assign appropriate data types and lengths to all attributes. Refine the schema to include necessary integrity constraints such as domain constraints, null value constraints and possibly more complex business rules. Document your final database schema clearly, e.g., you may use a form similar to the following one. 2 Task 4: SQL Script files Make an SQL script file (and name it for example airline.sql) that contain SQL statements to create the tables, insert sample data (minimum 5 records for each table), and do the queries (1), (5), (8), (9) and (10). You need to make sure your script files work correctly. Create a transcript of the execution of all your solutions (and name it for example airline.lst). IMPORTANT: In the transcript include the question number in SQL statements using SQLPlus comments (‘‐‐') for example: SELECT * FROM employee E ‐‐ Q5 3 Section II. Terra Firma Airlines Case Study Terra Firma Airlines requires a system to schedule flights and to control passenger reservations. The system must be designed to satisfy the following requirements: 1. The airline has several types of planes of varying passenger capacities and flight ranges. There are one or more airplanes of each type in the airline's fleet. Each airplane has a unique serial number. 2. The airline has pilots, flight attendants as well as other staff. For each member of staff it keeps data about his / her staff number (which is a unique identifier), the staff name, date of birth, home address, and date joined the airline. In addition, previous work experience including company, position, start and finish dates are also recorded. 3. For each staff member, an emergency contact person, including name, postal address, phone, email, and relationship with the staff, must be recorded. 4. Pilots are certified only on certain types of planes. Information on the types of planes certified for each pilot, as well as the date of certification, must be maintained. 5. The airline provides regular training to flight attendants. Records of all trainings received by each flight attendant must be recorded. Such records include the training program name, the start and finish dates, and a brief description about the content. 6. A flight, which is identified by a unique flight number, has an original city, a destination city and possibly one stop-over city. The time of departure from the origination city, time of arrival at the destination city, and the arrival and departure times at the stop-over city must be recorded (For example, flight TF88 departs Brisbane at 10:00, arrives at Sydney at 11:15, departs Sydney at 12:15, and arrives at Perth at 13:40 local time every day). In addition to all of the above information, an actual scheduled flight has a date, an airplane, a captain, a co-pilot and up to 8 flight attendants. The captain and co- pilot must both be pilots certified for the assigned type of airplane, and we assume that a pilot can work as captain on one scheduled flight, and as co-pilot on another. A scheduled flight is uniquely identified by the combination of a flight number and date. 7. The airline needs an online booking system that people can use to search for available flights and purchase tickets. For each ticket sold, the system must record the ticket number (which is an identifier of the ticket), date purchased, payment type (e.g., credit card, paypal), the flight number, flight date, departure city, arrival city, ticket type (e.g., promotion-fare, flexi-fare, premium-fare), ticket price (for each scheduled flight, the ticket type, departure city, arrival city and purchase date determine the ticket price) as well as details of the passenger: ID type and ID number (a valid ID can be a passport or a driver’s license), first name, last name, sex, address, contact phone, and email address. The booking system must ensure that the same passenger is never booked on the same scheduled flight for the same departure or arrival cities more than once. 4 8. The system must be able to make the following queries: (1) For each airplane (given the serial number), list the type code, type description, capacity and flight range, manufacture date, purchase date, and the next service date. (2) For each pilot (given the staff number), list the pilot name, the type code and description of each of the types of planes on which the pilot is certified. (3) List the flight number, date and airplane serial number for all flights on which the pilot is currently scheduled using the pilot staff number. (4) For each city served, list the city name, the state, and a description of the airport (we assume each city has only one airport). (5) For each city (given the city name), produce a list of the flights scheduled to arrive or depart within the next 24 hours. The list should include the flight number, the time of arrival or departure in ascending order of time. (6) Given flight number, list origination city, stop-over city (if applicable), and destination city as well as the departure and/or arrival times at these cities. (7) For a scheduled flight, list the captain, the co-pilot, the flight attendants, and the airplane serial number. (8) For each scheduled flight and a departure city, prepare a customer call list, which includes the ID type, ID number, full name, date of birth, address and contact phone number of the passengers booked to board the flight. (9) Given a date, an origination city and a destination city, list all scheduled flights and the number of available seats on each flight. (10) For a given passenger (identified by ID type and ID number), list the flights he or she has been booked on, including the departure/arrival city and time. (11) For a staff member, list the details of the emergency contact person. (12) For a given date, list the airplanes scheduled for flights and those not scheduled for flights. (13) For each flight attendant, find his/her work experiences and details of trainings he/she received. (14) For each airplane (given the serial number), list its scheduled flights (departure and arrival city as well as time) today. 5 Section III. Checklist Task 1: • You have recognised every entity (and represented it as an entity or as an attribute as appropriate). • You have recognised every weak entity. • You have correctly recognised generalisation hierarchies. • All relations are recognised and represented, or you have given suitable alternative representation of the corresponding facts. • Identifying relationships recognised and represented. • Correct primary keys represented for each entity participation and cardinality constraints are represented for each relationship (either of the two cardinality notations taught in the lectures is acceptable, as long as no ambiguity arises). • Partial keys are correctly represented for each weak entity. • Correct representation of complex and/or multi-valued attributes was given Any necessary assumptions are written on a separate sheet. I.e., you have listed all facts that you needed to complete your diagram, but the text did not contain the information (or the text was ambiguous). Task 2: • You have submitted a relational schema, and • You have marked all primary key and foreign key constraints. • You have correctly represented every attribute of each entity and relation. • You have chosen a correct way to map every entity, relation and attribute. Task 3: • All you tables are in 3NF (and otherwise convincing reasons are provided). • An appropriate data type and length for each attribute. Other important constraints such as null value constraints and domain constraints are present. Important business rules are also identified. Task 4: • All the SQL commands for creating tables are included. • All the SQL commands for inserting sample data (at 5 rows per table) are included. • All the SQL commands for doing the five queries are included. • You have included the complete execution results of all your commands from the transcript.

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

Task 1

• One Plane Type for many aircrafts to be flown.
• An Aircraft that has many Flights for passengers to take and pilots to fly
• A flight has multiple tickets depending on the capacity of the plane
• Passenger can book one flight ticket
• A Flight has many staff members including a pilot, co-pilot and 8 attendants
• The staff can have many certified pilots
• The staff can have many trained attendants with various training sessions
• One City can have many flights based on location
• One city can provide many flight tickets based on the availability of location and time of the flight
• There are no weak entities at all
• The time for all flights is to set on a 24 hour schedule.
• The passenger’s Sex to a single letter either ‘M’ or ‘F’
• The pay type of a ticket is to be used with methods of payment such as cash, Paypal, or credit card.
• The ticket type is to be Promotion-fare, flexi-fare, or premium-fare
•The stop over city and time fields are not required on flights...
$100.00 for this solution

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Upload a file
Continue without uploading

We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats