output1 for airline database

SQL> --TODO: [x][][][]() Create conceptual design
SQL>   --TODO: [x] ER Diagram
SQL>   --TODO: [\] Dependency Chart
SQL>   --TODO: [\] Relational Schema Supporting the ER Diagram
SQL>   --TODO: [\] Attribute Chart / Data Dictionary
SQL>       --TODO: () Each relation in 3NF
SQL> 
SQL> --TODO: delete reports & views
SQL> 
SQL> --Drop tables.
SQL> DROP TABLE RESERVATIONS;

Table dropped.

SQL> DROP TABLE CUSTOMER;

Table dropped.

SQL> DROP TABLE FLIGHT;

Table dropped.

SQL> DROP TABLE AIRCRAFT;

Table dropped.

SQL> DROP TABLE AIRCRAFT_MODEL;

Table dropped.

SQL> DROP TABLE FLIGHT_ROUTE;

Table dropped.

SQL> DROP TABLE AIRPORT;

Table dropped.

SQL> DROP TABLE CITY;

Table dropped.

SQL> 
SQL> COMMIT;

Commit complete.

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

Table created.

SQL> 
SQL> CREATE TABLE AIRPORT (
  2    AIRPORT_ID CHAR(3),
  3    AIRPORT_NAME VARCHAR(50) NOT NULL,
  4    CITY# NUMBER(3) NOT NULL,
  5    CONSTRAINT PK_airport_airport_ID PRIMARY KEY (AIRPORT_ID),
  6    CONSTRAINT FK_airport_city_ID FOREIGN KEY (CITY#) REFERENCES CITY(CITY_ID)
  7  );

Table created.

SQL> 
SQL> CREATE TABLE FLIGHT_ROUTE (
  2    FLIGHT_ID NUMBER(3),
  3    D_AIRPORT CHAR (3) NOT NULL,
  4    A_AIRPORT CHAR (3) NOT NULL,
  5    Time_Of_Departure NUMBER (4) NOT NULL,
  6    Time_Of_Arrival NUMBER (4) NOT NULL,
  7    CONSTRAINT PK_flightRoute_flightNum PRIMARY KEY (FLIGHT_ID),
  8    CONSTRAINT FK_flightRoute_depApt FOREIGN KEY (D_AIRPORT) REFERENCES AIRPORT(AIRPORT_ID),
  9    CONSTRAINT FK_flightRoute_arrApt FOREIGN KEY (A_AIRPORT) REFERENCES AIRPORT(AIRPORT_ID),
 10    CONSTRAINT CK_depTime CHECK (Time_Of_Departure BETWEEN ('0000') AND ('2400')),
 11    CONSTRAINT CK_arrTime CHECK (Time_Of_Arrival BETWEEN ('0000') AND ('2400'))
 12  );

Table created.

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

Table created.

SQL> 
SQL> CREATE TABLE AIRCRAFT (
  2    SERIAL_ID VARCHAR(7),
  3    MODEL_ID VARCHAR(5) NOT NULL,
  4    CONSTRAINT PK_aircraft_serial_ID PRIMARY KEY (SERIAL_ID),
  5    CONSTRAINT FK_aircraftModel_model_ID FOREIGN KEY (MODEL_ID) REFERENCES AIRCRAFT_MODEL(MODEL_ID)
  6  );

Table created.

SQL> 
SQL> CREATE TABLE CUSTOMER (
  2    CUSTOMER_ID NUMBER(4),
  3    CUSTOMER_NAME VARCHAR(50) NOT NULL,
  4    CONSTRAINT PK_customer_customer_ID PRIMARY KEY (CUSTOMER_ID)
  5  );

Table created.

SQL> 
SQL> CREATE TABLE FLIGHT (
  2    FLIGHT_ID NUMBER(3),
  3    SERIAL_ID VARCHAR(8) NOT NULL,
  4    FLIGHT_DATE DATE,
  5    CONSTRAINT PK_flight_comp_pk PRIMARY KEY (FLIGHT_ID,FLIGHT_DATE),
  6    CONSTRAINT FK_flight_flight_ID FOREIGN KEY (FLIGHT_ID)
  7      REFERENCES FLIGHT_ROUTE(FLIGHT_ID),
  8    CONSTRAINT FK_flight_serial_ID FOREIGN KEY (SERIAL_ID)
  9      REFERENCES AIRCRAFT(SERIAL_ID)
 10  );

Table created.

SQL> 
SQL> CREATE TABLE RESERVATIONS (
  2    RESERVATION_ID NUMBER(6),
  3    CUSTOMER_ID NUMBER(4),
  4    FLIGHT_ID NUMBER(3) NOT NULL,
  5    FLIGHT_DATE DATE NOT NULL,
  6    QTY_RESERVED_SEATS NUMBER(3),
  7    CONSTRAINT PK_reservations PRIMARY KEY (RESERVATION_ID),
  8    CONSTRAINT FK_reservations_customer_ID FOREIGN KEY (CUSTOMER_ID)
  9      REFERENCES CUSTOMER(CUSTOMER_ID),
 10    CONSTRAINT FK_reservations_flight_ID FOREIGN KEY (FLIGHT_ID)
 11      REFERENCES FLIGHT(FLIGHT_ID),
 12    CONSTRAINT FK_reservations_flight_ID FOREIGN KEY (FLIGHT_DATE)
 13      REFERENCES FLIGHT (FLIGHT_DATE)
 14  );
    REFERENCES FLIGHT(FLIGHT_ID),
                      *
ERROR at line 11:
ORA-02270: no matching unique or primary key for this column-list 


SQL> COMMIT;

Commit complete.

SQL> 
SQL> --
SQL> --Describing all tables.
SQL> --
SQL> 
SQL> DESC CUSTOMER;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUSTOMER_ID                               NOT NULL NUMBER(4)
 CUSTOMER_NAME                             NOT NULL VARCHAR2(50)

SQL> DESC AIRCRAFT_MODEL;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MODEL_ID                                  NOT NULL VARCHAR2(5)
 DESCRIPTION                                        VARCHAR2(32)
 CAPACITY                                           NUMBER(3)
 RANGE                                              NUMBER(4)

SQL> DESC AIRCRAFT;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SERIAL_ID                                 NOT NULL VARCHAR2(7)
 MODEL_ID                                  NOT NULL VARCHAR2(5)

SQL> DESC CITY;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CITY_ID                                   NOT NULL NUMBER(3)
 CITY                                      NOT NULL VARCHAR2(32)
 STATE                                     NOT NULL CHAR(2)

SQL> DESC AIRPORT;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 AIRPORT_ID                                NOT NULL CHAR(3)
 AIRPORT_NAME                              NOT NULL VARCHAR2(50)
 CITY#                                     NOT NULL NUMBER(3)

SQL> DESC FLIGHT_ROUTE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FLIGHT_ID                                 NOT NULL NUMBER(3)
 D_AIRPORT                                 NOT NULL CHAR(3)
 A_AIRPORT                                 NOT NULL CHAR(3)
 TIME_OF_DEPARTURE                         NOT NULL NUMBER(4)
 TIME_OF_ARRIVAL                           NOT NULL NUMBER(4)

SQL> DESC FLIGHT;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FLIGHT_ID                                 NOT NULL NUMBER(3)
 SERIAL_ID                                 NOT NULL VARCHAR2(8)
 FLIGHT_DATE                               NOT NULL DATE

SQL> DESC RESERVATIONS;
ERROR:
ORA-04043: object RESERVATIONS does not exist 


SQL> 
SQL> --
SQL> --Inserting data into tables.
SQL> --
SQL> INSERT INTO CUSTOMER
  2    VALUES ('1001','Ford');

1 row created.

SQL> INSERT INTO CUSTOMER
  2    VALUES ('1004','Pfeiffer');

1 row created.

SQL> INSERT INTO CUSTOMER
  2    VALUES ('1055','Harris');

1 row created.

SQL> INSERT INTO CUSTOMER
  2    VALUES ('1058','Codd');

1 row created.

SQL> INSERT INTO CUSTOMER
  2    VALUES ('1077','Nelson');

1 row created.

SQL> INSERT INTO CUSTOMER
  2    VALUES ('1080','Cassatt');

1 row created.

SQL> INSERT INTO CUSTOMER
  2    VALUES ('1100','Streep');

1 row created.

SQL> INSERT INTO CUSTOMER
  2    VALUES ('1155','Lechowick');

1 row created.

SQL> INSERT INTO CUSTOMER
  2    VALUES ('1158','Hilbert');

1 row created.

SQL> 
SQL> INSERT INTO AIRCRAFT_MODEL
  2    VALUES ('BO727','5-seat, 2-engine jet','110','1800');

1 row created.

SQL> INSERT INTO AIRCRAFT_MODEL
  2    VALUES ('DC9','6-seat, prop w/jet','168','2800');

1 row created.

SQL> INSERT INTO AIRCRAFT_MODEL
  2    VALUES ('BO737','6-seat, 2-engine jet','174','2500');

1 row created.

SQL> INSERT INTO AIRCRAFT_MODEL
  2    VALUES ('BO747','10-seat, 4-engine jet','300','3500');

1 row created.

SQL> 
SQL> INSERT INTO AIRCRAFT
  2    VALUES ('T100-001','BO727');
  VALUES ('T100-001','BO727')
          *
ERROR at line 2:
ORA-12899: value too large for column "S4730802"."AIRCRAFT"."SERIAL_ID" 
(actual: 8, maximum: 7) 


SQL> INSERT INTO AIRCRAFT
  2    VALUES ('T100-004','DC9');
  VALUES ('T100-004','DC9')
          *
ERROR at line 2:
ORA-12899: value too large for column "S4730802"."AIRCRAFT"."SERIAL_ID" 
(actual: 8, maximum: 7) 


SQL> INSERT INTO AIRCRAFT
  2    VALUES ('T100-007','BO737');
  VALUES ('T100-007','BO737')
          *
ERROR at line 2:
ORA-12899: value too large for column "S4730802"."AIRCRAFT"."SERIAL_ID" 
(actual: 8, maximum: 7) 


SQL> INSERT INTO AIRCRAFT
  2    VALUES ('T200-002','BO747');
  VALUES ('T200-002','BO747')
          *
ERROR at line 2:
ORA-12899: value too large for column "S4730802"."AIRCRAFT"."SERIAL_ID" 
(actual: 8, maximum: 7) 


SQL> INSERT INTO AIRCRAFT
  2    VALUES ('T200-004','DC9');
  VALUES ('T200-004','DC9')
          *
ERROR at line 2:
ORA-12899: value too large for column "S4730802"."AIRCRAFT"."SERIAL_ID" 
(actual: 8, maximum: 7) 


SQL> INSERT INTO AIRCRAFT
  2    VALUES ('T300-005','BO737');
  VALUES ('T300-005','BO737')
          *
ERROR at line 2:
ORA-12899: value too large for column "S4730802"."AIRCRAFT"."SERIAL_ID" 
(actual: 8, maximum: 7) 


SQL> INSERT INTO AIRCRAFT
  2    VALUES ('T300-009','BO727');
  VALUES ('T300-009','BO727')
          *
ERROR at line 2:
ORA-12899: value too large for column "S4730802"."AIRCRAFT"."SERIAL_ID" 
(actual: 8, maximum: 7) 


SQL> 
SQL> INSERT INTO CITY
  2    VALUES ('025','Austin','TX');

1 row created.

SQL> INSERT INTO CITY
  2    VALUES ('052','Houston','TX');

1 row created.

SQL> INSERT INTO CITY
  2    VALUES ('520','Chicago','IL');

1 row created.

SQL> INSERT INTO CITY
  2    VALUES ('380','Memphis','TN');

1 row created.

SQL> 
SQL> INSERT INTO AIRPORT
  2    VALUES ('AUS','Austin Municpl. Airport','025');

1 row created.

SQL> INSERT INTO AIRPORT
  2    VALUES ('HOU','Houston Internat. Airport', '052');

1 row created.

SQL> INSERT INTO AIRPORT
  2    VALUES ('MEM', 'Memphis Internat. Airport', '520');

1 row created.

SQL> INSERT INTO AIRPORT
  2    VALUES ('ORD', 'Chicago Internat. Airport', '380');

1 row created.

SQL> 
SQL> INSERT INTO FLIGHT_ROUTE
  2    VALUES ('110','025','380','0800','0935');
INSERT INTO FLIGHT_ROUTE
*
ERROR at line 1:
ORA-02291: integrity constraint (S4730802.FK_FLIGHTROUTE_ARRAPT) violated - 
parent key not found 


SQL> INSERT INTO FLIGHT_ROUTE
  2    VALUES ('181','052','520','0830','1130');
INSERT INTO FLIGHT_ROUTE
*
ERROR at line 1:
ORA-02291: integrity constraint (S4730802.FK_FLIGHTROUTE_ARRAPT) violated - 
parent key not found 


SQL> INSERT INTO FLIGHT_ROUTE
  2    VALUES ('285','025','052','0800','0845');
INSERT INTO FLIGHT_ROUTE
*
ERROR at line 1:
ORA-02291: integrity constraint (S4730802.FK_FLIGHTROUTE_ARRAPT) violated - 
parent key not found 


SQL> INSERT INTO FLIGHT_ROUTE
  2    VALUES ('333','520','052','1700','2000');
INSERT INTO FLIGHT_ROUTE
*
ERROR at line 1:
ORA-02291: integrity constraint (S4730802.FK_FLIGHTROUTE_ARRAPT) violated - 
parent key not found 


SQL> INSERT INTO FLIGHT_ROUTE
  2    VALUES ('359','052','025','2130','2215');
INSERT INTO FLIGHT_ROUTE
*
ERROR at line 1:
ORA-02291: integrity constraint (S4730802.FK_FLIGHTROUTE_ARRAPT) violated - 
parent key not found 


SQL> INSERT INTO FLIGHT_ROUTE
  2    VALUES ('887','380','520','1045','1215');
INSERT INTO FLIGHT_ROUTE
*
ERROR at line 1:
ORA-02291: integrity constraint (S4730802.FK_FLIGHTROUTE_ARRAPT) violated - 
parent key not found 


SQL> INSERT INTO FLIGHT_ROUTE
  2    VALUES ('899','380','025','1400','1545');
INSERT INTO FLIGHT_ROUTE
*
ERROR at line 1:
ORA-02291: integrity constraint (S4730802.FK_FLIGHTROUTE_ARRAPT) violated - 
parent key not found 


SQL> INSERT INTO FLIGHT_ROUTE
  2    VALUES ('950','520','380','1300','1500');
INSERT INTO FLIGHT_ROUTE
*
ERROR at line 1:
ORA-02291: integrity constraint (S4730802.FK_FLIGHTROUTE_ARRAPT) violated - 
parent key not found 


SQL> 
SQL> INSERT INTO FLIGHT
  2    VALUES ('110','T100-001','7/12/2000');
  VALUES ('110','T100-001','7/12/2000')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL> INSERT INTO FLIGHT
  2    VALUES ('181','T200-002','7/12/2000');
  VALUES ('181','T200-002','7/12/2000')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL> INSERT INTO FLIGHT
  2    VALUES ('285','T100-007','7/12/2000');
  VALUES ('285','T100-007','7/12/2000')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL> INSERT INTO FLIGHT
  2    VALUES ('333','T200-002','7/12/2000');
  VALUES ('333','T200-002','7/12/2000')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL> INSERT INTO FLIGHT
  2    VALUES ('887','T200-004','7/12/2000');
  VALUES ('887','T200-004','7/12/2000')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL> INSERT INTO FLIGHT
  2    VALUES ('899','T300-009','7/12/2000');
  VALUES ('899','T300-009','7/12/2000')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL> 
SQL> INSERT INTO FLIGHT
  2    VALUES ('110','T100-001','7/13/2000');
  VALUES ('110','T100-001','7/13/2000')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL> INSERT INTO FLIGHT
  2    VALUES ('181','T200-002','7/13/2000');
  VALUES ('181','T200-002','7/13/2000')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL> INSERT INTO FLIGHT
  2    VALUES ('285','T300-005','7/13/2000');
  VALUES ('285','T300-005','7/13/2000')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL> INSERT INTO FLIGHT
  2    VALUES ('333','T200-002','7/13/2000');
  VALUES ('333','T200-002','7/13/2000')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL> INSERT INTO FLIGHT
  2    VALUES ('887','','7/13/2000');
  VALUES ('887','','7/13/2000')
                *
ERROR at line 2:
ORA-01400: cannot insert NULL into ("S4730802"."FLIGHT"."SERIAL_ID") 


SQL> INSERT INTO FLIGHT
  2    VALUES ('899','T100-001','7/13/2000');
  VALUES ('899','T100-001','7/13/2000')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL> INSERT INTO FLIGHT
  2    VALUES ('950','T100-007','7/13/2000');
  VALUES ('950','T100-007','7/13/2000')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL> 
SQL> INSERT INTO FLIGHT
  2    VALUES ('110','T100-001','7/14/2000');
  VALUES ('110','T100-001','7/14/2000')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL> INSERT INTO FLIGHT
  2    VALUES ('285','T300-005','7/14/2000');
  VALUES ('285','T300-005','7/14/2000')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL> INSERT INTO FLIGHT
  2    VALUES ('359','','7/14/2000');
  VALUES ('359','','7/14/2000')
                *
ERROR at line 2:
ORA-01400: cannot insert NULL into ("S4730802"."FLIGHT"."SERIAL_ID") 


SQL> INSERT INTO FLIGHT
  2    VALUES ('899','T300-009','7/14/2000');
  VALUES ('899','T300-009','7/14/2000')
                           *
ERROR at line 2:
ORA-01843: not a valid month 


SQL> 
SQL> INSERT INTO RESERVATIONS
  2    VALUES ('1004','110','7/12/2000','');
INSERT INTO RESERVATIONS
            *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> INSERT INTO RESERVATIONS
  2    VALUES ('1077','110','7/12/2000','');
INSERT INTO RESERVATIONS
            *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> INSERT INTO RESERVATIONS
  2    VALUES ('1158','110','7/12/2000','');
INSERT INTO RESERVATIONS
            *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> INSERT INTO RESERVATIONS
  2    VALUES ('1001','181','7/13/2000','');
INSERT INTO RESERVATIONS
            *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> INSERT INTO RESERVATIONS
  2    VALUES ('1080','333','7/13/2000','');
INSERT INTO RESERVATIONS
            *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> INSERT INTO RESERVATIONS
  2    VALUES ('1055','359','7/13/2000','');
INSERT INTO RESERVATIONS
            *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> INSERT INTO RESERVATIONS
  2    VALUES ('1155','359','7/13/2000','');
INSERT INTO RESERVATIONS
            *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> INSERT INTO RESERVATIONS
  2    VALUES ('1100','899','7/14/2000','');
INSERT INTO RESERVATIONS
            *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> INSERT INTO RESERVATIONS
  2    VALUES ('1158','899','7/13/2000','');
INSERT INTO RESERVATIONS
            *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> INSERT INTO RESERVATIONS
  2    VALUES ('1004','899','7/14/2000','');
INSERT INTO RESERVATIONS
            *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> INSERT INTO RESERVATIONS
  2    VALUES ('1058','899','7/14/2000','');
INSERT INTO RESERVATIONS
            *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> INSERT INTO RESERVATIONS
  2    VALUES ('1058','950','7/13/2000','');
INSERT INTO RESERVATIONS
            *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL> SELECT * FROM CUSTOMER;

CUSTOMER_ID CUSTOMER_NAME                                                       
----------- --------------------------------------------------                  
       1001 Ford                                                                
       1004 Pfeiffer                                                            
       1055 Harris                                                              
       1058 Codd                                                                
       1077 Nelson                                                              
       1080 Cassatt                                                             
       1100 Streep                                                              
       1155 Lechowick                                                           
       1158 Hilbert                                                             

9 rows selected.

SQL> SELECT * FROM RESERVATIONS;
SELECT * FROM RESERVATIONS
              *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> SELECT * FROM FLIGHT;

no rows selected

SQL> SELECT * FROM AIRCRAFT;

no rows selected

SQL> SELECT * FROM AIRCRAFT_MODEL;

MODEL DESCRIPTION                        CAPACITY      RANGE                    
----- -------------------------------- ---------- ----------                    
BO727 5-seat, 2-engine jet                    110       1800                    
DC9   6-seat, prop w/jet                      168       2800                    
BO737 6-seat, 2-engine jet                    174       2500                    
BO747 10-seat, 4-engine jet                   300       3500                    

SQL> SELECT * FROM FLIGHT_ROUTE;

no rows selected

SQL> SELECT * FROM AIRPORT;

AIR AIRPORT_NAME                                            CITY#               
--- -------------------------------------------------- ----------               
AUS Austin Municpl. Airport                                    25               
HOU Houston Internat. Airport                                  52               
MEM Memphis Internat. Airport                                 520               
ORD Chicago Internat. Airport                                 380               

SQL> SELECT * FROM CITY;

   CITY_ID CITY                             ST                                  
---------- -------------------------------- --                                  
        25 Austin                           TX                                  
        52 Houston                          TX                                  
       520 Chicago                          IL                                  
       380 Memphis                          TN                                  

SQL> 
SQL> --[X](1) report #1
SQL> CREATE VIEW Report_1(SERIAL_ID, MODEL_ID, DESCRIPTION, CAPACITY, RANGE) AS
  2    SELECT AIRCRAFT.SERIAL_ID, AIRCRAFT.MODEL_ID, AIRCRAFT_MODEL.DESCRIPTION, AIRCRAFT_MODEL.CAPACITY, AIRCRAFT_MODEL.RANGE
  3      FROM AIRCRAFT, AIRCRAFT_MODEL
  4        WHERE AIRCRAFT.MODEL_ID = AIRCRAFT_MODEL.MODEL_ID;
CREATE VIEW Report_1(SERIAL_ID, MODEL_ID, DESCRIPTION, CAPACITY, RANGE) AS
            *
ERROR at line 1:
ORA-00955: name is already used by an existing object 


SQL> 
SQL> CLEAR COLUMNS
columns cleared
SQL> CLEAR COMPUTES
computes cleared
SQL> CLEAR BREAKS
breaks cleared
SQL> TTITLE OFF
SQL> 
SQL> SELECT * FROM Report_1
  2    ORDER BY AIRCRAFT.SERIAL_ID;
  ORDER BY AIRCRAFT.SERIAL_ID
           *
ERROR at line 2:
ORA-00904: "AIRCRAFT"."SERIAL_ID": invalid identifier 


SQL> 
SQL> COLUMN AIRCRAFT.SERIAL_ID HEADING 'Serial#' FORMAT A18
SQL> COLUMN AIRCRAFT.MODEL_ID HEADING 'Type' FORMAT A18
SQL> COLUMN AIRCRAFT_MODEL.DESCRIPTION HEADING 'Description' FORMAT A18
SQL> COLUMN AIRCRAFT_MODEL.CAPACITY HEADING 'Capacity' FORMAT A18
SQL> COLUMN AIRCRAFT_MODEL.RANGE HEADING 'Range' FORMAT A18
SQL> 
SQL> BREAK ON Report_1.SERIAL_ID
SQL> COMPUTE SUM LABEL 'Number of Aircraft' OF AIRCRAFT.SERIAL_ID ON Report_1.SERIAL_ID
SQL> COMPUTE SUM LABEL 'Total Capacity' OF AIRCRAFT_MODEL.CAPACITY ON Report_1.CAPACITY
SQL> COMPUTE AVG LABEL 'Average Capacity' OF AIRCRAFT_MODEL.RANGE ON Report_1.RANGE
SQL> 
SQL> 
SQL> --TODO: [](2) report #2
SQL> CREATE VIEW Report#2(flight#, dep_city#, dep_city_name, d_airport#, TOD, arr_city#, arr_city_name, a_airport#, TOA) AS
  2    SELECT FLIGHT_ROUTE.FLIGHT_ID, AIRPORT.CITY#, CITY.CITY, FLIGHT_ROUTE.D_AIRPORT, FLIGHT_ROUTE.Time_Of_Departure, AIRPORT.CITY#, CITY.CITY, FLIGHT_ROUTE.A_AIRPORT, FLIGHT_ROUTE.Time_Of_Arrival
  3      FROM FLIGHT_ROUTE, AIRPORT, CITY
  4        WHERE FLIGHT_ROUTE.D_AIRPORT = AIRPORT.AIRPORT_ID AND
  5          FLIGHT_ROUTE.A_AIRPORT = AIRPORT.AIRPORT_ID AND
  6          AIRPORT.CITY# = CITY.CITY_ID;
CREATE VIEW Report#2(flight#, dep_city#, dep_city_name, d_airport#, TOD, arr_city#, arr_city_name, a_airport#, TOA) AS
            *
ERROR at line 1:
ORA-00955: name is already used by an existing object 


SQL> 
SQL> CLEAR COLUMNS
columns cleared
SQL> CLEAR COMPUTES
computes cleared
SQL> CLEAR BREAKS
breaks cleared
SQL> TTITLE OFF
SQL> 
SQL> --TODO: [](3) report #3
SQL> CREATE VIEW Report#3(Airport#, Airport_name, City_name, State_code) AS
  2    SELECT AIRPORT.AIRPORT_ID, AIRPORT.AIRPORT_NAME, CITY.CITY, CITY.STATE
  3      FROM AIRPORT, CITY
  4        WHERE AIRPORT.CITY# = CITY.CITY_ID;
CREATE VIEW Report#3(Airport#, Airport_name, City_name, State_code) AS
            *
ERROR at line 1:
ORA-00955: name is already used by an existing object 


SQL> 
SQL> CLEAR COLUMNS
columns cleared
SQL> CLEAR COMPUTES
computes cleared
SQL> CLEAR BREAKS
breaks cleared
SQL> TTITLE OFF
SQL> 
SQL> --TODO: [](4) report #4
SQL> 
SQL> 
SQL> CLEAR COLUMNS
columns cleared
SQL> CLEAR COMPUTES
computes cleared
SQL> CLEAR BREAKS
breaks cleared
SQL> TTITLE OFF
SQL> 
SQL> 
SQL> --TODO: [](5) report #5
SQL> 
SQL> CLEAR COLUMNS
columns cleared
SQL> CLEAR COMPUTES
computes cleared
SQL> CLEAR BREAKS
breaks cleared
SQL> TTITLE OFF
SQL> 
SQL> --TODO: [](6) report #6
SQL> 
SQL> CLEAR COLUMNS
columns cleared
SQL> CLEAR COMPUTES
computes cleared
SQL> CLEAR BREAKS
breaks cleared
SQL> TTITLE OFF
SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL> 
SQL> 
SQL> --TODO: [][] create views
SQL> --TODO: [](1) view #1
SQL> 
SQL> --TODO: [](2) view #2
SQL> 
SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL> 
SQL> --TODO: [][][][][] create queries
SQL> --TODO: [](1) query #1
SQL> 
SQL> --TODO: [](2) query #2
SQL> 
SQL> --TODO: [](3) query #3
SQL> 
SQL> --TODO: [](4) query #4
SQL> 
SQL> --TODO: [](5) query #5
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> --TODO: [][][]create modifications
SQL> --TODO: [](1) modification #1
SQL> 
SQL> --TODO: [](2) modification #2
SQL> 
SQL> --TODO: [](3) modification #3
SQL> 
SQL> COMMIT;

Commit complete.

SQL> spool todaysdiary.txt append