Drawing ER and EER Diagrams & Relational Mapping

 

A comprehensive introduction to ER & EER

Hi Readers!😍

Today I am going to tell you how to draw ER and EER diagrams for your database and how to map them into relational schemas.🤞

Before that here are some things you should know.

First Lets see what are ER and EER diagrams.

What are the ER diagrams?

ER diagram is a visual representation of data based on the ER model, and it describes how entities are related to each other in the database.

What are EER diagrams?

EER diagram is a visual representation of data, based on the EER model that is an extension of the original entity-relationship (ER) model.

When you read this article you will find these words.”Entity, Attribute, Relationship, Cardinality, and Participation”.🤔 Okay, Let’s see what are these.

Entity

An entity is any singular, identifiable and separate object. It refers to individuals, organizations, systems, bits of data or even distinct system components that are considered significant in and of themselves. For example, People, Property, Organizations, Agreements and, etc. In the ER diagram, the entity is represented by a rectangle.

Weak Entity

A weak entity is an entity that depends on the existence of another entity. In more technical terms it can be defined as an entity that cannot be identified by its own attributes.

Attributes

Attributes are the properties that define the entity type. For example, Roll_No, Name, DOB, Age, Address, Mobile_No are the attributes that define entity type Student. In the ER diagram, the attribute is represented by an oval.

Multi-valued Attribute

If an attribute can have more than one value it is called a multi-valued attribute.

Derived Attribute

An attribute-based on another attribute.

Relationships

A relationship is an association that describes the interaction between entities.

Recursive Relationship

If the same entity participates more than once in a relationship it is known as a recursive relationship.

The following are the types of entities, attributes, and relationships.

Cardinality

Cardinality refers to the maximum number of times an instance in one entity can relate to instances of another entity. There are three types of cardinalities.

1. one to one (1 to 1)
2. one to many(1 to N)
3. many to many (M to N)

Participation

Participation constraint specifies the existence of an entity when it is related to another entity in a relationship type. There are two types. Partial and Total participation.

Total Participation − Each entity is involved in the relationship. Total participation is represented by double lines.

Partial participation − Not all entities are involved in the relationship. Partial participation is represented by single lines.

There are three steps in database development.

1. Requirements Analysis- Understand the data problem through user interviews, forms, reports, observation and etc.
2. Component design stage- Create a data model that is a graphical representation of what will be finally will be implemented.
3. Implementation stage- Actual development of the database which leads to database actually being used in the real environment.

In the above stages after the first stage(Requirements analysis) you have to follow the database design stages.

1. Conceptual design
2. Logical design
3. Physical design

In the Conceptual design, we identify all entities, we define attributes and their properties and we define relationships between entities.

In the Logical design, we transform the conceptual design into relational, transform entities as tables, transform entity attributes into table column, and normalization.

In the Physical design, we specify internal storage structure and paths, assign one or more indexes and tune indexes.

At the conceptual design stage, we design the ER or EER diagrams.

Here are some rules for drawing ER and EER diagrams

• Write all entities in the singular form
• Write all relationships in a singular form.
• Write all attributes in the singular form.
• If you want to separate words use underscore mark.

Now, let’s see how to draw ER diagrams and EER diagrams.

Drawing ER and EER diagrams

Below points show how to go about creating an ER diagram.

1. Identify all the entities in the system. An entity should appear only once in a particular diagram. Create rectangles for all entities and name them properly.
2. Identify relationships between entities. Connect them using a line and add a diamond in the middle describing the relationship.
3. Add attributes for entities. Give meaningful attribute names so they can be understood easily.
4. Mark the cardinalities and participation between the entities.

Here is an example of ER diagrams.

Looks easy?🤩 Try more to draw complex diagrams.

Now let’s see how to draw EER diagrams.

Here we just need to add a few things to above.

1. As in drawing ER diagrams first, we have to identify all entities.

After we found entities from the scenario you should check whether those entities have sub-entities. If so you have to mark sub-entities in your diagram.

Dividing entities into sub-entities we called as specialization. And combining sub-entities to one entity is called a generalization.

2. Then you have to identify relationships between entities and mark them.

3. Add attributes for entities. Give meaningful attribute names so they can be understood easily.

4.Mark the cardinalities and participation

If it is an EER diagram you have to add a few to your diagram.

Here also we have to check whether sub-entities totally depend on the main entity or not. And you should mark it.

If all members in the superclass(main entity) participate in either one subclass it is known as total participation. It marks by double lines.

Total Participation

If at least one member in the superclass does not participate in subclass it is known as partial participation. It is denoted by one single line.

Partial Participation

If all the members in the superclass participate for only one subclass it is known as disjoint and denoted by “d”. If all the members in the superclass participate in more than one subclass it is known as overlap and denoted by “o”.

Now it ends, after following all the above steps you can come up with your ER and EER diagrams.😍

Benefits of ER and EER diagrams.

• Easy to understand and does not require a person to undergo extensive recently training to be able to work with it and accurately.
• Readily translatable to relational tables which can be used to quickly build databases
• Can directly be used by database developers as the blueprint for implementing databases in specific software application
• It can be applied in other contexts such as describing the different relationships and operations within an organization.

Now let’s move on. Our next topic is map ER and EER diagrams into relational schemas.

Mapping ER and EER diagrams into relational schemas

First I’ll tell you how to map the ER diagram into a relational schema.

Mapping ER diagrams into relational schemas

Follow the steps one by one to get it done.🤞

1. Mapping strong entities.
2. Mapping weak entities.
3. Map binary one-to-one relations.
4. Map binary one-to-many relations
5. Map binary many-to-many relations.
6. Map multivalued attributes.
7. Map N-ary relations

Let’s go deep with the examples.

1. Mapping strong entities.

2. Mapping weak entities.

Above it shows an ER diagram with its relationships. You can see there are two strong entities with relationships and a weak entity with a weak relationship.

When you going to make a relational schema first you have to identify all entities with their attributes. You have to write attributes in brackets as shown below. Definitely you have to underline the primary keys. In the above DEPENDENT is a weak entity. To make it strong go through the weak relationship and identify the entity which connects with this. Then you have written that entity’s primary key inside the weak entity bracket.

Then you have to map the primary key to where you took from as shown below.

3. Map binary one to one relations.

Let’s assume that the relationship between CUSTOMER and CARD is one to one.

There are three occasions where one to one relations take place according to the participation constraints.

I. Both sides have partial participation.

When both sides have partial participation you can send any of the entity’s primary key to others. At the same time, if there are attributes in the relationship between those two entities, it is also sent to other entity as shown above.

So, now let us see how we write the relational schema.

Here you can see I have written CUSTID and STARTDATE inside the CARD table. Now you have to map CUSTID from where it comes. That’s it.🤩

II. One side has partial participation.

You can see between the relationship and CARD entity it has total participation.

When there is total participation definitely the primary of others comes to this. And also if there are attributes in the relationship it also comes to total participation side.

Then you have to map them as below.

III. Both sides have total participation

If both sides have total participation you need to make a new relationship with a suitable name and merge entities and the relationship.

Following it shows how we should write the relation.

Now let us see how to map one to many relations.

4. Map binary one-to-many relations

If it is one-to-many, always to the many side other entities' primary keys and the attributes in the relationship go to the many side. No matter about participation. And then you have to map the primary key.

5. Map binary many to many relations.

If it is many to many relations you should always make a new relationship with the name of the relationship between the entities.

And there you should write both primary keys of the entities and attributes in the relationship and map them to the initials as shown below.

6. Map multivalued attributes.

If there are multivalued attributes you have to make a new relationship with a suitable name and write the primary key of the entity which belongs to the multivalued attribute and also the name of the multivalued attribute as shown below.

7. Map N-ary relations.

First, let us consider unary relationships.

We categorized them into two.

I. one-to-one and one to many relations.

If it is unary and one to one or one to many relations you do not need to make a new relationship you just want to add a new primary key to the current entity as shown below and map it to the initial. For example, in the above diagram, the employee is supervised by the supervisor. Therefore we need to make a new primary key as SID and map it to EMPID. Because of SID also an EMPID.

II. many-to-many relations.

If it is unary and many to many relations you need to make a new relationship with a suitable name. Then you have to give it a proper primary key and it should map to where it comes as shown below.

Now let us see how to map relations with more than two entities.

If there are more than three entities for a relationship you have to make a new relation table and put all primary keys of connected entities and all attributes in the relationship. And in the end, you have to map them as shown below.

👍 That’s how we map ER diagrams into relational schemas.

Now let us see how to map EER diagrams.

Mapping EER diagrams into relational schemas.

Let us go through the following diagram.

There are four ways to draw relational schema for an EER. You have to choose the most suitable one. In the end, I'll give you the guidelines on how to choose the best and most suitable way.

First method

Here we write separate relations to all superclass entities and subclass entities. And here we have to write the superclass entities' primary key to all subclass entities and then map them as shown above. Note that we write only the attributes belongs to each entity.

Second method

Here we do not write the superclass entity but in each subclass entity, we write all attributes that are in superclass entity.

Third method

Here we write only the superclass entity and write all the attributes which belong to subclass entities. Specialty in here is that to identify that PERSON is an EMPLOYEE or STUDENT we add a column as PERSONTYPE. After the table creates we can mark as a STUDENT or EMPLOYEE.

Fourth method

Here instead of PERSONTYPE, we write STUDENT and EMPLOYEE both.

The reason for that is sometime PERSON will belong to both categories.

Somewhat confusing right?🙄 Don't worry read the following guidelines to clear out them.

Now let us see how to select the best and most suitable method to write the relational schema.

Guidelines

1.If sub-entities have more attributes (local or foreign attributes)

Select the first or second method.

From this two,

If EER is totally specialized -> select the second method.

If EER is partially specialized -> select first method.

2. If sub-entities have fewer attributes (local or foreign attributes)

Select the third or fourth method.

From this two,

If EER is disjoint-> select the third method.

If EER is overlap -> select forth method.

👍 That’s how we map EER diagrams into relational schemas.

Try more and more to come up with the best.

I hope you got clear information.

Thanks for reading!!😍

https://medium.com/nerd-for-tech/drawing-er-and-eer-diagrams-mapping-4965e2b3cc3e

https://tinman.cs.gsu.edu/~raj/4710/sp03/ch9-part1.pdf