Relational Model

Relational Diagram

Visual Paradigm (Crow's foot notation)

Two versions of the Visual Paradigm diagram are provided. The identifying relationships version uses composite natural primary keys and identifying relationships everywhere a natural key is sufficient, making the full extent of key propagation visible. The surrogate keys version replaces excessively long composite keys with surrogate id attributes, making the diagram substantially easier to read. Both versions are semantically equivalent. See the Primary Key Strategy section for detailed reasoning.

Identifying Relationships version

Surrogate Keys version

Oracle SQL Developer Data Modeler (Barker's notation)

The following two diagrams were produced in Oracle SQL Developer Data Modeler using Barker's notation as a supplementary model. The Visual Paradigm diagrams above are 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

Relational Diagram

Attached files:

• RelationalModel-CMS-SurrogateKeys.svg — Visual Paradigm diagram with surrogate primary keys, exported as image (mandatory)
• RelationalModel-CMS-SurrogateKeys.vpp — Visual Paradigm model file, surrogate keys version (mandatory)
• RelationalModel-CMS-IdentifyingRelationships.svg — Visual Paradigm diagram with composite natural primary keys and identifying relationships, exported as image (mandatory)
• RelationalModel-CMS-IdentifyingRelationships.vpp — Visual Paradigm model file, identifying relationships version (mandatory)
• RelationalModel-CMS-Logical.svg — Oracle SQL Developer Data Modeler Logical diagram, exported as image
• RelationalModel-CMS-Relational.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: Survey_Response, Course_Equivalence, Course_Prerequisite, and Announcement_Replies are represented as entity boxes in Visual Paradigm but as relationship lines in Data Modeler. Teaches was previously also in this category, but has been promoted to a deliberate entity in Data Modeler following the decision to connect Announcement to Teaches rather than separately to Course_Edition and Member.

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.

• 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 two are correctly represented as pure relationship lines. Teaches was always an entity box in Visual Paradigm due to tool limitations, and has now been promoted to a deliberate entity in Data Modeler as well, because Announcement references it directly and it therefore requires a referenceable surrogate primary key.

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.

Primary Key Strategy

Two distinct situations were encountered when determining the primary key structure for each entity, and each was handled differently.

Rule 1 — Surrogate key replacing an excessively long composite natural key

In several entities, the natural primary key assembled from foreign key attributes of parent entities would have consisted of between five and twenty attributes. This accumulation arises from chains of identifying relationships: when entity C identifies itself through entity B, which in turn identifies itself through entity A, C inherits the full composite key of both. In the surrogate keys diagram, entities in this situation are assigned a single surrogate id attribute as their sole primary key. The columns that would have formed the composite natural key are retained and covered by a UNIQUE NOT NULL constraint, preserving the same semantic guarantee — the database still rejects duplicates — but without propagating the key further down the chain. As a consequence, all such relationships are non-identifying, reflected in the surrogate keys diagram by dashed relationship lines throughout.

Rule 2 — Surrogate key as a genuine additional identifier

In certain entities that arise from many-to-many relationships, the foreign keys of the two participating entities are not sufficient to form a unique primary key on their own. This occurs when the same pair of entities may legitimately appear in the relationship more than once — for example, a student may attempt the same exam multiple times, so (student_id, exam_id) alone does not uniquely identify an attempt. In these cases a surrogate id is added not to reduce key length but because no natural key exists. The foreign keys are retained and covered by an appropriate UNIQUE or partial uniqueness constraint where the business rules require it. This rule applies in both diagram versions.

The two diagram versions

The identifying relationships version applies only Rule 2, using composite natural primary keys and identifying relationships everywhere a natural key is sufficient. This makes the full extent of key propagation visible and serves as a reference point for understanding the motivation for Rule 1. The surrogate keys version applies both Rule 1 and Rule 2, replacing all excessively long composite keys with surrogate keys and making all such relationships non-identifying. Both versions are semantically equivalent in terms of what data they can represent and what integrity constraints they enforce.

Performance considerations

Identifying relationships with composite primary keys offer a physical storage benefit in database engines that use clustered indexes (such as MySQL/InnoDB or SQL Server), where child rows sharing the same parent key value are stored physically adjacent on disk, accelerating range scans. However, PostgreSQL uses a heap-based storage model and does not maintain physical row ordering by primary key automatically. This benefit therefore does not apply in our context. Query performance for parent–child lookups is instead provided by the B-tree indexes that PostgreSQL creates automatically for every UNIQUE constraint, which are functionally equivalent for the access patterns of this system. The identifying relationships diagram is nonetheless provided to make the clustering argument visible and to demonstrate awareness of this tradeoff in database engines where it does apply.