Week 3 - Slides: Logical Database Design - The 18 ERD-to-Relational Mapping Rules

Framework of Logical Design and the 18 Relational Rules

  • Course Framework: In logical database design, converting an Entity-Relationship Diagram (ERD) into a relational database schema is a deterministic, mechanical translation procedure using 18 precise rules.

  • Translation Output: Every rule systematically maps a specific ERD shape to exactly three structural components:

    • Table Name

    • Table Columns

    • Placement and constraints of Primary Keys (PK) and Foreign Keys (FK)

  • Schema Diagram Definition: A Schema Diagram is the final visual representation produced after converting an entire ERD into relations. Relations are depicted as boxes containing PK and FK attributes, with directed arrows illustrating foreign key references pointing from referring tables to referenced tables.

Summary of the 18 ERD-to-Relational Mapping Rules

  • Rule 1 (Strong/Regular Entity): Create a new table. Simple attributes become table columns, and the entity's primary key becomes the table's primary key.

  • Rule 2 (Composite Attribute): Flatten the attribute by dropping the parent container attribute and keeping only its simple component parts as columns (e.g., remove Name and keep FirstName, LastName).

  • Rule 3 (Multivalued Attribute): Create a brand new table containing the attribute value along with the owner entity's primary key as a foreign key. The composite primary key of this new table combines the owner's primary key and the attribute value (e.g., EmpPhone(EmpID, Number)).

  • Rule 4 (Derived Attribute): Ignore the attribute completely in the relational schema. Derived attributes are computed dynamically at query time using SQL queries or database views (e.g., retain DOB and drop Age).

  • Rule 5 (Binary 1:N Relationship): Copy the primary key of the "1" side entity into the table on the "N" side as a foreign key.

  • Rule 6 (1:N Relationship with Participation):

    • Total participation on the "N" side: The foreign key constraint must be set to [NOT NULL].

    • Partial participation on the "N" side: The foreign key is allowed to remain NULL.

  • Rule 7 (1:1 Relationship, Both Sides Partial): Place the foreign key in either entity's table with a [UNIQUE] constraint. Select the entity that participates more frequently in the relationship to minimize NULL values.

  • Rule 8 (1:1 Relationship, One Side Total, One Side Partial): Copy the primary key of the partial entity into the table of the totally participating entity as a foreign key, annotated with [UNIQUE, NOT NULL].

  • Rule 9 (1:1 Relationship, Both Sides Total): Merge both entities into a single relation to eliminate join overhead and avoid NULL checks; alternatively, place a foreign key in either table with [UNIQUE, NOT NULL].

  • Rule 10 (M:N Relationship): Create a new associative table. Copy the primary keys of both participating entities into the associative table. Together, these primary keys form a composite primary key while individually serving as foreign keys.

  • Rule 11 (Weak Entity): Create a table for the weak entity. Copy the primary key of the owner (strong) entity into the weak entity's relation as a foreign key and as a component of the primary key. The composite primary key equals the owner's primary key plus the weak entity's partial key.

  • Rule 12 (1:N Unary / Self-Referential Relationship): Add a recursive foreign key column to the same table that references the table's own primary key (e.g., ManagerID referencing EmpID in an Employee table).

  • Rule 13 (Specialization — Default / Easiest Option): Create a parent table for the superclass containing all shared attributes, plus one child table for each subclass holding subclass-specific attributes and the parent primary key (which acts as both the child's primary key and a foreign key referencing the parent table).

  • Rule 14 (Specialization — Total Only): Omit the parent table entirely. Create child tables only, replicating all superclass attributes into every child table.

  • Rule 15 (Relationship Attribute in 1:N): Place the relationship attribute on the table representing the "N" side of the relationship, directly alongside the foreign key.

  • Rule 16 (Relationship Attribute in 1:1): Place the relationship attribute in whichever table received the foreign key.

  • Rule 17 (n-ary Relationship, All Sides N): Create an associative table whose composite primary key is formed by combining the primary keys of all participating entities.

  • Rule 18 (n-ary Relationship, At Least One Side is 1): The primary key of any entity with a maximum cardinality of 1 enters the associative table as a foreign key only (excluded from the primary key). The composite primary key is constructed exclusively from the primary keys of the "N" sides.

Attribute Translation Mechanics (Rules 1–4)


The Four Attribute Shapes and What Each Becomes

  • Verbatim Definitions for Rules 1–4:

    • Rule 1: "Create a relation (table) for every strong entity. Simple attributes become table columns. Entity's Primary Key (PK) becomes the table's PK."

    • Rule 2: "Flatten the attribute by removing the parent container and inserting only its simple component parts as columns." — Note that there is no parent container column (e.g., Name) in the target relation.

    • Rule 3: "Create a separate table containing the attribute and the owner's PK (as a Foreign Key). The composite PK of this new table combines the owner's PK and the attribute value."

    • Rule 4: "Ignore from the relational schema completely because they are calculated dynamically via SQL queries/views." — Note that there is no derived column (e.g., Age), only the base source attribute (e.g., DOB).

  • Distinction Between Rule 2 and Rule 4 Deletions:

    • Rule 2 removes the parent container attribute while keeping all child components (e.g., Name is removed, but FirstName and LastName remain).

    • Rule 4 removes the calculated attribute while keeping the underlying source attribute from which it was derived (Age is removed, but DOB remains).

    • Column net cost: A composite attribute costs zero net columns (parent deleted, children added), whereas a derived attribute saves one net column (derived attribute deleted entirely).

Binary Relationship Translation and Foreign Key Placement (Rules 5–9)

  • Structural Placement Principles:

    • Cardinality dictates which table receives the foreign key.

    • Participation dictates what constraints ([UNIQUE], [NOT NULL]) are applied to the foreign key.

  • Summary of Foreign Key Placements and Constraints:

    • Rule 5 (1:N): Foreign key goes into the table on the "N" side, referencing the PK of the "1" side. No constraints added by default.

    • Rule 6 (1:N, Total Participation on N side): Foreign key goes into the "N" side table with a [NOT NULL] constraint.

    • Rule 6 (1:N, Partial Participation on N side): Foreign key goes into the "N" side table and is permitted to contain NULL values.

    • Rule 7 (1:1, Both Sides Partial): Foreign key can be placed in either table with a [UNIQUE] constraint. Place it in the table that participates more frequently to minimize null entries.

    • Rule 8 (1:1, One Total + One Partial): Foreign key goes into the table of the totally participating entity, referencing the partial entity's PK, with [UNIQUE, NOT NULL] constraints.

    • Rule 9 (1:1, Both Sides Total): Merge both entities into a single relation. If kept as two tables, place the foreign key in either table with [UNIQUE, NOT NULL] constraints.


The Three 1:1 Cases (Rules 7, 8, 9) and Their Schema Lines

  • Interpreting Crow's Foot Participation Markers:

    • Crow's-foot markers specify the obligation of the opposite entity. For instance, in a Person to Passport relationship, a double bar on the Person end denotes "exactly one Person".

    • Every Passport MUST be linked to a Person, meaning Passport is the totally participating entity that receives the foreign key SSN with [UNIQUE, NOT NULL].

    • Operational decision rule: Ask "Can entity B exist without entity A?" If no, entity B is total and takes the foreign key.

  • Slide 22 Syntax Error Warning:

    • On Slide 22, the example schema line reads: Employee( EmpID, FirstName, DeptID [UNIQUE, NOT NULL] ) under Rule 6 (1:N with total participation).

    • Placing a [UNIQUE] constraint on a 1:N foreign key erroneously restricts each department to having at most one employee, converting the 1:N relationship into a 1:1 relationship.

    • For 1:N relationships with total participation, specify [NOT NULL] ONLY.

Foreign Key Bracket Annotations: [UNIQUE] and [NOT NULL]

  • Derivation of Annotations:

    • [UNIQUE] originates from CARDINALITY: Indicates that no two rows in the table may reference the same parent row. 1:1 relationships require UNIQUE; 1:N relationships must never use UNIQUE on the foreign key.

    • [NOT NULL] originates from PARTICIPATION: Indicates that every row in the table holding the foreign key must reference a parent row (total participation). Partial participation leaves the foreign key nullable.


The 2x2 Grid: Every Annotation Combination and Rule

  • Annotation Matrix:

    • 1:N Cardinality, Partial Participation: Foreign key has no annotation (e.g., Employee( ..., DeptID )).

    • 1:N Cardinality, Total Participation: Foreign key has [NOT NULL] only (e.g., Employee( ..., DeptID [NOT NULL] )).

    • 1:1 Cardinality, Partial Participation: Foreign key has [UNIQUE] only (e.g., ParkingSpot( ..., EmpID [UNIQUE] )).

    • 1:1 Cardinality, Total Participation: Foreign key has [UNIQUE, NOT NULL] (e.g., Passport( ..., SSN [UNIQUE, NOT NULL] )).

  • Complete Inventory of Slide Bracket Examples:

    • Slide 22 (Rule 6): Employee( EmpID, FirstName, DeptID [UNIQUE, NOT NULL] ) — NOT NULL is correct for total participation; UNIQUE is a slide typo.

    • Slide 23 (Rule 7): ParkingSpot( SpotNo, Location, EmpID [UNIQUE] ) — UNIQUE enforces 1:1; optional participation allows NULL for unassigned spots.

    • Slide 24 (Rule 8): Passport( PassportNo, IssueDate, SSN [UNIQUE, NOT NULL] ) — UNIQUE enforces 1:1; total participation of Passport forces NOT NULL on SSN.

    • Slide 25 (Rule 9): Company( CompID, Name, TIN [UNIQUE, NOT NULL], Status ) — Entities merged; TIN (from TaxFile) retains key uniqueness via UNIQUE and mandatory status via NOT NULL.

    • Slide 27 (Rule 11): Dependent( DependID, Name, EmpID [NOT NULL] ) — EmpID is part of the composite primary key; primary key components are implicitly non-null, making [NOT NULL] redundant written for emphasis. No UNIQUE is used because an employee can have multiple dependents.

    • Slide 32 (Rule 16): ParkingSpot( SpotNo, Location, EmpID [UNIQUE], AssignedDate ) — Identical to Rule 7 with relationship attribute AssignedDate added.

  • The Pattern Behind Bracket Annotations:

    • Bracket annotations appear exclusively on foreign keys that are NOT part of the table's primary key.

    • When a foreign key forms part of the primary key (e.g., Rule 10 associative tables, Rule 11 weak entities, Rule 13 specialization child tables, Rule 17 n-ary tables), primary key semantics automatically mandate non-nullness and uniqueness. Consequently, Rules 1–5, 10, 12–15, 17, and 18 display no bracket annotations.

SQL Implementation and Column Constraint Mapping

  • ERD Meaning of Rejected SQL Rows:

    • NOT NULL constraint rejects (101, 'Ana', NULL) on Employee.DeptID $ ightarrow$ Rejects an employee belonging to no department (prevents partial participation violation).

    • UNIQUE constraint rejects (A1, 'Lot 1', 101) and (B7, 'Lot 3', 101) on ParkingSpot.EmpID $ ightarrow$ Rejects Employee 101 holding two parking spots (prevents 1:N cardinality expansion in a 1:1 relationship).

    • [UNIQUE, NOT NULL] rejects (P9, '2026-01-04', NULL) and duplicate SSN entries like (P4, ..., 555) / (P8, ..., 555) on Passport.SSN $ ightarrow$ Rejects passports without an owner and individuals with multiple passports.

    • Unconstrained 1:N Foreign Key rejects no rows $ ightarrow$ Allows multiple children per parent and nullable references.

  • SQL Schema Syntax Equivalents:

-- R5 / R6-partial : 1:N, optional
DeptID INT REFERENCES Department(DeptID)

-- R6-total : 1:N, mandatory
DeptID INT NOT NULL REFERENCES Department(DeptID)

-- R7 / R16 : 1:1, optional
EmpID INT UNIQUE REFERENCES Employee(EmpID)

-- R8 / R9 : 1:1, mandatory
SSN INT NOT NULL UNIQUE REFERENCES Person(SSN)

  • Rule 9 Option Selection on Exams:

    • Option A: Merge entities into a single relation (eliminates SQL JOIN overhead).

    • Option B: Retain separate tables and place [UNIQUE, NOT NULL] foreign key in either table (keeps entities independently addressable).

    • On examinations, explicitly state which option was selected and the trade-off justification.

  • Rule 12 Recursive Foreign Key Constraint Behavior:

    • Relation: Employee( EmpID, FirstName, ManagerID )

    • Constraints: ManagerID carries no brackets. It must remain nullable so top-level management without a manager can exist, and must not be UNIQUE because a manager manages multiple employees.

M:N Relationship Translation (Rule 10)

  • Formula and Structure:   PK(associative)={PKA,PKB}\text{PK}(\text{associative}) = \{\text{PK}_A, \text{PK}_B\}   Where both components are simultaneously primary key elements and foreign keys referencing participating entity tables.

  • Verbatim Rule 10 Definition: "Create a new associative table. The PKs of both participating entities are copied into the new table to form a composite PK while being FKs."

  • Worked Example (Student / Enrolls / Course):

    • Student( StudentID, Name )

    • Course( CourseID, Title )

    • Enrollment( StudentID, CourseID, EnrolledDate ) where (StudentID, CourseID) forms the composite primary key and individual foreign keys.

  • Attribute Placement: Relationship attribute EnrolledDate must reside in Enrollment because the associative table is the only relation aware of both participating entities.

  • Mandatory Resolution Principle: All M:N relationships must be resolved into associative entities in ERDs prior to final relational schema transformation.

Weak Entities and Unary/Self-Referential Relationships (Rules 11–12)

  • Weak Entity Primary Key Formula (Rule 11):   PK(weak)={owner PK (as FK),partial key}\text{PK}(\text{weak}) = \{\text{owner PK (as FK)}, \text{partial key}\}

  • Verbatim Rule 11 Definition: "Create a table for the weak entity. Copy the PK of the strong entity into the weak entity's relation as an FK + PK of the weak entity table. Weak entity table should now have a composite PK made up of the owner's PK (FK) and the weak entity's PK."

  • Weak Entity Worked Example:

    • Employee( EmpID, FirstName )

    • Dependent( DependID, Name, EmpID [NOT NULL] ) where DependID + EmpID form the composite primary key.

  • Rule 11 vs. Rule 5 Structural Distinction:

    • Rule 5 copies the owner's primary key into the child table strictly as a foreign key column.

    • Rule 11 copies the owner's primary key into the child table as a foreign key AND combines it into the child's composite primary key.

    • Decision rule: If a child entity cannot be uniquely identified without its parent's key, apply Rule 11.

  • Unary 1:N Definition and Structure (Rule 12):

    • Verbatim text: "Add a recursive FK column to the same table that references the table's own PK."

    • Schema line: Employee( EmpID, FirstName, ManagerID ) where ManagerID is an EmpID referencing Employee(EmpID).

Specialization and Generalization Strategies (Rules 13–14)

  • Rule 13 (Default / Easiest Option):

    • Application: Used for all specialization types (partial, disjoint, overlapping) unless Rule 14 is specifically selected.

    • Result: Parent table holds shared attributes; individual child tables hold subclass-specific attributes plus the parent primary key.

    • Schema Example:

    • Staff( StaffID, Name )

    • Nurse( StaffID, CertLevel )

    • Doctor( StaffID, Specialization )

    • Key structure: StaffID in Nurse and Doctor acts simultaneously as the child relation's primary key and a foreign key referencing Staff(StaffID).

  • Rule 14 (Total Specialization Only):

    • Application: Applied only when specialization is total (denoted by a double line entering the specialization circle).

    • Result: Parent table is completely omitted. Subclass child tables are created containing all superclass attributes replicated alongside subclass-specific attributes.

    • Schema Example:

    • Doctor( StaffID, Name, Specialization )

    • Nurse( StaffID, Name, CertLevel )

  • Hidden Architectural Cost of Rule 14:

    • Omitting the parent table eliminates any centralized target for foreign keys elsewhere in the schema.

    • If external relations reference Staff, deleting Staff leaves those foreign keys with no single relation to reference.

    • Replicates shared attributes across multiple child tables. Check global schema foreign key references before choosing Rule 14.

Placement of Relationship Attributes (Rules 15–16)

  • Unified Placement Principle: Relationship attributes are placed in whichever table contains the relationship reference itself.

  • Rule 15 (1:N Relationship Attribute): Placed in the table on the "N" side of the relationship alongside the foreign key.

    • Example: Employee( EmpID, FirstName, DeptID, DeptEnrolledDate )

  • Rule 16 (1:1 Relationship Attribute): Placed in whichever table received the foreign key.

    • Example: ParkingSpot( SpotNo, Location, EmpID [UNIQUE], AssignedDate )

  • Rule 10 (M:N Relationship Attribute): Placed in the newly created associative table.

    • Example: Enrollment( StudentID, CourseID, EnrolledDate )

Ternary and Higher N-ary Relationship Mapping (Rules 17–18)

  • Rule 17 (All Sides N):

    • Condition: All participating entities in the n-ary relationship have a maximum cardinality of N.

    • Result: Create an associative table whose composite primary key is formed by concatenating the primary keys of all participating entities.

    • Example: Supply( SupplierID, ProjectID, PartID )

  • Rule 18 (At Least One Side is 1):

    • Condition: At least one participating entity has a maximum cardinality of 1.

    • Result: The primary key of the entity on the "1" side enters the associative table strictly as a foreign key column and is excluded from the composite primary key.

    • Primary Key Formula:     PK={PKi:side i has max cardinality N}\text{PK} = \{\text{PK}_i : \text{side } i \text{ has max cardinality } N\}

    • Schema Example: Offering( SemesterID, CourseID, InstructorID ) where InstructorID is a foreign key only, and (SemesterID, CourseID) forms the composite primary key.

  • Mathematical Logic for Rule 18: If Instructor has maximum cardinality 1, the combination of SemesterID and CourseID uniquely determines the Instructor. Including InstructorID in the primary key would allow the same course in the same semester to have multiple instructors, violating the cardinality constraint.

Schema Diagrams and Structural Arrow Rules

  • Verbatim Schema Diagram Definition: "Once you translate an entire ERD into relations (with arrows pointing to the referring table), what you naturally end up with is a Schema Diagram."

  • Arrow Direction Conventions:

    • Draw directed arrows originating from the table/column holding the Foreign Key (FK) and pointing directly to the table/column holding the referenced Primary Key (PK).

    • Examples: SpectatorPhone.Email →\rightarrow Spectator.Email, Ticket.EventID →\rightarrow Event.EventID.

Complete Worked Example: Scenario 3.1 (EVENTCo) ERD Conversion


Scenario 3.1 (EVENTCo) M:N Resolved ERD

  • Conversion Steps Rule-by-Rule:

    • Spectator Entity: Contains composite Name, multivalued PhoneNo, and derived Age. Apply Rules 1, 2, 3, 4 →\rightarrow Spectator( Email, FirstName, LastName, DOB ), SpectatorPhone( Email, PhoneNo ). Drop Age; flatten Name into FirstName, LastName.

    • Event Entity: Contains composite DateTime. Apply Rules 1, 2 →\rightarrow Event( EventID, Venue, Date, StartTime ).

    • Ticket Weak Entity: Identified by Event via issues relationship, with IssueDate/IssueTime relationship attributes. Apply Rules 11, 15 $ ightarrow$ Ticket( TicketID, EventID, Price, Type, IssueDate, IssueTime ) where (TicketID, EventID) is the composite primary key and EventID is a foreign key.

    • TicketPurchase Associative Entity: Resolved M:N between Spectator and Ticket. Apply Rule 10 $ ightarrow$ TicketPurchase( TicketID, Email, PurchaseDate, PurchaseTime, PaymentMethod ).

    • Event Specialization: Disjoint and total ($d$-circle with double line) into Concert and Sport. Apply Rule 13 (rather than Rule 14) $ ightarrow$ Concert( EventID, type ), Sport( EventID, is_international? ) where EventID is child PK and FK referencing Event.

    • ConcertArtist Associative Entity: Resolved M:N between Concert and Artist. Apply Rule 10 $ ightarrow$ ConcertArtist( EventID, ArtistID ).

    • MatchTeam Associative Entity: Resolved M:N between Sport and Team with relationship attribute is_home?. Apply Rules 10, 15 $ ightarrow$ MatchTeam( EventID, TeamID, is_home? ).

    • Artist Entity: Contains multivalued Genre and derived Yrs_in_Industry. Apply Rules 1, 3, 4 $ ightarrow$ Artist( ArtistID, ActiveSince ), ArtistGenre( ArtistID, Genre ).

    • Team Entity: Strong entity. Apply Rule 1 $ ightarrow$ Team( TeamID, EstablishedIn, Division ).

  • Final Finished Relational Schema:

    • Spectator( Email, FirstName, LastName, DOB )

    • SpectatorPhone( Email -> Spectator, PhoneNo )

    • Event( EventID, Venue, Date, StartTime )

    • Concert( EventID -> Event, type )

    • Sport( EventID -> Event, is_international? )

    • Ticket( TicketID, EventID -> Event, Price, Type, IssueDate, IssueTime )

    • TicketPurchase( TicketID -> Ticket, Email -> Spectator, PurchaseDate, PurchaseTime, PaymentMethod )

    • Artist( ArtistID, ActiveSince )

    • ArtistGenre( ArtistID -> Artist, Genre )

    • ConcertArtist( EventID -> Concert, ArtistID -> Artist )

    • Team( TeamID, EstablishedIn, Division )

    • MatchTeam( EventID -> Sport, TeamID -> Team, is_home? )


Resulting Schema Diagram for Scenario 3.1

  • Structural Analysis of Final Schema:

    • Relational Count: Yields 12 relations from 9 initial entities. The 3 additional relations are two multivalued attribute tables (SpectatorPhone, ArtistGenre) and two specialization child tables (Concert, Sport) minus merged abstraction counts.

    • Independent Tables: 4 independent tables (Spectator, Event, Artist, Team). All other 8 relations contain foreign keys referencing these core tables.

  • Critical Exam Trade-off (Rule 13 vs Rule 14 on Event Specialization):

    • Even though the ERD specifies total disjoint specialization ($d$-circle with double line) which triggers Rule 14, Rule 13 must be used.

    • Ticket holds a foreign key EventID pointing to Event. Applying Rule 14 would delete Event, leaving Ticket.EventID referencing a non-existent relation.

  • ERD Structural Anomaly:

    • Ticket is modeled as a weak entity with composite primary key {TicketID, EventID}.

    • TicketPurchase references Ticket using TicketID alone. A foreign key referencing a composite primary key must include all primary key attributes. Either TicketPurchase requires (TicketID, EventID), or TicketID is globally unique and Ticket is a strong entity.

Unexercised ERD Features and Slide Errata

  • Rules Unexercised in Scenario 3.1 ERD:

    • 1:1 Relationships (Rules 7–9)

    • Unary / Self-referential Relationships (Rule 12)

    • Ternary Relationships (Rules 17–18)

  • Inventory of Slide Errata:

    • Typo "Emploee" in Rules 7, 11, and 16.

    • Typo "StundetID" in Rule 10.

    • Inconsistent naming "DepID / DepName" vs. "DeptID / DeptName" across Rules 5, 6, and 15.

    • Typo "PurcahseTime" in Scenario 3.1 ERD diagram.

Course Milestones, Quiz Logistics, and Quick Reference Guide

  • Course Milestones:

    • Project Idea Deadline: Finalize Track A or Track B choice by the end of Week 4.

    • Assessment Format: In-class Quiz 3 administered during the first 15 minutes (10 Multiple Choice Questions + 1 Bonus, 11 minutes total duration, 60 seconds per question, Respondus Lockdown Browser).

  • Quick Reference Q&A Summary Table:

    • Simple Attribute? Rule 1 — Becomes a table column.

    • Composite Attribute? Rule 2 — Flatten container; component parts become columns.

    • Multivalued Attribute? Rule 3 — New table; PK = Owner PK + Attribute Value.

    • Derived Attribute? Rule 4 — Ignore completely; compute via SQL/view.

    • 1:N Foreign Key Location? Rule 5 — Placed in the "N" side table.

    • 1:N Participation Constraints? Rule 6 — Total participation →\rightarrow [NOT NULL]. Partial $ ightarrow$ Nullable. Never UNIQUE.

    • 1:1 Optional Both Sides? Rule 7 — Foreign key in either table + [UNIQUE].

    • 1:1 Mandatory One Side? Rule 8 — Foreign key into mandatory side table + [UNIQUE, NOT NULL].

    • 1:1 Mandatory Both Sides? Rule 9 — Merge into one table, or foreign key either side + [UNIQUE, NOT NULL].

    • Where does UNIQUE come from? Cardinality. 1:1 →\rightarrow UNIQUE. 1:N $ ightarrow$ Never UNIQUE.

    • Where does NOT NULL come from? Participation. Total participation of FK-holding table $ ightarrow$ NOT NULL.

    • Which lines receive bracket annotations? Foreign keys that are NOT part of the primary key.

    • All 4 Annotation Combinations: 1:N Partial →\rightarrow None; 1:N Total →\rightarrow [NOT NULL]; 1:1 Partial →\rightarrow [UNIQUE]; 1:1 Total →\rightarrow [UNIQUE, NOT NULL].

    • M:N Relationship Translation? Rule 10 — New associative table with composite PK made of both owner PKs.

    • Weak Entity Translation? Rule 11 — Owner PK is FK and part of weak entity's composite PK.

    • Self-Referential Entity? Rule 12 — Recursive FK column added to same table.

    • Specialization Default? Rule 13 — Parent table + child tables; parent PK is child PK and FK.

    • Specialization Total? Rule 14 — Omit parent table; replicate shared attributes into every child table.

    • Relationship Attribute Placement? 1:N →\rightarrow Rule 15 ("N" side table); 1:1 →\rightarrow Rule 16 (FK table); M:N $ ightarrow$ Rule 10 (Associative table).

    • Ternary All N? Rule 17 — Associative table PK is composite of all participating PKs.

    • Ternary At Least One Side 1? Rule 18 — 1-side PK is FK only; PK constructed from N-sides only.