Analysis and Design of Data Systems. ER to Relational Mapping. (Lecture 10)

are here

of E:

For each strong entity type E in the ER schema create a corresponding relation R and include:

EMPLOYEE (Bdate, Address, Salary, Sex)

Simple attributes of EMPLOYEE entity type are:
Bdate, Address, Salary, Sex

attributes of a composite attribute:

Simple component attributes of a composite attribute Name of EMPLOYEE entity type are: Fname, Minit, Lname

EMPLOYEE (Bdate, Address, Salary, Sex, Fname, Minit, Lname)

attributes of E as the primary key for R. If the chosen key of E is a composite, then the set of simple attributes that form it will together form the primary key of R.

The key attribute of EMPLOYEE is Ssn

EMPLOYEE (Bdate, Sex, Salary, Address, Fname, Minit, Lname, Ssn)

Minit, Lname, Ssn)

For each weak entity type W in the ER schema with owner entity type E, create a relation R and include:

2.1) Include all the simple attributes (or simple components of composite attributes) of W as attributes of R.

DEPENDENT(Sex, Bdate, Relationship)

Minit, Lname, Ssn)

For each weak entity type W in the ER schema with owner entity type E, create a relation R and include:

2.2) Include as foreign key attributes of R, the primary key attribute(s) of the relation(s) that correspond to the owner entity type(s) E. Also include the attributes of partial key of W. These are the attributes of the primary key of W.

DEPENDENT(Sex, Bdate, Relationship, Essn, Dependent_name)

Relationship R

T

S

R

1

1

Case 1: One of the entity types, say T, has total participation and the second entity type has partial participation: Then Include as a foreign key in T the primary key of S. Include in S all the simple attributes of R.

Case 2: Both entity types have partial participations: Then arbitrary select one of the entity types, say T, and include its primary key in the relation corresponding to the second entity type (S) as a foreign key. Also, include in the S all the simple attributes of the 1:1 relation (K).

Case 3: Both entity types have total participations: Then merge two entity types in a single relation

Step 3: Mapping of Binary 1:1 Relationships

K

(Bdate, Sex, Salary, Address, Fname, Minit, Lname, Ssn)

Start_date

are entity types participating in 1:N Relationship R

T

S

R

1

N

Identify the relation that represents the participating entity type at the N-side of the relationship type (T)
Include as foreign key in T the primary key of the relation S
Include any simple attributes (K) (or simple components of composite attributes) of the 1:N relationship type as attributes of T

K

(Bdate, Sex, Salary, Address, Fname, Minit, Lname, Ssn, Dno)

R, create a new relation F to represent R.
Include as foreign key attributes in F the primary keys of the relations that represent the participating entity types (S and T); their combination will form the primary key of F.
Include any simple attributes of the M:N relationship (K)(or simple components of composite attributes) as attributes of F

Sex, Salary, Address, Fname, Minit, Lname, Ssn, Dno)

WORKS_ON (Pno, Hours, Essn)

a new relation R.
Include in R an attribute corresponding to A
Include as a foreign key in R the primary key attribute of the relation that represents the entity type or relationship that has A as a multivalued attribute.
The primary key of R is the combination of A and K.
If the multivalued attribute is composite, we include its simple components.

where n > 2, create a new relation S to represent R.
Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types.
Include any simple attributes of the n-ary relationship type (or simple components of composite attributes) as attributes of S
The primary key of S is usually a combination of all the foreign keys that reference the relations representing the participating entity types.
if the cardinality constraints on any of the entity types E participating in R is 1, then the primary key of S should not include the foreign key attribute that references the relation E’ corresponding to E