-- DBF Sem 1 2019
-- Schema Definition
-- New Endor Integrated Data Infrastructure
DROP TABLE MODEL XXXXXXXXXXCASCADE CONSTRAINTS;
DROP TABLE LOCATION XXXXXXXXXXCASCADE CONSTRAINTS;
DROP TABLE TICKET XXXXXXXXXXCASCADE CONSTRAINTS;
DROP TABLE ROUTE XXXXXXXXXXCASCADE CONSTRAINTS;
DROP TABLE IRREGULAR_EVENT CASCADE CONSTRAINTS;
DROP TABLE SERVICE XXXXXXXXXXCASCADE CONSTRAINTS;
DROP TABLE AIRCRAFT XXXXXXXXXXCASCADE CONSTRAINTS;
DROP TABLE NEACC_MEMBER XXXXXXXXXXCASCADE CONSTRAINTS;
DROP TABLE STAFF XXXXXXXXXXCASCADE CONSTRAINTS;
DROP TABLE CUSTOMER XXXXXXXXXXCASCADE CONSTRAINTS;
DROP TABLE FLIGHT XXXXXXXXXXCASCADE CONSTRAINTS;
DROP TABLE ADDITIONAL_PILOT CASCADE CONSTRAINTS;
DROP TABLE HOSTING XXXXXXXXXXCASCADE CONSTRAINTS;
DROP TABLE PILOT_QUALIFICATION CASCADE CONSTRAINTS;
-- PURGE RECYCLEBIN;
------ CREATE TABLE STATEMENTS ------
CREATE TABLE MODEL (
modelID varchar2(15) not null,
economySeats number(3),
usinessSeats number(3),
firstClassSeats number(3),
cargoCapacity number(6),
fuelCapacity number(6),
planeLength number(5),
wingspan number(5),
serviceHours number(3),
PRIMARY KEY(modelID)
);
CREATE TABLE LOCATION (
airportCode char(3) not null,
country varchar2(10),
address varchar2(55),
phone varchar2(15),
PRIMARY KEY(airportCode)
);
CREATE TABLE NEACC_MEMBER (
memberID varchar2(5) not null,
flightGoldPoints number(5),
PRIMARY KEY(memberID)
);
CREATE TABLE ROUTE (
outeID varchar2(5) not null,
description varchar2(100),
a
iveAirportCode char(3) not null,
departAirportCode char(3) not null,
PRIMARY KEY(routeID),
FOREIGN KEY(a
iveAirportCode) REFERENCES LOCATION(airportCode),
FOREIGN KEY(departAirportCode) REFERENCES LOCATION(airportCode)
);
CREATE TABLE STAFF (
staffID varchar2(5) not null,
name varchar2(20),
address varchar2(55),
email varchar2(25),
phone varchar2(15),
passportNum varchar2(10) not null, -- all NEA flight staff must have a passport
pilotYesNo char(1) CHECK ( pilotYesNo IN ('y','n') ),
prevHrsPilotExp number(5),
attendantYesNo char(1) CHECK ( attendantYesNo IN ('y','n') ),
memberID varchar2(5),
PRIMARY KEY(staffID),
FOREIGN KEY(memberID) REFERENCES NEACC_MEMBER(memberID)
);
CREATE TABLE AIRCRAFT (
aircraftID varchar2(5) not null,
mailCargoCapacity number(3),
numMedPacks number(2),
numDefi
illators number(2),
haulType varchar2(5) CHECK( haulType IN ('long', 'short') ),
modelID varchar2(15) not null,
PRIMARY KEY(aircraftID),
FOREIGN KEY(modelID) REFERENCES MODEL(modelID)
);
CREATE TABLE SERVICE (
serviceDate date XXXXXXXXXXnot null,
aircraftID varchar2(5) not null,
description varchar2(35),
cost varchar2(5),
PRIMARY KEY(serviceDate, aircraftID),
FOREIGN KEY(aircraftID) REFERENCES AIRCRAFT(aircraftID)
);
CREATE TABLE CUSTOMER (
customerID varchar2(5) not null,
name varchar2(20),
address varchar2(55),
country varchar2(20),
email varchar2(25),
phone varchar2(15),
irthdate date,
passportNum varchar2(10),
memberID varchar2(5),
PRIMARY KEY(customerID),
FOREIGN KEY(memberID) REFERENCES NEACC_MEMBER(memberID)
);
CREATE TABLE FLIGHT (
flightID char(9) not null,
estDepartureDateTime date,
actDepartDateTime date,
actA
iveDateTime date,
avgSpeed number(6),
avgHeight number(6),
estDuration XXXXXXXXXXnumber(4),
estFuel number(5),
haulType XXXXXXXXXXvarchar2(5) CHECK( haulType IN ('long', 'short') ),
captainStaffID varchar2(5) not null, -- every flight must have captain, first officer, route and aircraft
firstOfficerStaffID varchar2(5) not null,
outeID varchar2(5) not null,
aircraftID varchar2(5) not null,
PRIMARY KEY(flightID),
FOREIGN KEY(captainStaffID) REFERENCES STAFF(staffID),
FOREIGN KEY(firstOfficerStaffID) REFERENCES STAFF(staffID),
FOREIGN KEY(routeID) REFERENCES ROUTE(routeID),
FOREIGN KEY(aircraftID) REFERENCES AIRCRAFT(aircraftID)
);
CREATE TABLE TICKET (
ticketNum varchar2(5) not null,
luggageLimit number(2), -- must be in kilograms
seatNum number(3),
classCode XXXXXXXXXXchar(2) CHECK( classCode IN ('E','B','F') ),
medicalCondition XXXXXXXXXXvarchar2(30),
mealChoice XXXXXXXXXXchar(2) CHECK( mealChoice IN ('ST', 'VG') ),
customerID varchar2(5) not null,
flightID char(9) not null,
PRIMARY KEY(ticketNum),
FOREIGN KEY(customerID) REFERENCES CUSTOMER(customerID),
FOREIGN KEY(flightID) REFERENCES FLIGHT(flightID)
);
CREATE TABLE IRREGULAR_EVENT (
eventNumber number(2) not null, -- assume no more than 99 i
egular events on one flight
flightID char(9) not null,
eventDateTIme date,
eventDescription varchar2(50),
PRIMARY KEY(eventNumber, flightID),
FOREIGN KEY(flightID) REFERENCES FLIGHT(flightID)
);
CREATE TABLE ADDITIONAL_PILOT (
staffID varchar2(5) not null,
flightID char(9) not null,
activityCode varchar2(5),
activityDesc varchar2(40),
PRIMARY KEY(staffID, flightID),
FOREIGN KEY(staffID) REFERENCES STAFF(staffID),
FOREIGN KEY(flightID) REFERENCES FLIGHT(flightID)
);
CREATE TABLE HOSTING (
staffID varchar2(5) not null,
flightID char(9) not null,
PRIMARY KEY(staffID, flightID),
FOREIGN KEY(staffID) REFERENCES STAFF(staffID),
FOREIGN KEY(flightID) REFERENCES FLIGHT(flightID)
);
CREATE TABLE PILOT_QUALIFICATION (
qualification varchar2(10) not null,
staffID varchar2(5) not null,
PRIMARY KEY(qualification, staffID),
FOREIGN KEY(staffID) REFERENCES STAFF(staffID)
);
--modelID,
--economySeats,
--businessSeats,
--firstClassSeats,
--cargoCapacity, --kg
--fuelCapacity, --L
--planeLength, --cm
--wingspan, --cm
--serviceHours,
INSERT INTO MODEL VALUES ('Boeing XXXXXXXXXX' ,162 ,12 ,NULL ,18125 ,26020 ,4200 ,4880 , 450);
INSERT INTO MODEL VALUES ('Boeing 747-8' ,350 ,50 ,10 ,95000 ,238610 ,7625 ,6840 , 400);
INSERT INTO MODEL VALUES ('Boeing 747-400D',660 ,NULL ,NULL ,20125 ,216840 ,7066 ,6444 , 400);
INSERT INTO MODEL VALUES ('Boeing 787-8' ,218 ,32 ,NULL ,46120 ,126206 ,5672 ,6012 , 600);
INSERT INTO MODEL VALUES ('Ai
us A220' ,108 ,NULL ,4 ,9650 ,17630 ,3870 ,3510 , 500);
--airportCode,
--country,
--address,
--phone,
INSERT INTO LOCATION VALUES ('LAX', 'USA' , '1 World Way, Los Angeles, CA 90046, USA' , ' XXXXXXXXXX');
INSERT INTO LOCATION VALUES ('NEX', 'New Endor' , '20 Lonely Mountain Way, Erebor, ERB 2038, New Endor' , ' XXXXXXXXXX');
INSERT INTO LOCATION VALUES ('NED', 'New Endor' , '5 Weathertop Street, Eriador, ERD 2446, New Endor' , ' XXXXXXXXXX');
INSERT INTO LOCATION VALUES ('MEL', 'AUS' , 'Departure Drive, Melbourne, VIC 3045, AUS' , ' XXXXXXXXXX');
INSERT INTO LOCATION VALUES ('LHR', 'UK' , 'Longford TW6, UK' , ' XXXXXXXXXX');
--memberID,
--flightGoldPoints,
INSERT INTO NEACC_MEMBER VALUES ('M0001', 220);
INSERT INTO NEACC_MEMBER VALUES ('M0002', 104);
INSERT INTO NEACC_MEMBER VALUES ('M0003', 302);
INSERT INTO NEACC_MEMBER VALUES ('M0004', 99);
INSERT INTO NEACC_MEMBER VALUES ('M0005', 50);
INSERT INTO NEACC_MEMBER VALUES ('M0006', 480);
INSERT INTO NEACC_MEMBER VALUES ('M0007', 12);
INSERT INTO NEACC_MEMBER VALUES ('M0008', 70);
INSERT INTO NEACC_MEMBER VALUES ('M0009', 300);
INSERT INTO NEACC_MEMBER VALUES ('M0010', 620);
INSERT INTO NEACC_MEMBER VALUES ('M0011', 321);
INSERT INTO NEACC_MEMBER VALUES ('M0012', 44);
INSERT INTO NEACC_MEMBER VALUES ('M0013', 390);
INSERT INTO NEACC_MEMBER VALUES ('M0014', 12);
INSERT INTO NEACC_MEMBER VALUES ('M0015', 123);
INSERT INTO NEACC_MEMBER VALUES ('M0016', 20);
--routeID,
--description,
--a
iveAirportCode,
--departAirportCode,
INSERT INTO ROUTE VALUES ('R0001', 'Refer to navigation plan NE123', 'LAX', 'NEX');
INSERT INTO ROUTE VALUES ('R0002', 'Refer to navigation plan NE223', 'NEX', 'LHR');
INSERT INTO ROUTE VALUES ('R0003', 'Refer to navigation plan NE323', 'MEL', 'NED');
INSERT INTO ROUTE VALUES ('R0004', 'Refer to navigation plan NE423', 'NED', 'NED');
INSERT INTO ROUTE VALUES ('R0005', 'Refer to navigation plan NE523', 'LHR', 'NEX');
INSERT INTO ROUTE VALUES ('R0006', 'Refer to navigation plan NE623', 'NED', 'NEX');
--staffID,
--name,
--address,
--email,
--phone,
--passportNum,
--pilotYesNo,
--prevHrsPilotExp,
--attendantYesNo,
--memberID,
INSERT INTO STAFF VALUES ('S0001', 'Evie Jude', '20 Kirp Street, Erebor, 2038, New Endor', ' XXXXXXXXXX', ' XXXXXXXXXX', ' XXXXXXXXXX', 'y', 4030, 'n', 'M0001');
INSERT INTO STAFF VALUES ('S0002', 'Jackson Jude', '20 Kirp Street, Erebor, 2038, New Endor', ' XXXXXXXXXX', ' XXXXXXXXXX', ' XXXXXXXXXX', 'y', 3589, 'n', 'M0002');
INSERT INTO STAFF VALUES ('S0003', 'Kaya Mahomed', '124 Vivid Lane, Fangorn, 2066, New Endor', ' XXXXXXXXXX', ' XXXXXXXXXX', ' XXXXXXXXXX', 'n', NULL, 'y', 'M0003');
INSERT INTO STAFF VALUES ('S0004', 'Thrain King', '14 Bree Road, Durin, 2045, New Endor', ' XXXXXXXXXX', ' XXXXXXXXXX', ' XXXXXXXXXX', 'n', NULL, 'y', 'M0004');
INSERT INTO STAFF VALUES ('S0005', 'Michael Sindarin', '37 Tenth Street, Durin, 2045, New Endor', ' XXXXXXXXXX', ' XXXXXXXXXX', ' XXXXXXXXXX', 'n', NULL, 'y', 'M0005');
INSERT INTO STAFF VALUES ('S0006', 'Rohan Anarion', '66 Ninth Street, Gondor, 2046, New Endor', ' XXXXXXXXXX', ' XXXXXXXXXX', ' XXXXXXXXXX', 'y', 2405, 'y', 'M0006');
INSERT INTO STAFF VALUES ('S0007'