ER Model to Relational Model

The ER (Entity-Relationship) model is a graphical representation used to design databases. On the other hand, the relational model is a mathematical representation that outlines the connections between database tables. After creating the system’s ER diagram, we must translate it into relational models, which can be instantly implemented by any RDBMS, such as Oracle, MySQL, etc. The steps below can be used to change an ER model into a relational model:

Entity becomes Table:

• In a relational model that corresponds to an ER model, each entity becomes a table that stores data about the real-world object or concept it represents in the ER model.
• The relational model converts the attributes of the entity from the ER model into columns of the corresponding table.
• The relational model’s table’s primary key is the one specified for the entity’s primary key in the ER model.

For example in the above given ER diagram a table with name Student will be created in relational model, which will have 3 columns, Stud_id, Studname, Studaddr  with Stud_id as the primary key for this table.

Relationship becomes a Relationship Table:

• A diamond or rhombus is used in an ER diagram to represent a connection between two entities.This relationship is represented by a relationship table in the corresponding relational model, creating a many-to-many relationship between the two entities.
• Two columns, one for each primary key of the two tables the relationship connects, are typically present in the relationship table.
• These two columns together form a composite primary key for the relationship table, which uniquely identifies each relationship between the two entities.
• Additional columns in the relationship table may be added to represent relationship characteristics, such as the date the relationship started or the type of relationship.

For example in the above given ER diagram an additional table will be created for the relationship, StudyIn. This table will hold the primary key for both Student and College, in a tuple to describe the relationship, which student study in College.

Note: proper foreign key constraints must be set for all the tables.

Important Points to Remember:

• Entity becomes Table: Each entity in the ER diagram should be transformed into a table in the relational database schema, with all the attributes of the entity becoming fields (columns) in the table.
• Relationship becomes a Table: In the relational database schema, each relationship between entities in the ER diagram should be translated into a table with the primary keys of the related entities also stored in it as foreign keys. The many-to-many relationship between the related entities is established by this table, which serves as a bridge table.
• Primary Keys: Each table in the relational database schema needs to have its primary keys properly set. This is significant because the primary key serves as a reference for other tables that are related to this table and uniquely identifies each record in the table.
• Foreign Key Constraints for Weak Entities: To maintain referential integrity, a weak entity’s table must define a foreign key constraint if its primary key depends on the primary key of another entity.

Note: also read about Relational Calculus

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py