| 17 | | [[Image(RelationalModel-CMS-Relational-FINAL.svg, 1720px)]] |
| | 17 | === Oracle SQL Developer Data Modeler (Barker's notation) |
| | 18 | |
| | 19 | 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. |
| | 20 | |
| | 21 | ==== Logical Diagram |
| | 22 | |
| | 23 | [[Image(RelationalModel-CMS-Logical.svg, 1720px)]] |
| | 24 | |
| | 25 | ==== Relational Diagram |
| | 26 | |
| | 27 | [[Image(RelationalModel-CMS-Relational.svg, 1720px)]] |
| 20 | | - `RelationalModel-CMS-FINAL.svg` — Visual Paradigm diagram, exported as image (mandatory) |
| 21 | | - `RelationalModel-CMS-FINAL.vpp` — Visual Paradigm model file (mandatory) |
| 22 | | - `RelationalModel-CMS-Logical-FINAL.svg` — Oracle SQL Developer Data Modeler Logical diagram, exported as image |
| 23 | | - `RelationalModel-CMS-Relational-FINAL.svg` — Oracle SQL Developer Data Modeler Relational diagram, exported as image |
| | 30 | - `RelationalModel-CMS-SurrogateKeys.svg` — Visual Paradigm diagram with surrogate primary keys, exported as image (mandatory) |
| | 31 | - `RelationalModel-CMS-SurrogateKeys.vpp` — Visual Paradigm model file, surrogate keys version (mandatory) |
| | 32 | - `RelationalModel-CMS-IdentifyingRelationships.svg` — Visual Paradigm diagram with composite natural primary keys and identifying relationships, exported as image (mandatory) |
| | 33 | - `RelationalModel-CMS-IdentifyingRelationships.vpp` — Visual Paradigm model file, identifying relationships version (mandatory) |
| | 34 | - `RelationalModel-CMS-Logical.svg` — Oracle SQL Developer Data Modeler Logical diagram, exported as image |
| | 35 | - `RelationalModel-CMS-Relational.svg` — Oracle SQL Developer Data Modeler Relational diagram, exported as image |
| | 104 | |
| | 105 | === Primary Key Strategy |
| | 106 | |
| | 107 | Two distinct situations were encountered when determining the primary key structure for each entity, and each was handled differently. |
| | 108 | |
| | 109 | ==== Rule 1 — Surrogate key replacing an excessively long composite natural key |
| | 110 | |
| | 111 | 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. |
| | 112 | |
| | 113 | ==== Rule 2 — Surrogate key as a genuine additional identifier |
| | 114 | |
| | 115 | 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. |
| | 116 | |
| | 117 | ==== The two diagram versions |
| | 118 | |
| | 119 | 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. |
| | 120 | |
| | 121 | ==== Performance considerations |
| | 122 | |
| | 123 | 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. |
