What is an ER diagram?
The full form of the ER diagram is the Entity Relationship diagram. This diagram is used in database management systems to have a rough idea of the relations between the entities present in a database system.
Entity generally means any real-life object which has attributes, and the entity may or may not exist physically in the real world. For example, a person, student, etc. are the entities that exist in this physical world, but bank accounts, etc., only exist logically. Attributes are the general properties of that entity by which we can distinguish it from other entities. One attribute can be common between more than one entity.
For example, a student entity has attributes like a school name, and a teacher entity also has the same attribute as a school name.
To draw the entity relationship diagram for any system, we have to identify each entity and its attributes. Also, we have to identify the relationship of a particular entity with other entities.
We will also have to determine the type of attributes and type of relationship between entities and then draw the diagram.
For a bank management system, let’s consider a scenario where a bank has lots of accounts and lots of customers. One bank has many branches with their unique branch number. Each account also has a unique account number, and there is also a loan account.
Let us identify the entity and its attributes in a bank system:
It is an entity where it has attributes like bank name, head branch address, starting date of establishment etc. bank has their unique code. So, unique code can be used as primary key.
As we know, one bank has a lot of branches, and we can consider one branch as the entity of the bank system. One branch can be attributes like branch name, branch address, branch code (IFSC code), etc. branch code or IFSC code is unique, so it is primary key.
One branch has many bank accounts. Each account has many properties like an account number (unique), date of account opening, account balance, type of account, etc. account number is the attribute that more than one account cannot have the same account number so that we can use it as the primary key for this entity.
A bank can serve a lot of customers. It is necessary that every person who has an account in the bank is a customer. Many people can be the customer of the bank, and each customer can have the customer id, customer name, phone number, address, etc. customer id for each customer is different, so it's a primary key.
The loan is also an entity as a bank issues loans to specific customers, and for a loan, there are a lot of properties attached to it like loan account number, loan amount, type of loan (home loan/student loan, etc.), date of the loan, maximum years of the loan, etc. Loan account number is different for different accounts so it will work here as the primary key.
Let us discuss the relationships between different entities:
1. Bank and branch
It is an easy relationship that 1 bank has many branches, but 1 branch cannot have many banks. So it is a 1 to N relationship.
2. Branch and account
One branch has a lot of accounts, and 1 account cannot exist in multiple branches. So it is also a 1 to N relationship.
3. Branch and Loan
One specific branch issues many loans, but one loan will not have multiple branches, so it is also a 1 to N relationship.
4. Loan and customer
One loan can be issued to many customers (joint account loan), and one customer can have many loans. So, it is like an M to N relationship.
5. Account and customer
One account can be held by more than one customer (joint account), and one person can have multiple accounts in the same branch. So it is also many to many, or we can say M to N relationship between these entities.
For drawing the ER diagram of any system, we should know that each entity will be represented by a rectangle box, and each relationship between two entities will be represented by a diamond-shaped box. Different attributes of an entity will be represented by circular shapes.
Let’s draw the ER diagram of the bank management system: