FOR FREE MATERIALS

ER Diagram to Relational Model:

There are 5 steps to convert the ER diagram to the Relational model.

Step 1: Consider this ER diagram and convert it to the corresponding table: In step 1 we are showing that we are not including multi-valued attribute. 

We do not include sname, only include single attribute FN and LN. Here in this table, we are not considering attribute ph_no from ER diagram because ph_no is multi-valued.

So, for Ph_No we can create another table.

Here two may have duplicate value because a student has more than one phone number and two students may share the same room. So, better to make both as a primary as combined.

Sometimes Ph_No may be complex attribute also.

In that case same way in the new table, we include STD and NO., not Ph_No.

Step 2: In this step, we are showing Strong and weak entity.

In the case of the weak entity which depends on the employee table (relation), we should include E_No (primary key of employee table) at the weak entity (dependent) as a foreign key. And Name at the dependent table let work as the partial key.

Note:  Partial key means one attribute which not uniquely identify all tuples of the table but few rows of the table.

Because of the weak entity and as per referential integrity if we want to delete something from E_no (primary key) of the employee table, we also delete all data of the dependent table related to this E_no (Cascading process).

We can do that only because it is the weak entity.

Step 3: In that case, we are showing how to convert ER diagram with a 1:1 relationship to the table.

In that case, where the relationship is 1:1we can either add D_No to the employee table as FK (foreign key) or add E_No to the department table as FK (foreign key) but better to add the primary key of one table add to another table which total participation. 

Here should add E_No to the department table as FK (foreign key). The reason is if we add E_No to the department for every department we must have a manager (as employee, E_no).

But we add Dept_no. to the employee for every employee of E_no, we don’t get Dept_no. because every employee is not the manager who manages the department.

Another thing FK (E-no), we can make it manages for better understanding but make sure that manager (value) should match which E_No. of employee table.

Note: Same way attribute of relation start date is better to add at total participation. So it is better to add start date to the department table.

Another important case:

If two (table) relation has 1:1 relationship and both are total participation. In that case, it is sure for every element of person we can set a match with a license card table. It is sure that both no. of attributes are the same.

In that case, it is better to add (join) both tables in a single table. It has created no problem.

Step 4: In that case we are showing how to convert ER diagram with many : 1 or 1 : many relationship to table.

Now in this case where we put Foreign Key (FK), employee side, or department side?

It must add Foreign Key (D_No.) to employee because every employee has a department but a department has many employees so, add E_No. to the department such meaningless and not possible. So, we always create the foreign key from attribute with cardinality is N (Department) and put it to the attribute with cardinality 1 (Employee).

For the same reason, we also add the start date at the employee table.

Step 5: In that case, we are showing how to convert ER diagram with many : many relationship to the table.

In this case many : many relationships we create foreign key (FK) to neither employee table nor project table because both side foreign key create multi-valued and the reason is employee works on many projects and the same way a project group with many employees.

In both case addition of foreign key is not possible. We should make a different table for that.

And make a primary key by both E_No and P_id. And also shift hours (Relationship attribute) at the new table, because of same reason (many : many). We can’t shift start date neither employee nor project table.

n - way Relationship

N-way relationships can be considered as a collection of binary relationships. In the case of n-way relationship, we make a new table of all tables and the primary key of all tables as Foreign Key of relationship.

A new table for relationship of all tables

Make primary key by all of them. And also if any attribute of a relationship, add them into the same new table.

Conversion rule of ER diagram to table: