Transforming EER Diagrams into Relations

1
Transforming EER Diagrams into Relations

• Step 1 Mapping Regular Entities to Relations
• Simple attributes E-R attributes map directly
  onto the relation
• Composite attributes Use only their simple,
  component attributes
• Multivalued AttributeBecomes a separate relation
  with a foreign key taken from the superior entity
• Identifier becomes the primary key

2
Figure 5-8 Mapping a regular entity
(a) CUSTOMER entity type with simple attributes
(b) CUSTOMER relation
3
Figure 5-9 Mapping a composite attribute
(a) CUSTOMER entity type with composite attribute
(b) CUSTOMER relation with address detail
4
Figure 5-10 Mapping an entity with a multivalued
attribute
(a)
Onetomany relationship between original entity
and new relation
5
Transforming EER Diagrams into Relations (cont.)

• Step 2 Mapping Weak Entities
• Becomes a separate relation with a foreign key
  taken from the superior entity
• Primary key composed of
• Partial identifier of weak entity
• Primary key of identifying relation (strong
  entity)

6
Figure 5-11 Example of mapping a weak entity a)
Weak entity DEPENDENT
7
Figure 5-11 Example of mapping a weak entity
(cont.) b) Relations resulting from weak entity
NOTE the domain constraint for the foreign key
should NOT allow null value if DEPENDENT is a
weak entity
Foreign key
8
Transforming EER Diagrams into Relations (cont.)

• Step 3 Mapping Binary Relationships
• One-to-ManyPrimary key on the one side becomes a
  foreign key on the many side
• Many-to-ManyCreate a new relation with the
  primary keys of the two entities as its primary
  key
• One-to-OnePrimary key on the mandatory side
  becomes a foreign key on the optional side avoid
  storing null values in foreign key

9
Figure 5-12 Example of mapping a 1M relationship
a) Relationship between customers and orders
Note the mandatory one
Again, no null value in the foreign keythis is
because of the mandatory minimum cardinality
Foreign key
10
Figure 5-13 Example of mapping an MN relationship
a) Completes relationship (MN)
11
Figure 5-13 Example of mapping an MN
relationship (cont.)
b) Three resulting relations
New intersection relation
12
Figure 5-14 Example of mapping a binary 11
relationship
a) In_charge relationship (11)
13
Figure 5-14 Example of mapping a binary 11
relationship (cont.)
b) Resulting relations
14
Transforming EER Diagrams into Relations (cont.)

• Step 4 Mapping Associative Entities
• Identifier Not Assigned
• Default primary key for the association relation
  is composed of the primary keys of the two
  entities (as in MN relationship)
• Identifier Assigned, because 1)It is natural and
  familiar to end-users or 2) Default identifier
  may not be unique
• Use identifier as primary key
• The primary key of the two participating entity
  types becomes foreign key in the associative
  entity

15
Figure 5-15 Example of mapping an associative
entity
a) An associative entity
16
Figure 5-15 Example of mapping an associative
entity (cont.)
b) Three resulting relations
17
Figure 5-16 Example of mapping an associative
entity with an identifier
a) SHIPMENT associative entity
18
Figure 5-16 Example of mapping an associative
entity with an identifier (cont.)
b) Three resulting relations
Primary key differs from foreign keys
Why not use combination of Customer_ID and
Vendor_ID?
19
Transforming EER Diagrams into Relations (cont.)

• Step 5 Mapping Unary Relationships
• One-to-ManyRecursive foreign key in the same
  relation
• Many-to-ManyTwo relations
• One for the entity type
• One for an associative relation in which the
  primary key has two attributes, both taken from
  the primary key of the entity

20
Figure 5-17 Mapping a unary 1N relationship
(a) EMPLOYEE entity with unary relationship
(b) EMPLOYEE relation with recursive foreign key
21
Figure 5-18 Mapping a unary MN relationship
(a) Bill-of-materials relationships (MN)
(b) ITEM and COMPONENT relations
22
Transforming EER Diagrams into Relations (cont.)

• Step 6 Mapping Ternary (and n-ary) Relationships
• One relation for each entity and one for the
  associative entity
• Associative entity has foreign keys to each
  entity in the relationship
• Default primary key of associative entity
  consists of the three primary keys for the
  participating entity types
• Additional attributes may be required to from a
  unique primary key for the associative entity

23
Figure 5-19 Mapping a ternary relationship
a) PATIENT TREATMENT Ternary relationship with
associative entity
24
Figure 5-19 Mapping a ternary relationship (cont.)
b) Mapping the ternary relationship PATIENT
TREATMENT
It would be better to create a surrogate key like
Treatment
Remember that the primary key MUST be unique
But this makes a very cumbersome key
This is why treatment date and time are included
in the composite primary key
25
Transforming EER Diagrams into Relations (cont.)

• Step 7 Mapping Supertype/Subtype Relationships
• One relation for supertype and for each subtype
• Supertype attributes (including identifier and
  subtype discriminator) go into supertype relation
• Subtype attributes go into each subtype primary
  key of supertype relation also becomes primary
  key of subtype relation
• One attribute of the supertype functions as the
  subtype discriminator

26
Figure 5-20 Supertype/subtype relationships
27
Figure 5-21 Mapping Supertype/subtype
relationships to relations
These are implemented as one-to-one relationships