RelationalSchema: routemk_create_and_delete.sql

DROP SCHEMA IF EXISTS routemk CASCADE;
CREATE SCHEMA routemk;
SET SEARCH_PATH TO routemk;

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 TYPE day_of_week AS ENUM ('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY');
CREATE TYPE trip_status AS ENUM ('NOT_STARTED', 'IN_PROGRESS', 'FINISHED');

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 NUMERIC(10, 2) 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)
);

CREATE TABLE location (
    location_id SERIAL PRIMARY KEY,
    latitude NUMERIC(9, 6) NOT NULL,
    longitude NUMERIC(9, 6) NOT NULL,
    name VARCHAR(100) NOT NULL
);

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 NUMERIC(10, 2) CHECK (price >= 0),
    seat VARCHAR(10),
    payment_id INT NOT NULL,
    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
);

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 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 account(account_id),
    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,
    transport_organizer_id INT NOT NULL,
    route_id INT NOT NULL,
    free_seats INT CHECK (free_seats >= 0),
    date DATE NOT NULL,
    status trip_status DEFAULT 'NOT_STARTED'::trip_status NOT NULL,
    CONSTRAINT trip_transport_organizer_id_fkey FOREIGN KEY (transport_organizer_id) REFERENCES account(account_id),
    CONSTRAINT trip_route_id_fkey FOREIGN KEY (route_id) REFERENCES route(route_id) ON DELETE CASCADE
);

CREATE TABLE vehicle (
    vehicle_id SERIAL PRIMARY KEY,
    model VARCHAR(30) NOT NULL,
    brand VARCHAR(30) NOT NULL,
    capacity VARCHAR(20) NOT NULL,
    year_manufactured VARCHAR(10)
);

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 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 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 day_of_week 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 NUMERIC(5, 2),
    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 NUMERIC(5, 2),
    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
);

CREATE TABLE ticket_relations (
    ticket_relation_id SERIAL PRIMARY KEY,
    parent_ticket_id INT NOT NULL,
    child_ticket_id INT NOT NULL,
    CONSTRAINT ticket_relations_parent_ticket_id_fkey FOREIGN KEY (parent_ticket_id) REFERENCES ticket(ticket_id) ON DELETE CASCADE,
    CONSTRAINT ticket_relations_child_ticket_id_fkey FOREIGN KEY (child_ticket_id) REFERENCES ticket(ticket_id) ON DELETE CASCADE
);