Changes between Version 2 and Version 3 of RelationalDesignAIUsage

Timestamp:

09/02/26 16:16:57 (4 days ago)

Author:

236024

Comment:

--

RelationalDesignAIUsage

@@ -129 +129 @@
 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.
 
+'''Prompt 18:'''
+With the following schema design, would {{{ef_no}}} and {{{es_no}}} have to be entered manually by the user? Can they be generated automatically even though they're not global?
+
+'''AI response 18:'''
+The AI explained that the user should not manually enter these numbers. They are internal partial identifiers, and the application can later assign the next available number inside the selected event edition.
+
+'''Prompt 19:'''
+How should the {{{applications}}} relation reference event editions and event functions if not every application is for a function?
+
+'''AI response 19:'''
+The AI explained that {{{ee_id}}} should always reference {{{event_editions}}}, while {{{ef_no}}} can be nullable. The composite foreign key {{{(ee_id, ef_no)}}} references {{{event_functions}}} only when {{{ef_no}}} is present, so participation applications can reference only an event edition.
+
+'''Prompt 20:'''
+Debated with AI whether {{{young_members}}} table should reference full members indices since a full member would at one point become an alumni or could revert to a young member.
+
+'''AI response 20:'''
+The AI explained that referencing {{{members}}} is more flexible for historical cases, but referencing {{{full_members}}} matches the ER model more strictly. Since the ER model keeps mentorship between !FullMembers and !YoungMembers, the stricter relational representation is {{{mentor_index REFERENCES full_members(student_index)}}}. After back and forth discussion I expressed that the young member is technically automatically mentored by the current HR Coordinator should the full member not be a full member anymore, so the young members table should keep referencing full member indices. The business logic would get implemented later on.
+
+'''Prompt 21:'''
+Change the sample data so that {{{data_load.sql}}} stays consistent with the updated relational schema.
+
+'''AI response 21:'''
+The AI aligned the existing sample data with the updated table names, numeric identifiers, event function/session numbers, application types, and foreign key references, while keeping the same realistic BEST Skopje data requirements from the earlier sample-data prompt.
+
+'''Prompt 22:'''
+Check whether the current {{{schema_creation.sql}}} and {{{data_load.sql}}} are consistent.
+
+'''AI response 22:'''
+The AI checked that every inserted table exists in the schema, every inserted column exists in the corresponding table, and the table names match between the schema and data load scripts.
+
+'''Prompt 23:'''
+Asked the AI model to help me update the TracWikis by only proposing the necessary changes and additions.
+
+'''AI response 23:'''
+The AI kept the existing page text as much as possible and only helped with updating outdated parts: the ER model version reference, the relational schema block, old string-key explanations, old weak-entity descriptions, renamed relations, and references to the new Locations entity.
+
+'''Prompt 24:'''
+Check whether the updated {{{RelationalDesign}}} TracWiki relation list matches the current schema.
+
+'''AI response 24:'''
+The AI checked that the relation names in the TracWiki schema block match the tables created in {{{schema_creation.sql}}}.
+
 == Changes made based on AI assistance ==
 
@@ -139 +181 @@
 * 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.
+* 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.
@@ -147 +189 @@
 * 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.
+* The consistency of `schema_creation.sql`, `data_load.sql`, the relational design page, and the use-case SQL was checked after the changes.