DBMS ER Model to Relational Model

DBMS ER Model to Relational Model

When conceptualised in diagrams, the ER model gives a good overview of the relationship between entities, which is easier to understand. It is possible to map ER diagrams to a relational schema, that is, to create a relational schema using an ER diagram. We can not import all the ER constraints into the relational model, but it is possible to generate an approximate schema.

To convert ER Diagrams to a Relational Schema, there are several processes and algorithms available. Some of them are manual and some of them are automated. Here, we will focus on mapping the substance of the diagram to relational principles.

ER diagrams usually comprise of −

• Entity and the attributes
• Relationship, which is a relationship between entities.

Mapping Entity

An entity with certain attributes is a real-world object.

Mapping Process (Algorithm)

• For each entity, create a table.
• The attributes of the entity should become fields for tables with their respective data types.
• Place the primary key.

Mapping Relationship

An association between entities is a relationship.

Mapping Process

• Build a relationship table.
• Add the primary keys of all participating entities as table fields with their corresponding types of data.
• If there is a relationship with any attribute, add each attribute as a table field.
• Declare a primary key that composes all of the participating entities' primary keys.
• Declare all of the foreign key constraints

Mapping Weak Entity Sets

One which does not have any primary key associated with it is a weak entity set.

Mapping Process

• Creating a Weak Entity Set Table.
• Add as a field all its attributes to the table.
• Add the Entity Set identifying Primary Key.
• Declare all key foreign constraints.

Mapping Hierarchical Entities

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

Mapping Process

• For all higher-level entities, create tables.
• For lower-level entities, create tables.
• In the table of lower-level entities, add the primary keys for higher-level entities.
• Add all other attributes of lower-level entities into the lower-level tables.
• Declaring the higher-level table primary key and the lower-level table primary key.
• Declaring key foreign constraints.