Креирање на базата

DDL

`

CREATE TABLE "Event" (

event_id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, description TEXT NOT NULL, min_age INT NOT NULL

);

CREATE TABLE "Concert" (

event_id BIGINT PRIMARY KEY, concert_type VARCHAR(50) NOT NULL, setlist TEXT NOT NULL,

CONSTRAINT fk_concert_event FOREIGN KEY (event_id) REFERENCES "Event" (event_id)

ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE "Play" (

event_id BIGINT PRIMARY KEY, genre VARCHAR(50) NOT NULL, number_of_acts INT NOT NULL, language VARCHAR(50) NOT NULL, director VARCHAR(255) NOT NULL,

CONSTRAINT fk_play_event FOREIGN KEY (event_id) REFERENCES "Event" (event_id)

ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE "Performer" (

performer_id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, number_of_members INT NOT NULL, contact_email VARCHAR(255) NOT NULL UNIQUE, technical_requirements TEXT NOT NULL,

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

);

CREATE TABLE "Musical_Performer" (

performer_id BIGINT PRIMARY KEY, musician_type VARCHAR(50) NOT NULL, genre VARCHAR(50) NOT NULL, record_label VARCHAR(255) NOT NULL,

CONSTRAINT fk_musical_performer_performer FOREIGN KEY (performer_id) REFERENCES "Performer" (performer_id)

ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE "Acting_Performer" (

performer_id BIGINT PRIMARY KEY, role_type VARCHAR(50) NOT NULL, acting_style VARCHAR(100) NOT NULL, agency VARCHAR(255) NOT NULL,

CONSTRAINT fk_acting_performer_performer FOREIGN KEY (performer_id) REFERENCES "Performer" (performer_id)

ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE "Venue" (

venue_id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, number_of_sections INT NOT NULL, number_of_seats INT NOT NULL, address_street VARCHAR(255) NOT NULL, address_city VARCHAR(100) NOT NULL, address_country VARCHAR(100) NOT NULL

);

CREATE TABLE "Section" (

section_id BIGSERIAL PRIMARY KEY, venue_id BIGINT NOT NULL, name VARCHAR(255) NOT NULL, number_of_seats INT NOT NULL,

CONSTRAINT fk_section_venue FOREIGN KEY (venue_id) REFERENCES "Venue" (venue_id)

ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT uq_section_venue_name UNIQUE (venue_id, name)

);

CREATE TABLE "Seat" (

seat_id BIGSERIAL PRIMARY KEY, section_id BIGINT NOT NULL, seat_number INT NOT NULL,

CONSTRAINT fk_seat_section FOREIGN KEY (section_id) REFERENCES "Section" (section_id)

ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT uq_seat_section_number UNIQUE (section_id, seat_number)

);

CREATE TABLE "Organizer" (

organizer_id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, contact_email VARCHAR(255) NOT NULL UNIQUE,

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

);

CREATE TABLE "Sponsor" (

sponsor_id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, contact_email VARCHAR(255) NOT NULL UNIQUE, sponsor_type VARCHAR(50) NOT NULL,

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

);

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 INT NOT NULL,

CONSTRAINT fk_event_happening_event FOREIGN KEY (event_id) REFERENCES "Event" (event_id)

ON DELETE RESTRICT ON UPDATE CASCADE,

CONSTRAINT fk_event_happening_venue FOREIGN KEY (venue_id) REFERENCES "Venue" (venue_id)

ON DELETE RESTRICT ON UPDATE CASCADE,

CONSTRAINT uq_happening_time_venue UNIQUE (event_time, venue_id)

);

CREATE TABLE "Event_Period" (

period_id BIGSERIAL PRIMARY KEY, event_happening_id BIGINT NOT NULL, name VARCHAR(255) NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, price_change_percent INT NOT NULL, increase_decrease BOOLEAN NOT NULL,

CONSTRAINT fk_event_period_event_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)

ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT uq_period_happening_name UNIQUE (event_happening_id, name), CONSTRAINT event_period_dates_check CHECK (start_date <= end_date)

);

CREATE TABLE "Event_Happening_Performer" (

event_happening_id BIGINT NOT NULL, performer_id BIGINT NOT NULL,

CONSTRAINT pk_event_happening_performer PRIMARY KEY (event_happening_id, performer_id), CONSTRAINT fk_ehp_event_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,

CONSTRAINT uq_performer_at_time UNIQUE (performer_id, event_happening_id)

);

CREATE TABLE "Event_Happening_Organizer" (

event_happening_id BIGINT NOT NULL, organizer_id BIGINT NOT NULL,

CONSTRAINT event_happening_organizer PRIMARY KEY (event_happening_id, organizer_id), CONSTRAINT fk_eho_event_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)

ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT fk_eho_organizer FOREIGN KEY (organizer_id) REFERENCES "Organizer" (organizer_id)

ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE "Event_Happening_Sponsor" (

event_happening_id BIGINT NOT NULL, sponsor_id BIGINT NOT NULL,

CONSTRAINT event_happening_sponsor PRIMARY KEY (event_happening_id, sponsor_id), CONSTRAINT fk_ehs_event_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)

ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT fk_ehs_sponsor FOREIGN KEY (sponsor_id) REFERENCES "Sponsor" (sponsor_id)

ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE "User" (

user_id BIGSERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, date_of_birth DATE NOT NULL, contact_email VARCHAR(255) NOT NULL UNIQUE, password VARCHAR(100) NOT NULL, registration_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT user_date_of_birth_check CHECK (date_of_birth < CURRENT_DATE), CONSTRAINT user_contact_email_check CHECK (contact_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 "Event_Happening_Rating" (

rating_id BIGSERIAL PRIMARY KEY, rating INT NOT NULL CHECK (rating BETWEEN 1 AND 10), comment TEXT, event_happening_id BIGINT NOT NULL, user_id BIGINT NOT NULL DEFAULT 0,

CONSTRAINT fk_event_happening_rating_event_happening FOREIGN KEY (event_happening_id) REFERENCES "Event_Happening" (event_happening_id)

ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT fk_event_happening_rating_user FOREIGN KEY (user_id) REFERENCES "User" (user_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE,

CONSTRAINT uq_rating_happening_user UNIQUE (event_happening_id, user_id)

);

CREATE TABLE "Ticket" (

ticket_id BIGSERIAL PRIMARY KEY, ticket_type VARCHAR(50) NOT NULL, base_price FLOAT4 NOT NULL, is_available BOOLEAN NOT NULL DEFAULT TRUE, event_happening_id BIGINT NOT NULL, seat_id BIGINT NOT NULL,

CONSTRAINT fk_ticket_event_happening FOREIGN KEY (event_happening_id)

REFERENCES "Event_Happening" (event_happening_id) ON DELETE RESTRICT ON UPDATE CASCADE,

CONSTRAINT fk_ticket_seat FOREIGN KEY (seat_id)

REFERENCES "Seat" (seat_id) ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT uq_ticket_event_happening_seat UNIQUE (event_happening_id, seat_id)

);

CREATE TABLE "Ticket_Purchase" (

purchase_id BIGSERIAL PRIMARY KEY, ticket_id BIGINT NOT NULL, user_id BIGINT NOT NULL DEFAULT 0, qr_code VARCHAR(255) NOT NULL UNIQUE, purchase_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, purchase_amount FLOAT4 NOT NULL,

CONSTRAINT fk_purchase_ticket FOREIGN KEY (ticket_id) REFERENCES "Ticket" (ticket_id)

ON DELETE RESTRICT ON UPDATE CASCADE,

CONSTRAINT fk_purchase_user FOREIGN KEY (user_id) REFERENCES "User" (user_id)

ON DELETE SET DEFAULT ON UPDATE CASCADE

);

CREATE TABLE "Ticket_Refund" (

refund_id BIGSERIAL PRIMARY KEY, purchase_id BIGINT NOT NULL UNIQUE, refund_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, refund_amount FLOAT4 NOT NULL, reason TEXT,

CONSTRAINT fk_refund_purchase FOREIGN KEY (purchase_id) REFERENCES "Ticket_Purchase" (purchase_id)

ON DELETE CASCADE ON UPDATE CASCADE

);

`

Погледи (Views)