Креирање на базата на податоци

DDL скрипти

User, Admin, Regular_User

CREATE TABLE "User" (
    user_id BIGSERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    registration_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT user_email_check CHECK (
        email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
    ),
    CONSTRAINT user_password_check CHECK (
        length(password) >= 8 AND password ~ '[A-Z]' AND password ~ '[a-z]' AND password ~ '[0-9]' AND password ~ '[^a-zA-Z0-9]'
    )
);

CREATE TABLE "Admin" (
    user_id BIGINT PRIMARY KEY,

    CONSTRAINT fk_admin_user FOREIGN KEY (user_id) REFERENCES "User" (user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE "Regular_User" (
    user_id BIGINT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    date_of_birth DATE NOT NULL,

    CONSTRAINT fk_regular_user FOREIGN KEY (user_id) REFERENCES "User" (user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Performer

CREATE TABLE "Performer" (
    performer_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

Event_Type, Event

CREATE TABLE "Event_Type" (
    type_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE "Event" (
    event_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type_id BIGINT NOT NULL,
    description TEXT NOT NULL,
    min_age INTEGER NOT NULL,
    created_by BIGINT NOT NULL,

    CONSTRAINT fk_event_type FOREIGN KEY (type_id) REFERENCES "Event_Type" (type_id)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT,
    CONSTRAINT fk_event_admin FOREIGN KEY (created_by) REFERENCES "Admin" (user_id)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
);

Venue, Section, Seat

CREATE TABLE "Venue" (
    venue_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    number_of_sections INTEGER NOT NULL,
    number_of_seats INTEGER NOT NULL,
    address_street VARCHAR(255) NOT NULL,
    address_city VARCHAR(255) NOT NULL,
    address_country VARCHAR(255) NOT NULL
);

CREATE TABLE "Section" (
    section_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    number_of_seats INTEGER NOT NULL,
    venue_id BIGINT NOT NULL,

    CONSTRAINT uq_section_venue_name UNIQUE (venue_id, name),
    CONSTRAINT fk_section_venue FOREIGN KEY (venue_id) REFERENCES "Venue" (venue_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE "Seat" (
    seat_id BIGSERIAL PRIMARY KEY,
    seat_number INTEGER NOT NULL,
    row_number INTEGER NOT NULL,
    section_id BIGINT NOT NULL,

    CONSTRAINT uq_seat_section_number UNIQUE (section_id, seat_number),
    CONSTRAINT fk_seat_section FOREIGN KEY (section_id) REFERENCES "Section" (section_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Event_Happening, Event_Period

CREATE TABLE "Event_Happening" (
    event_happening_id BIGSERIAL PRIMARY KEY,
    event_id BIGINT NOT NULL,
    event_time TIMESTAMP NOT NULL,
    venue_id BIGINT NOT NULL,
    duration_minutes INTEGER NOT NULL,
    organizers TEXT NOT NULL,
    sponsors TEXT,

    CONSTRAINT uq_happening_time_venue UNIQUE (event_time, venue_id),
    CONSTRAINT fk_happening_event FOREIGN KEY (event_id) REFERENCES "Event" (event_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_happening_venue FOREIGN KEY (venue_id) REFERENCES "Venue" (venue_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE "Event_Period" (
    period_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    price_discount_percent INTEGER NOT NULL,
    event_happening_id BIGINT NOT NULL,

    CONSTRAINT uq_period_happening_name UNIQUE (event_happening_id, name),
    CONSTRAINT event_period_dates_check CHECK (start_date <= end_date),
    CONSTRAINT fk_period_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Event_Happening_Performer

CREATE TABLE "Event_Happening_Performer" (
    event_happening_id BIGINT NOT NULL,
    performer_id BIGINT NOT NULL,

    CONSTRAINT pk_ehp PRIMARY KEY (event_happening_id, performer_id),
    CONSTRAINT fk_ehp_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_ehp_performer FOREIGN KEY (performer_id) REFERENCES "Performer" (performer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Event_Happening_Rating

CREATE TABLE "Event_Happening_Rating" (
    rating_id BIGSERIAL PRIMARY KEY,
    rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 10),
    comment TEXT,
    timestamp TIMESTAMP NOT NULL,
    event_happening_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL DEFAULT 0,

    CONSTRAINT uq_rating_happening_user UNIQUE (event_happening_id, user_id),
    CONSTRAINT fk_rating_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_rating_regular_user FOREIGN KEY (user_id) REFERENCES "Regular_User" (user_id)
        ON DELETE SET DEFAULT
        ON UPDATE CASCADE
);

Ticket

CREATE TABLE "Ticket" (
    ticket_id BIGSERIAL PRIMARY KEY,
    base_price DECIMAL(10,2) NOT NULL,
    is_available BOOLEAN NOT NULL DEFAULT TRUE,
    event_happening_id BIGINT NOT NULL,
    seat_id BIGINT NOT NULL,

    CONSTRAINT uq_ticket_happening_seat UNIQUE (event_happening_id, seat_id),
    CONSTRAINT fk_ticket_seat FOREIGN KEY (seat_id) REFERENCES "Seat" (seat_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_ticket_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Ticket_Order, Ticket_Order_Item

CREATE TABLE "Ticket_Order" (
    order_id BIGSERIAL PRIMARY KEY,
    order_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    order_amount DECIMAL(10,2) NOT NULL DEFAULT 0.0,
    user_id BIGINT NOT NULL,

    CONSTRAINT fk_order_regular_user FOREIGN KEY (user_id) REFERENCES "Regular_User" (user_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE "Ticket_Order_Item" (
    order_item_id BIGSERIAL PRIMARY KEY,
    qr_code VARCHAR(255),
    item_price DECIMAL(10,2) NOT NULL,
    order_id BIGINT NOT NULL,
    ticket_id BIGINT NOT NULL,

    CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES "Ticket_Order" (order_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_item_ticket FOREIGN KEY (ticket_id) REFERENCES "Ticket" (ticket_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Ticket_Refund, Ticket_Refund_Item

CREATE TABLE "Ticket_Refund" (
    refund_id BIGSERIAL PRIMARY KEY,
    refund_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    refund_amount DECIMAL(10,2) NOT NULL DEFAULT 0.0,
    reasons TEXT,
    order_id BIGINT NOT NULL,

    CONSTRAINT fk_refund_order FOREIGN KEY (order_id) REFERENCES "Ticket_Order" (order_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE "Ticket_Refund_Item" (
    refund_item_id BIGSERIAL PRIMARY KEY,
    item_price DECIMAL(10,2) NOT NULL,
    refund_id BIGINT NOT NULL,
    order_item_id BIGINT NOT NULL UNIQUE,

    CONSTRAINT fk_ri_refund FOREIGN KEY (refund_id) REFERENCES "Ticket_Refund" (refund_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_ri_order_item FOREIGN KEY (order_item_id) REFERENCES "Ticket_Order_Item" (order_item_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

DML скрипти - Погледи (Views)

Venue_Layout

Овој поглед ја прикажува деталната физичка структура на секој објект (сала), поврзувајќи ги поединечните седишта со нивните сектори и самите локации. Патеката на релациите е поставена линеарно, овозможувајќи брза проверка на точната позиција на седиштето преку неговиот ред и број.

CREATE OR REPLACE VIEW "Venue_Layout" AS
SELECT v.venue_id,
       v.name AS venue_name,
       s.section_id,
       s.name AS section_name,
       st.seat_id,
       st.row_number,
       st.seat_number
FROM "Venue" v
JOIN "Section" s ON v.venue_id = s.venue_id
JOIN "Seat" st ON s.section_id = st.section_id;

User_Tickets

Овој поглед дава детален хронолошки преглед на сите купени поединечни билети по корисник, вклучувајќи ја точната платена цена, QR-кодот за влез и терминот на настанот. Преку релацијата со ставките за рефундација, погледот нуди и инстантна информација за тоа кои карти се откажани и кога се вратени парите.

CREATE OR REPLACE VIEW "User_Tickets" AS
SELECT u.user_id,
       u.username,
       toi.order_item_id,
       t.ticket_id,
       e.event_id,
       e.name AS event_name,
       eh.event_time,
       toi.qr_code,
       toi.item_price AS price_paid,
       tri.refund_item_id,
       tr.refund_time
FROM "User" u
JOIN "Regular_User" ru ON u.user_id = ru.user_id
JOIN "Ticket_Order" o ON ru.user_id = o.user_id
JOIN "Ticket_Order_Item" toi ON o.order_id = toi.order_id
JOIN "Ticket" t ON toi.ticket_id = t.ticket_id
JOIN "Event_Happening" eh ON t.event_happening_id = eh.event_happening_id
JOIN "Event" e ON eh.event_id = e.event_id
LEFT JOIN "Ticket_Refund_Item" tri ON toi.order_item_id = tri.order_item_id
LEFT JOIN "Ticket_Refund" tr ON tri.refund_id = tr.refund_id;

Future_Events

Овој поглед служи за динамично генерирање на репертоарот, прикажувајќи ги исклучиво претстојните настани преку филтрирање на изминатите термини во однос на моменталното време на системот. Дополнително, тој ја прикажува комплетната географска адреса и локација на објектот каде ќе се одржи настанот.

CREATE OR REPLACE VIEW "Future_Events" AS
SELECT
    e.event_id,
    e.name AS event_name,
    eh.event_happening_id,
    eh.event_time,
    v.venue_id,
    v.name AS venue_name,
    v.address_street AS street,
    v.address_city AS city,
    v.address_country AS country
FROM "Event" e
JOIN "Event_Happening" eh ON e.event_id = eh.event_id
JOIN "Venue" v ON eh.venue_id = v.venue_id
WHERE eh.event_time > CURRENT_TIMESTAMP;

Available_Tickets

Овој поглед овозможува моментален увид во инвентарот на достапни слободни седишта за активните настани. Во него е имплементирана комплексна логика која ја калкулира крајната продажна цена во реално време, земајќи го предвид актуелниот процент на попуст за тековниот временски период во кој се наоѓа купувачот.

CREATE OR REPLACE VIEW "Available_Tickets" AS
SELECT
    t.ticket_id,
    ROUND(
        t.base_price * (1 - COALESCE(ep.price_discount_percent, 0) / 100.0),
        2
    ) AS price,
    e.event_id,
    e.name AS event_name,
    eh.event_happening_id,
    eh.event_time,
    v.name AS venue_name,
    s.name AS section_name,
    st.row_number,
    st.seat_number
FROM "Ticket" t
JOIN "Event_Happening" eh ON t.event_happening_id = eh.event_happening_id
JOIN "Event" e ON eh.event_id = e.event_id
JOIN "Venue" v ON eh.venue_id = v.venue_id
JOIN "Seat" st ON t.seat_id = st.seat_id
JOIN "Section" s ON st.section_id = s.section_id
LEFT JOIN "Event_Period" ep ON eh.event_happening_id = ep.event_happening_id
                           AND CURRENT_DATE BETWEEN ep.start_date AND ep.end_date
WHERE t.is_available = TRUE;

Event_Overall_Ratings

Овој материјализиран поглед врши брза статистичка анализа на задоволството на публиката преку пресметување на просечната оцена за секој настан поединечно. Со оглед на тоа што користи агрегациски функции (COUNT и AVG), тој е зачуван како материјализиран поглед со цел да се избегне постојано пресметување и да се зачуваат перформансите на базата.

CREATE MATERIALIZED VIEW "Event_Overall_Ratings" AS
SELECT
    e.event_id,
    e.name AS event_name,
    eh.event_happening_id,
    eh.event_time,
    COUNT(ehr.rating_id) AS total_reviews,
    ROUND(AVG(ehr.rating), 2) AS average_rating
FROM "Event" e
JOIN "Event_Happening" eh ON e.event_id = eh.event_id
JOIN "Event_Happening_Rating" ehr ON eh.event_happening_id = ehr.event_happening_id
GROUP BY e.event_id, e.name, eh.event_happening_id, eh.event_time;

User_Order_History

Овој материјализиран поглед генерира финансиски профил и историја на нарачки за секој корисник, сумирајќи ги направените трошоци на ниво на главна нарачка. Тој нуди брз увид во точниот број на купени ставки, бројот на рефундирани билети како и вкупната сума на вратени пари по нарачка без оптоварување на трансакциските табели.

CREATE MATERIALIZED VIEW "User_Order_History" AS
SELECT u.user_id,
       u.username,
       o.order_id,
       o.order_time,
       o.order_amount,
       COALESCE(items.total_items_ordered, 0) AS total_items_ordered,
       COALESCE(items.total_items_refunded, 0) AS total_items_refunded,
       COALESCE(items.total_amount_refunded, 0.00) AS total_amount_refunded
FROM "User" u
JOIN "Regular_User" ru ON u.user_id = ru.user_id
JOIN "Ticket_Order" o ON ru.user_id = o.user_id
LEFT JOIN (
    SELECT
        toi.order_id,
        COUNT(toi.order_item_id) AS total_items_ordered,
        COUNT(tri.refund_item_id) AS total_items_refunded,
        SUM(COALESCE(tri.item_price, 0.00)) AS total_amount_refunded
    FROM "Ticket_Order_Item" toi
    LEFT JOIN "Ticket_Refund_Item" tri ON toi.order_item_id = tri.order_item_id
    GROUP BY toi.order_id
) items ON o.order_id = items.order_id;

Venue_Occupancy_Report

Овој комплексен аналитички поглед ја мери успешноста на продажбата преку споредба на бројот на продадени карти со максималниот капацитет на седишта во салата. Крајниот резултат дава прецизен процент на пополнетост за секој термин на настан, што е клучен бизнис индикатор за менаџерите и организаторите.

CREATE MATERIALIZED VIEW "Venue_Occupancy_Report" AS
SELECT e.event_id,
       e.name AS event_name,
       eh.event_happening_id,
       eh.event_time,
       v.name AS venue_name,
       v.number_of_seats AS total_venue_capacity,
       COALESCE(stats.sold_count, 0) AS tickets_sold,
       ROUND((COALESCE(stats.sold_count, 0)::NUMERIC / v.number_of_seats::NUMERIC) * 100, 2) AS occupancy_percentage
FROM "Event" e
JOIN "Event_Happening" eh ON e.event_id = eh.event_id
JOIN "Venue" v ON eh.venue_id = v.venue_id
LEFT JOIN (
    SELECT t.event_happening_id, COUNT(toi.order_item_id) AS sold_count
    FROM "Ticket_Order_Item" toi
    JOIN "Ticket" t ON toi.ticket_id = t.ticket_id
    GROUP BY t.event_happening_id
) stats ON eh.event_happening_id = stats.event_happening_id
GROUP BY e.event_id, e.name, eh.event_happening_id, eh.event_time, v.name, v.number_of_seats, stats.sold_count;