Translating ER Schema to Relational Model

1
Translating ER Schema to Relational Model
Other ER Constructs
2
Weak entity types Example
3
Weak entity types

• Consider weak entity type E
• A relation for E, say E
• Attributes of E
• attributes of E in ER keys for all identifying
  entity types.
• Key for E
• key for E in ER keys for all identifying entity
  types.
• Foreign Keys
• Identify appropriate FKs from E to the
  identifying entity types.

4
Weak entity types Example
Dept (dNumber, dName) Course (cNumber, dNumber,
cName) PRIMARY KEY (Dept) ltdNumbergt PRIMARY
KEY (Course) ltcNumber, dNumbergt FOREIGN KEY
Course (dNumber) REFERENCES Dept (dNumber)
5
ISA Relationship Types
An UGStudent instance will be represented in both
Student relation as well as UGStudent relation (
similarly GradStudent )
6
ISA Relationship types Method 1 with 3 relations
Student (sNumber, sName) UGStudent (sNumber,
year) GradStudent (sNumber, program) PRIMARY KEY
(Student) ltsNumbergt PRIMARY KEY (UGStudent)
ltsNumbergt PRIMARY KEY (GradStudent)
ltsNumbergt FOREIGN KEY UGStudent (sNumber)
REFERENCES Student (sNumber) FOREIGN KEY
UGStudent (sNumber) REFERENCES Student
(sNumber)
An UGStudent will be represented in both Student
relation as well as UGStudent relation (similarly
GradStudent)
7
ISA Relationship types Method 2 with 1 relation
Student (sNumber, sName, year, program) PRIMARY
KEY (Student) ltsNumbergt Note There will be
null values in the relation.
8
ISA Relationship types Method 3 with 2 relations
UGStudent (sNumber, sName, year) GradStudent
(sNumber, sName, program) PRIMARY KEY
(UGStudent) ltsNumbergt PRIMARY KEY (GradStudent)
ltsNumbergt
Based on semantics, any student will be
represented in only one or possibly both
relations as appropriate.
9
ISA Relationship types Method 4 with 4 relations
Student (sNumber, sName) UGStudent (sNumber,
sName, year) GradStudent (sNumber, sName,
program) UGGradStudent (sNumber, sName,
year, program) PRIMARY KEY
(Student) ltsNumbergt PRIMARY KEY (UGStudent)
ltsNumbergt PRIMARY KEY (GradStudent)
ltsNumbergt PRIMARY KEY (UGGradStudent) ltsNumbergt
Any student will be represented in only one of
the relations as appropriate.
10
Summary ER to RM !

• Study IS-A relationships in ER
• Map to 1 relation
• Map to 2 relations
• Map to 3 relations
• Map to 4 relations
• Now consider overlap and covering IS-A
  relationships constraints !
• Which mapping naturally supports which semantics
  ?