Post By: Hanan Mannan
Contact Number: Pak (+92)-321-59-95-634
-------------------------------------------------------

ER Model to Relational Model

ER Model when conceptualized into diagrams gives a good overview of entity-relationship, which is easier to understand. ER diagrams can be mapped to Relational schema that is, it is possible to create relational schema using ER diagram. Though we cannot import all the ER constraints into Relational model but an approximate schema can be generated.

There are more than one processes and algorithms available to convert ER Diagrams into Relational Schema. Some of them are automated and some of them are manual process. We may focus here on the mapping diagram contents to relational basics.

ER Diagrams mainly comprised of:

• Entity and its attributes
• Relationship, which is association among entities.

Mapping Entity

An entity is a real world object with some attributes.

Mapping Process (Algorithm):

[Image: Mapping Entity]

• Create table for each entity
• Entity's attributes should become fields of tables with their respective data types.
• Declare primary key

Mapping relationship

A relationship is association among entities.

Mapping process (Algorithm):

[Image: Mapping relationship]

• Create table for a relationship
• Add the primary keys of all participating Entities as fields of table with their respective data types.
• If relationship has any attribute, add each attribute as field of table.
• Declare a primary key composing all the primary keys of participating entities.
• Declare all foreign key constraints.

Mapping Weak Entity Sets

A weak entity sets is one which does not have any primary key associated with it.

Mapping process (Algorithm):

[Image: Mapping Weak Entity Sets]

• Create table for weak entity set
• Add all its attributes to table as field
• Add the primary key of identifying entity set
• Declare all foreign key constraints

Mapping hierarchical entities

ER specialization or generalization comes in the form of hierarchical entity sets.

Mapping process (Algorithm):

[Image: Mapping hierarchical entities]

• Create tables for all higher level entities
• Create tables for lower level entities
• Add primary keys of higher level entities in the table of lower level entities
• In lower level tables, add all other attributes of lower entities.
• Declare primary key of higher level table the primary key for lower level table
• Declare foreign key constraints.