DB Models. Mapping ER-to-Relational Shema

Lecture 3
DB Models. Mapping ER-to-Relational Shema

Mapping ER Model into the Relational Model

Step 1:For each regular entity type e in the ER schema, we create a relation r that includes all the simple attributes of e. For a composite attribute we include only the simple component attributes. We chose one of the key attributes of e as primary key for r. If the chosen key of r is composite, then the set of simple attributes that form it will together form the primary key of r.

Department

deptname location

p.k.

Student

stuid stfn stln credits

p.k.

Class

course# shed room

p.k.

Step 2: For each weak entity type w in the ER schema with owner entity type e, we create a relation r and include all simple attributes (or simple components of composite attributes) of w as attributes of r. In addition, we include as foreign key attributes of r the primary key attribute(s) of the relation that corresponds to the owner entity type e; this takes care of the identifying relationship type of w. The primary key of r is the combination of the primary key of the owner and the partial key of the weak entity type w.

Faculty

f.k.

deptname facid facname rank

p.k.

Step 3: For each binary 1:1 relationship type r in the ER schema, we identify the relations s and t that correspond to the entity types participating in r. We choose one of the relations, s say, and include as foreign key in s the primary key of t. It is better to choose an entity type with total participation in r in the role of s. We include all the simple attributes (or simple components of a composite attribute) of the 1:1 relationship type r as attributes of s.

Step 4: For each regular (nonweak) binary 1:n relationship type r, we identify the relation s that represents the participating entity type at the n-side of the relationship type. We include as foreign key in s the primary key of the relation t that represents the other entity type participating in r; this is because each entity instance on the n-side is related to at most one entity instance on the 1-st of the relationship type. We include any simple attributes (or simple components of a composite attribute) of the 1:n relationship type as attributes of s.

Student

Class

Step 5: For each binary m:n relationship type r, we create a new relation s to represent r. We include as foreign key attributes in s the primary keys of the relations that represent the participating entity types; their combination will form the primary key of s. We also include any simple attributes of the m:n relationship type (or simple components of a composite attribute) as attributes of s.

Enrolment

f.k.

f.k.

course# stuid grade

p.k.

Step 6: For each multivalued attribute a, we create a new relation r that includes an attribute corresponding to a plus the primary key attribute k of the relation that represents the entity type or relationship type that has a as an attribute. The primary key of r is then combination of a and k. If the multivalued attribute is composite, we include its simple components.

Faculty_location

facultyid location

p.k.

Step 7: For each n-ary relationship type r, n>2, we create a new relation s to represent r. We include as foreign key attributes in s the primary keys of the relations that represent the participating entity types. We also include any simple attributes of the n-ary relationship type (or simple components of complete 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. however, if the participation constrain (min, max) of one of the entity types e participating in r has max = 1, then the primary key of s can be the single foreign key attribute that references the relation e' corresponding to e; this is because in this case each entity e in e will participate in at most one relationship instance of r and can hence uniquely identify that relationship instance.