basic db for airline with reports

Airline DB creation and code

spool todaysdiary.txt

--drop reports & views
DROP VIEW Report_1;
DROP VIEW Report_2;
DROP VIEW Report_3;
DROP VIEW Report_4;
DROP VIEW Report_5;
DROP VIEW REPORT_6;
DROP VIEW View_1;
DROP VIEW View_2;

--Drop tables.
DROP TABLE RESERVATIONS; --Level 4
DROP TABLE FLIGHT; --Level 3
DROP TABLE FLIGHT_ROUTE; --Level 2
DROP TABLE AIRCRAFT; --Level 1
DROP TABLE AIRPORT; --Level 1
DROP TABLE AIRCRAFT_MODEL; --Level 0
DROP TABLE CITY; --Level 0
DROP TABLE CUSTOMER; --Level 0
COMMIT;


--
--Create Tables
--
CREATE TABLE CUSTOMER (
  CUSTOMER_ID NUMBER(4),
  CUSTOMER_NAME VARCHAR(15) CONSTRAINT NOT NULL,
  CONSTRAINT customer_id_PK PRIMARY KEY (CUSTOMER_ID)
);

CREATE TABLE CITY (
  CITY_ID NUMBER(3),
  CITY VARCHAR(32) NOT NULL,
  STATE CHAR(2) CONSTRAINT CK_state CHECK(STATE IN('AL','AK','AS','AZ','AR','CA','CO','CT','DE','DC','FL','GA','GU','HI',
  'ID','IL','IN','IN','IA','KS','KY','LA','ME','MD','MH','MA','MI','FM','MN','MS','MO','MT','NE',
  'NV','NH','NJ','NM','NY','NC','ND','MP','OH','OK','OR','PW','PA','PR','RI','SC','SD','TN','TX',
  'UT','VT','VA','VI','WA','WV','WI','WY'))NOT NULL,
  CONSTRAINT PK_city_city_ID_state PRIMARY KEY (CITY_ID)
);

CREATE TABLE AIRPORT (
  AIRPORT_ID CHAR(3),
  AIRPORT_NAME VARCHAR(50) NOT NULL,
  CITY_ID NUMBER(3) NOT NULL,
  CONSTRAINT airport_ID_PK PRIMARY KEY (AIRPORT_ID),
  CONSTRAINT airport_city_ID FOREIGN KEY (CITY_ID) REFERENCES CITY(CITY_ID)
);

CREATE TABLE FLIGHT_ROUTE (
  ROUTE_ID NUMBER(3),
  D_AIRPORT CHAR (3) NOT NULL,
  A_AIRPORT CHAR (3) NOT NULL,
  Time_Of_Departure NUMBER (4) NOT NULL,
  Time_Of_Arrival NUMBER (4) NOT NULL,
  CONSTRAINT PK_flightRoute_flightNum PRIMARY KEY (ROUTE_ID),
  CONSTRAINT FK_flightRoute_depApt FOREIGN KEY (D_AIRPORT) REFERENCES AIRPORT(AIRPORT_ID),
  CONSTRAINT FK_flightRoute_arrApt FOREIGN KEY (A_AIRPORT) REFERENCES AIRPORT(AIRPORT_ID),
  CONSTRAINT CK_depTime CHECK (Time_Of_Departure BETWEEN ('0000') AND ('2400')),
  CONSTRAINT CK_arrTime CHECK (Time_Of_Arrival BETWEEN ('0000') AND ('2400'))
);

CREATE TABLE AIRCRAFT_MODEL (
  MODEL_ID VARCHAR(5),
  DESCRIPTION VARCHAR(32),
  CAPACITY NUMBER(3),
  RANGE NUMBER (4),
  CONSTRAINT PK_aircraftModel_model_ID PRIMARY KEY (MODEL_ID)
);

CREATE TABLE AIRCRAFT (
  AIRCRAFT_ID VARCHAR(8),
  MODEL_ID VARCHAR(5),
  CONSTRAINT PK_aircraft_serial_ID PRIMARY KEY (AIRCRAFT_ID),
  CONSTRAINT FK_aircraftModel_model_ID FOREIGN KEY (MODEL_ID) REFERENCES AIRCRAFT_MODEL(MODEL_ID)
);

CREATE TABLE FLIGHT (
  FLIGHT_ID NUMBER(5),
  ROUTE_ID NUMBER (3),
  AIRCRAFT_ID VARCHAR(8),
  FLIGHT_DATE DATE,
  CONSTRAINT PK_flight_comp_pk PRIMARY KEY (FLIGHT_ID),
  CONSTRAINT FK_flight_route_ID FOREIGN KEY (ROUTE_ID)
    REFERENCES FLIGHT_ROUTE(ROUTE_ID),
  CONSTRAINT FK_flight_serial_ID FOREIGN KEY (AIRCRAFT_ID)
    REFERENCES AIRCRAFT(AIRCRAFT_ID)
);

CREATE TABLE RESERVATIONS (
  RESERVATIONS_ID NUMBER (6),
  CUSTOMER_ID NUMBER(4),
  FLIGHT_ID NUMBER(5),
  QTY_RESERVED_SEATS NUMBER(3),
  CONSTRAINT PK_reservations PRIMARY KEY (RESERVATIONS_ID),
  CONSTRAINT FK_reservations_customer_ID FOREIGN KEY (CUSTOMER_ID)
    REFERENCES CUSTOMER(CUSTOMER_ID),
  CONSTRAINT FK_reservations_flight_ID FOREIGN KEY (FLIGHT_ID)
    REFERENCES FLIGHT(FLIGHT_ID)
);
COMMIT;

--
--Describing all tables.
--

DESC CUSTOMER;
DESC AIRCRAFT_MODEL;
DESC AIRCRAFT;
DESC CITY;
DESC AIRPORT;
DESC FLIGHT_ROUTE;
DESC FLIGHT;
DESC RESERVATIONS;

--
--Inserting data into tables.
--
INSERT INTO CUSTOMER
  VALUES ('1001','Ford');
INSERT INTO CUSTOMER
  VALUES ('1004','Pfeiffer');
INSERT INTO CUSTOMER
  VALUES ('1055','Harris');
INSERT INTO CUSTOMER
  VALUES ('1058','Codd');
INSERT INTO CUSTOMER
  VALUES ('1077','Nelson');
INSERT INTO CUSTOMER
  VALUES ('1080','Cassatt');
INSERT INTO CUSTOMER
  VALUES ('1100','Streep');
INSERT INTO CUSTOMER
  VALUES ('1155','Lechowick');
INSERT INTO CUSTOMER
  VALUES ('1158','Hilbert');

INSERT INTO AIRCRAFT_MODEL
  VALUES ('BO727','5-seat, 2-engine jet','110','1800');
INSERT INTO AIRCRAFT_MODEL
  VALUES ('DC9','6-seat, prop w/jet','168','2800');
INSERT INTO AIRCRAFT_MODEL
  VALUES ('BO737','6-seat, 2-engine jet','174','2500');
INSERT INTO AIRCRAFT_MODEL
  VALUES ('BO747','10-seat, 4-engine jet','300','3500');

INSERT INTO AIRCRAFT
  VALUES ('T100-001','BO727');
INSERT INTO AIRCRAFT
  VALUES ('T100-004','DC9');
INSERT INTO AIRCRAFT
  VALUES ('T100-007','BO737');
INSERT INTO AIRCRAFT
  VALUES ('T200-002','BO747');
INSERT INTO AIRCRAFT
  VALUES ('T200-004','DC9');
INSERT INTO AIRCRAFT
  VALUES ('T300-005','BO737');
INSERT INTO AIRCRAFT
  VALUES ('T300-009','BO727');

INSERT INTO CITY
  VALUES ('025','Austin','TX');
INSERT INTO CITY
  VALUES ('052','Houston','TX');
INSERT INTO CITY
  VALUES ('520','Chicago','IL');
INSERT INTO CITY
  VALUES ('380','Memphis','TN');

INSERT INTO AIRPORT
  VALUES ('AUS','Austin Municpl. Airport','025');
INSERT INTO AIRPORT
  VALUES ('HOU','Houston Internat. Airport', '052');
INSERT INTO AIRPORT
  VALUES ('MEM', 'Memphis Internat. Airport', '520');
INSERT INTO AIRPORT
  VALUES ('ORD', 'Chicago Internat. Airport', '380');

INSERT INTO FLIGHT_ROUTE
  VALUES ('110','025','380','0800','0935');
INSERT INTO FLIGHT_ROUTE
  VALUES ('181','052','520','0830','1130');
INSERT INTO FLIGHT_ROUTE
  VALUES ('285','025','052','0800','0845');
INSERT INTO FLIGHT_ROUTE
  VALUES ('333','520','052','1700','2000');
INSERT INTO FLIGHT_ROUTE
  VALUES ('359','052','025','2130','2215');
INSERT INTO FLIGHT_ROUTE
  VALUES ('887','380','520','1045','1215');
INSERT INTO FLIGHT_ROUTE
  VALUES ('899','380','025','1400','1545');
INSERT INTO FLIGHT_ROUTE
  VALUES ('950','520','380','1300','1500');



INSERT INTO FLIGHT
  VALUES ('00001', '110','T100-001',TO_DATE('07/12/2000','MM/DD/YYYY'));
INSERT INTO FLIGHT
  VALUES ('00002','110','T100-001',TO_DATE('07/13/2000','MM/DD/YYYY'));
INSERT INTO FLIGHT
  VALUES ('00003','110','T100-001',TO_DATE('07/14/2000','MM/DD/YYYY'));
INSERT INTO FLIGHT
  VALUES ('00004','181','T200-002',TO_DATE('07/12/2000','MM/DD/YYYY'));
INSERT INTO FLIGHT
  VALUES ('00005','181','T200-002',TO_DATE('07/13/2000','MM/DD/YYYY'));
INSERT INTO FLIGHT
  VALUES ('00006','285','T100-007',TO_DATE('07/12/2000','MM/DD/YYYY'));
INSERT INTO FLIGHT
  VALUES ('00007','285','T300-005',TO_DATE('07/13/2000','MM/DD/YYYY'));
INSERT INTO FLIGHT
  VALUES ('00008','285','T300-005',TO_DATE('07/14/2000','MM/DD/YYYY'));
INSERT INTO FLIGHT
  VALUES ('00009','333','T200-002',TO_DATE('07/12/2000','MM/DD/YYYY'));
INSERT INTO FLIGHT
  VALUES ('00010','333','T200-002',TO_DATE('07/13/2000','MM/DD/YYYY'));
INSERT INTO FLIGHT
  VALUES ('00011','359','T300-005',TO_DATE('07/13/2000','MM/DD/YYYY'));
INSERT INTO FLIGHT
  VALUES ('00013','359','',TO_DATE('07/14/2000','MM/DD/YYYY'));
INSERT INTO FLIGHT
  VALUES ('00014','887','T200-004',TO_DATE('07/12/2000','MM/DD/YYYY'));
INSERT INTO FLIGHT
  VALUES ('00015','887','',TO_DATE('07/13/2000','MM/DD/YYYY'));
INSERT INTO FLIGHT
  VALUES ('00016','899','T300-009',TO_DATE('07/12/2000','MM/DD/YYYY'));
INSERT INTO FLIGHT
  VALUES ('00017','899','T100-001',TO_DATE('07/13/2000','MM/DD/YYYY'));
INSERT INTO FLIGHT
  VALUES ('00018','899','T300-009',TO_DATE('07/14/2000','MM/DD/YYYY'));
INSERT INTO FLIGHT
  VALUES ('00019','950','T100-007',TO_DATE('07/13/2000','MM/DD/YYYY'));




INSERT INTO RESERVATIONS
  VALUES ('11111','1004','110','');
INSERT INTO RESERVATIONS
  VALUES ('11112','1077','110','');
INSERT INTO RESERVATIONS
  VALUES ('11113','1158','110','');
INSERT INTO RESERVATIONS
  VALUES ('11114','1001','181','');
INSERT INTO RESERVATIONS
  VALUES ('11115','1080','333','');
INSERT INTO RESERVATIONS
  VALUES ('11116','1055','359','');
INSERT INTO RESERVATIONS
  VALUES ('11117','1155','359','');
INSERT INTO RESERVATIONS
  VALUES ('11118','1100','899','');
INSERT INTO RESERVATIONS
  VALUES ('11119','1158','899','');
INSERT INTO RESERVATIONS
  VALUES ('11120','1004','899','');
INSERT INTO RESERVATIONS
  VALUES ('11121','1058','899','');
INSERT INTO RESERVATIONS
  VALUES ('11122','1058','950','');

COMMIT;

SELECT * FROM CUSTOMER;
SELECT * FROM RESERVATIONS;
SELECT * FROM FLIGHT;
SELECT * FROM AIRCRAFT;
SELECT * FROM AIRCRAFT_MODEL;
SELECT * FROM FLIGHT_ROUTE;
SELECT * FROM AIRPORT;
SELECT * FROM CITY;

--[X](1) report #1
CREATE VIEW Report_1(SERIAL_ID, MODEL_ID, DESCRIPTION, CAPACITY, RANGE) AS
  SELECT AIRCRAFT.AIRCRAFT_ID, AIRCRAFT.MODEL_ID, AIRCRAFT_MODEL.DESCRIPTION, AIRCRAFT_MODEL.CAPACITY, AIRCRAFT_MODEL.RANGE
    FROM AIRCRAFT, AIRCRAFT_MODEL
      WHERE AIRCRAFT.MODEL_ID = AIRCRAFT_MODEL.MODEL_ID;

CLEAR COLUMNS
CLEAR COMPUTES
CLEAR BREAKS
TTITLE OFF

SELECT * FROM Report_1
  ORDER BY AIRCRAFT.AIRCRAFT_ID;

COLUMN AIRCRAFT.AIRCRAFT_ID HEADING 'Serial#' FORMAT A18
COLUMN AIRCRAFT.MODEL_ID HEADING 'Type' FORMAT A18
COLUMN AIRCRAFT_MODEL.DESCRIPTION HEADING 'Description' FORMAT A18
COLUMN AIRCRAFT_MODEL.CAPACITY HEADING 'Capacity' FORMAT A18
COLUMN AIRCRAFT_MODEL.RANGE HEADING 'Range' FORMAT A18

BREAK ON Report_1.SERIAL_ID
COMPUTE SUM LABEL 'Number of Aircraft' OF AIRCRAFT.AIRCRAFT_ID ON Report_1.SERIAL_ID
COMPUTE SUM LABEL 'Total Capacity' OF AIRCRAFT_MODEL.CAPACITY ON Report_1.CAPACITY
COMPUTE AVG LABEL 'Average Capacity' OF AIRCRAFT_MODEL.RANGE ON Report_1.RANGE


--TODO: [](2) report #2
CREATE VIEW Report_2(flight#, dep_city#, dep_city_name, d_airport#, TOD, arr_city#, arr_city_name, a_airport#, TOA) AS
  SELECT FLIGHT_ROUTE.ROUTE_ID, AIRPORT.CITY_ID, CITY.CITY, FLIGHT_ROUTE.D_AIRPORT, FLIGHT_ROUTE.Time_Of_Departure, AIRPORT.CITY_ID, CITY.CITY, FLIGHT_ROUTE.A_AIRPORT, FLIGHT_ROUTE.Time_Of_Arrival
    FROM FLIGHT_ROUTE, AIRPORT, CITY
      WHERE FLIGHT_ROUTE.D_AIRPORT = AIRPORT.AIRPORT_ID AND
        FLIGHT_ROUTE.A_AIRPORT = AIRPORT.AIRPORT_ID AND
        AIRPORT.CITY_ID = CITY.CITY_ID;

CLEAR COLUMNS
CLEAR COMPUTES
CLEAR BREAKS
TTITLE OFF

--TODO: [](3) report #3
CREATE VIEW Report_3(Airport#, Airport_name, City_name, State_code) AS
  SELECT AIRPORT.AIRPORT_ID, AIRPORT.AIRPORT_NAME, CITY.CITY, CITY.STATE
    FROM AIRPORT, CITY
      WHERE AIRPORT.CITY_ID = CITY.CITY_ID;

CLEAR COLUMNS
CLEAR COMPUTES
CLEAR BREAKS
TTITLE OFF

--TODO: [](4) report #4


CLEAR COLUMNS
CLEAR COMPUTES
CLEAR BREAKS
TTITLE OFF


--TODO: [](5) report #5

CLEAR COLUMNS
CLEAR COMPUTES
CLEAR BREAKS
TTITLE OFF

--TODO: [](6) report #6

CLEAR COLUMNS
CLEAR COMPUTES
CLEAR BREAKS
TTITLE OFF

COMMIT;



--TODO: [][] create views
--TODO: [](1) view #1

--TODO: [](2) view #2


COMMIT;


--TODO: [][][][][] create queries
--TODO: [](1) query #1

--TODO: [](2) query #2

--TODO: [](3) query #3

--TODO: [](4) query #4

--TODO: [](5) query #5





--TODO: [][][]create modifications
--TODO: [](1) modification #1

--TODO: [](2) modification #2

--TODO: [](3) modification #3

COMMIT;

spool todaysdiary.txt append

Made a lot of changes on this go around - had a long break in connectivity to Oracle DB.