ER Diagram

[thiruvasagamani]

An entity-relationship model (ERM) is an abstract conceptual representation of structured data. Entity-relationship modeling is a relational schema database modeling method, used in software engineering to produce a type of conceptual data model (or semantic data model) of a system, often a relational database, and its requirements in a top-down fashion.

How to Make Entity Relationship Diagram

An E-R diagram express the overall logical structure of a database graphically.

1.) Use rectangles representing entity sets.

2.) Use ellipses representing attributes

3.) Use ellipses representing attributes.

4.) Use diamonds representing relationship sets.

5.) Use lines linking attributes to entity sets and entity sets to relationship sets.

Mapping ER-Diagram to Tables

A database conforming to an E-R diagram can be represented by a collection of tables.

1.) Pre-mapping preparation work (Optional)

Mapping a conceptual ERD model to relations is not straightforward because some concepts may not be implemented directly in relations. Furthermore, the text-based representation of relations (tables) is not as comprehensive as graphical models. Thus, pre-mapping work in Logical ER models are used to bridge the gap. Typical transformations include:

   1. converting all many-to-many relationships to many-to-one relationships
   2. turning all weak entities into regular entities
   3. separating multivalued attributes (repeating values) separate entities
   4. converting all n-ary (n > 2) relationships into binary relationships
   5. implementing all relationships by adding appropriate foreign keys to the connected entities

After the logical model is validated, the logical model is then mapped to relations in a rather straightforward way.

2.) Mapping Logical ER-Diagram models to Relational Tables

For each entity set and relationship set, there is a unique table which is assigned the name of the corresponding set. Each table has a number of columns with unique names.

STEP 1: For regular entity type E in ER schema, create a relation R that includes all the simple attributes, and component attributes of composite attributes. Select the primary key.

STEP 2: For weak entity type W in ER schema, with owner entity type E, create a relation R, include all simple attributes (or component attributes of composite attributes) of W as attributes of R. In addition, include the primary key attributes of the relation Q for the owner entity type E. Primary key is the combination of primary key of Q and partial key of R.

STEP 3: For 1:1 relationship X, suppose S and T are the relations for the entity types participating in it. Include primary key of T as foreign key of S. Include other attributes of the relationship X as attributes of S.

STEP 4: For 1:N relationship Y, suppose S relation corresponds to the entity type at the N-side, and T relation corresponds to the entity type at the other side. Include primary key of T as foreign key of S.

STEP 5: For M:N relationship Z, create a new relation R to represent Z. Include simple attributes of Z in R. Include the primary keys of S and T as foreign keys of R, their combination forms the primary key of R.

STEP 6: For multivalued attribute A, create a new relation R that includes an attribute corresponding to A. Include primary key of the relation of the entity type having A as an attribute. Primary key is their combination.

STEP 7: For n-ary relationship type X, and n>2, create a new relation R. Include primary key of each participating entity type's relation as foreign key of R. Include attributes of X as simple attributes of R.

[karthikn]

Thank you for your kind information