As we all know that ER Model can be represented using ER Diagrams which is a great way of designing and representing the database design in more of a flow chart form.
It is very convenient to design the database using the ER Model by creating an ER diagram and later on converting it into relational model to design your tables.
Not all the ER Model constraints and components can be directly transformed into relational model, but an approximate schema can be derived.
So let's take a few examples of ER diagrams and convert it into relational model schema, hence creating tables in RDBMS.
Entity in ER Model is changed into tables, or we can say for every Entity in ER model, a table is created in Relational Model.
And the attributes of the Entity gets converted to columns of the table.
And the primary key specified for the entity in the ER model, will become the primary key for the table in relational model.
For example, for the below ER Diagram in ER Model,
A table with name Student will be created in relational model, which will have 4 columns,
id will be the primary key for this table.
In ER diagram, we use diamond/rhombus to reprsent a relationship between two entities. In Relational model we create a relationship table for ER Model relationships too.
In the ER diagram below, we have two entities Teacher and Student with a relationship between them.
As discussd above, entity gets mapped to table, hence we will create table for Teacher and a table for Student with all the attributes converted into columns.
Now, an additional table will be created for the relationship, for example StudentTeacher or give it any name you like. This table will hold the primary key for both Student and Teacher, in a tuple to describe the relationship, which teacher teaches which student.
If there are additional attributes related to this relationship, then they become the columns for this table, like subject name.
Also proper foriegn key constraints must be set for all the tables.
Similarly we can generate relational database schema using the ER diagram. Following are some key points to keep in mind while doing so: