Changes between Version 1 and Version 2 of DatabaseCreation

Timestamp:

04/21/26 20:44:57 (11 days ago)

Author:

231027

Comment:

--

DatabaseCreation

@@ -1 +1 @@
 = Креирање на базата
 
+
 == DDL
 
+```sql
+
+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)