RelationalSchema: RouteMK.2.sql

DROP TABLE IF EXISTS child_ticket CASCADE;
DROP TABLE IF EXISTS ticket_relations CASCADE;
DROP TABLE IF EXISTS ticket CASCADE;
DROP TABLE IF EXISTS payment CASCADE;
DROP TABLE IF EXISTS review CASCADE;
DROP TABLE IF EXISTS student_ticket CASCADE;
DROP TABLE IF EXISTS student CASCADE;
DROP TABLE IF EXISTS trip_stops CASCADE;
DROP TABLE IF EXISTS trip_days_active CASCADE;
DROP TABLE IF EXISTS trip CASCADE;
DROP TABLE IF EXISTS route CASCADE;
DROP TABLE IF EXISTS favorite CASCADE;
DROP TABLE IF EXISTS driver_drives_on_trip CASCADE;
DROP TABLE IF EXISTS driver_vehicle_operation CASCADE;
DROP TABLE IF EXISTS driver CASCADE;
DROP TABLE IF EXISTS transport_organizer CASCADE;
DROP TABLE IF EXISTS admin CASCADE;
DROP TABLE IF EXISTS account CASCADE;
DROP TABLE IF EXISTS location CASCADE;
DROP TABLE IF EXISTS train CASCADE;
DROP TABLE IF EXISTS van CASCADE;
DROP TABLE IF EXISTS bus CASCADE;
DROP TABLE IF EXISTS automobile CASCADE;
DROP TABLE IF EXISTS vehicle CASCADE;

CREATE TABLE account
(
    account_id SERIAL PRIMARY KEY,
    email      VARCHAR(100) NOT NULL UNIQUE,
    name       VARCHAR(50)  NOT NULL,
    surname    VARCHAR(50)  NOT NULL,
    password   VARCHAR(60)  NOT NULL
);

CREATE TABLE admin
(
    admin_id   SERIAL PRIMARY KEY,
    account_id INT NOT NULL,
    CONSTRAINT fk_admin_account_id FOREIGN KEY (account_id) REFERENCES account (account_id) ON DELETE CASCADE
);

CREATE TABLE payment
(
    payment_id  SERIAL PRIMARY KEY,
    account_id  INT              NOT NULL,
    date        DATE             NOT NULL,
    total_price DOUBLE PRECISION NOT NULL CHECK (total_price >= 0),
    n_tickets   INT              NOT NULL CHECK (n_tickets > 0),
    CONSTRAINT payment_account_id_fkey FOREIGN KEY (account_id) REFERENCES account (account_id) ON DELETE CASCADE
);

CREATE TABLE location
(
    location_id SERIAL PRIMARY KEY,
    latitude    DOUBLE PRECISION NOT NULL,
    longitude   DOUBLE PRECISION NOT NULL,
    name        VARCHAR(100)     NOT NULL
);

CREATE TABLE transport_organizer
(
    transport_organizer_id SERIAL PRIMARY KEY,
    account_id             INT          NOT NULL,
    company_name           VARCHAR(100) NOT NULL,
    company_embg           VARCHAR(50)  NOT NULL,
    CONSTRAINT transport_organizer_account_id_fkey FOREIGN KEY (account_id) REFERENCES account (account_id) ON DELETE CASCADE
);

CREATE TABLE route
(
    route_id               SERIAL PRIMARY KEY,
    transport_organizer_id INT NOT NULL,
    from_location_id       INT NOT NULL,
    to_location_id         INT NOT NULL,
    CONSTRAINT route_transport_organizer_id_fkey FOREIGN KEY (transport_organizer_id) REFERENCES transport_organizer (transport_organizer_id) ON DELETE CASCADE,
    CONSTRAINT route_from_location_id_fkey FOREIGN KEY (from_location_id) REFERENCES location (location_id),
    CONSTRAINT route_to_location_id_fkey FOREIGN KEY (to_location_id) REFERENCES location (location_id)
);

CREATE TABLE trip
(
    trip_id                SERIAL PRIMARY KEY,
    base_price             DOUBLE PRECISION,
    transport_organizer_id INT  NOT NULL,
    route_id               INT  NOT NULL,
    free_seats             INT CHECK (free_seats >= 0),
    date                   DATE NOT NULL,
    status                 VARCHAR(30),
    CONSTRAINT trip_transport_organizer_id_fkey FOREIGN KEY (transport_organizer_id) REFERENCES transport_organizer (transport_organizer_id),
    CONSTRAINT trip_route_id_fkey FOREIGN KEY (route_id) REFERENCES route (route_id) ON DELETE CASCADE
);

CREATE TABLE ticket
(
    ticket_id            SERIAL PRIMARY KEY,
    trip_id              INT                    NOT NULL,
    gets_on_location_id  INT                    NOT NULL,
    gets_off_location_id INT                    NOT NULL,
    account_id           INT                    NOT NULL,
    date_purchased       DATE                   NOT NULL,
    time_purchased       TIME WITHOUT TIME ZONE NOT NULL,
    price                DOUBLE PRECISION CHECK (price >= 0),
    seat                 VARCHAR(10),
    payment_id           INT                    NOT NULL,
        ticket_related_with_id   INT,
    CONSTRAINT gets_on_location_fkey FOREIGN KEY (gets_on_location_id) REFERENCES location (location_id) ON DELETE CASCADE,
    CONSTRAINT gets_off_location_fkey FOREIGN KEY (gets_off_location_id) REFERENCES location (location_id) ON DELETE CASCADE,
    CONSTRAINT ticket_account_id_fkey FOREIGN KEY (account_id) REFERENCES account (account_id) ON DELETE CASCADE,
    CONSTRAINT ticket_payment_id_fkey FOREIGN KEY (payment_id) REFERENCES payment (payment_id) ON DELETE CASCADE,
    CONSTRAINT trip_id_fkey FOREIGN KEY (trip_id) REFERENCES trip (trip_id) ON DELETE CASCADE,
    CONSTRAINT ticket_related_with_id_fk FOREIGN KEY (ticket_related_with_id) REFERENCES ticket(ticket_id) ON DELETE CASCADE
);

CREATE TABLE review
(
    review_id   SERIAL PRIMARY KEY,
    account_id  INT NOT NULL,
    ticket_id   INT NOT NULL,
    description TEXT,
    rating      INT CHECK (rating >= 1 AND rating <= 5),
    CONSTRAINT review_account_id_fkey FOREIGN KEY (account_id) REFERENCES account (account_id) ON DELETE CASCADE,
    CONSTRAINT review_ticket_id_fkey FOREIGN KEY (ticket_id) REFERENCES ticket (ticket_id) ON DELETE CASCADE
);

CREATE TABLE vehicle
(
    transport_organizer_id INT         NOT NULL,
    vehicle_id             SERIAL PRIMARY KEY,
    model                  VARCHAR(30) NOT NULL,
    brand                  VARCHAR(30) NOT NULL,
    capacity               VARCHAR(20) NOT NULL,
    year_manufactured      VARCHAR(10),
    CONSTRAINT vehicle_transport_organizer_id_fkey FOREIGN KEY (transport_organizer_id) REFERENCES transport_organizer (transport_organizer_id) ON DELETE CASCADE

);

CREATE TABLE automobile
(
    automobile_id SERIAL PRIMARY KEY,
    vehicle_id    INT NOT NULL UNIQUE,
    CONSTRAINT automobile_vehicle_id_fkey FOREIGN KEY (vehicle_id) REFERENCES vehicle (vehicle_id) ON DELETE CASCADE
);

CREATE TABLE bus
(
    bus_id     SERIAL PRIMARY KEY,
    vehicle_id INT NOT NULL UNIQUE,
    CONSTRAINT bus_vehicle_id_fkey FOREIGN KEY (vehicle_id) REFERENCES vehicle (vehicle_id) ON DELETE CASCADE
);

CREATE TABLE van
(
    van_id     SERIAL PRIMARY KEY,
    vehicle_id INT NOT NULL UNIQUE,
    CONSTRAINT van_vehicle_id_fkey FOREIGN KEY (vehicle_id) REFERENCES vehicle (vehicle_id) ON DELETE CASCADE
);

CREATE TABLE train
(
    train_id   SERIAL PRIMARY KEY,
    vehicle_id INT NOT NULL UNIQUE,
    CONSTRAINT train_vehicle_id_fkey FOREIGN KEY (vehicle_id) REFERENCES vehicle (vehicle_id) ON DELETE CASCADE
);

CREATE TABLE driver
(
    driver_id              SERIAL PRIMARY KEY,
    account_id             INT NOT NULL,
    years_experience       INT NOT NULL,
    transport_organizer_id INT,
    CONSTRAINT driver_account_id_fkey FOREIGN KEY (account_id) REFERENCES account (account_id) ON DELETE CASCADE,
    CONSTRAINT driver_transport_organizer_id_fkey FOREIGN KEY (transport_organizer_id) REFERENCES transport_organizer (transport_organizer_id) ON DELETE CASCADE
);

CREATE TABLE driver_vehicle_operation
(
    driver_vehicle_operation_id SERIAL PRIMARY KEY,
    driver_id                   INT NOT NULL,
    vehicle_id                  INT NOT NULL,
    CONSTRAINT driver_vehicle_operation_driver_id_fkey FOREIGN KEY (driver_id) REFERENCES driver (driver_id) ON DELETE CASCADE,
    CONSTRAINT driver_vehicle_operation_vehicle_id_fkey FOREIGN KEY (vehicle_id) REFERENCES vehicle (vehicle_id) ON DELETE CASCADE
);

CREATE TABLE driver_drives_on_trip
(
    driver_drives_on_trip_id SERIAL PRIMARY KEY,
    driver_id                INT NOT NULL,
    trip_id                  INT NOT NULL,
    CONSTRAINT driver_drives_on_trip_driver_id_fkey FOREIGN KEY (driver_id) REFERENCES driver (driver_id) ON DELETE CASCADE,
    CONSTRAINT driver_drives_on_trip_trip_id_fkey FOREIGN KEY (trip_id) REFERENCES trip (trip_id) ON DELETE CASCADE
);

CREATE TABLE favorite
(
    favorite_id SERIAL PRIMARY KEY,
    route_id    INT NOT NULL,
    account_id  INT NOT NULL,
    CONSTRAINT favorite_route_id_fkey FOREIGN KEY (route_id) REFERENCES route (route_id) ON DELETE CASCADE,
    CONSTRAINT favorite_account_id_fkey FOREIGN KEY (account_id) REFERENCES account (account_id) ON DELETE CASCADE
);

CREATE TABLE trip_days_active
(
    trip_days_active_id SERIAL PRIMARY KEY,
    route_id            INT         NOT NULL,
    day                 VARCHAR(20) NOT NULL,
    CONSTRAINT trip_days_active_route_id_fkey FOREIGN KEY (route_id) REFERENCES route (route_id) ON DELETE CASCADE
);

CREATE TABLE trip_stops
(
    trip_stop_id SERIAL PRIMARY KEY,
    trip_id      INT                    NOT NULL,
    location_id  INT                    NOT NULL,
    stop_time    TIME WITHOUT TIME ZONE NOT NULL,
    CONSTRAINT trip_stops_trip_id_fkey FOREIGN KEY (trip_id) REFERENCES trip (trip_id) ON DELETE CASCADE,
    CONSTRAINT trip_stops_location_id_fkey FOREIGN KEY (location_id) REFERENCES location (location_id) ON DELETE CASCADE
);

CREATE TABLE student
(
    student_id   SERIAL PRIMARY KEY,
    account_id   INT          NOT NULL,
    university   VARCHAR(100) NOT NULL,
    index_number VARCHAR(20)  NOT NULL,
    CONSTRAINT student_account_id_fkey FOREIGN KEY (account_id) REFERENCES account (account_id) ON DELETE CASCADE
);

CREATE TABLE student_ticket
(
    student_ticket_id SERIAL PRIMARY KEY,
    ticket_id         INT NOT NULL,
    discount          DOUBLE PRECISION,
    CONSTRAINT student_ticket_ticket_id_fkey FOREIGN KEY (ticket_id) REFERENCES ticket (ticket_id) ON DELETE CASCADE
);

CREATE TABLE child_ticket
(
    child_ticket_id SERIAL PRIMARY KEY,
    ticket_id       INT         NOT NULL,
    discount        DOUBLE PRECISION,
    embg            VARCHAR(13) NOT NULL,
    parent_embg     VARCHAR(13) NOT NULL,
    CONSTRAINT child_ticket_ticket_id_fkey FOREIGN KEY (ticket_id) REFERENCES ticket (ticket_id) ON DELETE CASCADE
);