Transforming EER Diagrams into Relations (the 7 Rules)

From conceptual model to logical tables

Logical design takes the EER diagram from Modules 2–3 and mechanically maps it to relations. The textbook codifies seven steps (Table 4-2). They are technology-independent — they describe *how structures map*, regardless of DBMS.

1. Regular (strong) entity → a relation. The identifier becomes the primary key; simple attributes become columns. A composite attribute is replaced by its *component* parts (store street, city, zip, not address). A multivalued attribute becomes a separate relation whose key is the parent's PK + the multivalued attribute.
2. Weak entity → a relation whose primary key is the owner's PK + the weak entity's partial identifier (a composite), with the owner's PK also serving as a foreign key. (Or substitute a surrogate key.)
3. Binary relationship →
4. - 1:M — put the PK of the *one* side into the *many* side as a foreign key ('the PK migrates to the many side').
5. - M:N — create a new associative relation whose primary key is the composite of both entities' PKs (each also a foreign key).
6. - 1:1 — put the foreign key on the optional side (to avoid nulls).
7. Associative entity → like an M:N: its own relation, keyed by an assigned identifier if one exists, otherwise the composite of the participants' keys.
8. Unary relationship → 1:M uses a recursive foreign key in the same relation; M:N needs a separate associative relation (e.g. bill-of-materials assembly/component).
9. Ternary (n-ary) relationship → an associative relation whose primary key is the composite of all three participating entities' keys.
10. Supertype/subtype → a relation for the supertype plus one for each subtype, all sharing the same primary key; the discriminator lives in the supertype, and each subtype holds only its unique attributes.

Worked example: mapping a small EER

Given an EER with entity EMPLOYEE (a unary *manages* 1:M relationship), a weak entity DEPENDENT owned by EMPLOYEE, and an M:N WORKS_ON between EMPLOYEE and PROJECT:

EMPLOYEE(EmpID, Name, MgrID)          -- MgrID = recursive FK -> EmpID  (rule 5, unary 1:M)
DEPENDENT(EmpID, DepName, Birthdate)  -- PK = (EmpID, DepName); EmpID FK -> EMPLOYEE  (rule 2, weak)
PROJECT(ProjID, Title, Budget)
WORKS_ON(EmpID, ProjID, Hours)        -- PK = (EmpID, ProjID); both FKs  (rule 3, M:N)

Notice the M:N WORKS_ON *gained its own relation* with a composite key, the weak DEPENDENT borrowed its owner's key into a composite PK, and the unary *manages* became a recursive FK rather than a new table.