Mapping ER to Relational Model
ER models are converted into the relational model because RDBMS can easily implement relational models like Oracle, My SQL, etc.
ER diagrams mainly consist of
- Entity: An entity is a real-world object having some attributes.
- Relationship: It is an association among different entities.
CASE1: For Strong Entity Set
- A strong entity set having only simple attributes or composite attributes will require only one table in the relational model.
- The simple attribute of the table will be the attribute of the entity set. Simple attributes of the composite attributes are considered and not composite attributes themselves.
- The primary key of the resulting table will be the key attribute of the entity set.
FIG1: Strong entity with Simple Attributes
FIG2: Strong entity with Composite Attribute
CASE 2: For Strong Entity Set With Multi-Valued Attributes
- A strong entity set having any number of multi-valued attributes will require two tables in the relational model.
- One table will consist of all the simple attributes with the primary key.
- Another table will contain the primary key and all the multi-valued attributes.
In this example, Mobile_no is a multivalued attribute. Here, Roll_no is the primary key. So we made a table using primary key Roll_no and remaining simple attributes and other tables having attributes Roll_no and Mobile_no.
CASE 3: Binary Relationship with 1:1 cardinality with the total participation of an entity
In this example, a person can have 0 or 1 passport number. A single person owns each passport shown., So it is one-to-one (1:1) cardinality with the full participation of the Passport entity.
Firstly, we will convert every entity and relationship to tables. We have three tables corresponding to two entities and one for the relation between the entities. Person table and Passport table correspond to Person and Passport entity respectively. The Has table shows the relationship between Passport and Person table. So it will take attribute Per-Id from Person and Pass-No from Passport.
As we can see from the given table, Each Per-Id and Pass-No has only one tuple in Has table. So, we can merge the three tables into one with the attributes given below. Every Per-Id will be unique and not null. So it will be the key to the table. Pass-No can be NULL sometimes. So it cannot be taken as key.
CASE 4: Binary Relationship with 1:1 cardinality and partial participation of both entities
In this example, a male can marry 0 or 1 female and vice-versa. So it is one-to-one cardinality with partial participation from both male and female entities. We will have three tables corresponding to two entities and one for the relation between the entities. The male and Female table correspond to the Male and female entities, respectively. The Marry table shows the relationship between the Male and Female table. So it will take attribute M-Id from Male and F-Id from Female.
As we can see from the given table, Some males and females do not marry. Some M-Id, F-Id will be NULL in the table, So no attribute is always not NULL. So we cannot merge the three tables into one. We will convert it into two tables. In one table, M-Id who are married will be associated with F-Id. It will be NULL for others. Another table contains information about all females. Take a look at the underlined primary key.
Note: Binary relationship with one-to-one cardinality will have two tables in case of partial participation of both entities in the relationship. There will be only one table if at least one entity has total participation.
CASE 5: Binary Relationship having n: 1 cardinality
In this example, each student can enroll in only one elective course, but multiple students can be enrolled for an elective course. We will have three tables corresponding to two entities and one for the relation between the entities. The Student table and Elective_Course table correspond to the Student and Elective_Course entity. The Enrolls table shows the relationship between the Student and Elective_Course table. So it will take attribute S-Id from and Student E-Id from Elective_Course.
As we can see from the given table, S-Id is unique in Enrolls Table. So it can be a key of Enrolls table. The key is the same for both Student and Enrolls Table. So, these two tables can be merged into a single table. The resultant tables are shown below with underlined primary keys.
CASE 6: Binary Relationship having m: n cardinality
In this example, each student can enroll in more than one compulsory course, and for each compulsory course, one or more students can enroll.
We will have three tables corresponding to two entities and one for the relation between the entities. The Student table and Compulsory_Courses table correspond to the Student and Compulsory Courses entity. The Enrolls table shows the relationship between the Student and Compulsory_Courses table. So it will take attribute S-Id from Person and C-Id from Compulsory_Courses.
As we can see from the given Table, S-Id and C-Id repeat in Enrolls Table. But their combination is not repeating. So the combination of S-Id and C-Id can be taken as the primary key for Enrolls Table. The key is different for all tables. So they can not be merged.
CASE 7: Binary Relationship having a weak entity
In this example, an employee can have many dependents, and one dependent can depend on one employee. A dependent does have no existence without an employee. So dependent will be a weak entity whose participation will always be total. The weak entity does not have its key. So its key will be a combination of its identifying entity (E-Id of Employee in this case) and its partial key (D-Name). We will have three tables corresponding to two entities and one for the relation between the entities. The Employee table and Dependents table correspond to the Employee and Dependent entity. The Has table shows the relationship between the Employee and Dependents table. So it will take attribute E-Id from Employee and D-Name from Dependents.
As we can see from the given Table, E-Id, D-Name is key for Has and Dependents Table. So we can merge these two tables into a single table. The resulting tables are shown below with underlined primary Keys.
Frequently Asked Questions
- What is the purpose of ER diagram?
An Entity-Relationship (ER) Diagram is a flowchart that illustrates how entities such as objects or people are related to each other within a system. ER diagrams help us visualize database design ideas give us a chance to identify our mistakes and design flaws so that we can make corrections before executing the changes in the database.
- How do you convert a weak entity to a strong entity?
We can convert any weak entity set to a strong entity set by simply adding appropriate attributes.
- What is the necessity of keeping weak entity sets in an ER model representation?
Each weak set must be a part of a one-to-many relationship set. A weak entity set is required to avoid the inconsistencies caused by duplicating the key of the strong entity.
We learned how to convert the ER model to the Relational model for different entities and multiple cases of relationships among entities.
Visit here to learn more about different topics related to database and management systems. Ninjas don’t stop here check out the Top 100 SQL Problems to master frequently asked questions in big companies and land your dream job. Also, try CodeStudio to practice a wide range of DSA questions asked in lots of interviews.