Changes between Version 3 and Version 4 of RelationalModel
- Timestamp:
- 04/21/26 23:21:29 (11 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
RelationalModel
v3 v4 7 7 [[Image(RelationalModel-CMS-FINAL.svg, 1720px)]] 8 8 9 The following two diagrams were produced in Oracle SQL Developer Data Modeler using Barker's notation as a supplementary model. The Visual Paradigm diagram above is the mandatory deliverable. The Data Modeler diagrams are provided because they overcome several limitations of Visual Paradigm documented in the section below, and allow the model to be expressed with proper per-side participation, bilateral relationship naming, correct data types, non-transferability, and direct recursive relationship lines without intermediate entities. 10 11 === ''Logical Diagram (Barker's notation)'' 12 13 [[Image(RelationalModel-CMS-Logical-FINAL.svg, 1720px)]] 14 15 === ''Relational Diagram (Barker's notation)'' 16 17 [[Image(RelationalModel-CMS-Relational-FINAL.svg, 1720px)]] 18 9 19 Attached files: 10 20 - `RelationalModel-CMS-FINAL.svg` - exported diagram image … … 13 23 == Descriptive Documentation and Argumentation 14 24 25 === ''Diagram review and professor consultation'' 26 27 The Visual Paradigm diagram is the mandatory deliverable for this phase and was presented to the supervising professor during the required consultation. The professor's remarks were subsequently implemented in both the Visual Paradigm and the Oracle SQL Developer Data Modeler diagrams. The two diagrams are conceptually equivalent and nearly visually identical. The only structural differences between them stem from tool behaviour, described in the tooling limitations section below. 28 15 29 === Member / Student / Teacher (ISA specialisation) 16 30 17 The system has a single `Member` entity that stores identity and authentication data common to all users. `Student` and `Teacher` are specialisations of `Member`, sharing its primary key rather than introducing a separate surrogate key. This models the ISA relationship directly: every student and every teacher is a member, but a member need not be either. The shared key also means that deleting a member cascades to the corresponding student or teacher record automatically. In Visual Paradigm this is represented as an identifying relationship (solid line) from `Member` to each subtype. 31 The system has a single `Member` entity that stores identity and authentication data common to all users. `Student` and `Teacher` are specialisations of `Member`, sharing its primary key rather than introducing a separate surrogate key. This models the ISA relationship directly: every student and every teacher is a member, but a member need not be either. The shared key also means that deleting a member cascades to the corresponding student or teacher record automatically. The `Student` entity additionally carries an `index` column representing the student's institutional index number. In Visual Paradigm this is represented as an identifying relationship (solid line) from `Member` to each subtype. 32 33 === Program_Enrollment and program switching 34 35 The original design had a direct M:1 relationship from `Student` to `Academic_Program`, meaning a student could belong to exactly one program with no history of changes. Following consultation with the supervising professor, this was replaced with a M:N relationship resolved through `Program_Enrollment`. Each record carries `date_enrollment` and `date_disenrollment`. The `date_enrollment` attribute is part of the primary key because the same student may enroll in the same program more than once — for example, enrolling in program A, switching to program B, then returning to program A produces two distinct records for the same student–program pair. The `date_disenrollment` attribute is nullable because a currently active enrollment has not yet ended. 18 36 19 37 === Course vs. Course_Edition … … 23 41 === Course prerequisites (recursive M:N on Course) 24 42 25 A course may require one or more other courses as prerequisites before a student may take it, and a course may itself be a prerequisite for one or more other courses. This is a many-to-many recursive relationship on `Course`. In Visual Paradigm, which does not support connecting an entity directly to itself, this required the introduction of an intermediate entity by the tool. In the conceptual model it is a pure relationship with no additional attributes, resolved in the physical model by a junction table with a composite primary key and a check constraint preventing a course from listing itself as its own prerequisite. 43 A course may require one or more other courses as prerequisites before a student may take it, and a course may itself be a prerequisite for one or more other courses. This is a many-to-many recursive relationship on `Course`, resolved in the physical model by a junction table with a composite primary key and a check constraint preventing a course from listing itself as its own prerequisite. In Visual Paradigm, which does not support connecting an entity directly to itself, this required the introduction of an intermediate entity by the tool. 44 45 === Course equivalences (recursive M:N on Course) 46 47 Courses across different academic programs may be declared equivalent — for example, "Databases 2" in one program may be considered equivalent to "Advanced Databases" in another. When a student switches programs, equivalent courses are recognised so that the student does not lose credit for passed work. This is modeled as a symmetric many-to-many recursive relationship on `Course`, resolved by the junction table `Course_Equivalence`. Because the relationship is symmetric (if A is equivalent to B then B is equivalent to A), application-level logic or a database constraint must enforce this symmetry. As with prerequisites, Visual Paradigm required an intermediate entity for this recursive relationship. 48 49 === Course_Enrollment as the activity anchor 50 51 Previously, `Exercise_Submission`, `Exam_Attempt`, `Student_Grade`, and `Survey_Response` were all linked directly to `Student`. This allowed a student to submit, attempt an exam, receive a grade, or respond to a survey in a course they had never enrolled in — an integrity violation identified during professor consultation. All four are now linked to `Course_Enrollment` instead. Since a `Course_Enrollment` record can only exist if the student is enrolled in that course edition via a `Semester_Enrollment`, this structurally enforces that all student activity is restricted to courses the student is actually enrolled in. 26 52 27 53 === Semester_Enrollment and the hash chain 28 54 29 `Semester_Enrollment` records a student's enrollment for a given academic semester . It is modeled as an entity (not a pure relationship) for two reasons: it carries its own surrogate primary key that the course enrollment association references, and it implements a blockchain-inspired hash chain for tamper-evidence. Each record stores `prev_hash` (the SHA-256 hash of the previous enrollment record for the same student) and `current_hash` (the SHA-256 hash of the concatenation of the current record's data with `prev_hash`). Any retroactive modification to an earlier record invalidates all subsequent hashes in the chain, making tampering detectable. Implementation details are deferred to Phase 5.55 `Semester_Enrollment` records a student's enrollment for a given academic semester and is the parent of all `Course_Enrollment` records for that term. It implements a blockchain-inspired hash chain for tamper-evidence: each record stores `prev_hash` (the SHA-256 hash of the previous enrollment record for the same student) and `current_hash` (the SHA-256 hash of the concatenation of the current record's data with `prev_hash`). Any retroactive modification to an earlier record invalidates all subsequent hashes, making tampering detectable. Implementation details are deferred to Phase 5. 30 56 31 57 === Exam_Attempt as an entity 32 58 33 `Exam_Attempt` records a student's single attempt at an exam and carries `attempt_number` and `total_points`. It could superficially appear to be a pure M:N junction between `Student` and `Exam`, but it is modeled as an entity because `Student_Answer` must reference a specific attempt. Collapsing `Exam_Attempt` into a relationship line would leave `Student_Answer` with no entity to connect to, which is structurally invalid in ER notation. As an entity, `Exam_Attempt` participates in two clean 1:N relationships: one from `Student` and onefrom `Exam`.59 `Exam_Attempt` records a student's single attempt at an exam and carries `attempt_number` and `total_points`. It is modeled as an entity because `Student_Answer` must reference a specific attempt. It participates in a 1:N relationship from `Course_Enrollment` (replacing the previous link from `Student`, to enforce the enrollment restriction) and a 1:N relationship from `Exam`. 34 60 35 61 === Student_Answer 36 `Student_Answer` records a student's response to a specific `Exam_Problem` within a specific `Exam_Attempt`, along with the points awarded for that response. It sits at the intersection of `Exam_Attempt` and `Exam_Problem`. Because it carries meaningful per-answer data (`answer`, `points_acquired`) and depends on `Exam_Attempt` as an entity, it is itself modeled as an entity with a composite primary key across the attempt and problem identifiers. 62 63 `Student_Answer` records a student's response to a specific `Exam_Problem` within a specific `Exam_Attempt`, along with the points awarded for that response. It carries meaningful per-answer data (`answer`, `points_acquired`) and is itself modeled as an entity with a composite primary key across the attempt and problem identifiers. 37 64 38 65 === Announcement threading (recursive 1:N on Announcement) 39 66 40 Announcements support threaded replies: an announcement may be a reply to at most one parent announcement, and a parent may have zero or more replies. This is a one-to-many recursive relationship on `Announcement`, expressed as a nullable self-referencing foreign key (`parent_announcement_id`). Because Visual Paradigm does not support directly connecting an entity to itself, the tool introduced an intermediate entity for this relationship in the diagram; the underlying structure is a simple nullable FK on the same table.67 Announcements support threaded replies: an announcement may be a reply to at most one parent announcement, and a parent may have zero or more replies. This is a one-to-many recursive relationship on `Announcement`, expressed as a nullable self-referencing foreign key. Because Visual Paradigm does not support connecting an entity directly to itself, the tool introduced an intermediate entity; the underlying structure is a simple nullable FK on the same table. 41 68 42 69 === Member_Message (recursive M:N on Member) 43 70 44 Direct private messages between members form a many-to-many recursive relationship on `Member` : a member may send zero or more messages and receive zero or more messages. Each message has exactly one sender and one receiver, and a check constraint prohibits self-messaging. In Visual Paradigm, the same limitation regarding recursive relationships applies here as with `Course_Prerequisite` and `Announcement`.71 Direct private messages between members form a many-to-many recursive relationship on `Member`. Each message has exactly one sender and one receiver, and a check constraint prohibits self-messaging. Visual Paradigm required an intermediate entity for this recursive relationship as well. 45 72 46 73 === Visual Paradigm tooling limitations … … 49 76 50 77 - Line style (dashed vs. solid) denotes non-identifying vs. identifying relationships, not partial vs. total participation, because Visual Paradigm does not support mixed participation on a single line. 51 - Recursive relationships required intermediate entities introduced automatically by the tool. 78 79 - Recursive relationships required intermediate entities introduced automatically by the tool. This affects the prerequisite and equivalence relationships on `Course`, the threaded reply relationship on `Announcement`, and the direct messaging relationship on `Member`. In the Data Modeler diagram all four are drawn as direct recursive relationship lines. 80 52 81 - Data types in the diagram use `varchar(255)` and `integer(10)` as approximations of the intended PostgreSQL `TEXT` and `INT` types, which Visual Paradigm does not natively support. 53 - Relationship names are limited to one per relationship rather than one per participating entity end, as Barker's notation would allow.54 82 55 These limitations are the reason a supplementary Barker's notation diagram was produced in Oracle SQL Developer Data Modeler, which supports proper per-side participation, bilateral relationship naming, native data types, and non-transferability. 83 - Relationship names are limited to one per relationship rather than one per participating entity end. 84 85 - `Teaches`, `Survey_Response`, and `Course_Equivalence` appear as entity boxes in the Visual Paradigm diagram due to the tool's handling of many-to-many relationships. In the Data Modeler diagram these three are correctly represented as pure relationship lines (connections) between their respective entities, since Data Modeler does not require intermediate entity boxes for M:N relationships. This is the only structural difference between the two diagrams and is a tooling artefact, not a modeling decision. 86 87 These limitations are the reason a supplementary Barker's notation diagram was produced in Oracle SQL Developer Data Modeler, which supports proper per-side participation, bilateral relationship naming, native data types, non-transferability, and direct representation of M:N relationships without forced intermediate entities.
