ER diagram for a commercial bank

Steps

Identify the Entities
Figure out Relationships and Participation Constraints
Specify Cardinality Constraints
List the Attributes
Final ER Diagram

For a more detailed explanation of each step, reading the article(Steps to create ER Diagram) is strongly recommended.

1. Identify the Entities

The list of entities is provided clearly in the given question.

The entities are:

Depositor
Account
Loan
Branch

2. Figure out Relationships and Participation Constraints

Based on our knowledge of the functioning of Banks, we assume the following Relationships and Participation Constraints.

Depositor holds Account

Every Depositor must have at least one Account. Hence, the participation of the Depositor in the relationship holds is Total.
Every Account must be owned by at least one Depositor. Hence, the participation of the Account in the relationship holds is Total.

Account is created by Branch

Every Account must be created by at least one Branch. Hence, the participation of the Account in the relationship 'created by' is Total.
Some Branches may not have created any Accounts. Hence, the participation of the Branch in the relationship 'created by' is Partial.

Note: We are representing Loan as a Weak Entity. The relationship borrows is an Identifying Relationship.

Depositor borrows Loan

Some Depositors may borrow a Loan. Some may not. Hence, the participation of the Depositor in the relationship borrows is Partial.
Every Loan is borrowed by at least one Depositor. Hence, the participation of the Loan in the relationship borrows is Total.

Loan is lent by Branch

Every Loan is lent by at least one Branch. Hence, the participation of the Loan in the relationship 'lent by' is Total.
A Branch may or may not lend Loan. Hence, the participation of the Branch in the relationship 'lent by' is Partial.

3. Specify Cardinality Constraints

Assumptions

A Depositor can hold multiple Accounts. An Account can be held by multiple Depositors. The concept of Joint Account is used here. Thus, "Depositor holds Account" is a Many-to-Many relationship.

An Account cannot be created by more than one Branch. A Branch can create multiple Accounts. Thus, "Account created by Branch" is a Many-to-One relationship.

A Depositor can borrow multiple Loans. A Loan can be associated with multiple Depositors, through Joint Account. Thus, "Depositor borrows Loan" is a Many-to-Many relationship.

A Loan cannot be lent by more than one Branch. A Branch can lend multiple Loans. Thus, "Loan lent by Branch" is a Many-to-One relationship.

4. List the Attributes

We are assuming the following attributes for each entity.

Entity	Attributes
Depositor	D_ID Contact D_Name Email
Account	Acc_No Balance Acc_type Creation_date
Branch	B_ID B_Name Location(State, District, City)
Loan	Loan_No Amount

5. Final ER Diagram

Downloads