In the world of database design, entity relationship diagrams serve as valuable tools for designing complex systems and their relationships. In this article will go through the step-by-step process of designing an ER diagram, and defining how entities, attributes, and relationships are defined. Entity relationship diagrams are extremely important in database design and require a clear structure of all data.
Let’s take an example through which we can learn how to design an ER and understand all the required methods and terminology of an ER Diagram.
Entity Relationship Diagram for BANK
We are following the below steps to design an ER diagram:
- Defining Entities
- Adding Attributes
- Establishing Relationships
- Specify Cardinality
- Identify Primary Keys
- Draw the ER Diagram
- Benefits of ER Diagram
Defining Entities
A thing in the real world with an independent existence. It is may be an object with physical existence (ex: house, person) or with a conceptual existence (ex: course, job). The are represented by rectangle.
Let’s Defining Entities for Bank are:
Bank, Branch, Employee, customer, loan, account.
Entities
Adding Attributes
Attributes are the kind of properties that describe the entities. They are represented by ovals.
Let’s Defining the attributes for Bank are:
- For Bank Entity the Attributes are Bname, code.
- For Branch Entity the Attributes are Blocation, Bname.
- For Employee Entity the Attributes are Eid, Designation, salary.
- For Customer Entity the Attributes are Cid, Cname, Address, DOB.
- For Loan Entity the Attributes are Loan_no, amount, rate.
- For Account Entity the Attributes are acc_no, type.
Attributes
Establishing Relationships
Entities have some relationships with each other. Relationships define how entities are associated with each other.
Let’s Establishing Relationships between them are:
-
The Bank has branches.
- The Branch provides loan.
- The Employee works in branch.
- The Branch contains customers.
- The Customers has account.
- The Branch maintains account.
- The Customer avails loan.
Relationships
Specify Cardinality
Cardinality defines the numerical constraints on the relationships between entities. It is a notation that tells the ERD reader whether there are one, many or some combination of those factors between each entity.
1. One to One relationship(1:1)
In the entity relationship diagram, the one to one relationship means that single entity in one table is associated with single entity in another table. For example, one driver have only one license.
2. One to Many relationship(1:N)
One to many relationships means that single entity in one table is related to more than one entities in another table. For example, one bank has many branches.
3. Many to One(N:1)
Many-to-one relationship in entity relationship diagram means that multiple entities are related to only single entity in another table. For example, many developers works on single project.
4. Many to Many relationship(M:N)
Many to many relationship means that multiple entities in one table is associated with multiple entities in another table. For example, multiple customers have multiple accounts.
Specify cardinality for Bank:
- Bank and branch has One to Many relationship (a bank has multiple branches).
- Branch and loan has also One to Many relationship(a branch can provide many loans).
- Branch and employee has One to Many relationship(one branch has many employees).
- Branch and account has One to Many relationship(one branch has many accounts).
- Branch and customer has Many to Many relationship(multiple branches have multiple customers).
- Customer and account has Many to Many relationship(multiple customers have multiple accounts).
- Customer and loan has Many to Many relationships(multiple customers have multiple loans)
ER Diagram
Identify Primary Keys
Primary keys are the unique identifier for each record in database table. It is denoted by an underline under the attribute name.
- The Primary key of Bank is code.
- The Primary key of Branch is branch_code
- The Primary key of Employee is Eid.
- The Primary key of Customer is Cid.
- The Primary key of Loan is loan_no.
- The Primary key of Account is acc_no.
Final ER Diagram
The below diagram is our final entity relationship diagram for bank with all entities, their attributes and the relationship between them with the PRIMARY KEY and Cardinality ratio.
Final ER Diagram
Benefits of an ER Diagram
- ER diagrams provides a clear and visual representation of the database structure. This make it easier for developers and designers to understand the relationships between the entities.
- It is an effective communication tool for database designers and the stakeholders which helps to understand the data model.
- ER diagrams serve as the foundation for designing the database schema. They guide the creation of tables, relationships and constraints in the database management system.
- Creating an ER diagram helps in understanding and analyzing the data requirements of the system.
- Database managers can more quickly and efficiently undertake maintenance activities, including adding new entities or changing existing relationships, when they have a well-designed Entity Relationship diagram that makes the database structure easy to understand.
Conclusion
In the conclusion, the main components of the ER diagram, with a focus on the importance of correct representation, clear relationships, and well-defined attributes are stated in this article. The above example illustrates the step-by-step process of designing an ER diagram for a bank. We have identified the relationships, cardinality, and primary keys. The ER diagram provides a visual representation of the database structure. Refinement may be necessary based on specific requirements or feedback from stakeholders.