ER Model Explained — DBMS Concepts | Coding Chronicles
Tags: database, computerscience, writing, todayilearned
Published: 2023-02-12 04:07:29

# **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:

1. Entity
    
2. Attributes
    
3. 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

![Entity in an ER Model](https://cdn.hashnode.com/res/hashnode/image/upload/v1676144127414/c074f321-11b9-42e9-8c78-e56a0df5eac4.png)

.

# 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.

![Attribute in an ER Model](https://cdn.hashnode.com/res/hashnode/image/upload/v1676144155671/076bda0a-65a1-4e2e-a083-8d5ab52891ea.png)

## 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.

![Key Attribute in an ER Model](https://cdn.hashnode.com/res/hashnode/image/upload/v1676144172102/1e41a913-96c0-4779-8948-79abf57e5731.png)

### Composite Attribute

An attribute that is composed of multiple attributes is called a composite attribute.

Here’s how a composite attribute is represented:

![Composite attribute in an ER Model](https://cdn.hashnode.com/res/hashnode/image/upload/v1676144192222/af9c450b-5790-46d5-9b81-ed6cb325cfae.png)

### 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 in an ER Model](https://cdn.hashnode.com/res/hashnode/image/upload/v1676144224900/24bf412f-7f1b-4766-aeaa-0584601a8e32.png)

# 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:

1. One-to-One (1-1)
    
2. One-to-Many (1-M)
    
3. Many-to-One (M-1)
    
4. 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.

![One-to-One relationship in an ER Model](https://cdn.hashnode.com/res/hashnode/image/upload/v1676144237489/b7aa750b-8655-4aea-8ae0-529c014b597c.png)

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.

&lt;aside&gt; 💡 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.

&lt;/aside&gt;

### 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.

![One-to-Many relationship in an ER Model](https://cdn.hashnode.com/res/hashnode/image/upload/v1676144263378/ecdad9ff-8bef-43e9-99f0-97186a1d3cd1.png)

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.

![Many-to-One relationship in an ER Model](https://cdn.hashnode.com/res/hashnode/image/upload/v1676144284171/4fcf4eaa-143b-4321-b7cb-1ee9908dae11.png)

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.

![Many-to-Many relationship in an ER Model](https://cdn.hashnode.com/res/hashnode/image/upload/v1676144306211/6e7af0d9-e9b8-4c14-bf18-9142341c72cd.png)

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**](https://codingchronicles.adityakarad.com/series/dbms-concepts) **is the perfect way to do it.**

# Coding Chronicles Newsletter

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jcw5xc8wdvz4vwktvw0d.png)

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](https://codingchronicles.adityakarad.com).