RelationalDesign: schema_creation.sql

BEGIN;

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

CREATE TABLE students (
    student_index integer PRIMARY KEY,
    first_name text NOT NULL CHECK (length(trim(first_name)) > 0),
    last_name text NOT NULL CHECK (length(trim(last_name)) > 0),
    faculty text NOT NULL CHECK (length(trim(faculty)) > 0),
    field_of_studies text NOT NULL CHECK (length(trim(field_of_studies)) > 0),
    year_of_studies integer NOT NULL CHECK (year_of_studies >= 0),
    birthday date NOT NULL CHECK (birthday < CURRENT_DATE),
    email text NOT NULL UNIQUE CHECK (email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$'),
    phone_number text NOT NULL CHECK (length(trim(phone_number)) >= 6)
);

CREATE TABLE members (
    student_index integer PRIMARY KEY REFERENCES students(student_index)
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE observers (
    student_index integer PRIMARY KEY REFERENCES members(student_index)
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE full_members (
    student_index integer PRIMARY KEY REFERENCES members(student_index)
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE young_members (
    student_index integer PRIMARY KEY REFERENCES members(student_index)
        ON UPDATE CASCADE ON DELETE CASCADE,
    mentor_index integer NOT NULL REFERENCES full_members(student_index)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CHECK (student_index <> mentor_index)
);

CREATE TABLE alumni (
    student_index integer PRIMARY KEY REFERENCES members(student_index)
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE membership_stages (
    ms_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    ms_name text NOT NULL UNIQUE CHECK (length(trim(ms_name)) > 0)
);

CREATE TABLE has_stage (
    student_index integer NOT NULL REFERENCES members(student_index)
        ON UPDATE CASCADE ON DELETE CASCADE,
    ms_id integer NOT NULL REFERENCES membership_stages(ms_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    valid_from date NOT NULL,
    valid_to date,
    PRIMARY KEY (student_index, ms_id, valid_from),
    CHECK (valid_to IS NULL OR valid_to >= valid_from)
);

CREATE TABLE organizational_functions (
    of_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    of_title text NOT NULL UNIQUE CHECK (length(trim(of_title)) > 0)
);

CREATE TABLE board (
    of_id integer PRIMARY KEY REFERENCES organizational_functions(of_id)
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE non_board (
    of_id integer PRIMARY KEY REFERENCES organizational_functions(of_id)
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE function_mandates (
    student_index integer NOT NULL REFERENCES full_members(student_index)
        ON UPDATE CASCADE ON DELETE CASCADE,
    of_id integer NOT NULL REFERENCES organizational_functions(of_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    mandate_year integer NOT NULL CHECK (mandate_year >= 2002),
    PRIMARY KEY (student_index, of_id, mandate_year)
);

CREATE TABLE activity_types (
    at_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    at_name text NOT NULL UNIQUE CHECK (length(trim(at_name)) > 0),
    at_description text NOT NULL CHECK (length(trim(at_description)) > 0)
);

CREATE TABLE event_types (
    et_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    et_name text NOT NULL UNIQUE CHECK (length(trim(et_name)) > 0),
    et_description text NOT NULL CHECK (length(trim(et_description)) > 0)
);

CREATE TABLE event_session_types (
    est_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    est_name text NOT NULL UNIQUE CHECK (length(trim(est_name)) > 0),
    est_description text NOT NULL CHECK (length(trim(est_description)) > 0)
);

CREATE TABLE event_function_types (
    eft_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    eft_name text NOT NULL UNIQUE CHECK (length(trim(eft_name)) > 0)
);

CREATE TABLE locations (
    l_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    l_name text NOT NULL UNIQUE CHECK (length(trim(l_name)) > 0)
);

CREATE TABLE event_editions (
    ee_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    et_id integer NOT NULL REFERENCES event_types(et_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    ee_title text NOT NULL CHECK (length(trim(ee_title)) > 0),
    ee_start_time timestamp NOT NULL,
    ee_end_time timestamp NOT NULL,
    CHECK (ee_end_time >= ee_start_time)
);

CREATE TABLE event_edition_locations (
    ee_id integer NOT NULL REFERENCES event_editions(ee_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    l_id integer NOT NULL REFERENCES locations(l_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    PRIMARY KEY (ee_id, l_id)
);

CREATE TABLE event_functions (
    ee_id integer NOT NULL REFERENCES event_editions(ee_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    ef_no integer NOT NULL CHECK (ef_no > 0),
    eft_id integer NOT NULL REFERENCES event_function_types(eft_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    PRIMARY KEY (ee_id, ef_no)
);

CREATE TABLE event_sessions (
    ee_id integer NOT NULL REFERENCES event_editions(ee_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    es_no integer NOT NULL CHECK (es_no > 0),
    est_id integer NOT NULL REFERENCES event_session_types(est_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    l_id integer NOT NULL REFERENCES locations(l_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    es_start_time timestamp NOT NULL,
    es_title text NOT NULL CHECK (length(trim(es_title)) > 0),
    needed_no_volunteers integer NOT NULL CHECK (needed_no_volunteers >= 0),
    PRIMARY KEY (ee_id, es_no)
);

CREATE TABLE activity_sessions (
    as_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    at_id integer NOT NULL REFERENCES activity_types(at_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    l_id integer NOT NULL REFERENCES locations(l_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    as_start_time timestamp NOT NULL
);

CREATE TABLE related_to (
    as_id integer PRIMARY KEY REFERENCES activity_sessions(as_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    ee_id integer NOT NULL REFERENCES event_editions(ee_id)
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE applications (
    application_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    student_index integer NOT NULL REFERENCES students(student_index)
        ON UPDATE CASCADE ON DELETE CASCADE,
    ee_id integer NOT NULL REFERENCES event_editions(ee_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    ef_no integer,
    motivational_letter text NOT NULL CHECK (length(trim(motivational_letter)) > 0),
    application_type text NOT NULL CHECK (
        application_type IN (
            'event_function',
            'event_participation',
            'young_member',
            'mentor'
        )
    ),
    application_status text NOT NULL CHECK (
        application_status IN ('submitted', 'accepted', 'rejected', 'withdrawn')
    ),
    FOREIGN KEY (ee_id, ef_no)
        REFERENCES event_functions(ee_id, ef_no)
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE holds_event_function (
    student_index integer NOT NULL REFERENCES members(student_index)
        ON UPDATE CASCADE ON DELETE CASCADE,
    ee_id integer NOT NULL,
    ef_no integer NOT NULL,
    PRIMARY KEY (student_index, ee_id, ef_no),
    FOREIGN KEY (ee_id, ef_no)
        REFERENCES event_functions(ee_id, ef_no)
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE participation (
    student_index integer NOT NULL REFERENCES students(student_index)
        ON UPDATE CASCADE ON DELETE CASCADE,
    as_id integer NOT NULL REFERENCES activity_sessions(as_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    rsvp_status text NOT NULL DEFAULT 'no_response' CHECK (
        rsvp_status IN ('going', 'not_going', 'maybe', 'no_response')
    ),
    rsvp_at timestamp,
    attendance_status text NOT NULL DEFAULT 'not_checked' CHECK (
        attendance_status IN ('present', 'absent', 'excused', 'late', 'online', 'left_early', 'not_checked')
    ),
    PRIMARY KEY (student_index, as_id),
    CHECK (
        (rsvp_status = 'no_response' AND rsvp_at IS NULL)
        OR (rsvp_status <> 'no_response' AND rsvp_at IS NOT NULL)
    )
);

CREATE TABLE required_attendance (
    student_index integer NOT NULL REFERENCES members(student_index)
        ON UPDATE CASCADE ON DELETE CASCADE,
    as_id integer NOT NULL REFERENCES activity_sessions(as_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (student_index, as_id)
);

CREATE TABLE announces_absence (
    student_index integer NOT NULL,
    as_id integer NOT NULL,
    reason text NOT NULL CHECK (length(trim(reason)) > 0),
    announced_at timestamp NOT NULL,
    PRIMARY KEY (student_index, as_id),
    FOREIGN KEY (student_index, as_id)
        REFERENCES required_attendance(student_index, as_id)
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE responsible_for (
    student_index integer NOT NULL REFERENCES members(student_index)
        ON UPDATE CASCADE ON DELETE CASCADE,
    as_id integer NOT NULL REFERENCES activity_sessions(as_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (student_index, as_id)
);

CREATE TABLE volunteers (
    student_index integer NOT NULL REFERENCES members(student_index)
        ON UPDATE CASCADE ON DELETE CASCADE,
    ee_id integer NOT NULL,
    es_no integer NOT NULL,
    volunteer_role text NOT NULL CHECK (length(trim(volunteer_role)) > 0),
    PRIMARY KEY (student_index, ee_id, es_no),
    FOREIGN KEY (ee_id, es_no)
        REFERENCES event_sessions(ee_id, es_no)
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE companies (
    c_id integer PRIMARY KEY,
    c_name text NOT NULL CHECK (length(trim(c_name)) > 0),
    naframa_reference text UNIQUE
);

CREATE TABLE cooperation (
    c_id integer NOT NULL REFERENCES companies(c_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    ee_id integer NOT NULL REFERENCES event_editions(ee_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    cooperation_type text NOT NULL CHECK (
        cooperation_type IN ('sponsor', 'partner', 'participant', 'challenge_provider', 'supporter')
    ),
    PRIMARY KEY (c_id, ee_id)
);

COMMIT;