SHARE
TWEET

Untitled

a guest Oct 21st, 2019 66 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2. ** Rutgers University, CS336 Fall 2019, Group 18 Project, Relational Schema
  3. ** Joseph Doll, Eric Kim, Michala Rose, Kieran Stokes
  4. **
  5. */
  6.  
  7. /* Table schema below */
  8. CREATE TABLE Airports(
  9.     id CHAR(3),
  10.     PRIMARY KEY(id)
  11. );
  12.  
  13. CREATE TABLE Airlines(
  14.     id CHAR(2),
  15.     PRIMARY KEY(id)
  16. );
  17.  
  18. CREATE TABLE Aircraft(
  19.     id CHAR(3),
  20.     PRIMARY KEY(id)
  21. );
  22.  
  23. CREATE TABLE Seats(
  24.     seat_num CHAR(3),
  25.     seat_class CHAR(10),
  26.     craft_id CHAR(3),
  27.     PRIMARY KEY(seat_num, craft_id),
  28.     FOREIGN KEY(craft_id) REFERENCES Aircraft (id) ON DELETE CASCADE
  29. );
  30.  
  31. CREATE TABLE Owned_Craft(
  32.     craft_id CHAR(3),
  33.     airline_id CHAR(2),
  34.     PRIMARY KEY(craft_id),
  35.     FOREIGN KEY(craft_id) REFERENCES Aircraft(id),
  36.     FOREIGN KEY(airline_id) REFERENCES Airlines(id)
  37. );
  38.  
  39. CREATE TABLE Tickets(
  40.     ticket_num CHAR(20),
  41.     booking_fee DECIMAL(6,2),
  42.     round_trip BOOLEAN,
  43.     issue_date DATE,
  44.     total_fare DECIMAL(10,2),
  45.     PRIMARY KEY(ticket_num)
  46.     /*needs customer foreign key*/
  47. );
  48.  
  49. CREATE TABLE Flights(
  50.     flight_num CHAR(4),
  51.     operating_airline CHAR(2),
  52.     flying_aircraft CHAR(3),
  53.     flight_type CHAR(10),
  54.     depart_time TIME,
  55.     arrive_time TIME,
  56.     PRIMARY KEY(operating_airline, flight_num),
  57.     FOREIGN KEY(operating_airline) REFERENCES Airlines(id) ON DELETE CASCADE,
  58.     FOREIGN KEY(flying_aircraft) REFERENCES Aircraft (id) ON DELETE CASCADE
  59.     /*not sure what fare_first and fare_economy are at the moment*/
  60.     /*needs days of operation*/
  61. );
  62.  
  63. CREATE TABLE Trips(
  64.     assigned_seat CHAR(3),
  65.     for_ticket CHAR(20),
  66.     airline CHAR(2),
  67.     flight_num CHAR(2),
  68.     date DATE,
  69.     meal CHAR(20), /*not sure how big it should be*/
  70.     FOREIGN KEY(assigned_seat) REFERENCES Seats (seat_num) ON DELETE CASCADE,
  71.     FOREIGN KEY(for_ticket) REFERENCES Tickets (ticket_num) ON DELETE CASCADE,
  72.     FOREIGN KEY(airline, flight_num) REFERENCES Flights (operating_airline, flight_num) ON DELETE CASCADE
  73. );
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top