Create Table Customer(
    cid INTEGER NOT NULL auto_increment,
    cname char(80) NOT NULL,
    email CHAR(40) NOT NULL,
    UNIQUE (email),
    address char(200),
    password char(16) NOT NULL,
    Primary Key(cid)
);

Create Table City(
    cityid integer NOT NULL auto_increment,
    title CHAR(50) NOT NULL,
    state char(2) NOT NULL,
    Primary key(cityid))auto_increment = 0;

CREATE TABLE Flight(
    fid INTEGER NOT null auto_increment,
    fnumber integer,
    fdate DATE NOT NULL,
    ftime TIME NOT NULL,
    price REAL NOT NULL,
    class INTEGER NOT NULL,
    capacity INTEGER NOT NULL,
    available INTEGER NOT NULL,
    orig INTEGER NOT NULL,
    dest INTEGER NOT NULL,
    PRIMARY KEY(fid),
    Foreign KEY(orig) references City(cityid) ON DELETE cascade,
    Foreign key(dest) references City(cityid) ON DELETE cascade) AUTO_INCREMENT = 0;

CREATE TABLE Reservation(
    ordernum INTEGER NOT null auto_increment,
    cid integer not null,
    dfid integer not null,
    rfid integer,
    qty integer not null,
    cardnum CHAR(16) not null,
    cardmonth integer not null,
    cardyear integer not null,
    order_date DATE,
    PRIMARY KEY(ordernum),
    Foreign KEY(cid) references Customer(cid) ON DELETE cascade,
    Foreign key(dfid) references Flight(fid) ON DELETE cascade,
    Foreign key(rfid) references Flight(fid) ON DELETE cascade)






DELIMITER $$
Create Trigger reservation_all
    after insert on Reservation
    for each row
    begin
        update Reservation
        set order_date = Current_Date
        where ordernum = New.ordernum;
        update Flight X
        set available = (Select available
                         from Flight f
                         where (f.fid = new.dfid)) - new.qty
        where X.fid = new.dfid;
        update Flight X1
        set available = (Select available
                         from Flight f
                         where (f.fid = new.rfid)) - new.qty
        where X1.fid = new.rfid;
        end;
$$