database.sql

create table AIRPORT(
    AP_NAME varchar(100) primary key,
    COUNTRY varchar(30),
    CITY_NAME varchar(15)
);

create table EMPLOYEE(
    EMP_ID INT PRIMARY KEY,
    ENAME varchar(100),
    JOBTYPE VARCHAR(40),
    SALARY REAL NOT NULL,
    DOB DATE NOT NULL,
    PHONE_NO VARCHAR(10),
    AP_NAME VARCHAR(100),
    FOREIGN KEY(AP_NAME) REFERENCES AIRPORT(AP_NAME) ON DELETE CASCADE
);


CREATE TABLE AIRLINE(
    AIRLINE_ID INT PRIMARY KEY,
    AL_NAME VARCHAR(100)
);

ALTER TABLE AIRLINE
ALTER AIRLINE_ID SET DEFAULT ROUND(RAND()*1000000 + 100000);

CREATE TABLE CONTAINS(
    AIRLINE_ID INT NOT NULL,
    AP_NAME VARCHAR(100) NOT NULL,
    PRIMARY KEY(AIRLINE_ID, AP_NAME),
    FOREIGN KEY(AIRLINE_ID) REFERENCES AIRLINE(AIRLINE_ID) ON DELETE CASCADE,
    FOREIGN KEY(AP_NAME) REFERENCES AIRPORT(AP_NAME) ON DELETE CASCADE
);

CREATE TABLE FLIGHTS(
    FLIGHT_CODE INT PRIMARY KEY,
    AIRLINE_ID INT NOT NULL,
    SOURCE VARCHAR(100),
    DESTINATION VARCHAR(100),
    ARRIVAL DATETIME,
    DEPARTURE DATETIME,
    STATUS VARCHAR(20), -- ON TIME OR DELAYED
    FOREIGN KEY(AIRLINE_ID) REFERENCES AIRLINE(AIRLINE_ID) ON DELETE CASCADE,
    FOREIGN KEY(SOURCE) REFERENCES AIRPORT(AP_NAME) ON DELETE CASCADE,
    FOREIGN KEY(DESTINATION) REFERENCES AIRPORT(AP_NAME) ON DELETE CASCADE
);
ALTER TABLE FLIGHTS
ALTER FLIGHT_CODE SET DEFAULT ROUND(RAND()*1000000 + 100000);

CREATE TABLE PASSENGER(
    PID INT PRIMARY KEY,
    FLIGHT_CODE INT,
    SEAT_NO varchar(5) NOT NULL,
    BOOK_DATE DATE NOT NULL,
    PNAME VARCHAR(100) NOT NULL,
    PHONE_NO VARCHAR(10) NOT NULL,
    FOREIGN KEY(FLIGHT_CODE) REFERENCES FLIGHTS(FLIGHT_CODE) ON DELETE CASCADE
);
ALTER TABLE PASSENGER
ALTER PID SET DEFAULT ROUND(RAND()*1000000 + 100000);