Airline Database assignment submit SQL statements as text

Assignment 1

Please submit SQL statements as text file. You do not need to show screen shot. Make sure you have tested your SQL statements in Oracle.

Problem 1. Please create the following tables for a flight reservation database with appropriate primary keys & foreign keys. [50 points]

Assumptions:

  1. Each airline has a number of flights.
  2. Each flight has a departure airport and an arrival airport,
  3. Each flight has a number of schedules. A flight schedule contains departure and arrival time.
  4. Each passenger can make reservations. Each reservation has number of passengers, departure airport, arrival airport, departure date, returning date (if round trip), total price.
  5. Each reservation has an outbound trip and for round trip reservation also an inbound trip. Each trip contains one or more flight legs, where each flight leg corresponding to a flight schedule.

The list of tables is:

  1. Airline table with columns: alcode (3 letter airline code, e.g., ‘UA’ is the code for united), alname (airline name)
  2. Airport table with columns: apcode (3 letter airport code, e.g., ‘BWI’ is the code for Baltimore Washington Airport), apname (airport name), city (city the airport belongs to), state, country
  3. Flight table with columns: fnumber (flight number, usually air line code + a number, e.g., UA 123), alcode (airline code), departure_apcode (departure airport), arrival_apcode (arrival airport)
  4. Flight schedule table with columns: sid (schedule ID), fnumber (flight number),

departure_time (use timestamp with time zone type, e.g., when inserting, timestamp ‘2017-9-12 06:41:00.00 -05:00’ means September 12 2017, 6:41 am Eastern time), arrival_time, price (price for that leg).

  1. Passenger table with columns: pid (passenger ID), pname (passenger name), phone
  2. Flight_reservation table with columns: rid (reservation id), pid (passenger ID), num_passengers (number of passengers), origin_apcode (starting airport), destination_apcode (final destination airport), departure_date (departure date), return_date (return date, null if one-way), total (charge).
  3. Trip detail table with columns: rid (reservation id), sid (schedule ID), flag (1 means outbound trip, 2 means inbound trip), leg (1 means first leg of the trip, 2 means second, …)

Problem 2. Insert at least three rows of data to each table. Make sure you keep the primary key and foreign key constraints. [50 points]

Looking for solution of this Assignment?

WHY CHOOSE US?

We deliver quality original papers

 Our experts write quality original papers using academic databases.We dont use AI in our work. We refund your money if AI is detected  

Free revisions

 We offer our clients multiple free revisions just to ensure you get what you want.

Discounted prices

 All our prices are discounted which makes it affordable to you. Use code FIRST15 to get your discount

100% originality

 We deliver papers that are written from scratch to deliver 100% originality. Our papers are free from plagiarism and NO similarity.We have ZERO TOLERANCE TO USE OF AI

On-time delivery

 We will deliver your paper on time even on short notice or  short deadline, overnight essay or even an urgent essay
 
/by