Relational Design AI Usage

Name of AI service/solution that was used

Name: OpenAI Codex / ChatGPT

URL: ​https://chatgpt.com/

Type of service/subscription: AI assistant used through an OpenAI account.

Final result

Diagram:

The official relational diagram was exported from DBeaver after the scripts were executed in the assigned PostgreSQL database.

Model description:

The final result of this phase is a relational design based on my existing conceptual ER model, ERModel_v09, for the BEST Skopje Hub system. The relational design uses partial transformation from the ER model into a PostgreSQL schema. Strong entities are transformed into relations, specializations are represented with supertype and subtype tables, weak entities are represented with composite primary keys that include the keys of their owner entities, M:N relationships are represented as separate relations, and the multivalued locations attribute of EventEditions is represented as a separate relation.

AI was used as a consultation, drafting, and checking tool during this phase. The main uses were: clarifying PostgreSQL syntax, asking for examples of constraints, checking whether relational transformations were consistent with the ER model, drafting realistic sample data based on project-specific rules and my own constraints, and reviewing the final files for inconsistencies. The modeling decisions and final corrections were reviewed against the project requirements and the ER model before being included in the submitted files.

Entire AI usage log

The following log summarizes the AI interactions used during Phase P2. The AI responses were used as support for understanding, drafting, checking, and improving the relational design, not as automatically accepted final decisions.

Prompt 1: What is SET search_path TO project?

AI response 1: The AI explained that SET search_path TO project sets the default schema for the current SQL session. This means that when table names are written without explicitly writing project.table_name, PostgreSQL first looks for them in the project schema.

Prompt 2: How do I make a check that verifies that a text attribute is not empty?

AI response 2: The AI suggested using CHECK (length(trim(of_title)) > 0). This prevents empty strings and strings made only of spaces from being accepted.

Prompt 3: How can I make sure an email has an email-like format?

AI response 3: The AI suggested using a PostgreSQL regular-expression check, for example a condition that checks for text before and after @, a domain part, and a top-level domain. This was used only as a practical input-format restriction, not as a complete guarantee that an email address exists.

Prompt 4: How can I make sure that mandates are written in the format xxxx/xxxx?

AI response 4: The AI suggested using CHECK (mandate ~ '^[0-9]{4}/[0-9]{4}$'), so values like 2025/2026 are accepted and differently formatted values are rejected.

Prompt 5: If an attribute references a key from another table, is there a point in writing NOT NULL?

AI response 5: The AI explained that a foreign key does not automatically mean the column is required, because nullable foreign-key columns are allowed in SQL. If participation in the relationship is mandatory, then NOT NULL should be added. If the relationship is optional, the foreign-key attribute can stay nullable.

Prompt 6: How do I reference a foreign composite key made of several attributes?

AI response 6: The AI explained the syntax for composite foreign keys, where all child columns are listed together in one FOREIGN KEY (...) constraint. It also explained that when the referenced columns are the primary key of the referenced table, PostgreSQL allows the shorter syntax REFERENCES table_name instead of repeating the referenced column list.

Prompt 7: How can applications be represented without adding unnecessary bridge tables, while still allowing applications for core-team functions, event participation, BOMBA young-member applications, and mentor applications?

AI response 7: The AI suggested keeping applications as one relation with an application_type, an application_status, a required event-edition reference, and an optional core-team-function reference. This keeps the relational design closer to the ER model and avoids extra application-target tables that are not shown in the conceptual model.

Prompt 8: I realised that "required_attendance" is kind of redundant. After several conversations I asked if the required_attendance idea be fixed, or if it is better to remove it from this phase?

AI response 8: The AI explained that a simple relation between membership stages and activity types would not cleanly express the real attendance rules, because some obligations depend on Board function, core-team membership, and the related event edition. It confirmed that it is fine to remove this relation from the Phase P2 model and derive mandatory attendance later through SQL queries, views, procedures, or application logic.

Prompt 9: How should the nullable event-related columns in activity_sessions be handled?

AI response 9: The AI explained that if only some activity sessions are related to event editions, then storing event-edition columns directly in activity_sessions would create many null values. It suggested keeping activity_sessions focused on the activity-session key and representing the optional connection to event editions with a separate related_to relation.

Prompt 10: Create realistic sample data for data_load.sql using my existing schema and the following constraints: realistic UKIM fields of study for FINKI, FEEIT, MFS, TMF, and PMF; fulfilled Board and Non-Board functions; accepted applications for event function slots; weekly meetings in Baraka 2.2; workshops in Baraka 2.1, 2.2, 3.1, 3.2, Netaville, or Base42; full-member, Board, and core-team meetings in BEST Office; BCS meetings at the BEST Benches; realistic event descriptions and locations for Job Fair, Hackathon, BEST Course in Summer, Motivational Weekend, BOMBA, beBESTie, and BEST Skopje Birthday; several core-team functions for larger events, only Main Organiser functions for smaller events, and no Main Organiser for BOMBA because it is coordinated by HR; realistic responsible members, participation, absences, volunteers, companies, and cooperation records.

AI response 10: The AI drafted connected sample data based on those constraints. The sample data included students, members, membership-stage history, organisational functions, event types, event editions, event locations, event sessions, core-team/event-function slots, applications, activity sessions, event-related activity-session links, participation/RSVP records, announced absences, responsible members, volunteers, companies, and cooperation records. The data was then reviewed and corrected against the ER model and project requirements.

Prompt 11: Fix the sample data so that Weekly Meetings and BCS Meetings always start at 20:00.

AI response 11: The AI updated the relevant activity sessions and dependent references so Weekly Meetings and BCS Meetings use 20:00 as their start time.

Prompt 12: Check whether ERModel_v09, schema_creation.sql, and data_load.sql are consistent and correct.

AI response 12: The AI reviewed the ER model and the Phase P2 files for consistency. It checked that deleted concepts such as the simple attendance-rule relation were removed, that related_to remained present, that DDL and DML covered the same tables, that composite keys and foreign keys matched the ER model, and that the realistic sample-data rules were followed.

Prompt 13: Fix any small inconsistencies found in the final check.

AI response 13: The AI helped update the final documentation to reference ERModel_v09 and adjusted the relational representation of volunteers and cooperation so that volunteer_role and cooperation_type are stored as relationship attributes instead of being part of the primary key.

Prompt 14: I think application_id for applications should be automatically generated. How to do this in PostgreSQL?

AI response 14: The AI shared the solution: "application_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY".

Prompt 15: Job Fair is public and does not have participation applications. beBESTie also has open registration and does not need a motivational letter. Hackathon and BEST Course in Summer have applications through external Google Forms with more fields than the ER model. How should this be represented?

AI response 15: The AI suggested keeping public/open attendance outside the applications relation, so Job Fair and beBESTie participation were not modeled as applications. For Hackathon and BEST Course in Summer, the AI suggested keeping only the most important summarized application information in applications, such as the applicant, event edition, status, application type, and main motivation/answer, while leaving the full Google Form outside the current phase scope.

Prompt 16: I noticed that there are no Main Organiser applications for beBESTie, Birthday, and Motivational Weekend, even though those events should still have a Main Organiser or event function slot.

AI response 16: The AI explained that the current core_team_functions relation was being used more generally as an event-function relation in the physical schema. It suggested adding Main Organiser function slots and accepted applications for Motivational Weekend, Birthday, and beBESTie, while still leaving BOMBA without a Main Organiser because it is coordinated by HR.

Prompt 17: For an event edition to be held, it must have had a Main Organiser or a core team, except for BOMBA. Is that the case currently?

AI response 17: The AI checked the sample data and confirmed that every event edition except BOMBA has either a Main Organiser function or a full core team with accepted applications. BOMBA was left as the exception because it is coordinated through the HR function.

Changes made based on AI assistance

The following relational design and documentation work was completed with AI assistance:

• PostgreSQL syntax and constraint examples were clarified before being used in the DDL script.
• Application targets were represented directly in applications with foreign keys to event editions and, when needed, core team functions.
• The optional event-related connection of activity sessions was represented with the separate related_to relation.
• The simple attendance-rule relation was removed from the final Phase P2 relational design because attendance obligations will be derived later through SQL and application logic.
• Realistic, connected sample data was prepared and revised for all tables.
• The sample data was revised with official UKIM/faculty study programme names and more realistic BEST Skopje event, activity, location, responsibility, and core team examples.
• Membership-stage history and sample attendance data were corrected to better represent previous Observer/Young member stages, Board meeting attendance, and core team attendance for event-related meetings.
• The final files were checked for duplicate names in the ER model, stale removed concepts, matching DDL/DML table sets, duplicate primary keys, and broken foreign-key references.
• The RelationalDesign wiki page was prepared according to the required phase template.
• This RelationalDesignAIUsage page was prepared according to the required AI-use template.
• application_id was implemented as an automatically generated identity value in the physical schema.
• Public attendance for Job Fair and open registration-style attendance for beBESTie were kept outside the applications relation.
• Participation applications were kept only for event editions where an actual application process exists in the sample data, such as Hackathon and BEST Course in Summer.
• Event function slots were adjusted so larger events have several core-team functions, smaller events have only a Main Organiser function, and BOMBA has no Main Organiser because it is coordinated by HR.