Part 1: Translation between models. Part 2: Joins - appendix to the relational models
User Queries 3 Updates Answers User Queries 2 Updates Answers User Queries 1 Updates Answers Updates Queries Answers
Processing of queries and updates
Access to stored data
Paweá Pietrzak Physical database
Todays lecture
ER to Relations
• Translation from ER to Relations • Translation from EER to relations • Joins
ER to Relations
ER to Relations
Step 1
Step 2
For each (strong) entity R, create a relation E that has the same simple attributes as R.
For each weak entity W with owner entity E, create a relation R that has the same simple attributes as W, also add the primary key attributes from the relation that corresponds to E.
1
ER to Relations
ER to Relations
Step 3
Step 4
For each binary 1:1 relation, identify the relations that corresponds to the incoming entities, S and T. Choose one of these and add the primary key from one of them as foreign key in the other.
For each binary 1:N relation, identify the relations that correspond to the incoming entities, S and T. Choose the relation on the relation on the 1 side and add its primary key as foreign key in the relation on the N side.
ER to Relations
ER to Relations
Step 5
Step 6
For each binary N:M relation, identify the relations that correspond to the incoming entities, S and T. Create a new relation R and use the primary keys from S and T as foreign keys in R. If there are attributes on the relation these are also added to R.
For each complex attribute A in R, create a new relation R that contains an attribute for each attribute in A and the primary key for R as foreign key.
ER to Relations
Example Startdate
Address
ENr
1
Step 7
1
Employee
Sex
1
Name GName
Relat. to N
Sex
Relative Name
Supervises
M
Works at
1
FName
Name
N N
For each n-ary relation with n>2, create a new relation S that contains the primary keys from the incoming relations as foreign keys.
Binary 1:1 relations • Manages - Employee and Department
Employee: ENr Departm:
Gname
ENr
Name
FName Address Sex Address Sex
Binary 1:N relations • Supervises - Employee and Employee • Controls - Department and Project
GName FName Address Sex
Name
Number
Manag.
Startdate
Foreign key to the Employee relation
Binary 1:N relations • Supervises - Employee and Employee
Employee: ENr
GName FName Address Sex
Superv. by
Binary 1:N relations • Controls - Department and Project
Departm:
Name
Number
Project:
Name
ContrBy
Manag.
Startdate
3
Binary N:M relations • Works at - Employee and Department • Works with - Employee and Project
WorksAt:
Employ
WorksWith: Employ
Department Project
Hours
Complete relational schema •
Employee: ENr
GName FName Address Sex
Departm:
Name
Number
Project:
Name
ContrBy Name
Man.
Relative:
Enr
WorksAt:
Employ.
Departm.
WorksFor:
Employ.
Project
SupervisedBy
Startdate
GName FName Address Sex
Hours
EER to relations
EER to relations
Assume that we have a superclass C with a number of subclasses Si
Assume that we have a superclass C with a number of subclasses Si
– Four options for translation
Example for option 1 Employee:
ENr
Technician:
ENr
Comp.
ENr
Lang.
Administrator:
Teleph. Salary
Option 1: Create a relation R for C with all attributes in C. Then create a relation Ri for each subclass Si with all attributes from Si and the primary key from R
EER to relations Assume that we have a superclass C with a number of subclasses Si Option 2: Create a relation for each subclass Si. The attributes in each relations are the attributes from C and the attributes from Si.
Works well for disjoint or overlapping, total or partial.
4
Example for option 2 Technician:
ENr
Teleph.
Administrator:
ENr
Teleph.
Salary
EER to relations Assume that we have a superclass C with a number of subclasses Si
Comp.
Salary
Lang.
Option 3: For disjoint subclasses. Create a relation R with all attributes from C and all the subclasses Si. Also add a type attribute, used to discriminate between subclasses.
Works well only for disjoint and total. Why?
Example for option 3
EER to relations Assume that we have a superclass C with a number of subclasses Si
Employee:
ENr
Teleph.
Salary
JobType Comp.
Lang.
Option 4: Create a relation R with all attributes from C and all the subclasses S i. Also add a flag Fi attribute to R for each subclass Si. Good for disjoint and total or partial. Why?
