RelationalDesign: schema_creation.sql

DROP SCHEMA IF EXISTS project CASCADE;
CREATE SCHEMA project;
SET search_path TO project;

-- =====================
-- USER
-- =====================
CREATE TABLE "user" (
    user_id        SERIAL PRIMARY KEY,
    first_name     VARCHAR(50) NOT NULL,
    last_name      VARCHAR(50) NOT NULL,
    email          VARCHAR(120) NOT NULL UNIQUE,
    phone_number   VARCHAR(30),
    gender         VARCHAR(20),
    birthday       DATE
);

-- =====================
-- WEDDING
-- =====================
CREATE TABLE wedding (
    wedding_id     SERIAL PRIMARY KEY,
    "date"         DATE NOT NULL,
    budget         NUMERIC(12,2),
    notes          TEXT,
    user_id        INTEGER NOT NULL,
    CONSTRAINT fk_wedding_user
        FOREIGN KEY (user_id)
        REFERENCES "user"(user_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

-- =====================
-- CHURCH & PRIEST
-- =====================
CREATE TABLE church (
    church_id      SERIAL PRIMARY KEY,
    name           VARCHAR(120) NOT NULL,
    location       VARCHAR(150) NOT NULL,
    contact        VARCHAR(120) NOT NULL,
    wedding_id     INTEGER UNIQUE,
    CONSTRAINT fk_church_wedding
        FOREIGN KEY (wedding_id)
        REFERENCES wedding(wedding_id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
);

CREATE TABLE priest (
    priest_id      SERIAL PRIMARY KEY,
    name           VARCHAR(120) NOT NULL,
    contact        VARCHAR(120) NOT NULL,
    church_id      INTEGER NOT NULL,
    CONSTRAINT fk_priest_church
        FOREIGN KEY (church_id)
        REFERENCES church(church_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

-- =====================
-- EVENT
-- =====================
CREATE TABLE event (
    event_id       SERIAL PRIMARY KEY,
    event_type     VARCHAR(60) NOT NULL,
    "date"         DATE NOT NULL,
    start_time     TIME NOT NULL,
    end_time       TIME NOT NULL,
    status         VARCHAR(30) NOT NULL,
    wedding_id     INTEGER NOT NULL,
    CONSTRAINT fk_event_wedding
        FOREIGN KEY (wedding_id)
        REFERENCES wedding(wedding_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT chk_event_time CHECK (end_time > start_time)
);

-- =====================
-- GUEST
-- =====================
CREATE TABLE guest (
    guest_id       SERIAL PRIMARY KEY,
    first_name     VARCHAR(50) NOT NULL,
    last_name      VARCHAR(50) NOT NULL,
    email          VARCHAR(120),
    wedding_id     INTEGER NOT NULL,
    CONSTRAINT fk_guest_wedding
        FOREIGN KEY (wedding_id)
        REFERENCES wedding(wedding_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

-- =====================
-- RSVP
-- =====================
CREATE TABLE event_rsvp (
    response_id    SERIAL PRIMARY KEY,
    status         VARCHAR(30) NOT NULL,
    response_date  DATE NOT NULL,
    guest_id       INTEGER NOT NULL,
    event_id       INTEGER NOT NULL,
    CONSTRAINT fk_rsvp_guest
        FOREIGN KEY (guest_id) REFERENCES guest(guest_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_rsvp_event
        FOREIGN KEY (event_id) REFERENCES event(event_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT uq_rsvp UNIQUE (guest_id, event_id)
);

-- =====================
-- ATTENDANCE
-- =====================
CREATE TABLE attendance (
    attendance_id  SERIAL PRIMARY KEY,
    status         VARCHAR(30) NOT NULL,
    table_number   INTEGER,
    role           VARCHAR(40) NOT NULL,
    guest_id       INTEGER NOT NULL,
    event_id       INTEGER NOT NULL,
    CONSTRAINT fk_att_guest
        FOREIGN KEY (guest_id) REFERENCES guest(guest_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_att_event
        FOREIGN KEY (event_id) REFERENCES event(event_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT uq_attendance UNIQUE (guest_id, event_id),
    CONSTRAINT chk_table_number CHECK (table_number IS NULL OR table_number > 0)
);

-- =====================
-- VENUE
-- =====================
CREATE TABLE venue_type (
    type_id        SERIAL PRIMARY KEY,
    type_name      VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE venue (
    venue_id        SERIAL PRIMARY KEY,
    name            VARCHAR(120) NOT NULL,
    location        VARCHAR(150) NOT NULL,
    city            VARCHAR(80) NOT NULL,
    address         VARCHAR(150) NOT NULL,
    capacity        INTEGER NOT NULL CHECK (capacity > 0),
    menu            TEXT,
    phone_number    VARCHAR(30),
    price_per_guest NUMERIC(10,2) NOT NULL CHECK (price_per_guest >= 0),
    type_id         INTEGER NOT NULL,
    CONSTRAINT fk_venue_type
        FOREIGN KEY (type_id)
        REFERENCES venue_type(type_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

CREATE TABLE venue_booking (
    booking_id     SERIAL PRIMARY KEY,
    "date"         DATE NOT NULL,
    start_time     TIME NOT NULL,
    end_time       TIME NOT NULL,
    status         VARCHAR(30) NOT NULL,
    price          NUMERIC(12,2) NOT NULL,
    venue_id       INTEGER NOT NULL,
    wedding_id     INTEGER NOT NULL,
    CONSTRAINT fk_vb_venue
        FOREIGN KEY (venue_id) REFERENCES venue(venue_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_vb_wedding
        FOREIGN KEY (wedding_id) REFERENCES wedding(wedding_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT chk_vb_time CHECK (end_time > start_time)
);

-- =====================
-- PHOTOGRAPHER
-- =====================
CREATE TABLE photographer (
    photographer_id SERIAL PRIMARY KEY,
    name            VARCHAR(120) NOT NULL,
    email           VARCHAR(120) NOT NULL UNIQUE,
    phone_number    VARCHAR(30) NOT NULL,
    price_per_hour  NUMERIC(10,2) NOT NULL CHECK (price_per_hour >= 0)
);

CREATE TABLE photographer_booking (
    booking_id      SERIAL PRIMARY KEY,
    "date"          DATE NOT NULL,
    start_time      TIME NOT NULL,
    end_time        TIME NOT NULL,
    status          VARCHAR(30) NOT NULL,
    photographer_id INTEGER NOT NULL,
    wedding_id      INTEGER NOT NULL,
    CONSTRAINT fk_pb_photographer
        FOREIGN KEY (photographer_id)
        REFERENCES photographer(photographer_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_pb_wedding
        FOREIGN KEY (wedding_id)
        REFERENCES wedding(wedding_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT chk_pb_time CHECK (end_time > start_time)
);

-- =====================
-- BAND
-- =====================
CREATE TABLE band (
    band_id        SERIAL PRIMARY KEY,
    band_name      VARCHAR(120) NOT NULL,
    genre          VARCHAR(60) NOT NULL,
    equipment      TEXT,
    phone_number   VARCHAR(30) NOT NULL,
    price_per_hour NUMERIC(10,2) NOT NULL CHECK (price_per_hour >= 0)
);

CREATE TABLE band_booking (
    booking_id     SERIAL PRIMARY KEY,
    "date"         DATE NOT NULL,
    start_time     TIME NOT NULL,
    end_time       TIME NOT NULL,
    status         VARCHAR(30) NOT NULL,
    band_id        INTEGER NOT NULL,
    wedding_id     INTEGER NOT NULL,
    CONSTRAINT fk_bb_band
        FOREIGN KEY (band_id) REFERENCES band(band_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_bb_wedding
        FOREIGN KEY (wedding_id) REFERENCES wedding(wedding_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT chk_bb_time CHECK (end_time > start_time)
);

-- =====================
-- REGISTRAR
-- =====================
CREATE TABLE registrar (
    registrar_id   SERIAL PRIMARY KEY,
    name           VARCHAR(120) NOT NULL,
    contact        VARCHAR(100),
    location       VARCHAR(150),
    working_hours  VARCHAR(100)
);

CREATE TABLE registrar_booking (
    booking_id     SERIAL PRIMARY KEY,
    "date"         DATE NOT NULL,
    start_time     TIME NOT NULL,
    end_time       TIME NOT NULL,
    status         VARCHAR(30) NOT NULL,
    registrar_id   INTEGER NOT NULL,
    wedding_id     INTEGER NOT NULL,
    CONSTRAINT fk_rb_registrar
        FOREIGN KEY (registrar_id)
        REFERENCES registrar(registrar_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_rb_wedding
        FOREIGN KEY (wedding_id)
        REFERENCES wedding(wedding_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT chk_rb_time CHECK (end_time > start_time)
);

-- =====================
-- INDEXES
-- =====================
CREATE INDEX idx_wedding_user ON wedding(user_id);
CREATE INDEX idx_event_wedding ON event(wedding_id);
CREATE INDEX idx_guest_wedding ON guest(wedding_id);
CREATE INDEX idx_vb_wedding ON venue_booking(wedding_id);
CREATE INDEX idx_vb_venue ON venue_booking(venue_id);
CREATE INDEX idx_pb_wedding ON photographer_booking(wedding_id);
CREATE INDEX idx_bb_wedding ON band_booking(wedding_id);
CREATE INDEX idx_rb_wedding ON registrar_booking(wedding_id);