NonCreature0714 NonCreature0714 - 7 months ago 13
SQL Question

SQL table creation / insertion problematic

I'm a college student and my assignment is to create a small database for an airline. I don' think my syntax is off, but something is going wrong in table creations/test data insertion. I'm using the Oracle DBMS and SQL syntax. I'm not looking for anyone to writecode for me, but I'm very constrained for time and need clear indications of what I'm doing wrong. I'm new to SQL.

Here is a link to my code on Github Gist. Which I will actively update with other reports/modications.

Here is a link to my output.

And here is my code.

--TODO: delete reports ??? I don't know if I have to
--TODO: delete views ??? I don't know if I have to

--TODO: enter spool, pagesize and etc. on command line

--Drop tables.
DROP TABLE CITY;
DROP TABLE AIRPORT;
DROP TABLE FLIGHT_ROUTE;
DROP TABLE AIRCRAFT_MODEL;
DROP TABLE AIRCRAFT;
DROP TABLE CUSTOMER;
DROP TABLE FLIGHT;
DROP TABLE RESERVATIONS;
COMMIT;

--
--Create Tables
--
CREATE TABLE CITY (
CITY# NUMBER(3) NOT NULL,
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#_state PRIMARY KEY (CITY#, STATE)
);

CREATE TABLE AIRPORT (
AIRPORT# CHAR(3) UNIQUE NOT NULL ,
AIRPORT_NAME VARCHAR(50) NOT NULL,
CITY# NUMBER(3) NOT NULL,
CONSTRAINT PK_airport_airport# PRIMARY KEY (AIRPORT#),
CONSTRAINT FK_airport_city# FOREIGN KEY (CITY#) REFERENCES CITY(CITY#)
);

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

CREATE TABLE AIRCRAFT_MODEL (
MODEL# VARCHAR(5) UNIQUE NOT NULL,
DESCRIPTION VARCHAR(32),
CAPACITY NUMBER(3),
RANGE NUMBER (4),
CONSTRAINT PK_aircraftModel_model# PRIMARY KEY (MODEL#)
);

CREATE TABLE AIRCRAFT (
SERIAL# VARCHAR(7) UNIQUE NOT NULL,
MODEL# VARCHAR(5) NOT NULL,
CONSTRAINT PK_aircraft_serial# PRIMARY KEY (SERIAL#),
CONSTRAINT FK_aircraftModel_model# FOREIGN KEY (MODEL#) REFERENCES AIRCRAFT_MODEL(MODEL#)
);

CREATE TABLE CUSTOMER (
CUSTOMER# NUMBER(4) UNIQUE NOT NULL,
CUSTOMER_NAME VARCHAR(50) NOT NULL,
CONSTRAINT PK_customer_customer# PRIMARY KEY (CUSTOMER#)
);

CREATE TABLE FLIGHT (
FLIGHT# NUMBER(3) NOT NULL ,
SERIAL# VARCHAR(8),
FLIGHT_DATE DATE NOT NULL ,
CONSTRAINT PK_flight_flight#_serial#_flight_date PRIMARY KEY (FLIGHT#,SERIAL#,FLIGHT_DATE),
CONSTRAINT FK_flight_flight# FOREIGN KEY (FLIGHT#) REFERENCES FLIGHT_ROUTE(FLIGHT#),
CONSTRAINT FK_flight_serial# FOREIGN KEY (SERIAL#) REFERENCES AIRCRAFT(SERIAL#)
);

CREATE TABLE RESERVATIONS (
CUSTOMER# NUMBER(4) NOT NULL,
FLIGHT# NUMBER(3) NOT NULL,
FLIGHT_DATE DATE NOT NULL,
QTY_RESERVED_SEATS NUMBER(3),
CONSTRAINT PK_reservations_customer#_flight#_flightDate PRIMARY KEY (CUSTOMER#,FLIGHT#,FLIGHT_DATE),
CONSTRAINT FK_reservations_customer# FOREIGN KEY (CUSTOMER#) REFERENCES CUSTOMER(CUSTOMER#),
CONSTRAINT FK_reservations_flight# FOREIGN KEY (FLIGHT#) REFERENCES FLIGHT(FLIGHT#),
CONSTRAINT FK_reservations_flightDate FOREIGN KEY (FLIGHT_DATE) REFERENCES FLIGHT(FLIGHT_DATE)
);
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 ('110','T100-001','7/12/2000');
INSERT INTO FLIGHT VALUES ('181','T200-002','7/12/2000');
INSERT INTO FLIGHT VALUES ('285','T100-007','7/12/2000');
INSERT INTO FLIGHT VALUES ('333','T200-002','7/12/2000');
INSERT INTO FLIGHT VALUES ('887','T200-004','7/12/2000');
INSERT INTO FLIGHT VALUES ('899','T300-009','7/12/2000');

INSERT INTO FLIGHT VALUES ('110','T100-001','7/13/2000');
INSERT INTO FLIGHT VALUES ('181','T200-002','7/13/2000');
INSERT INTO FLIGHT VALUES ('285','T300-005','7/13/2000');
INSERT INTO FLIGHT VALUES ('333','T200-002','7/13/2000');
INSERT INTO FLIGHT VALUES ('887','','7/13/2000');
INSERT INTO FLIGHT VALUES ('899','T100-001','7/13/2000');
INSERT INTO FLIGHT VALUES ('950','T100-007','7/13/2000');

INSERT INTO FLIGHT VALUES ('110','T100-001','7/14/2000');
INSERT INTO FLIGHT VALUES ('285','T300-005','7/14/2000');
INSERT INTO FLIGHT VALUES ('359','','7/14/2000');
INSERT INTO FLIGHT VALUES ('899','T300-009','7/14/2000');

INSERT INTO RESERVATIONS VALUES ('1004','110','7/12/2000','');
INSERT INTO RESERVATIONS VALUES ('1077','110','7/12/2000','');
INSERT INTO RESERVATIONS VALUES ('1158','110','7/12/2000','');
INSERT INTO RESERVATIONS VALUES ('1001','181','7/13/2000','');
INSERT INTO RESERVATIONS VALUES ('1080','333','7/13/2000','');
INSERT INTO RESERVATIONS VALUES ('1055','359','7/13/2000','');
INSERT INTO RESERVATIONS VALUES ('1155','359','7/13/2000','');
INSERT INTO RESERVATIONS VALUES ('1100','899','7/14/2000','');
INSERT INTO RESERVATIONS VALUES ('1158','899','7/13/2000','');
INSERT INTO RESERVATIONS VALUES ('1004','899','7/14/2000','');
INSERT INTO RESERVATIONS VALUES ('1058','899','7/14/2000','');
INSERT INTO RESERVATIONS VALUES ('1058','950','7/13/2000','');

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#, Model#, Description, Capacity#, Range#) AS
SELECT AIRCRAFT.SERIAL#, AIRCRAFT.MODEL#, AIRCRAFT_MODEL.DESCRIPTION, AIRCRAFT_MODEL.CAPACITY, AIRCRAFT_MODEL.RANGE
FROM AIRCRAFT, AIRCRAFT_MODEL
WHERE AIRCRAFT.MODEL# = AIRCRAFT_MODEL.MODEL#;

CLEAR COLUMNS
CLEAR COMPUTES
CLEAR BREAKS
TTITLE OFF

SELECT * FROM Report#1
ORDER BY AIRCRAFT.SERIAL#;

COLUMN Serial# HEADING 'Serial#' FORMAT A18
COLUMN Model# HEADING 'Type' FORMAT A18
COLUMN Description HEADING 'Description' FORMAT A18
COLUMN Capacity# HEADING 'Capacity' FORMAT A18
COLUMN Range# HEADING 'Range' FORMAT A18

BREAK ON Report#1
COMPUTE SUM LABEL 'Number of Aircraft' OF AIRCRAFT.SERIAL# ON Report#1.SERIAL#
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

Answer

One problem is that the airport table has a foreign key to city which only references CITY#. The issue is that the primary key in CITY is CITY# and STATE

CONSTRAINT FK_airport_city# FOREIGN KEY (CITY#) REFERENCES CITY(CITY#)

Make CITY# the primary key for CITY as I don't see a use case for a compound key. As in

CONSTRAINT PK_city_city#_state PRIMARY KEY (CITY#)

I find that the use of the # symbol to be not a usual practice. I would use CITY_ID. Your constraint on CITY is inflexible. What if you do business outside the United States? Better to have a table of Prov_State ID, VALUE and foreign key on the ID

In AIRCRAFT_MODEL you make MODEL# unique and then make it a primary key. Skip the unique as this is implied by it being a primary key. You would be better off to have a number as a surrogate primary key as I don't think you can guarantee that there will never be duplicate model numbers for a plane.

When you are declaring NUMBER types build for the future. Right now when you have customer number 10000 you are out of luck and will have to make the column number larger. Life is easier if you settle on one precision for all NUMBER such as NUMBER(9). Templating code is easier, remembering how large any number can get is easier and it does not affect performance or space in a significant manner.