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.

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.

Logical Diagram (Barker's notation)

Relational Diagram (Barker's notation)

Attached files:

• RelationalModel-CMS-FINAL.svg — Visual Paradigm diagram, exported as image (mandatory)
• RelationalModel-CMS-FINAL.vpp — Visual Paradigm model file (mandatory)
• RelationalModel-CMS-Logical-FINAL.svg — Oracle SQL Developer Data Modeler Logical diagram, exported as image
• RelationalModel-CMS-Relational-FINAL.svg — Oracle SQL Developer Data Modeler Relational diagram, exported as image

Descriptive Documentation and Argumentation

Diagram review and professor consultation

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.

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. 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.

Program_Enrollment and program switching

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.

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, 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.

Course equivalences (recursive M:N on Course)

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.

Course_Enrollment as the activity anchor

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.

Semester_Enrollment and the hash chain

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.

Exam_Attempt as an entity

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.

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 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.

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. 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.

Member_Message (recursive M:N on Member)

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.

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. 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.

• 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.

• 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.

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.