Entity Relationship to Relational Mapping

1
Entity Relationship to Relational Mapping
2
Entity Relationship to Relational Mapping

• For each regular entity type E
• Create a relation R that includes all the simple
  attributes of E
• Include only the simple component attributes of a
  composite attribute
• Designate a (primary)key
• If the key of E is composite, the set of simple
  attributes that form it will together form the
  primary key of R

3
Entity Relationship to Relational Mapping

• For each weak entity type W with the owner entity
  type E
• Create a relation R that includes all the simple
  attributes of W (or simple components of a
  composite attribute)
• Include primary key attributes of E as a foreign
  key of R this takes care of the identifying
  relationship type of W
• Primary key of R is the combination of the
  primary key of E and the partial key of W

4
Entity Relationship to Relational Mapping

• For each binary 11 relationship type R
• Identify S and T that participates in R
• Choose one of the relations (say S) and include
  the primary key of T as a foreign key of S
• Better to choose an entity type with total
  participation in R

5
Entity Relationship to Relational Mapping

• For each binary 1N relationship type R
• Identify the entity type S at the N-side of R
• Include the primary key of T (1-side entity type
  in R) as a foreign key in S

6
Entity Relationship to Relational Mapping

• For each binary NM relationship type R
• Create a new relation S to represent R
• Include the primary keys of participating
  entities in R as foreign key attributes in S
  their combination will form the primary key of S

7
Entity Relationship to Relational Mapping

• For each multi-valued attribute A.
• Create a relation R that includes an attributes
  corresponding to A plus the primary key attribute
  K (as a foreign key in R) of the relation that
  represents as entity or relationship type that
  has a A as an attribute.

8
Entity Relationship to Relational Mapping

• For each n-ary (ngt2) relationship type R,
• create a new relation S to represent R
• include the primary keys of participating
  entities in R as foreign key attributes in S
• include any attributes of R as attributes in S
• primary key of S is usually the combination of
  all the foreign keys that reference the relations
  representing the participating entity types

9
Entity Relationship to Relational Mapping

• For a superclass C with m subclasses
• S1, S2, , Sm, there are 4 options
• A. create L(k, a1,, an) and Li(k, attr(Si)), 1?
  i? m.
• B. create L(k, a1,, an, attr(Si)), 1? I? m..
• C. create L(k, a1,, an, attr(Si) , attr(Sm),t)
• D. create L(k, a1,, an, attr(Si), ,
  attr(Sm),t1,,tm).
• where
• - (k, a1,, an) are attributes of C and k is
  the key - attr(Si) denotes the
  attributes of Si, 1? i? m - t and ti,
  1? i? m are type attributes.

10
Regular Entity
CUSTOMER
11
Composite Attribute
CUSTOMER
12
Multi-Valued Attribute
EMPLOYEE
EMPLOYEE_DEDUCTION
13
Weak Entity
BUILDING
ROOM
14
1 to 1 Relationship
EMPLOYEE
POSITION
15
1 to Many Relationship
EMPLOYEE
POSITION
16
Many to 1 Relationship
EMPLOYEE
POSITION
17
Many to Many Relationship
EMPLOYEE_POSITION
EMPLOYEE
POSITION
18
Unary 1 to 1 Relationship
PERSON
19
Unary 1 to Many Relationship
WOMAN
20
Unary Many to Many Relationship
PERSON
SIBLING
21
Associative Entity with Identifiers
22
Associative Entity with Identifiers
EMPLOYEE
EMPLOYMENT_HISTORY
POSITION