--  Create ARS Tables for Derby with Database name ARS
--  Copyright 2021, Stephen Withrow, Webucator
--
DRIVER 'org.apache.derby.jdbc.ClientDriver';
CONNECT 'jdbc:derby://localhost:1527/ARS;create=true' USER 'guest' PASSWORD 'password' ;
DROP TABLE reservations;
DROP TABLE flights;
DROP TABLE airlines;
DROP TABLE cities;
DROP TABLE paxprofiles;

CREATE TABLE airlines 
(
  code CHAR(2) NOT NULL,
  name VARCHAR(50) NOT NULL,
  CONSTRAINT pk_airlines PRIMARY KEY (code)
);

CREATE TABLE cities
(
  cityname VARCHAR(50) NOT NULL,
  airport VARCHAR(50) NOT NULL,
  CONSTRAINT pk_cities PRIMARY KEY (cityname)
);

CREATE TABLE flights
(
  flightNo NUMERIC(5) NOT NULL,
  carrier CHAR(2) NOT NULL,
  originCity VARCHAR(50) NOT NULL,
  destinationCity VARCHAR(50) NOT NULL,
  totalSeats NUMERIC(4),
  fare NUMERIC(5,2),
  departHour NUMERIC(2),
  departMin NUMERIC(2),
  departAM NUMERIC(1),
  arriveHour NUMERIC(2),
  arriveMin NUMERIC(2),
  arriveAM NUMERIC(1),
  CONSTRAINT pk_flights PRIMARY KEY (flightno),
  CONSTRAINT fk_flights_airlines foreign key (carrier) REFERENCES airlines
);

CREATE TABLE paxprofiles
(  
  paxID CHAR(20) NOT NULL,
  paxPassword VARCHAR(30) NOT NULL,
  paxLName VARCHAR(50) NOT NULL,
  paxFName VARCHAR(50) NOT NULL,
  accesscount NUMERIC(10),
  CONSTRAINT pk_paxprofiles PRIMARY KEY (paxID)
);

CREATE TABLE reservations
(  
  paxID CHAR(20) NOT NULL,
  flightNo NUMERIC(5) NOT NULL,
  numberofseats NUMERIC(3) NOT NULL,
  seats VARCHAR(100),
  fare NUMERIC(5,2),
  CONSTRAINT pk_reservations PRIMARY KEY (paxID, flightno),
  CONSTRAINT fk_res_pax foreign key (paxID) REFERENCES paxprofiles,
  CONSTRAINT fk_res_flights foreign key (flightNo) REFERENCES flights
);

INSERT INTO airlines VALUES ('DL', 'Delta');
INSERT INTO airlines VALUES ('NW', 'NorthWest');
INSERT INTO airlines VALUES ('UA', 'United Airlines');
INSERT INTO airlines VALUES ('US', 'US Airways');
INSERT INTO cities VALUES ('Chicago', 'O''Hare International Airport');
INSERT INTO cities VALUES ('Orlando', 'McCoy Field');
INSERT INTO cities VALUES ('New York', 'JFK Airport');
INSERT INTO cities VALUES ('Las Vegas', 'McHarran Airport');
INSERT INTO cities VALUES ('Los Angeles', 'Los Angeles International Airport');
INSERT INTO flights (flightNo, carrier, originCity, destinationCity, totalSeats, fare)
VALUES (100, 'DL', 'Orlando', 'New York', 100, 200.10);
INSERT INTO flights (flightNo, carrier, originCity, destinationCity, totalSeats, fare)
VALUES (200, 'DL', 'New York', 'Orlando', 100, 200.10);
INSERT INTO flights (flightNo, carrier, originCity, destinationCity, totalSeats, fare)
VALUES (300, 'US', 'Las Vegas', 'New York', 100, 300.10);
INSERT INTO flights (flightNo, carrier, originCity, destinationCity, totalSeats, fare)
VALUES (400, 'UA', 'Los Angelese', 'Chicago', 200, 200.10);
INSERT INTO paxprofiles VALUES ('Stephen', 'Withrow', 'Withrow', 'Stephen', 0);
INSERT INTO paxprofiles VALUES ('Nancy', 'Tomcat', 'Campbell', 'Nancy', 0);
INSERT INTO reservations VALUES ('Stephen', 100, 1, '26A', 250.00);
