Relational Model

Relational Diagram

The diagram was produced using Visual Paradigm (desktop version, Community Edition) in Crow's foot notation. A supplementary diagram in Barker's notation was also produced using Oracle SQL Developer Data Modeler, providing a logical model and an engineered relational model.

Attached files:

• RelationalModel-CMS-FINAL.svg - exported diagram image
• RelationalModel-CMS-FINAL.vpp - original Visual Paradigm model file

Descriptive Documentation and Argumentation

Member / Student / Teacher (ISA specialisation)

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.

Course vs. Course_Edition

Course represents an abstract entry in the course catalog (a code, a name, and a credit value). Course_Edition represents a concrete scheduled offering of that course in a specific academic year and semester. This separation is intentional: the catalog remains stable across years while editions are created each time the course is taught. All content items (lectures, exercises, exams, surveys, announcements) and all enrollment and grading records are attached to a Course_Edition, never to the abstract Course.

Course prerequisites (recursive M:N on Course)

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.

Semester_Enrollment and the hash chain

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.

Exam_Attempt as an entity

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 one from Exam.

Student_Answer

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.

Announcement threading (recursive 1:N on Announcement)

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.

Member_Message (recursive M:N on Member)

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.

Visual Paradigm tooling limitations

Several aspects of the Visual Paradigm diagram are a consequence of tool limitations rather than modeling decisions, and should be interpreted accordingly:

• 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.
• Recursive relationships required intermediate entities introduced automatically by the tool.
• 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.
• Relationship names are limited to one per relationship rather than one per participating entity end, as Barker's notation would allow.

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.