RelationalDesign: schema_creation.sql

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

CREATE TABLE buildings (
    building_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR(128) NOT NULL,
    address VARCHAR(128) NOT NULL,

    CONSTRAINT pk_buildings
        PRIMARY KEY (building_id),

    CONSTRAINT uq_buildings_name_address
        UNIQUE (name, address)
);

CREATE TABLE rooms (
    room_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    building_id INTEGER NOT NULL,
    room_code VARCHAR(128) NOT NULL,
    capacity INTEGER NOT NULL,
    type VARCHAR(128) NOT NULL,

    CONSTRAINT pk_rooms
        PRIMARY KEY (room_id),

    CONSTRAINT fk_rooms_buildings
        FOREIGN KEY (building_id)
        REFERENCES buildings(building_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT uq_rooms_room_code
        UNIQUE (room_code),

    CONSTRAINT chk_rooms_capacity
        CHECK (capacity > 0),

    CONSTRAINT chk_rooms_type
        CHECK (type IN ('classroom', 'office', 'meeting_room', 'lab'))
);

CREATE TABLE equipment (
    equipment_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR(128) NOT NULL,
    stock_quantity INTEGER NOT NULL,

    CONSTRAINT pk_equipment
        PRIMARY KEY (equipment_id),

    CONSTRAINT uq_equipment_name
        UNIQUE (name),

    CONSTRAINT chk_equipment_stock_quantity
        CHECK (stock_quantity >= 0)
);

CREATE TABLE room_equipment (
    room_id INTEGER NOT NULL,
    equipment_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,

    CONSTRAINT pk_room_equipment
        PRIMARY KEY (room_id, equipment_id),

    CONSTRAINT fk_room_equipment_rooms
        FOREIGN KEY (room_id)
        REFERENCES rooms(room_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_room_equipment_equipment
        FOREIGN KEY (equipment_id)
        REFERENCES equipment(equipment_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT chk_room_equipment_quantity
        CHECK (quantity > 0)
);

CREATE TABLE users (
    user_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    username VARCHAR(128) NOT NULL,
    email VARCHAR(128) NOT NULL,
    full_name VARCHAR(128) NOT NULL,
    role VARCHAR(128) NOT NULL,

    CONSTRAINT pk_users
        PRIMARY KEY (user_id),

    CONSTRAINT uq_users_username
        UNIQUE (username),

    CONSTRAINT uq_users_email
        UNIQUE (email),

    CONSTRAINT chk_users_email
        CHECK (position('@' in email) > 1),

    CONSTRAINT chk_users_role
        CHECK (role IN ('regular', 'admin', 'approver'))
);

CREATE TABLE reservations (
    reservation_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    room_id INTEGER,
    user_id INTEGER NOT NULL,
    reservation_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    status VARCHAR(128) NOT NULL,

    CONSTRAINT pk_reservations
        PRIMARY KEY (reservation_id),

    CONSTRAINT fk_reservations_rooms
        FOREIGN KEY (room_id)
        REFERENCES rooms(room_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT fk_reservations_users
        FOREIGN KEY (user_id)
        REFERENCES users(user_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT chk_reservations_time
        CHECK (end_time > start_time),

    CONSTRAINT chk_reservations_status
        CHECK (status IN ('pending', 'approved', 'rejected', 'cancelled')),

    CONSTRAINT uq_reservations_room_exact_interval
        UNIQUE (room_id, reservation_date, start_time, end_time)
);

CREATE TABLE reservation_equipment (
    reservation_id INTEGER NOT NULL,
    equipment_id INTEGER NOT NULL,
    requested_quantity INTEGER NOT NULL,

    CONSTRAINT pk_reservation_equipment
        PRIMARY KEY (reservation_id, equipment_id),

    CONSTRAINT fk_reservation_equipment_reservations
        FOREIGN KEY (reservation_id)
        REFERENCES reservations(reservation_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_reservation_equipment_equipment
        FOREIGN KEY (equipment_id)
        REFERENCES equipment(equipment_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT chk_reservation_equipment_requested_quantity
        CHECK (requested_quantity > 0)
);

CREATE TABLE approvals (
    approval_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    reservation_id INTEGER NOT NULL,
    approver_id INTEGER NOT NULL,
    decision VARCHAR(128) NOT NULL,
    decision_time TIMESTAMP NOT NULL,
    note VARCHAR(255),

    CONSTRAINT pk_approvals
        PRIMARY KEY (approval_id),

    CONSTRAINT fk_approvals_reservations
        FOREIGN KEY (reservation_id)
        REFERENCES reservations(reservation_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_approvals_users
        FOREIGN KEY (approver_id)
        REFERENCES users(user_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT uq_approvals_reservation
        UNIQUE (reservation_id),

    CONSTRAINT chk_approvals_decision
        CHECK (decision IN ('approved', 'rejected'))
);