CREATE TABLE AIRPLANESINFO(P_TYPE VARCHAR(20) PRIMARY KEY,P_WEIGHT INT ,FUEL INT,
PASSENGERS INT);
CREATE TABLE AIRPLANES(P_ID INT PRIMARY KEY, P_TYPE VARCHAR(20),MAINTAINANCE
INT ,FOREIGN KEY(P_TYPE) REFERENCES AIRPLANESINFO(P_TYPE));
CREATE TABLE AIRPORTSINFO_LOC(A_LOC VARCHAR(20) PRIMARY KEY,A_RANK
INT,PLANES_CAPACITY INT);
CREATE TABLE AIRPORTSINFO_NAME(A_NAME VARCHAR(20) PRIMARY KEY,A_LOC
VARCHAR(20),FOREIGN KEY(A_LOC) REFERENCES AIRPORTSINFO_LOC(A_LOC));
CREATE TABLE AIRPORTSINFO_ID(A_ID INT PRIMARY KEY,A_NAME VARCHAR(20),FOREIGN
KEY(A_NAME) REFERENCES AIRPORTSINFO_NAME(A_NAME));
CREATE TABLE SALARY(JOB VARCHAR(20) PRIMARY KEY,A_LOC VARCHAR(20),SALARY INT,FOREIGN
KEY(A_LOC) REFERENCES AIRPORTSINFO_LOC(A_LOC));
CREATE TABLE EMP(E_ID INT PRIMARY KEY,E_NAME VARCHAR(20),E_EMAIL VARCHAR(20),JOB
VARCHAR(20) ,A_LOC VARCHAR(20),JOINDATE DATE,FOREIGN KEY(A_LOC) REFERENCES
AIRPORTSINFO_LOC(A_LOC));
CREATE TABLE CUSTOMER(C_ID INT PRIMARY KEY,C_NAME VARCHAR(20),C_EMAIL
VARCHAR(20),NO_OF_FLIGHTS INT);
CREATE TABLE FLIGHT(F_ID INT PRIMARY KEY,P_ID INT, FROMA_ID INT, TOA_ID INT,DEPARTURE
TIMESTAMP(2));
CREATE TABLE FLIGHTINFO(F_ID INT PRIMARY KEY,P_ID INT, FROMA_ID INT, TOA_ID INT,DEPARTURE
TIMESTAMP(2),ARRIVAL TIMESTAMP(2),FARE INT);
CREATE OR REPLACE TRIGGER FLIGHT_INFO_TRIGGER
AFTER INSERT ON FLIGHT
FOR EACH ROW
BEGIN
IF :NEW.FROMA_ID=1 AND :NEW.TOA_ID =5 THEN
INSERT INTO FLIGHTINFO
VALUES(:NEW.F_ID,:NEW.P_ID,:NEW.FROMA_ID,:NEW.TOA_ID,:NEW.DEPARTURE,:NEW.DEPARTURE+I
NTERVAL '08' HOUR,4000);
END IF;
IF :NEW.FROMA_ID=2 AND :NEW.TOA_ID =4 THEN
INSERT INTO FLIGHTINFO
VALUES(:NEW.F_ID,:NEW.P_ID,:NEW.FROMA_ID,:NEW.TOA_ID,:NEW.DEPARTURE,:NEW.DEPARTURE+I
NTERVAL '12' HOUR,6200);
END IF;
END;
CREATE TABLE FLIGHTINFO_EMP(F_ID INT,E_ID INT,FOREIGN KEY(F_ID) REFERENCES
FLIGHT(F_ID),FOREIGN KEY(E_ID) REFERENCES EMP(E_ID));
CREATE SEQUENCE CUSTOMER_IN_FLIGHT
START WITH 1
INCREMENT BY 1
MAXVALUE 100
NOCYCLE
NOCACHE;
CREATE TABLE FLIGHTINFO_CUSTOMER(SEQ INT,C_ID INT, F_ID INT,FOREIGN KEY(C_ID) REFERENCES
CUSTOMER(C_ID),FOREIGN KEY(F_ID) REFERENCES FLIGHT(F_ID),PRIMARY KEY(C_ID,F_ID));
INSERT INTO AIRPLANESINFO VALUES('AIRBUS360',10000,500,30);
INSERT INTO AIRPLANESINFO VALUES('BOEING778',15000,600,45);
INSERT INTO AIRPLANES VALUES(1,'BOEING778',0);
INSERT INTO AIRPLANES VALUES(4,'BOEING778',1);
INSERT INTO AIRPLANES VALUES(6,'AIRBUS360',0);
INSERT INTO AIRPORTSINFO_LOC VALUES ('Delhi', 1, 100);
INSERT INTO AIRPORTSINFO_LOC VALUES('Los Angeles', 2, 200);
INSERT INTO AIRPORTSINFO_LOC VALUES('New York', 3, 300);
INSERT INTO AIRPORTSINFO_LOC VALUES('London ', 4, 400);
INSERT INTO AIRPORTSINFO_LOC VALUES('Dubai ', 5, 500);
INSERT INTO AIRPORTSINFO_NAME VALUES ('IGI','Delhi' );
INSERT INTO AIRPORTSINFO_NAME VALUES('Los Angeles','Los Angeles');
INSERT INTO AIRPORTSINFO_NAME VALUES('LaGuardia','New York');
INSERT INTO AIRPORTSINFO_NAME VALUES('Heathrow','London ');
INSERT INTO AIRPORTSINFO_NAME VALUES('Dubai International','Dubai ');
INSERT INTO AIRPORTSINFO_ID VALUES (1,'IGI');
INSERT INTO AIRPORTSINFO_ID VALUES(2,'Los Angeles');
INSERT INTO AIRPORTSINFO_ID VALUES(3,'LaGuardia');
INSERT INTO AIRPORTSINFO_ID VALUES(4,'Heathrow');
INSERT INTO AIRPORTSINFO_ID VALUES(5,'Dubai International');
INSERT INTO SALARY VALUES ('Engineer','Delhi', 100000);
INSERT INTO SALARY VALUES ('Receptionist','Delhi', 78000);
INSERT INTO SALARY VALUES('Ground Worker','Los Angeles', 20000);
INSERT INTO SALARY VALUES('Pilot','New York', 331000);
INSERT INTO SALARY VALUES('Hostess','London ',40012);
INSERT INTO EMP VALUES (1,'Manish Singh','[email protected]','Engineer','Delhi', '05-08-1995');
INSERT INTO EMP VALUES (2,'Piyush Singh','[email protected]','Receptionist','Delhi', '09-11-2005');
INSERT INTO EMP VALUES(3,'Raju','[email protected]','Ground Worker','Los Angeles', '11-02-2007');
INSERT INTO EMP VALUES(4,'Prateek','[email protected]','Pilot','New York', '02-04-2012');
INSERT INTO CUSTOMER VALUES (1,'Manpreet','[email protected]',6);
INSERT INTO CUSTOMER VALUES (2,'Ankit','[email protected]',1);
INSERT INTO CUSTOMER VALUES(3,'Nilesh','[email protected]',4);
INSERT INTO CUSTOMER VALUES(4,'Prateek','[email protected]', 6);
INSERT INTO FLIGHT VALUES (1,3,1,5,LOCALTIMESTAMP(2));
INSERT INTO FLIGHT VALUES(2,1,2,4,LOCALTIMESTAMP(2));
INSERT INTO FLIGHTINFO_EMP VALUES(1,2);
INSERT INTO FLIGHTINFO_EMP VALUES(1,3);
INSERT INTO FLIGHTINFO_EMP VALUES(1,4);
INSERT INTO FLIGHTINFO_EMP VALUES(2,1);
INSERT INTO FLIGHTINFO_EMP VALUES(2,4);
INSERT INTO FLIGHTINFO_CUSTOMER VALUES(CUSTOMER_IN_FLIGHT.NEXTVAL,3,1);
INSERT INTO FLIGHTINFO_CUSTOMER VALUES(CUSTOMER_IN_FLIGHT.NEXTVAL,4,1);
INSERT INTO FLIGHTINFO_CUSTOMER VALUES(CUSTOMER_IN_FLIGHT.NEXTVAL,1,2);
INSERT INTO FLIGHTINFO_CUSTOMER VALUES(CUSTOMER_IN_FLIGHT.NEXTVAL,4,2);
