EER and ER to Relational Mapping | Database Management System

EER and ER to Relational Mapping | Database Management System

EER and ER to Relational Mapping : Since 1980s there has been an increase in emergence of new database applications with more demanding requirements. Basic concepts of ER modelling are not sufficient to represent requirements of newer, more complex applications. Response is development of additional ‘semantic modelling concepts.

The EER stand for extended/enhanced entity-relationship (EER) model. EER is a language for definition of structuring (and functionality) of database or information systems. It uses inductive development of Structuring. Basic attributes are assigned to base data types. Complex attributes can be constructed by applying constructors such as tuple, list or set constructors to attributes that have already been constructed. Entity types conceptualize structuring of things of reality through attributes. Cluster types allow the generalize or to combine types into singleton types. Relationship types associate types that have already been constructed into an association type. The types may be restricted by integrity Constraints and by Specification of identification of objects defined on the corresponding type. Typical integrity constraint of the extended entity-relationship model are participation, lookup and general cardinality constraints. Entity, cluster and relationship classes contain a finite set of objects defined on these types. The types of an EER schema are typically depicted by an EER diagram.

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.

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 Was 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). –

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.

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:1 relationship type as attributes of S.

Step 5: For each binary A: 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.

Step 6 : For each multi valued 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 multi valued attribute is composite, we include its components,

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 Sthe 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 the participation constraint min, max) of one of the entity types Eparticipating 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.

Step 8: To convert each specialization and generalized super class 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 super class, k is the primary key and a are the attributes. (a) Create a relation L for C with attributes ATTRS(L) = {k, a 1, a2,…, a, and PK(L)=k. Create a relation L for each subclass S, 1 < t < m, with the attributes ATTRS(L) = {k} U attributes of S} and PK (L) = k. (b) Create a relation L for each subclass S, 1 <ism, with the attributes ATTRS(L)={attributes of S} U ck, a1, a2,…, d) and PK (Li) = k. (c) Create a single relation L with attributes Attrs.(I) ={k, a 1,…, a U attributes of S} U. U (attributes of S} U{t} and PK(L) = k. This option is for specialization whose sub classes 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. (d) Create a single relation Schema L with attributes ATTRS(L) = {k, a 1,…, a U attributes of S} U. U attributes of S} U{t1,…,t} and PK(1) = k. This option is for Specialization whose sub classes are overlapping and each ti ,1 < j < m, is a Boolean attribute indicating whether a tuple belongs to Subclass Si.