Фаза 2A – Генерирање DDL

Опис

Оваа фаза опфаќа генерирање на DDL за ЕР дијаграмот со соодветни ограничувања и дифолтни вредности.

Содржина на DDL скрипта

CREATE TABLE APP_USER (

id BIGSERIAL NOT NULL, first_name VARCHAR(20), last_name VARCHAR(20), email VARCHAR(50) NOT NULL UNIQUE, phone VARCHAR(20) NOT NULL, created_at DATE NOT NULL, password_hash VARCHAR(20) NOT NULL, PRIMARY KEY (id), CONSTRAINT CHK_email_format

CHECK (email ~ '^{[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),}

CONSTRAINT CHK_phone_number_format

CHECK (phone ~ '^{[0-9]{7,15}$')}

);

CREATE TABLE ROLES (

id BIGSERIAL NOT NULL, role VARCHAR(20) NOT NULL, PRIMARY KEY (id), CONSTRAINT CHK_role_title

CHECK (role IN ('ADMINISTRATOR', 'USER'))

);

CREATE TABLE USER_ROLES (

APP_USERid BIGINT NOT NULL, ROLESid BIGINT NOT NULL, PRIMARY KEY (APP_USERid, ROLESid), CONSTRAINT FK_user_roles_user

FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id) ON UPDATE CASCADE ON DELETE RESTRICT,

CONSTRAINT FK_user_roles_role

FOREIGN KEY (ROLESid) REFERENCES ROLES (id) ON UPDATE CASCADE ON DELETE RESTRICT

);

CREATE TABLE CATEGORIZATION (

id BIGSERIAL NOT NULL, name VARCHAR(20) NOT NULL, PRIMARY KEY (id), CONSTRAINT CHK_category_values

CHECK (name IN ('Concerts', 'Theatre', 'Cinema', 'Festivals', 'Exhibitions', 'Seminars'))

);

CREATE TABLE SUBCATEGORY (

id BIGSERIAL NOT NULL, name VARCHAR(20) NOT NULL, CATEGORIZATIONid BIGINT, PRIMARY KEY (id), CONSTRAINT FK_subcategory_categorization

FOREIGN KEY (CATEGORIZATIONid) REFERENCES CATEGORIZATION (id) ON UPDATE CASCADE ON DELETE SET NULL

);

CREATE TABLE USER_CATEGORY_SUBSCRIPTION (

APP_USERid BIGINT NOT NULL DEFAULT 1, CATEGORIZATIONid BIGINT NOT NULL DEFAULT 1, PRIMARY KEY (APP_USERid, CATEGORIZATIONid), CONSTRAINT FK_user_category_sub_user

FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id) ON UPDATE CASCADE ON DELETE SET DEFAULT,

CONSTRAINT FK_user_category_sub_categorization

FOREIGN KEY (CATEGORIZATIONid) REFERENCES CATEGORIZATION (id) ON UPDATE CASCADE ON DELETE SET DEFAULT

);

CREATE TABLE USER_SUBCATEGORY_SUBSCRIPTION (

APP_USERid BIGINT NOT NULL DEFAULT 1, SUBCATEGORYid BIGINT NOT NULL DEFAULT 1, PRIMARY KEY (APP_USERid, SUBCATEGORYid), CONSTRAINT FK_user_subcategory_sub_user

FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id) ON UPDATE CASCADE ON DELETE SET DEFAULT,

CONSTRAINT FK_user_subcategory_sub_subcategory

FOREIGN KEY (SUBCATEGORYid) REFERENCES SUBCATEGORY (id) ON UPDATE CASCADE ON DELETE SET DEFAULT

);

CREATE TABLE EVENT (

id BIGSERIAL NOT NULL, title VARCHAR(50) NOT NULL, description VARCHAR(255), start_date DATE NOT NULL, end_date DATE NOT NULL, CATEGORIZATIONid BIGINT, PRIMARY KEY (id), CONSTRAINT CHK_event_dates

CHECK (end_date >= start_date),

CONSTRAINT FK_event_categorization

FOREIGN KEY (CATEGORIZATIONid) REFERENCES CATEGORIZATION (id) ON UPDATE CASCADE ON DELETE SET NULL

);

CREATE TABLE EVENT_IMAGE (

id BIGSERIAL NOT NULL, image_url VARCHAR(255) NOT NULL, EVENTid BIGINT, PRIMARY KEY (id), CONSTRAINT FK_event_image_event

FOREIGN KEY (EVENTid) REFERENCES EVENT (id) ON UPDATE CASCADE ON DELETE CASCADE

);

CREATE TABLE EVENT_ROLE (

id BIGSERIAL NOT NULL, role VARCHAR(20) NOT NULL, PRIMARY KEY (id), CONSTRAINT CHK_event_role_values

CHECK (role IN ('EVENT_ADMIN', 'SALES_MANAGER', 'INFO_ADMIN', 'CONTENT_MANAGER'))

);

CREATE TABLE USER_EVENT (

APP_USERid BIGINT NOT NULL, EVENTid BIGINT NOT NULL, EVENT_ROLEid BIGINT NOT NULL, PRIMARY KEY (APP_USERid, EVENTid), CONSTRAINT FK_user_event_user

FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id) ON UPDATE CASCADE ON DELETE RESTRICT,

CONSTRAINT FK_user_event_event

FOREIGN KEY (EVENTid) REFERENCES EVENT (id) ON UPDATE CASCADE ON DELETE RESTRICT,

CONSTRAINT FK_user_event_role

FOREIGN KEY (EVENT_ROLEid) REFERENCES EVENT_ROLE (id) ON UPDATE CASCADE ON DELETE RESTRICT

);

CREATE TABLE VENUE (

id BIGSERIAL NOT NULL, name VARCHAR(20) NOT NULL, city VARCHAR(20), address VARCHAR(50) NOT NULL, PRIMARY KEY (id)

);

CREATE TABLE HALL (

id BIGSERIAL NOT NULL, name VARCHAR(20) NOT NULL, capacity INT NOT NULL, VENUEid BIGINT, PRIMARY KEY (id), CONSTRAINT CHK_capacity_positive_number

CHECK (capacity > 0),

CONSTRAINT FK_hall_venue

FOREIGN KEY (VENUEid) REFERENCES VENUE (id) ON UPDATE CASCADE ON DELETE RESTRICT

);

CREATE TABLE EVENT_HALL (

EVENTid BIGINT NOT NULL, HALLid BIGINT NOT NULL, allowed_access BOOLEAN NOT NULL, PRIMARY KEY (EVENTid, HALLid), CONSTRAINT FK_event_hall_event

FOREIGN KEY (EVENTid) REFERENCES EVENT (id) ON UPDATE CASCADE ON DELETE RESTRICT,

CONSTRAINT FK_event_hall_hall

FOREIGN KEY (HALLid) REFERENCES HALL (id) ON UPDATE CASCADE ON DELETE RESTRICT

);

CREATE TABLE TICKET_TYPE (

id BIGSERIAL NOT NULL, name VARCHAR(50) NOT NULL, quantity_available INT NOT NULL, PRIMARY KEY (id), CONSTRAINT CHK_ticket_quantity_positive_num

CHECK (quantity_available >= 0),

CONSTRAINT CHK_ticket_type_values

CHECK (name IN ('GENERAL_ADMISSION', 'STANDARD', 'PARTER', 'VIP'))

);

CREATE TABLE SEAT (

id BIGSERIAL NOT NULL, number INT NOT NULL, HALLid BIGINT, TICKET_TYPEid BIGINT, PRIMARY KEY (id), CONSTRAINT FK_seat_hall

FOREIGN KEY (HALLid) REFERENCES HALL (id) ON UPDATE CASCADE ON DELETE RESTRICT,

CONSTRAINT FK_seat_ticket_type

FOREIGN KEY (TICKET_TYPEid) REFERENCES TICKET_TYPE (id) ON UPDATE CASCADE ON DELETE SET NULL

);

CREATE TABLE EVENT_TICKET_TYPE (

EVENTid BIGINT NOT NULL, TICKET_TYPEid BIGINT NOT NULL, price INT NOT NULL, PRIMARY KEY (EVENTid, TICKET_TYPEid), CONSTRAINT CHK_ticket_price_positive_num

CHECK (price > 0),

CONSTRAINT FK_event_ticket_type_event

FOREIGN KEY (EVENTid) REFERENCES EVENT (id) ON UPDATE CASCADE ON DELETE RESTRICT,

CONSTRAINT FK_event_ticket_type_ticket_type

FOREIGN KEY (TICKET_TYPEid) REFERENCES TICKET_TYPE (id) ON UPDATE CASCADE ON DELETE RESTRICT

);

CREATE TABLE STATUS (

id BIGSERIAL NOT NULL, name VARCHAR(20) NOT NULL, description VARCHAR(255), PRIMARY KEY (id), CONSTRAINT CHK_order_status_values

CHECK (name IN ('CREATED', 'PAID', 'CANCELLED'))

);

CREATE TABLE PROMO_CODE (

id BIGSERIAL NOT NULL, code VARCHAR(20) NOT NULL UNIQUE, discount_percent INT NOT NULL, expiration_date DATE NOT NULL, APP_USERid BIGINT DEFAULT 1, PRIMARY KEY (id), CONSTRAINT CHK_discount_percent

CHECK (discount_percent > 0 AND discount_percent < 100),

CONSTRAINT FK_promo_code_user

FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id) ON UPDATE CASCADE ON DELETE SET DEFAULT

);

CREATE TABLE USER_ORDER (

id BIGSERIAL NOT NULL, order_date DATE NOT NULL, total_amount INT NOT NULL, APP_USERid BIGINT, STATUSid BIGINT, PROMO_CODEid BIGINT, PRIMARY KEY (id), CONSTRAINT CHK_order_amount_positive_num

CHECK (total_amount > 0),

CONSTRAINT FK_user_order_user

FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id) ON UPDATE CASCADE ON DELETE RESTRICT,

CONSTRAINT FK_user_order_status

FOREIGN KEY (STATUSid) REFERENCES STATUS (id) ON UPDATE CASCADE ON DELETE RESTRICT,

CONSTRAINT FK_user_order_promo_code

FOREIGN KEY (PROMO_CODEid) REFERENCES PROMO_CODE (id) ON UPDATE CASCADE ON DELETE SET NULL

);

CREATE TABLE TICKET (

id BIGSERIAL NOT NULL, code VARCHAR(50) NOT NULL UNIQUE, status VARCHAR(20) NOT NULL, TICKET_TYPEid BIGINT, USER_ORDERid BIGINT, SEATid BIGINT, APP_USERid BIGINT, EVENTid BIGINT, HALLid BIGINT, PRIMARY KEY (id), CONSTRAINT CHK_ticket_status_values

CHECK (status IN ('ACTIVE', 'USED', 'CANCELLED')),

CONSTRAINT FK_ticket_ticket_type

FOREIGN KEY (TICKET_TYPEid) REFERENCES TICKET_TYPE (id) ON UPDATE CASCADE ON DELETE RESTRICT,

CONSTRAINT FK_ticket_user_order

FOREIGN KEY (USER_ORDERid) REFERENCES USER_ORDER (id) ON UPDATE CASCADE ON DELETE RESTRICT,

CONSTRAINT FK_ticket_seat

FOREIGN KEY (SEATid) REFERENCES SEAT (id) ON UPDATE CASCADE ON DELETE RESTRICT,

CONSTRAINT FK_ticket_user

FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id) ON UPDATE CASCADE ON DELETE RESTRICT,

CONSTRAINT FK_ticket_event

FOREIGN KEY (EVENTid) REFERENCES EVENT (id) ON UPDATE CASCADE ON DELETE RESTRICT,

CONSTRAINT FK_ticket_hall

FOREIGN KEY (HALLid) REFERENCES HALL (id) ON UPDATE CASCADE ON DELETE RESTRICT

);

CREATE TABLE PAYMENT_METHOD (

id BIGSERIAL NOT NULL, name VARCHAR(20) NOT NULL, PRIMARY KEY (id), CONSTRAINT CHK_payment_method_name

CHECK (name IN ('CASH', 'CARD'))

);

CREATE TABLE PAYMENT (

id BIGSERIAL NOT NULL, amount INT NOT NULL, status VARCHAR(20) NOT NULL, payment_date DATE NOT NULL, USER_ORDERid BIGINT, PAYMENT_METHODid BIGINT, PRIMARY KEY (id), CONSTRAINT CHK_payment_status_values

CHECK (status IN ('PENDING', 'COMPLETED', 'FAILED')),

CONSTRAINT CHK_amount_positive_number

CHECK (amount > 0),

CONSTRAINT FK_payment_user_order

FOREIGN KEY (USER_ORDERid) REFERENCES USER_ORDER (id) ON UPDATE CASCADE ON DELETE RESTRICT,

CONSTRAINT FK_payment_payment_method

FOREIGN KEY (PAYMENT_METHODid) REFERENCES PAYMENT_METHOD (id) ON UPDATE CASCADE ON DELETE RESTRICT

);

CREATE TABLE REFUND (

id BIGSERIAL NOT NULL, amount INT NOT NULL, reason VARCHAR(255) NOT NULL, status VARCHAR(20) NOT NULL, PAYMENTid BIGINT, PRIMARY KEY (id), CONSTRAINT CHK_refund_amount_positive_num

CHECK (amount > 0),

CONSTRAINT CHK_refund_status_values

CHECK (status IN ('REQUESTED', 'APPROVED', 'REJECTED')),

CONSTRAINT FK_refund_payment

FOREIGN KEY (PAYMENTid) REFERENCES PAYMENT (id) ON UPDATE CASCADE ON DELETE RESTRICT

);

CREATE TABLE REVIEW (

id BIGSERIAL NOT NULL, rating INT NOT NULL, comment VARCHAR(255), APP_USERid BIGINT DEFAULT 1, EVENTid BIGINT, PRIMARY KEY (id), CONSTRAINT CHK_rating_range

CHECK (rating BETWEEN 1 AND 5),

CONSTRAINT FK_review_user

FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id) ON UPDATE CASCADE ON DELETE SET DEFAULT,

CONSTRAINT FK_review_event

FOREIGN KEY (EVENTid) REFERENCES EVENT (id) ON UPDATE CASCADE ON DELETE CASCADE

);

CREATE TABLE NOTIFICATION (

id BIGSERIAL NOT NULL, message VARCHAR(150) NOT NULL, created_at DATE NOT NULL, APP_USERid BIGINT DEFAULT 1, PRIMARY KEY (id), CONSTRAINT FK_notification_user

FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id) ON UPDATE CASCADE ON DELETE SET DEFAULT

);

CREATE TABLE WAITLIST (

id BIGSERIAL NOT NULL, created_at DATE NOT NULL, status VARCHAR(20) NOT NULL, EVENTid BIGINT, PRIMARY KEY (id), CONSTRAINT CHK_waitlist_status_values

CHECK (status IN ('PENDING', 'CONFIRMED', 'CANCELLED')),

CONSTRAINT FK_waitlist_event

FOREIGN KEY (EVENTid) REFERENCES EVENT (id) ON UPDATE CASCADE ON DELETE RESTRICT

);

CREATE TABLE USER_WAITLIST (

APP_USERid BIGINT NOT NULL, WAITLISTid BIGINT NOT NULL, PRIMARY KEY (APP_USERid, WAITLISTid), CONSTRAINT FK_user_waitlist_user

FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id) ON UPDATE CASCADE ON DELETE RESTRICT,

CONSTRAINT FK_user_waitlist_waitlist

FOREIGN KEY (WAITLISTid) REFERENCES WAITLIST (id) ON UPDATE CASCADE ON DELETE RESTRICT

);