1 / 14

ER & EER to Relational Mapping

ER & EER to Relational Mapping. ER to Relational Mapping.

alexia
Download Presentation

ER & EER to Relational Mapping

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ER & EER to Relational Mapping Chapter 9

  2. ER to Relational Mapping • Step 1: For each regular entity type E in the ER schema, create a relation R that includes all the simple attributes of E. Include on the simple component attributes of a composite attribute. Choose one of the key attributes of E as primary key for R. If the chosen key of E is composite, the set of simple attributes that form it will together form the primary key of R. Chapter 9

  3. ER to Relational Mapping(con’t) • Step 2: For each weak entity type W in the ER Schema with owner entity type E, create a relation R, and include all simple attributes (or simple components of composite attributes of W as attributes of R. In addition, include as foreign key attributes of R the primary key attribute(s) of the relation(s) that correspond to the owner entity type(s). Chapter 9

  4. ER to Relational Mapping(con’t) • Step 3: For each binary 1:1 relationship type R in the ER schema, identify the relations S and T that correspond to the entity types participating in R. 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. Include the simple attributes of the 1:1 relationship type R as attributes of S. Chapter 9

  5. ER to Relational Mapping(con’t) • Step 4: For each regular (non-weak) binary 1:N relationship type R, identify the relation S that represents the participating entity type at the N-side of the relationship type. Include as foreign key in S the primary key of the relations T that represents the other entity type participating in R. Include an simple attributes of the 1:n relationship type as attributes of S. Chapter 9

  6. ER to Relational Mapping(con’t) • Step 5: For each binary M:N relationship type R, 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; their combination will form the primary key of S. Also, include any simple attributes of the M:N relationship type as attributes of S. Chapter 9

  7. ER to Relational Mapping(con’t) • Step 6: For each multivalued attribute A, create a new relation R that includes an attribute corresponding to A plus the primary key attribute K(as a foreign key in R) of the relation that represents the entity type or relationship type that has A as an attribute. The primary key of R is the combination of A and K. If a multivalued attribute is composite, we include its components. Chapter 9

  8. ER to Relational Mapping(con’t) • Step 7: For each n-ary relationship type R, n>2, create a new relation S to represent R. Include as foreign key attributes in the S the primary keys of the relations that represent the participating entity types. Also include any simple attributes of the n-ary relationship types as attributes of S. The primary key for S is usually a combination of all the foreign keys that reference the relations representing the participating entity types. However, if Chapter 9

  9. Step 7 (con’t) the participation constraint (min,max) of one of the entity types E participating in the 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 hence can uniquely identify that relationship instance. Chapter 9

  10. Step 8 • To Convert each Specialization and Generalized Superclass into a relational schema you must use one of the four options available. Selection is based first on the restrictions of the options and secondly, on the utilization of the database. • C is the superclass, k is the primary key and a are the attributes. Chapter 9

  11. Step 8A Create a relation L for C with attributes Attrs(L) = {k, a1, a2, …, an} and PK(L) = k. Create a relation Li for each subclass Si, 1 < I < m, with the attributes ATTRS(Li) = {k} U {attributes of Si} and PK(Li) = k. Chapter 9

  12. Step 8B Create a relation Li for each subclass Si, 1 < i < m, with the attributes ATTRS(Li) = {attributes of Si} U {k, a1, a2, …, an} and PK(Li) = k. Chapter 9

  13. Step 8C • Create a single relation L with attributes Attrs(l) ={k, a1, …, an}U{attributes of S1} U… U {attributes of Sm} U {t} and PK(L)=k • This option is for specialization whose subclasses are DISJOINT, and t is a type attribute that indicates the subclass to which each tuple belongs, if any. This option may generate a large number of null values. Chapter 9

  14. Step 8D • Create a single relation schema L with attributes Attrs(L) = {k, a1, …, an} U{attributes of S1} U… U {attributes of Sm} U {t1, …, tn} and PK(L)=k • This option is for specialization whose subclasses are overlapping, and each ti, 1 < i < m, is a Boolean attribute indicating whether a tuple belongs to subclass Si. Chapter 9

More Related