Database Management Systems
Relational Database Design by ER-to-Relational
Mapping
Correspondence with E-R Model
• Relations (tables) correspond with entity types and with many-to-
many relationship types
• Rows correspond with entity instances and with many-to-many
relationship instances
• Columns correspond with attributes
• There is no such thing as a multi-valued attribute in relational model
• An ER model does not include FK information
2
ER-to-Relational Mapping Algorithm
• Mapping ER Model Constructs to Relations
• Mapping of Regular Entity Types
• Mapping of Composite attributes.
• Mapping of Multivalued attributes.
• Mapping of Weak Entity Types
• Mapping of Binary 1:1 Relation Types
• Mapping of Binary 1:N Relationship Types.
• Mapping of Binary M:N Relationship Types.
• Mapping of N-ary Relationship Types.
• Mapping EER Model Constructs to Relations
• Options for Mapping Specialization or Generalization.
3
Mapping Regular Entities to Relations
• Mapping Regular Entities to Relations
• Simple attributes: E-R attributes map directly onto the relation
• Composite attributes: Use only their simple, component attributes
• Multivalued Attribute: Becomes a separate relation with a foreign key taken
from the superior entity
4
Mapping Regular Entities
(a) CUSTOMER
entity type with
simple
attributes
(b) CUSTOMER relation
5
Mapping of an entity with a composite
attribute
(a) CUSTOMER
entity type with
composite
attribute
(b) CUSTOMER relation with address detail
6
Mapping an entity with a multivalued
attribute
(a)
Multivalued attribute becomes a separate relation with foreign key
(b)
7
Mapping Weak Entities
• 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)
8
Mapping a weak entity DEPENDENT
NOTE: the domain
constraint for the foreign key
should NOT allow null value
if DEPENDENT is a weak
entity
Foreign key
Composite primary key
9
Mapping Binary Relationships
• Mapping Binary Relationships
• One-to-Many–Primary key on the one side becomes a foreign key on the
many side
• Many-to-Many–Create a new relation with the primary keys of the two
entities as its primary key
• One-to-One–Primary key on mandatory side becomes a foreign key on
optional side
10
Mapping a 1:M relationship
a) Relationship between customers and orders
Note the mandatory one
b) Mapping the relationship
Again, no null value in the
foreign key…this is because
of the mandatory minimum
cardinality. 11
Foreign key
Mapping an M:N relationship
The Completes relationship will need to become a separate relation.
12
Mapping an M:N relationship
Composite primary key
Foreign key
new
Foreign key
intersection
relation
13
Mapping a binary 1:1 relationship
Often in 1:1 relationships, one direction is optional
Foreign key goes in the relation on the optional side, matching the primary key on
14
the
Mapping Unary Relationships
• Mapping Unary Relationships
• One-to-Many–Recursive foreign key in the same relation
• Many-to-Many–Two 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
15
Mapping a unary 1:N relationship
(a) EMPLOYEE
entity with unary
relationship
(b)
EMPLOYEE
relation with
recursive
foreign key
16
Mapping a unary M:N relationship
(a) Bill-of-materials
relationships (M:N)
(b) ITEM and
COMPONENT
relations
17
Mapping of n-ary relationships
• For each n-ary relationship type R, where n>2, create a new
relationship S to represent R.
• Include as foreign key attributes in S the primary keys of the relations that represent the
participating entity types.
• Also include any simple attributes of the n-ary relationship type (or simple components
of composite attributes) as attributes of S.
18
Mapping the n-ary relationship type
SUPPLY
19
Mapping Specialization/Generalization
to Relations
• Convert each specialization with m subclasses {S1, S2,….,Sm} and
generalized superclass C, where the attributes of C are {k,a1,…an} and
k is the (primary) key, into relational schemas using one of the four
following options:
• Option 8A: Multiple relations-Superclass and subclasses
• Option 8B: Multiple relations-Subclass relations only
• Option 8C: Single relation with one type attribute
• Option 8D: Single relation with multiple type attributes
20
Mapping Specialization/Generalization
to Relations
• Option 8A: Multiple relations-Superclass and subclasses
• Create a relation L for C with attributes Attrs(L) = {k,a1,…an} and PK(L) = k.
Create a relation Li for each subclass Si, 1 < i < m, with the attributesAttrs(Li) =
{k} U {attributes of Si} and PK(Li)=k. This option works for any specialization
(total or partial, disjoint of over-lapping).
• Option 8B: Multiple relations-Subclass relations only
• Create a relation Li for each subclass Si, 1 < i < m, with the attributes Attr(Li) =
{attributes of Si} U {k,a1…,an} and PK(Li) = k. This option only works for a
specialization whose subclasses are total (every entity in the superclass must
belong to (at least) one of the subclasses).
21
EER diagram notation for an attribute-
defined specialization on JobType
22
Mapping using option 8A
23
Mapping using option 8B
24
Mapping Specialization/Generalization
to Relations(contd.)
• Option 8C: Single relation with one type attribute
• Create a single relation L with attributes Attrs(L) = {k,a1,…an} U {attributes of
S1} U…U {attributes of Sm} U {t} and PK(L) = k. The attribute t is called a type
(or discriminating) attribute that indicates the subclass to which each tuple
belongs
• Option 8D: Single relation with multiple type attributes
• Create a single relation schema L with attributes Attrs(L) = {k,a1,…an} U
{attributes of S1} U…U {attributes of Sm} U {t1, t2,…,tm} and PK(L) = k. Each ti,
1 < I < m, is a Boolean type attribute indicating whether a tuple belongs to the
subclass Si.
25
Mapping using option 8C
26
Mapping using option 8D
27
Mapping Exercise (An ER schema for a
SHIP_TRACKING database.)
28