Tables

CREATE TABLE STATIONS (
  ID INT PRIMARY KEY,
  NAME VARCHAR(255) NOT NULL
);

CREATE TABLE ROUTES (
  ID INTEGER,
  STATION_ID INTEGER REFERENCES STATIONS(ID),
  ORDER_NUMBER INTEGER NOT NULL,
  PRIMARY KEY (ID, STATION_ID)
);

CREATE TABLE TRANSITS (
  TRAIN_ID INTEGER,
  ROUTE_ID INTEGER,
  STATR_STATION INTEGER,
  FINISH_STATION INTEGER,
  ORDER_NUMBER INTEGER,
  PRIMARY KEY (ID, ROUTE_ID)
);

CREATE TABLE CREWS (
  ID INTEGER PRIMARY KEY,
  HEADSTATION_ID INTEGER NOT NULL REFERENCES STATIONS(ID)
);

CREATE TABLE CREW_MEMBERS (
  CREW_ID INTEGER REFERENCES CREWS(ID),
  EMPLOYEE_LASTNAME VARCHAR(255),
  EMPLOYEE_POST VARCHAR(255),
  PRIMARY KEY (CREW_ID, EMPLOYEE_LASTNAME)
);

CREATE TABLE TRAINS(
  ID INTEGER,
  ROUTE_ID INTEGER NOT NULL,
  CREW_ID INTEGER NOT NULL REFERENCES CREWS(ID),
  TYPE VARCHAR(255) NOT NULL CHECK (TYPE IN ('1st class', '2nd class', '3rd class', 'common')),
  PRIMARY KEY (ID)
);

CREATE TABLE TRIPS (
  ID INTEGER PRIMARY KEY,
  TICKETS_QUANTITY INTEGER,
  TRAIN_ID INTEGER REFERENCES TRAINS(ID)
);

CREATE TABLE TIMETABLE (
  TRIP_ID INTEGER REFERENCES TRIPS(ID),
  STATION_NUMBER INTEGER NOT NULL,
  ARRIVAL_TIME TIMESTAMP NOT NULL,
  DEPARTURE_TIME TIMESTAMP NOT NULL,
  PASSENGERS_CHANGE INTEGER,
  TRANSIT_PASSENGERS_CHANGE INTEGER,
  PRIMARY KEY (TRIP_ID, STATION_NUMBER)
);

CREATE TABLE DELAYS (
  TRIP_ID INTEGER,
  STATION_NUMBER INTEGER NOT NULL,
  DELAY INTEGER,  --IN MINUTES
  PRIMARY KEY (TRIP_ID, STATION_NUMBER),
  FOREIGN KEY (TRIP_ID, STATION_NUMBER) REFERENCES TIMETABLE (TRIP_ID, STATION_NUMBER)
);