CREATE TABLE city (
  name           VARCHAR(30) NOT NULL,
  state          CHAR(2) NOT NULL,
  CONSTRAINT city_pk
    PRIMARY KEY (name, state)
);

CREATE TABLE station (
  id             INT NOT NULL,
  name           VARCHAR(30),
  city_name      VARCHAR(30) NOT NULL,
  city_state     CHAR(2) NOT NULL, 
  CONSTRAINT station_pk
    PRIMARY KEY (id),
  CONSTRAINT station_fk_city
    FOREIGN KEY (city_name, city_state) REFERENCES city(name, state)
);

CREATE TABLE track (
  id             INT NOT NULL,
  length         FLOAT NOT NULL,
  sta_from       INT NOT NULL,
  sta_to         INT NOT NULL,
  CONSTRAINT track_pk
    PRIMARY KEY (id),
  CONSTRAINT track_fk_station_from
    FOREIGN KEY (stat_from) REFERENCES station(id),
  CONSTRAINT track_fk_station_to
    FOREIGN KEY (stat_to) REFERENCES station(id)
);

CREATE TABLE locomotive (
  id             INT NOT NULL,
  gross_weight   FLOAT NOT NULL,
  max_weight     FLOAT NOT NULL,
  CONSTRAINT locomotive_pk
    PRIMARY KEY (id)
);

CREATE TABLE car (
  id             INT NOT NULL,
  gross_weight   FLOAT NOT NULL,
  CONSTRAINT car_pk
    PRIMARY KEY (id)
);

CREATE TABLE seat (
  id             INT NOT NULL,
  car_id         INT NOT NULL,
  CONSTRAINT seat_pk
    PRIMARY KEY (id, car_id),
  CONSTRAINT seat_fk_car
    FOREIGN KEY (car_id) REFERENCES car(id)
);

CREATE TABLE train (
  id             INT NOT NULL,
  start          DATETIME NOT NULL,
  end            DATETIME NOT NULL,
  locomotive_id  INT NOT NULL,
  track_id       INT NOT NULL,
  CONSTRAINT train_pk
    PRIMARY KEY (id),
  CONSTRAINT train_fk_locomotive
    FOREIGN KEY (locomotive_id) REFERENCES locomotive(id),
  CONSTRAINT train_fk_track
    FOREIGN KEY (track_id) REFERENCES track(id)
);

CREATE TABLE composition (
  train_id       INT NOT NULL,
  car_id         INT NOT NULL,
  CONSTRAINT composition_pk
    PRIMARY KEY (train_id, car_id),
  CONSTRAINT composition_fk_train
    FOREIGN KEY (train_id) REFERENCES train(id),
  CONSTRAINT composition_fk_car
    FOREIGN KEY (car_id) REFERENCES car(id)
);

CREATE TABLE reservation (
  conf           INT NOT NULL,
  train_id       INT NOT NULL,
  seat_id        INT NOT NULL,
  car_id         INT NOT NULL,
  for_cust       VARCHAR(20) NOT NULL,
  CONSTRAINT reservation_pk
    PRIMARY KEY (conf),
  CONSTRAINT reservation_un_tcsd
    UNIQUE (train_id, car_id, seat_id),
  CONSTRAINT reservation_fk_composition
    FOREIGN KEY (train_id, car_id) REFERENCES composition(train_id, car_id),
  CONSTRAINT reservation_fk_seat
    FOREIGN KEY (seat_id, car_id) REFERENCES seat(id, car_id),
  CONSTRAINT reservation_fk_customer
    FOREIGN KEY (for_cust) REFERENCES customer(phone)
);

CREATE TABLE customer (
  name           VARCHAR(30) NOT NULL,
  phone          VARCHAR(20) NOT NULL,
  CONSTRAINT customer_pk
    PRIMARY KEY (name, phone)
);