-- DBF Sem 1 2019 -- Schema Definition -- New Endor Integrated Data Infrastructure DROP TABLE MODEL XXXXXXXXXXCASCADE CONSTRAINTS; DROP TABLE LOCATION XXXXXXXXXXCASCADE CONSTRAINTS; DROP TABLE TICKET...

hi this assignment as no page limit or word limit


-- DBF Sem 1 2019 -- Schema Definition -- New Endor Integrated Data Infrastructure DROP TABLE MODEL CASCADE CONSTRAINTS; DROP TABLE LOCATION CASCADE CONSTRAINTS; DROP TABLE TICKET CASCADE CONSTRAINTS; DROP TABLE ROUTE CASCADE CONSTRAINTS; DROP TABLE IRREGULAR_EVENT CASCADE CONSTRAINTS; DROP TABLE SERVICE CASCADE CONSTRAINTS; DROP TABLE AIRCRAFT CASCADE CONSTRAINTS; DROP TABLE NEACC_MEMBER CASCADE CONSTRAINTS; DROP TABLE STAFF CASCADE CONSTRAINTS; DROP TABLE CUSTOMER CASCADE CONSTRAINTS; DROP TABLE FLIGHT CASCADE CONSTRAINTS; DROP TABLE ADDITIONAL_PILOT CASCADE CONSTRAINTS; DROP TABLE HOSTING CASCADE CONSTRAINTS; DROP TABLE PILOT_QUALIFICATION CASCADE CONSTRAINTS; -- PURGE RECYCLEBIN; ------ CREATE TABLE STATEMENTS ------ CREATE TABLE MODEL ( modelID varchar2(15) not null, economySeats number(3), businessSeats 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 ( routeID varchar2(5) not null, description varchar2(100), arriveAirportCode char(3) not null, departAirportCode char(3) not null, PRIMARY KEY(routeID), FOREIGN KEY(arriveAirportCode) 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), numDefibrillators 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 not 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), birthdate 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, actArriveDateTime date, avgSpeed number(6), avgHeight number(6), estDuration number(4), estFuel number(5), haulType varchar2(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, routeID 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 char(2) CHECK( classCode IN ('E','B','F') ), medicalCondition varchar2(30), mealChoice char(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 irregular 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 737-800' ,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 ('Airbus 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', '+18554635252'); INSERT INTO LOCATION VALUES ('NEX', 'New Endor', '20 Lonely Mountain Way, Erebor, ERB 2038, New Endor', '+4123456789'); INSERT INTO LOCATION VALUES ('NED', 'New Endor', '5 Weathertop Street, Eriador, ERD 2446, New Endor', '+4234567891'); INSERT INTO LOCATION VALUES ('MEL', 'AUS', 'Departure Drive, Melbourne, VIC 3045, AUS', '+61392971600'); INSERT INTO LOCATION VALUES ('LHR', 'UK', 'Longford TW6, UK', '+448443351801'); --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, --arriveAirportCode, --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', '[email protected]', '+4933321531', '92814566', 'y', 4030, 'n', 'M0001'); INSERT INTO STAFF VALUES ('S0002', 'Jackson Jude', '20 Kirp Street, Erebor, 2038, New Endor', '[email protected]', '+4936321439', '93812289', 'y', 3589, 'n', 'M0002'); INSERT INTO STAFF VALUES ('S0003', 'Kaya Mahomed', '124 Vivid Lane, Fangorn, 2066, New Endor', '[email protected]', '+4934325536', '96855599', 'n', NULL, 'y', 'M0003'); INSERT INTO STAFF VALUES ('S0004', 'Thrain King', '14 Bree Road, Durin, 2045, New Endor', '[email protected]', '+4966329933', '99675599', 'n', NULL, 'y', 'M0004'); INSERT INTO STAFF VALUES ('S0005', 'Michael Sindarin', '37 Tenth Street, Durin, 2045, New Endor', '[email protected]', '+4914828967', '92450599', 'n', NULL, 'y', 'M0005'); INSERT INTO STAFF VALUES ('S0006', 'Rohan Anarion', '66 Ninth Street, Gondor, 2046, New Endor', '[email protected]', '+4912325689', '93607519', 'y', 2405, 'y', 'M0006'); INSERT INTO STAFF VALUES ('S0007'