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

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)

Performer_Events

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

CREATE VIEW "Performer_Events" AS
SELECT p.performer_id,
       p.name AS performer_name,
       e.event_id,
       e.name AS event_name,
       eh.event_time
FROM "Performer" p
JOIN "Event_Happening_Performer" ehp ON p.performer_id = ehp.performer_id
JOIN "Event_Happening" eh ON ehp.event_happening_id = eh.event_happening_id
JOIN "Event" e ON eh.event_id = e.event_id;

Venue_Layout

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

CREATE 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.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 VIEW "User_Tickets" AS
SELECT u.user_id,
       u.username,
       t.ticket_id,
       e.event_id,
       e.name AS event_name,
       tp.purchase_id,
       tp.qr_code,
       tr.refund_id,
       tr.refund_time
FROM "User" u
JOIN "Ticket_Purchase" tp ON u.user_id = tp.user_id
JOIN "Ticket" t ON tp.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" tr ON tp.purchase_id = tr.purchase_id;

Event_User_Ratings

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

CREATE VIEW "Event_User_Ratings" AS
SELECT eh.event_happening_id,
       e.event_id,
       e.name AS event_name,
       u.user_id,
       u.username,
       ehr.rating_id,
       ehr.rating,
       ehr.comment
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
JOIN "User" u ON ehr.user_id = u.user_id;

Event_Overall_Ratings

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

CREATE 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,
    AVG(ehr.rating) 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;

Event_Financial_Summary

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

CREATE VIEW "Event_Financial_Summary" AS
SELECT 
    e.event_id,
    e.name AS event_name,
    eh.event_happening_id,
    eh.event_time,
    COUNT(tp.purchase_id) AS total_tickets_sold,
    
    -- total revenue
    SUM(tp.purchase_amount) - SUM(CASE WHEN tr.refund_amount IS NOT NULL THEN tr.refund_amount ELSE 0 END) AS net_revenue,
    
    -- refund taxes
    SUM(CASE WHEN tr.refund_id IS NOT NULL THEN tp.purchase_amount - tr.refund_amount ELSE 0 END) AS refund_tax_profit

FROM "Event" e
JOIN "Event_Happening" eh ON e.event_id = eh.event_id
JOIN "Ticket" t ON eh.event_happening_id = t.event_happening_id
JOIN "Ticket_Purchase" tp ON t.ticket_id = tp.ticket_id
LEFT JOIN "Ticket_Refund" tr ON tp.purchase_id = tr.purchase_id
GROUP BY e.event_id, e.name, eh.event_happening_id, eh.event_time;

Future_Events

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

CREATE 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_city AS city
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 VIEW "Available_Tickets" AS
SELECT
    t.ticket_id,
    t.ticket_type,
    t.base_price,
    e.event_id,
    e.name AS event_name,
    eh.event_happening_id,
    eh.event_time,
    s.name AS section_name,
    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 "Seat" st ON t.seat_id = st.seat_id
JOIN "Section" s ON st.section_id = s.section_id
WHERE t.is_available = TRUE;