ER Model Explained — DBMS Concepts | Coding Chronicles
In this post, we will discuss ER Model in DBMS, and its components like attributes, entities, relationships and their different types.
5 min read
Table of contents
- What is an Entity-Relationship Model (ER Model)
- Relationship Table
- Types of Relationships
- That's it, folks!
- Coding Chronicles Newsletter
What is an Entity-Relationship Model (ER Model)
An ER Model is a conceptual view of your database. It’s kind of a block diagram to logically visualise your database.
An ER model is used to represent the database structure or architecture of a system.
ER model consists of various components:
Entity is an object with a physical or conceptual existence.
It’s something that’s relevant to our database. For example, if we’re designing a school database, a student or a teacher (physical existence) is an entity.
A course taught at the school can also be an entity (conceptual existence)
Entity is represented by a rectangle in a ER model diagram.
Attribute is a property of an entity.
For example, a student (an entity) has various attributes like height, weight, blood group, class, and more.
Attribute is represented by an eclipse or oval in a ER model diagram.
Types of Attributes
There are many different types of attributes, so the most commonly used terms are below:
A key attribute is used to uniquely identify one entity instance from another.
It represents a primary key of that entity.
It’s represented by an oval with the text underlined.
For an entity like Student, Student ID can be key attribute which can uniquely identify a student.
An attribute that is composed of multiple attributes is called a composite attribute.
Here’s how a composite attribute is represented:
An attribute which has more than one value is called a multivalued attribute.
It’s represented by a double oval in the ER diagram.
An attribute which can be derived from another is called a derived attribute.
It’s represented by an oval with dashed lines in the ER diagram.
A relationship describes the relation between two entities.
It’s represented by a diamond or rhombus in the ER diagram.
Oh, and in case you haven’t noticed already, straight lines are used to link attributes to entities and an entity to its relationship.
A relationship table contains the mapping between two entities of a relationship.
It also gives us more information of the type of relationship between two entities.
employee_id is a foreign key referencing the primary key of
project_id is a foreign key referencing the primary key of
Types of Relationships
The number of times an entity of an entity set participates in a relationship set is known as cardinality.
On the basis of cardinality, relationships can be of different types:
When each entity in an entity set can take part only once in the relationship, the relationship is said to be one-to-one type of relationship.
Assuming an ideal world where a person is married to only one person, we can say that a male is married to only one female. Similarly, a female is also married to only one male.
This is an example of a one-to-one relationship.
relationship table in a one-to-one relationship looks like:
Here, since any
male_id as well as
female_id are unique, we can use either as a primary key for
💡 To save space, we can also merge
relationshiptables, since the primary key for both is
male_id. We can simply add a column to
Similarly, we can also merge
When one entity in an entity set can take part only once in the relationship and the other can take part more than once, the relationship is said to be one-to-many type of relationship.
Consider an e-commerce store like Amazon. An Amazon customer can place multiple orders, but an order can only belong to one customer.
This is an example of a one-to-many relationship.
relationship table in a one-to-many relationship looks like:
Here, since any
order_id is unique, we can use
order_id as a primary key for
Since there can be duplicate
customer_id in this table, we cannot merge it with
We can merge this table with
The definition for Many-to-One relationship is the same as One-to-Many relationship.
Consider the example of privately-owned cars. An individual can own multiple cars, but a car has only one owner.
This is an example of a many-to-one relationship.
Opposite of a one-to-many type of relationship, we can use
individual_id as a primary key for the relationship table.
Similarly, we can merge the relationship table with the
individuals table, but not
When each entity in an entity set can take part more than once in the relationship, the relationship is said to be Many-to-Many type of relationship.
In a corporate environment, an employee can be assigned to multiple projects, and a project can also be assigned to multiple employees.
This is an example of a many-to-many relationship.
relationship table in a many-to-many relationship looks like:
employee_id as well as
project_id can repeat in the table.
But a combination of
project_id is unique in this table. We can use
project_id as a primary key for this table.
We cannot reduce this table by merging it with either of the individual tables.
That's it, folks!
Learning about database management systems (DBMS) is crucial for developers as it improves data management skills, enhances problem-solving abilities, and helps with database design.
If you're looking to deepen your knowledge or learn Database Management Systems from scratch, following the Coding Chronicles DBMS series is the perfect way to do it.
Coding Chronicles Newsletter
If you like these posts, do subscribe to the newsletter. You'll be the first to know when a new blog post is released on Coding Chronicles.
Did you find this article valuable?
Support Aditya Karad by becoming a sponsor. Any amount is appreciated!