Here, we will learn how to convert the Entity-Relationship diagram into the relational model in the database management system. Before understanding this conversion, you must know about the ER Model and Relational Model.
When the user creates the ER diagram of any database, then he/she has a need to convert the created ER model into the relational model (Tables). Because, the relational model can be easily and directly implemented in the RDBMS software like MySQL, MS-SQL, and Oracle.
An Entity Relationship diagram contains strong entities with a different type of attributes and relationship set between the tables.
Following are the rules which are compulsory to be followed for converting the ER model into the relational model:
Rule 1: Strong Entity with Simple Attributes
The Strong entity with simple attributes is represented by a single table in the relational model.
In this rule, simple attributes of an entity are taken as fields of the table. And, the key attribute is specified as the primary key of the corresponding table.
Example:
In this example, a strong entity Employee in the ER diagram is represented by the Employee table in the relational model. And, the five attributes of Employee entity selected as the fields of the employee table. And, the Emp_ID acts as the primary key of the table.
Rule 2: Strong Entity with Composite Attributes
The Strong entity with Composite attributes is represented by a single table in the relational model.
In this rule, the simple attributes of composite attributes are taken as columns of the relational table, but the composite attributes are not taken as a column in the table. And, the key attribute is specified as the primary key of the corresponding table.
Example:
In this example, a strong entity Employee in the ER diagram contains one composite attribute.
After conversion, a strong entity Employee is represented by the Employee table in the relational model. And, the attributes of the Name ( composite attribute ) selected as the fields of the employee table. And, the Emp_ID acts as the primary key of the table.
Rule 3: Strong Entity with Multi-valued Attributes
The Strong entity with multi-valued attributes is represented by two tables in the relational model.
In this rule, one relational table contains the primary key and the multi-valued attributes of the strong entity. And, the other relational table contains the primary key and other simple attributes.
Example:
In this example, the strong entity Employee contains three simple attributes and two multi-valued attributes in the ER diagram. After conversion, Employee entity is represented by the two tables in the relational model.
The attributes of the first table are Emp_ID and Email_ID. The attributes of another table are Emp_ID, Name, and Age.
Rule 4: Convert Relationship Set into Relationship Table
The Relationship set in ER diagram is represented by only one table in the relational model.
In this rule, the key attributes of the participating entity set are taken as the attributes of the relationship table in the relational model. And, if there is any attribute of relationship set in the ER diagram, then that attribute is added as attributes of the relationship table.
Example:
In this example, two strong entities Employee and Department are related to each other with a relationship Works-In. After Conversion, the key attributes Emp_ID and Dept_ID of Employee and Department entity will be the attributes of Works-In table in the relational model. And, the attribute (Works_From) of Works-In relationship set is taken as the another field of relationship table.