Final

--Город
DROP TABLE IF EXISTS City CASCADE;
CREATE TABLE City (
    ID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Name VARCHAR(32)
);



--Маршрут
DROP TABLE IF EXISTS Route CASCADE;
CREATE TABLE Route (
    ID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    WeekDay VARCHAR(2),
    TimeDeparture TIME,
    TravelTime TIME,
    ID_CitiDeparture INTEGER references  City(ID),
    ID_CityArrival INTEGER references  City(ID),
    Price INTEGER,
    MIN_QuantityPlace INTEGER
);



--Автик
DROP TABLE IF EXISTS Bus CASCADE;
CREATE TABLE Bus (
    ID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Trademark VARCHAR(16),
    QuantityPlace INTEGER,
    StateNumber VARCHAR(16)
);


--Водитель
DROP TABLE IF EXISTS Driver CASCADE;
CREATE TABLE Driver (
    ID SERIAL PRIMARY KEY,
    Name VARCHAR(16),
    ID_City Integer references  City(ID)
);


--Касса
DROP TABLE IF EXISTS TicketOffice CASCADE;
CREATE TABLE TicketOffice (
    ID SERIAL PRIMARY KEY,
    ID_City INTEGER references  City(ID),
    Address VARCHAR(16)
);

--Рейс
DROP TABLE IF EXISTS Trip CASCADE;
CREATE TABLE Trip (
    ID SERIAL PRIMARY KEY,
    ID_Route INTEGER references Route(ID),
    ID_Bus INTEGER references Bus(ID),
    ID_Driver INTEGER references Driver(ID),
    DateDeparture DATE
);

--Клиент
DROP TABLE IF EXISTS Client CASCADE;
CREATE TABLE Client (
    ID SERIAL PRIMARY KEY,
    Name VARCHAR(32),
    Info VARCHAR(64)
);

--Билет
DROP TABLE IF EXISTS Ticket CASCADE;
CREATE TABLE Ticket (
    ID SERIAL PRIMARY KEY,
    ID_TicketOffice INTEGER references TicketOffice(ID),
    ID_Trip INTEGER references Trip(ID),
    Place VARCHAR(32),
    DateOfDale DATE,
    ID_Client INTEGER references Client(ID)
);