The seven steps of mapping ER to relations
1 regular entity sets; 2 weak entity sets; 3 binary 1:1; 4 binary 1:N; 5 binary M:N; 6 multivalued attributes; 7 n-ary relationship sets.
Step 1: regular (strong) entity set
Create a relation R that includes all the simple attributes of E.
Step 2: weak entity set
Create a relation with all its simple attributes, and include the primary key of the owner as foreign key attributes — DEPENDENT (Essn, name, Sex, Birthdate, Relationship).
The three approaches for a binary 1:1 relationship
(1) Foreign key approach; (2) merged relation option; (3) cross-reference (relationship relation) option.
1:1 foreign key approach: which relation gets the foreign key?
Better to choose the entity set with total participation in R to play the role of S (the relation that includes the other's primary key).
When is the merged relation option appropriate for a 1:1 relationship?
When both participations are total.
MANAGES (1:1): how is it mapped?
EMPLOYEE's primary key goes into DEPARTMENT as Mgr_ssn, with Start_date as Mgr_start_date.
Step 4: binary 1:N relationship
In the relation S at the N-side, include the primary key of the other entity set as a foreign key, plus the relationship's simple attributes.
SUPERVISION (1:N recursive on EMPLOYEE): how is it mapped?
The primary key of EMPLOYEE is added to EMPLOYEE itself as a foreign key, Super_ssn.
Step 5: binary M:N relationship
Create a new relation S with the primary keys of the participating entity sets as foreign keys and any simple attributes — WORKS_ON (Essn, Pno, Hours).
Step 6: multivalued attribute A of an entity with key K
Create a new relation R whose primary key is the combination of A and K — DEPT_LOCATIONS (Dnumber, Dlocation).
Step 7: n-ary relationship (with the example)
Create a new relation with the primary keys of all participating entity sets as foreign keys and any simple attributes — SUPPLY (Sname, Proj_name, Part_no, Quantity).
ER → relational: 1:1/1:N, M:N, n-ary
1:1 or 1:N: a foreign key. M:N: a "relationship" relation and two foreign keys. n-ary: a "relationship" relation and n foreign keys.
ER → relational: composite and multivalued attributes
Composite: a set of simple component attributes. Multivalued: a relation and a foreign key.
Option 8A
Multiple relations — superclass and subclasses: L for C with {k, a1…an}; Li for each subclass with {k} ∪ its attributes. Works for any specialization.
Option 8B and when it works
Subclass relations only, each with its attributes ∪ {k, a1…an}. Only works for total subclasses; only recommended if disjoint.
Option 8C and when it works
A single relation with all attributes plus one type (discriminating) attribute t naming each tuple's subclass. Works only for disjoint subclasses.
Option 8D and its example
A single relation with all attributes plus a Boolean type attribute for each subclass; used for overlapping subclasses — PART with Mflag and Pflag.