rockcairn logoRockCairn.com rockcairn logo
PROMPT
drop table Flight_Segments;
drop table Departures;
drop table Flights;
drop table Planes;
drop table Mileages;
drop table Plane_models;
drop table Airports;
COMMIT;

PROMPT
create table Airports (airport_name CHAR(3) NOT NULL, max_incoming_traffic NUMBER(4),
PRIMARY KEY (airport_name) );
COMMIT;

PROMPT
create table Plane_models (pmodel VARCHAR(10) NOT NULL, pcapacity NUMBER(3),
PRIMARY KEY (pmodel) );
COMMIT;

PROMPT
create table Mileages (dep_airport CHAR(3) NOT NULL,
arr_airport CHAR(3) NOT NULL, mileage NUMBER(6),
PRIMARY KEY (dep_airport, arr_airport) );
COMMIT;

PROMPT
create table Planes (plane_id CHAR(6) NOT NULL, pmodel VARCHAR(10),
current_loc CHAR(3), airline_name VARCHAR(20),
PRIMARY KEY (plane_id),
FOREIGN KEY (pmodel) REFERENCES Plane_models,
FOREIGN KEY (current_loc) REFERENCES Airports (airport_name) );
COMMIT;

PROMPT
create table Flights (airline_name VARCHAR(20) NOT NULL, flight_num NUMBER(4) NOT NULL,
flight_cap NUMBER(3), dep_airport CHAR(3), arr_airport CHAR(3),
dep_time CHAR(5), arr_time CHAR(5),
PRIMARY KEY (airline_name,flight_num) );
COMMIT;

PROMPT
create table Departures (dep_date VARCHAR(8) NOT NULL, airline_name VARCHAR(20) NOT NULL,
flight_num NUMBER(4) NOT NULL, flight_seg NUMBER(2) NOT NULL, num_passinger NUMBER(3),
plane_id CHAR(6),
PRIMARY KEY (dep_date,airline_name,flight_num,flight_seg),
FOREIGN KEY (plane_id) REFERENCES Planes,
FOREIGN KEY (airline_name,flight_num) REFERENCES Flights );
COMMIT;

PROMPT
create table Flight_Segments (airline_name VARCHAR(20) NOT NULL,
flight_num NUMBER(4) NOT NULL, flight_seg NUMBER(2) NOT NULL, pmodel VARCHAR(10),
dep_time CHAR(5), arr_time CHAR(5), dep_airport CHAR(3),
arr_airport CHAR(3), cost REAL,
PRIMARY KEY (airline_name,flight_num,flight_seg),
FOREIGN KEY (pmodel) REFERENCES Plane_models,
FOREIGN KEY (airline_name,flight_num) REFERENCES Flights );
COMMIT;