CREATE TABLE airport(
   id int NOT NULL,
   name CHAR(50) NOT NULL,
   location CHAR(50) NOT NULL,
   numberOfPlane int NOT NULL,
   CONSTRAINT airport_pk PRIMARY KEY (id)
);

DROP TABLE airport;


/*-------------------------------*/
CREATE TABLE airplane(
  id int NOT NULL,
  name CHAR(50) NOT NULL,
  numberOfSeat int NOT NULL,
  airport_id int,
  CONSTRAINT airplane_pk PRIMARY KEY(id),
  CONSTRAINT fk_airplane
    FOREIGN KEY (airport_id)
    REFERENCES airport (id)
);

DROP TABLE airplane;


/*-------------------------------*/
CREATE TABLE passenger(
  id int NOT NULL,
  name CHAR(50) NOT NULL,
  phone CHAR(50) NOT NULL,
  mail CHAR(50) NOT NULL,
  CONSTRAINT passenger_pk
    PRIMARY KEY (id)
);

DROP TABLE passenger;


/*-------------------------------*/
CREATE TABLE flight1(
  flightid int NOT NULL,
  passenger_id int NOT NULL,
  airplane_id int NOT NULL,
  departure_time DATE,
  arrival_time DATE,
  fromWhere CHAR(50),
  toWhere CHAR(50),
  PRIMARY KEY (flightid),
  CONSTRAINT flight_fk1
    FOREIGN KEY (airplane_id)
    REFERENCES airplane1 (id),
  CONSTRAINT flight_fk2
    FOREIGN KEY (passenger_id)
    REFERENCES passenger1 (id)
);

DROP TABLE flight1;