Understanding the ER Model in DBMS

The ER model in DBMS helps us build and understand databases easily. It uses simple shapes like rectangles, ellipses, and diamonds to represent data items and how they connect with each other. This way, before creating the actual database, we can plan out what information needs to be stored and how different pieces of information relate to each other, making the whole process much more organized.

The Entity-Relationship (ER) model in DBMS is like a blueprint for organizing and illustrating how data elements relate to each other within a certain system. It helps in sketching a preliminary database design, presenting a simple and uncomplicated visualization of data structures and connections.

During the ER modelling process, we create a diagram known as an entity-relationship diagram, which helps to offer a straightforward, picture-like view of data and how different data elements interact and relate to each other, making the process of setting up a database easier & simpler to understand.

Explore Popular Databases Courses

Uses of ER Diagrams

• ER diagrams play a pivotal role in conceptualizing and designing databases by helping to outline the relationships between different data entities clearly.
• These diagrams can serve as a reference guide or documentation, offering an overview of the system structure and relationships, which can be particularly helpful for new team members or stakeholders to understand the database schema quickly.
• ER diagrams can be used to identify and solve potential issues in the database schema during the design phase, preventing complications at later stages of development.
• For maintenance and upgrade purposes, ER diagrams can be used to understand the impact of changes on different components and relationships within the system.

Also read: What are the Advantages of DBMS?

Let’s see the components in the ER Diagram.

Let’s see the symbols used in the ER Model in DBMS & then understand each component in detail.

1. Entity

An Entity refers to a thing or object in the real world that is distinguishable from other objects. Entities represent data we want to store in the database. They are usually depicted using rectangles in ER diagrams.

There are two major types of entities:

1. Strong Entity

This entity doesn’t depend on any other entity in the diagram. It has a primary key, uniquely identifying each entity instance.

2. Weak Entity

A weak entity depends on another entity to exist. It does not have a primary key but a partial key, which helps identify its instances in combination with the identifying entity’s primary key.

2. Attributes

An Attribute refers to a property or characteristic of an entity or a relationship. Each attribute contains a type of data that describes an aspect of the entity.

There are four major types of attributes:

1. Key Attribute

These are attributes used to identify an entity within an entity set uniquely. For instance, “Employee ID” could be a key attribute for the “Employee” entity.

2. Composite Attribute

These attributes can be divided into smaller sub-parts, representing more basic attributes with independent meanings. For instance, a “Full Name” attribute can be divided into “First Name” and “Last Name”.

3. Multivalued Attribute

These are attributes that can hold multiple values. For instance, a “Phone Number” attribute might be multi-valued if employees can have more than one phone number.

4. Derived Attribute

This is an attribute whose values can be derived from other attributes in the database. For example, an employee’s age can be derived from their “Date of Birth”.

3. Relationship

A Relationship is a logical link between two or more entities, depicting how the entities interact with each other. Relationships help to define the associations and dependencies between entities in a database.

There are four major types of relationships:

1. One to One Relationship

In this type of relationship, a single instance of an entity A is associated with a single instance of entity B. For instance, in a company database, an entity ‘Employee’ can have a one-to-one relationship with an entity ‘EmployeeDetails’, where each employee has one set of details.

2. One to Many: This is a relationship where a single instance of an entity A can be associated with multiple instances of entity B, but each instance of entity B can be associated with only one instance of entity A. For example, a ‘Department’ entity can have a one-to-many relationship with an ‘Employee’ entity, representing that one department can have many employees, but each employee belongs to one department.

3. Many to One: This is the inverse of the one-to-many relationship, where many instances of entity A can be associated with a single instance of entity B.

4. Many to Many: In this relationship, many instances of entity A can be associated with many instances of entity B. For example, an ‘Author’ entity can have a many-to-many relationship with a ‘Book’ entity, indicating that one author can write many books, and one book can have many authors.

Let’s see an example to understand this ER Model based on the information above.

Let’s take an e-commerce platform example. The goal is to illustrate how different components (entities) in a typical e-commerce business can interact with each other and what kind of attributes they might have. This ER model helps to depict the logical structure and flow of data in the system.

Entities
1. Customer

This entity represents the customers who are using the e-commerce platform.

Attributes

• CustomerID: A unique identifier for each customer registered on the platform.
• Name: The name of the customer.
• Email: The email address of the customer.

2. Product

This entity represents the products available for purchase on the platform.

Attributes

• ProductID: A unique identifier for each product listed on the platform.
• Name: The name of the product.
• Price: The price of the product.

3. Order

This entity represents the orders placed by customers on the platform.

Attributes

• OrderID: A unique identifier for each order placed on the platform.
• OrderDate: The date on which the order was placed.
• CustomerID (Foreign Key): Reference to the CustomerID in the Customer entity to identify who placed the order.
• ProductID (Foreign Key): Reference to the ProductID in the Product entity to identify which product is ordered.
• Quantity: The number of units of the product ordered.

A Foreign Key is an attribute in one entity that refers to the primary key of another entity. It is used to establish a relationship between the two entities.

Relationships

1. Places

• This is a relationship between the Customer and Order entities.
• Customers can place multiple orders, thus creating a one-to-many relationship from Customer to Order.

2. Contains

• This is a relationship between the Order and Product entities.
• An order can contain multiple products; similarly, a product can be contained in multiple orders, establishing a many-to-many relationship.

This ER model gives a foundational view of how an e-commerce database might be structured, facilitating various operations like order tracking, inventory management, and customer relationship management. It helps understand how data is stored, retrieved, and managed in an e-commerce environment.

Let’s make an ER Diagram for the same.

Thus, the ER model in DBMS is a foundational concept. Its graphical representation facilitates the clear and concise depiction of data entities, attributes, and the relationships between them. Utilizing symbols such as rectangles (to represent entities), ellipses (to denote attributes), and diamonds (to symbolize relationships) allows for a structured and detailed portrayal of complex data. Hence, you must learn it. Keep learning & keep exploring!

Check out DBMS courses here!