Changes between Initial Version and Version 1 of RelationalDesignAIUsage

Timestamp:

08/28/26 21:41:23 (9 days ago)

Author:

236024

Comment:

--

RelationalDesignAIUsage

@@ +1 @@
+= 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 will be exported from DBeaver after the scripts are 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 core-team applications for the organised event editions; 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; appropriate core-team functions for each event type; 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 functions, 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.
+
+== 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.