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
Attributes
Relationships
Entity
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
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:
Key Attribute
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.
Composite Attribute
An attribute that is composed of multiple attributes is called a composite attribute.
Here’s how a composite attribute is represented:
Multivalued Attribute
An attribute which has more than one value is called a multivalued attribute.
It’s represented by a double oval in the ER diagram.
Derived Attribute
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.
Relationship
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.
Relationship Table
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 | project_id |
|---|---|
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
Here,employee_id is a foreign key referencing the primary key of employee table
AND
project_id is a foreign key referencing the primary key of project table.
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:
One-to-One (1-1)
One-to-Many (1-M)
Many-to-One (M-1)
Many-to-Many (M-N)
One-to-One relationship
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.
The relationship table in a one-to-one relationship looks like:
| male_id | female_id |
|---|---|
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
Here, since any male_id as well as female_id are unique, we can use either as a primary key for relationship table.
<aside> 💡 To save space, we can also merge male and relationship tables, since the primary key for both is male_id. We can simply add a column to employee table named female_id.
Similarly, we can also merge female and relationship table.
</aside>
One-to-Many relationship
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.
The relationship table in a one-to-many relationship looks like:
| customer_id | order_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 1 | 4 |
Here, since any order_id is unique, we can use order_id as a primary key for relationship table.
Since there can be duplicate customer_id in this table, we cannot merge it with customers table.
We can merge this table with orders table.
Many-to-One relationship
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 cars table.
Many-to-Many relationship
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.
The relationship table in a many-to-many relationship looks like:
| employee_id | project_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 1 | 4 |
Here, employee_id as well as project_id can repeat in the table.
But a combination of employee_id and project_id is unique in this table. We can use employee_id + 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.
Top comments (0)