Relational Design

This page documents the relational design for BEST Skopje Hub, based on !ERModel_v09. The design uses partial transformation from the ER model into a PostgreSQL relational schema. Strong entities are mapped directly to relations, weak entities include the primary keys of their owner entities, specializations are mapped with one relation for the supertype and one relation for each subtype, and M:N relationships are mapped as separate relations.

Some attribute names were adapted to SQL-friendly snake_case names. For example, the ER attribute index is represented as student_index, and the EventEditions attributes title, start, and end are represented as event_title, event_start_time, and event_end_time.

Descriptive representation of the relational schema

students(
    student_index, first_name, last_name, faculty, field_of_studies,
    year_of_studies, birthday, email, phone_number,
    PK(student_index)
)

members(
    student_index,
    PK(student_index),
    FK(student_index) -> students(student_index)
)

observers(
    student_index,
    PK(student_index),
    FK(student_index) -> members(student_index)
)

young_members(
    student_index, mentor_index,
    PK(student_index),
    FK(student_index) -> members(student_index),
    FK(mentor_index) -> full_members(student_index)
)

full_members(
    student_index,
    PK(student_index),
    FK(student_index) -> members(student_index)
)

alumni(
    student_index,
    PK(student_index),
    FK(student_index) -> members(student_index)
)

membership_stages(
    ms_name,
    PK(ms_name)
)

has_stage(
    student_index, ms_name, valid_from, valid_to,
    PK(student_index, ms_name, valid_from),
    FK(student_index) -> members(student_index),
    FK(ms_name) -> membership_stages(ms_name)
)

organizational_functions(
    of_title,
    PK(of_title)
)

board(
    of_title,
    PK(of_title),
    FK(of_title) -> organizational_functions(of_title)
)

non_board(
    of_title,
    PK(of_title),
    FK(of_title) -> organizational_functions(of_title)
)

is_organizational_function(
    student_index, of_title, mandate,
    PK(student_index, of_title, mandate),
    FK(student_index) -> full_members(student_index),
    FK(of_title) -> organizational_functions(of_title)
)

activity_types(
    at_name, at_description,
    PK(at_name)
)

activity_sessions(
    at_name, as_start_time, as_location,
    PK(at_name, as_start_time, as_location),
    FK(at_name) -> activity_types(at_name)
)

related_to(
    at_name, as_start_time, as_location, et_name, event_title, event_start_time,
    PK(at_name, as_start_time, as_location),
    FK(at_name, as_start_time, as_location) -> activity_sessions(at_name, as_start_time, as_location),
    FK(et_name, event_title, event_start_time) -> event_editions(et_name, event_title, event_start_time)
)

event_types(
    et_name, et_description,
    PK(et_name)
)

event_editions(
    et_name, event_title, event_start_time, event_end_time,
    PK(et_name, event_title, event_start_time),
    FK(et_name) -> event_types(et_name)
)

event_edition_locations(
    et_name, event_title, event_start_time, location,
    PK(et_name, event_title, event_start_time, location),
    FK(et_name, event_title, event_start_time) -> event_editions(et_name, event_title, event_start_time)
)

event_session_types(
    est_name, est_description, needed_no_volunteers,
    PK(est_name)
)

event_sessions(
    et_name, event_title, event_start_time, est_name, es_start_time, location, es_title,
    PK(et_name, event_title, event_start_time, est_name, es_start_time, location),
    FK(et_name, event_title, event_start_time) -> event_editions(et_name, event_title, event_start_time),
    FK(est_name) -> event_session_types(est_name)
)

core_team_functions(
    et_name, event_title, event_start_time, function_name,
    PK(et_name, event_title, event_start_time, function_name),
    FK(et_name, event_title, event_start_time) -> event_editions(et_name, event_title, event_start_time)
)

applications(
    application_id, student_index, et_name, event_title, event_start_time,
    function_name, motivational_letter, application_type, application_status,
    PK(application_id),
    FK(student_index) -> students(student_index),
    FK(et_name, event_title, event_start_time) -> event_editions(et_name, event_title, event_start_time),
    FK(et_name, event_title, event_start_time, function_name) -> core_team_functions(et_name, event_title, event_start_time, function_name)
)

participation(
    student_index, at_name, as_start_time, as_location,
    rsvp_status, rsvp_at, attendance_status,
    PK(student_index, at_name, as_start_time, as_location),
    FK(student_index) -> students(student_index),
    FK(at_name, as_start_time, as_location) -> activity_sessions(at_name, as_start_time, as_location)
)

announces_absence(
    student_index, at_name, as_start_time, as_location, reason, announced_at,
    PK(student_index, at_name, as_start_time, as_location),
    FK(student_index) -> members(student_index),
    FK(at_name, as_start_time, as_location) -> activity_sessions(at_name, as_start_time, as_location)
)

responsible_for(
    student_index, at_name, as_start_time, as_location,
    PK(student_index, at_name, as_start_time, as_location),
    FK(student_index) -> members(student_index),
    FK(at_name, as_start_time, as_location) -> activity_sessions(at_name, as_start_time, as_location)
)

volunteers(
    student_index, et_name, event_title, event_start_time,
    est_name, es_start_time, location, volunteer_role,
    PK(student_index, et_name, event_title, event_start_time, est_name, es_start_time, location),
    FK(student_index) -> members(student_index),
    FK(et_name, event_title, event_start_time, est_name, es_start_time, location) -> event_sessions(et_name, event_title, event_start_time, est_name, es_start_time, location)
)

companies(
    c_id, c_name, naframa_reference,
    PK(c_id)
)

cooperation(
    c_id, et_name, event_title, event_start_time, cooperation_type,
    PK(c_id, et_name, event_title, event_start_time),
    FK(c_id) -> companies(c_id),
    FK(et_name, event_title, event_start_time) -> event_editions(et_name, event_title, event_start_time)
)

In the physical PostgreSQL schema, application_id is implemented as an automatically generated identity value. It is still listed as the primary key of applications, but it does not have to be manually inserted in the data loading script.

The applications relation is used only when there is an actual application process. Public attendance for events such as Job Fair, and open registration-style attendance for events such as beBESTie, is not represented as an application in this phase. For Hackathon and BEST Course in Summer participation applications, the motivational_letter attribute represents the most important motivation/answer kept from an external application form.

The specialization of students into members and then into observers, young members, full members, and alumni is represented through subtype tables whose primary keys are also foreign keys. young_members also contains mentor_index, which represents the mentorship relationship directly as a foreign key to full_members.

The weak entities from the ER model are represented using composite primary keys. activity_sessions includes the key of activity_types. event_editions includes the key of event_types. event_sessions includes the composite key of event_editions, the key of event_session_types, and its partial key attributes es_start_time and location. core_team_functions includes the composite key of event_editions and its partial key function_name.

The multivalued locations attribute of EventEditions is transformed into the separate relation event_edition_locations.

The relationships has_stage, participation, announces_absence, responsible_for, volunteers, cooperation, related_to, and is are represented as relations because they either have M:N cardinality, their own attributes, or are clearer without nullable foreign-key columns. The relationship related_to is represented separately because only some activity sessions are connected to event editions. The relationship applies_with is represented with the foreign key student_index in applications, because each application belongs to one student. The N:1 relationships for_function, for_participation, and mentorship are represented directly with foreign keys on the N-side relation.

DDL script for creating the database schema and objects

The DDL script recreates the official project schema and all database objects. It first drops the existing project schema if it exists, then creates the schema, tables, primary keys, foreign keys, and checks.

schema_creation.sql​

DML script for filling tables with data

The DML script recreates realistic sample data for all relations. It clears the current contents of the tables and inserts connected sample data for students, members, membership stages, activity sessions, event-related activity links, event editions, event sessions, applications, companies, volunteering, mentorship, attendance/RSVP, announced absences, and organizational functions.

data_load.sql​

Relational diagram

The relational diagram was exported from DBeaver after running schema_creation.sql in the assigned PostgreSQL database and opening the project schema diagram. The diagram uses crow-feet notation.

AI Use

AI was used during this phase as a consultation, drafting, and checking tool while preparing the relational design from the already completed !ERModel_v09. The AI-assisted parts were reviewed against the existing ER model and project requirements before being included.

Full AI usage documentation: RelationalDesignAIUsage.