EntityRelationship to Relational Mapping

1
Entity-Relationship to Relational Mapping
MSIT 124 Database Systems
2
Entity-Relationship to Relational Mapping

• 1. 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
ER to Relational Mapping (Cont.)

• 2. 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 component 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
ER to Relational Mapping (Cont.)

• For each binary 11 relationship type R
• identify S and T that participate in R
• choose one of the relations (say S) and include
  the primary key of T as a foreign key in S
• better to choose an entity type with total
  participation in R.
• 4. 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

5
ER to Relational Mapping (Cont.)

• 5. 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.
• 6. 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.

6
ER to Relational Mapping (Cont.)

• 7. 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

7
EER to Relational Mapping

• 8. 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.

8
EER to Relational Mapping

• Option B will lose the objects only in C if the
  specialization is not total.
• Inherited attributes will be stored redundantly
  in option B if the specialization is not
  disjoint.
• Option C works only when the specialization is
  disjoint.
• Options C and D may introduce many null values.

9
Regular Entity
Address
Name
SSN
CUSTOMER
CUSTOMER
SSN Name Address
10
Composite Attribute
Maiden
Last
Title
Middle
First
Suffix
Name
Address
SSN
CUSTOMER
CUSTOMER
SSN First Middle Last Maiden Title Suffix Address
11
Multi-valued attribute
SSN
Name
Deduction
Address
EMPLOYEE
EMPLOYEE
SSN Name Address
EMPLOYEE_DEDUCTION
SSN Deduction
12
Weak Entity

heating
ROOM
BUILDING
Contains
M
N
BUILDING
Name Heating
ROOM
Building_Name Room
13
1 to 1 relationship
Effective Date
Job_ID
Name
SSN
EMPLOYEE
POSITION
Fills
1
1
EMPLOYEE
SSN Name Job_ID Effective_Date
POSITION
Job_ID
14
1 to many relationship
Effective Date
Job_ID
Name
SSN
EMPLOYEE
Fills
POSITION
N
1
EMPLOYEE
SSN Name
POSITION
Job_ID SSN Effective_Date
15
Many to 1 relationship
Effective Date
Job_ID
Name
SSN
EMPLOYEE
POSITION
Fills
N
1
EMPLOYEE
SSN Name Job_ID Effective_Date
POSITION
Job_ID
16
Many to many relationship
Effective Date
Job_ID
Name
SSN
EMPLOYEE
POSITION
Fills
M
N
EMPLOYEE
SSN Name
EMPLOYEE_POSITION
SSN Job_ID Effective_Date
POSITION
Job_ID
17
Unary 1 to 1 Relationship
Husband
SSN
1
Has Spouse
PERSON
1
Name
Wife
PERSON
SSN Name Wife_Id
18
Unary 1 to Many Relationship
Mother
SSN
1
Has Daughter
WOMAN
N
Name
Daughter
WOMAN
SSN Name Mother_Id
19
Unary Many to Many Relationship
SSN
M
Has Sibling
PERSON
Name
N
PERSON
SSN Name
SIBLING
Sibling1_Id Sibling2_Id
20
Ternary Relationship
type
training
Talent
name
N
Performer
Performs
M
address
L
Show
dates
location
title
21
Ternary Relationship (contd)
PERFORMER
Name Address
TALENT
Type Training
SHOW
Title Location Date
PERFORMS
Performer_name Talent_type Show_title
22
Associative Entity with identifier
Effective Date
Job_ID
Name
SSN
EMPLOYEE
Employment History
POSITION
M
N
History_Id
EMPLOYEE
SSN Name
EMPLOYMENT_HISTORY
History_ID SSN Job_ID Effective_Date
POSITION
Job_ID
23
A supertype and subtypes
account
balance
U
U
max overdraft
24
A supertype and subtypes (contd)
ACCOUNT
Account Balance
SAVINGS_ACCOUNT
S_Account Interest_rate
CHECKING_ACCOUNT
C_Account Max_Overdraft