In this article, we have covered Entity Relationship Model in depth along with basics like entity, attributes, notations and much more. We have present an example of ER Diagram / Entity Relationship Model for Job Portal database.

Table of content:

1. What is Entity relationship model?
2. Entity and Attributes
3. Relationship
4. Mapping of Entities
5. Notations for ER Diagram
6. Keys
7. Converting an ER model into Relational model

Let us get started.

What is Entity relationship model?

The Entity-Relationship model (or ER model) is the pictorial representation of a database model.

It is frequently used to design a System before developing it. You can imagine this as a blueprint of a house which is made before building a house.

This is a standard technique which every developer should not to enable them to convey their ideas freely across their development team. This model help developers to actually implement the system following the design.

To give you an idea, this is an example of Entity Relationship model:

Entity and Attributes

An Entity is a 'thing' or and 'object' in the real world. Every entity has some properties. These properties are called Attributes.

Types of attributes :

• Simple and Composite attributes

• Single-valued and Multivalued attributes

• Derived attributes

• Simple and Composite attributes - Composite attributes are the one that can be divided into subparts or sub-attributes. For example, 'Location' can be subdivided into parts like 'house number', 'Street name', 'city', 'state' and 'country'. Simple attributes are the one that cannot be divided into subparts.

• Single-valued and Multivalued attributes - The attributes that have a single value are called Single valued and the one having multiple values are called Multivalued attributes. For example, attributes like 'employee id' always have single value thus are single valued and attributes like 'phone number' are multivalued as a single entry can have multiple phone numbers.

• Derived attributes - These can be derived from other attributes. For example, Attribute like 'Age' can be derived from 'date-of-birth'.

Relationship

A relationship is used to describe the 'relation' among Entities in a model.
There are different types of relationship cardinalities:

• One to one
• One to many
• Many to one
• Many to many

Let there be 2 entity sets X and Y.

• One-to-one : When one entity in X is associated with only one entity in Y.
• One-to-many : When one entity in X is associated with more than one entity in Y.
• Many-to-one : When the entities more than one in X are associated with only one entity in Y.
• Many-to-Many : When the entities more than one in X are associated with more than one entity in Y.

Mapping of Entities

There are two mapping of entities:

• Cardinality
• Ordinality

Cardinality : Maximum number of times an instance in one entity can relate to instance of another entity.

Ordinality : Minimum number of times an instance in one entity can relate to instance of another entity.

Both are depicted diagramatically using connectors as below:

Notations for ER Diagram

Consider an example of a Job Portal database.

Here, 'Phone', 'Authors' is Multivalued attributes.
'Age' is a derived attribute.
'Stu_id', 'Book_id' are primary keys.
'Reads' is the relation between 'Student' and 'Book'.
'Stud_name' is Composite attribute.

Keys

Keys are used to uniquely identify any entry of data in the database.
Types of keys:

• Primary key
• Candidate key
• Super key
• Foreign key

1. Primary key: It uniquely identifies only one entry in the database. For example, In the above example, 'Stu_id' is the primary key as it is unique for every student entry.

2. Candidate key: It is all the attributes that are suitable to be a primary key. For example, 'Stu_id' is a candidate key in the above example.

3. Super key: It is a set of attributes that are suitable to be a primary key. For example, In above example, if there will be a new attribute 'aadhar number' for every student, then candidate key would have been 'Stu_id' and 'aadhar number'.

4. Foreign key: When a primary key of one table is reflected another table to connect the tables, that key is called a foriegn key in the receiver table and is called primary key in the host table. For example, 'Book_id' from Book table when relfected into Student table, it will be called 'Foreign key' in student table.

Converting an ER model into Relational model

• The Entities becomes the Table.
• Single valued attributes become column for the table
• Primary key attribute is also displayed as column. It is just displayed with an underline to differentiate from other attributes.
• Multivalued attributes are represented using a separate table.
• Composite attributes are represented as columns separately.
• Derived attributes are not considered in the table.

Following is the Relational diagram for the previous example.

With this article at OpenGenus, you must have a strong idea of Entity Relationship Model. Enjoy.