Changes between Version 28 and Version 29 of RelationalDesign


Ignore:
Timestamp:
09/03/26 17:58:50 (7 days ago)
Author:
216009
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • RelationalDesign

    v28 v29  
    2020'''Student_Subject''' ( [Ss_Id, Student_Id* (Student), Subject_Id* (Subject)], Professor_Id* (Professor), Final_Grade, Status, Enrollment_Date, Absences_Count )
    2121
    22 '''Advice''' ( [Student_Id* (Student), Professor_Id* (Professor)], Start_Date, End_Date )
     22'''Advice''' ( [Student_Id* (Student), Professor_Id* (Professor), Start_Date], End_Date )
    2323
    2424'''Affiliated''' ( [University_Id* (University), Professor_Id* (Professor)] )
    … …  
    6161'''Student_Subject''' ( [Ss_Id, Student_Id* (Student), Subject_Id* (Subject)], Professor_Id* (Professor), Final_Grade, Status, Enrollment_Date, Absences_Count )
    6262
    63 '''Advice''' ( [Student_Id* (Student), Professor_Id* (Professor)], Start_Date, End_Date )
     63'''Advice''' ( [Student_Id* (Student), Professor_Id* (Professor), Start_Date], End_Date )
    6464
    6565'''Affiliated''' ( [University_Id* (University), Professor_Id* (Professor)] )
    … …  
    8383**Results in details / description:**
    8484
    85 Schema Normalization Review: I provided the AI with my initial 7-table layout (University, Faculty, Professor, Student, Subject, Subject_Professor, and Student_Subject). We reviewed the constraints to ensure proper cascading rules and column data integrity.
    86 
    87 Relationship Modeling & Professor Assignment: To handle business requirements for office hours and mentorship, I formulated an Advice entity. Additionally, based on professor feedback regarding ambiguity in grading when multiple professors teach the same course, we removed the legacy Subject_Professor table. The Professor_Id was successfully migrated directly into the Student_Subject enrollment schema. This ensures that for every specific course enrollment, the system explicitly tracks which professor taught the student and is authorized to enter the final grade, eliminating any multi-instructor ambiguity.
    88 
    89 Affiliation Tracking: I noticed a missing link where a professor might be affiliated with a specific university outside of just their faculty assignment. The AI assisted me in mapping this out cleanly as an Affiliated bridge table to prevent normalization redundancy.
    90 
    91 Documentation Generation: The AI assisted in translating my updated schema into the clean, underlined notation style required by our project's Trac Wiki submission guidelines.
    9285
    9386== Entire AI usage log:
    9487
    95 User: I have created an initial relational schema for my university database tracking universities, faculties, professors, students, and subject enrollments. Can you review my baseline table structure to see if there are any logical gaps for a complete academic management system?
     88Schema Mapping & Normalization Review: All entities from Phase 1 were mapped to relations. Foreign keys were introduced to represent 1:N relationships (`University_Id` in Faculty, `Faculty_Id` in Professor, Student, and Subject).
    9689
    97 AI: Analyzed your 7 core tables. The structure is highly normalized, but it currently lacks a way to track independent academic mentorship or office hours between a professor and a student outside of a classroom. It also lacks a clear way to track a professor's direct corporate or institutional affiliation to the primary university level if they span multiple departments.
     90Weak Entity Materialization: The weak entity `Student_Subject` was mapped by forming a composite primary key consisting of its discriminator (`Ss_Id`) along with the primary keys of its owner entities (`Student_Id` and `Subject_Id`). The regular 1:N relationship with `Professor` (`assignTo`) was materialized via a foreign key `Professor_Id` in this table.
    9891
    99 User: Good point. I want to add an office hours/mentorship feature called Advice and a connection table called Affiliated. How should I structure their primary keys and foreign keys so they map cleanly to my existing Student, Professor, and University tables?
    100 
    101 AI: Assisted by suggesting a many-to-many design pattern. For Advice, you can use a composite key pairing Student_Id and Professor_Id as foreign keys alongside tracking timestamps. For Affiliated, you can create a direct intersection table pairing University_Id and Professor_Id.
    102 
    103 User: Excellent, that fits exactly what I had in mind. Can you help me format my final, updated schema—including these two new tables—into the underlined notation style required for our project documentation?
    104 
    105 AI: Structured the final table schema list, explicitly emphasizing the primary keys with formatting underlines and clearly marking the foreign key dependencies and their referencing entities in parentheses.
     92Relationship Materialization:
     93- The M:N relationship `Advises` with attributes (`start_date`, `end_date`) was mapped to the `Advice` table with a composite primary key (`Student_Id`, `Professor_Id`, `Start_Date`).
     94- The M:N relationship `affiliated_with` was mapped to the `Affiliated` bridge table with composite primary key (`University_Id`, `Professor_Id`).
     95- The M:N relationship `Teach` was mapped to the `Teach` bridge table with composite primary key (`Professor_Id`, `Subject_Id`).