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

Vocabulary flashcards covering the 18 ERD-to-Relational transformation rules, constraint mappings, and structural concepts from the COMP 440 Week 3 lecture.

Rule 1 (Strong Entity)

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 (Composite Attribute)

Flatten the attribute by removing the parent container and inserting only its simple component parts as columns.

Rule 3 (Multivalued Attribute)

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 (Derived Attribute)

Ignore from the relational schema completely because they are calculated dynamically via SQL queries/views.

Four Attribute Shapes Diagram

Diagram showing how four attribute shapes lead to four distinct outcomes: simple attribute (one column), composite attribute (flattened columns), multivalued attribute (new table), and derived attribute (no column).

Rule 5 (Binary 1:N)

Place the Primary Key (PK) of the '1' side entity into the table on the 'N' side as a Foreign Key (FK).

Rule 6 (1:N with Participation)

Total participation on the 'N' side makes the FK NOT NULL. Partial participation leaves the FK nullable (allows NULL).

Rule 7 (1:1, Both Partial)

Place the FK in either table with a UNIQUE constraint. Pick the side that participates more often to minimise NULLs.

Rule 8 (1:1, Total + Partial)

Copy the PK of the partial entity into the total entity's table as an FK with NOT NULL and UNIQUE constraints.

Rule 9 (1:1, Both Total)

Merge both entities into a single relation (removing join overhead and NULL checks), or place an FK in either side with NOT NULL and UNIQUE constraints.

Three 1:1 Cases Diagram

Diagram illustrating the schema lines and foreign key placement for Rule 7 (both partial), Rule 8 (total + partial), and Rule 9 (both total).

Rule 10 (M:N Relationships)

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.

Rule 11 (Weak Entity)

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, forming a composite PK made up of the owner's PK (FK) and the weak entity's partial key.

Rule 12 (Unary 1:N / Self-Referential)

Add a recursive FK column to the same table that references the table's own PK.

Rule 13 (Specialization Default)

Create a parent table with shared attributes plus one child table for each subclass holding subclass attributes and the parent PK (which serves as both the child's PK and an FK).

Rule 14 (Specialization TOTAL Only)

Omit the parent table entirely and create child tables only, replicating all superclass attributes into every child table.

Rule 15 (Relationship Attribute, 1:N)

Place the relationship attribute on the 'N' side table, alongside the foreign key.

Rule 16 (Relationship Attribute, 1:1)

Place the relationship attribute in whichever table received the foreign key.

Rule 17 (n-ary, All Sides N)

Create an associative table whose primary key is the composite of all participating primary keys.

Rule 18 (n-ary, At Least One Side 1)

The PK of the '1' side entity goes in as an FK only (excluded from the composite PK), and the PK is built from the 'N' sides only.

Cardinality Annotation Source

Cardinality determines uniqueness: 1:1 relationships map to a UNIQUE constraint on the FK, whereas 1:N relationships are never UNIQUE.

Participation Annotation Source

Participation determines nullability: total participation of the table holding the FK maps to a NOT NULL constraint, whereas partial participation leaves the FK nullable.

2x2 Annotation Combinations Diagram

Grid displaying all four annotation combinations (no annotation, NOT NULL only, UNIQUE only, UNIQUE + NOT NULL) based on cardinality and participation.

Schema Diagram

The structure resulting from translating an ERD into relations, represented as boxes (relations with PK and FK rows labelled) joined by arrows pointing from the FK to the PK it references.

Associative Table

A relation created to resolve M:N or n-ary relationships whose primary key is formed by combining foreign keys referencing the participating entities.

Recursive FK

A foreign key column within a table that references that same table's own primary key, used to model unary (self-referential) 1:N relationships.