Skip to content
Related Articles

Related Articles

Improve Article

Mapping from ER Model to Relational Model

  • Difficulty Level : Medium
  • Last Updated : 05 Jul, 2021

To understand this, you should have an idea about: 

ER model 

Relation model 

After designing the ER diagram of system, we need to convert it to Relational models which can directly be implemented by any RDBMS like Oracle, MySQL etc.  In this article we will discuss how to convert ER diagram to Relational Model for different scenarios. 

Case 1:  Binary Relationship with 1:1 cardinality with total participation of an entity 



 

erm1

A person has 0 or 1 passport number and Passport is always owned by 1 person. So it is 1:1 cardinality with full participation constraint from Passport. 

First Convert each entity and relationship to tables.  Person table corresponds to Person Entity with key as Per-Id. Similarly Passport table corresponds to Passport Entity with key as Pass-No. Has Table represents relationship between Person and Passport (Which person has which passport). So it will take attribute Per-Id from Person and Pass-No from Passport. 

  
 

Person Has Passport
Per-IdOther Person AttributePer-IdPass-NoPass-NoOther PassportAttribute
PR1PR1PS1PS1
PR2PR2PS2PS2
PR3      

 

                                                                     Table 1                                                                                  

As we can see from Table 1, each Per-Id and Pass-No has only one entry in Has table. So we can merge all three tables into 1 with attributes shown in Table 2. Each Per-Id will be unique and not null. So it will be the key. Pass-No can’t be key because for some person, it can be NULL. 
 



Per-IdOther Person AttributePass-NoOther PassportAttribute

 

Table 2

Case 2: Binary Relationship with 1:1 cardinality and partial participation of both entities 

 

erm2

A male marries 0 or 1 female and vice versa as well. So it is 1:1 cardinality with partial participation constraint from both. First Convert each entity and relationship to tables.  Male table corresponds to Male Entity with key as M-Id. Similarly Female table corresponds to Female Entity with key as F-Id. Marry Table represents relationship between Male and Female (Which Male marries which female). So it will take attribute M-Id from Male and F-Id from Female. 

 

Male Marry Female
M-IdOther Male AttributeM-IdF-IdF-IdOther FemaleAttribute
M1M1F2F1
M2M2F1F2
M3    F3

 

Table 3

As we can see from Table 3, some males and some females do not marry. If we merge 3 tables into 1, for some M-Id, F-Id will be NULL. So there is no attribute which is always not NULL. So we can’t merge all three tables into 1. We can convert into 2 tables. In table 4, M-Id who are married will have F-Id associated. For others, it will be NULL. Table 5 will have information of all females. Primary Keys have been underlined. 
 



M-IdOther Male AttributeF-Id

 

 Table 4   

 

F-IdOther FemaleAttribute

 

                                                                Table 5                                                                 

Note: Binary relationship with 1:1 cardinality will have 2 table if partial participation of both entities in the relationship. If atleast 1 entity has total participation, number of tables required will be 1. 

Case 3: Binary Relationship with n: 1 cardinality 

 

erm3

In this scenario, every student can enroll only in one elective course but for an elective course there can be more than one student. First Convert each entity and relationship to tables.  Student table corresponds to Student Entity with key as S-Id. Similarly Elective_Course table corresponds to Elective_Course Entity with key as E-Id. Enrolls Table represents relationship between Student and Elective_Course (Which student enrolls in which course). So it will take attribute S-Id from and Student E-Id from Elective_Course. 
 



Student Enrolls Elective_Course
S-IdOther Student AttributeS-IdE-IdE-IdOther Elective CourseAttribute
S1S1E1E1
S2S2E2E2
S3 S3E1 E3
S4 S4E1   

 

Table 6

As we can see from Table 6, S-Id is not repeating in Enrolls Table. So it can be considered as a key of Enrolls table. Both Student and Enrolls Table’s key is same; we can merge it as a single table. The resultant tables are shown in Table 7 and Table 8. Primary Keys have been underlined. 
 

S-IdOther Student AttributeE-Id

 

Table 7 

 

E-IdOther Elective CourseAttribute

 

Table 8

Case 4: Binary Relationship with m: n cardinality

erm4



 

In this scenario, every student can enroll in more than 1 compulsory course and for a compulsory course there can be more than 1 student. First Convert each entity and relationship to tables.  Student table corresponds to Student Entity with key as S-Id. Similarly Compulsory_Courses table corresponds to Compulsory Courses Entity with key as C-Id. Enrolls Table represents relationship between Student and Compulsory_Courses (Which student enrolls in which course). So it will take attribute S-Id from Person and C-Id from Compulsory_Courses. 
 

Student Enrolls Compulsory_Courses
S-IdOther Student AttributeS-IdC-IdC-IdOther Compulsory CourseAttribute
S1S1C1C1
S2S1C2C2
S3 S3C1 C3
S4 S4C3 C4
   S4C2   
   S3C3   

 

Table 9

As we can see from Table 9, S-Id and C-Id both are repeating in Enrolls Table. But its combination is unique; so it can be considered as a key of Enrolls table. All tables’ keys are different, these can’t be merged.  Primary Keys of all tables have been underlined. 

Case 5: Binary Relationship with weak entity

erm5

 

In this scenario, an employee can have many dependents and one dependent can depend on one employee. A dependent does not have any existence without an employee (e.g; you as a child can be dependent of your father in his company). So it will be a weak entity and its participation will always be total. Weak Entity does not have key of its own. So its key will be combination of key of its identifying entity (E-Id of Employee in this case) and its partial key (D-Name). 

First Convert each entity and relationship to tables.  Employee table corresponds to Employee Entity with key as E-Id. Similarly Dependents table corresponds to Dependent Entity with key as  D-Name and E-Id. Has Table represents relationship between Employee and Dependents (Which employee has which dependents). So it will take attribute E-Id from Employee and D-Name from Dependents. 
 



Employee Has Dependents
E-IdOther Employee AttributeE-IdD-NameD-NameE-IdOther DependentsAttribute
E1E1RAMRAME1
E2E1SRINISRINIE1
E3E2RAMRAME2
  E3ASHISHASHISHE3

 

 Table 10

 As we can see from Table 10, E-Id, D-Name is key for Has as well as Dependents Table. So we can merge these two into 1. So the resultant tables are shown in Tables 11 and 12. Primary Keys of all tables have been underlined. 
 

E-IdOther Employee Attribute

 

Table 11

 

D-NameE-IdOther DependentsAttribute

 

Table 12

 

 

Article contributed by Sonal Tuteja. Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above
 

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

 




My Personal Notes arrow_drop_up
Recommended Articles
Page :