ER diagram for a Bank Database

Design a database using ER diagram for a commercial bank. This commercial bank maintains information about entities: Depositor, Account, Loan, and Branch. State any assumptions made in the design of the ER diagram.

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

Back to top

1. Identify the Entities

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

The entities are:

  • Depositor
  • Account
  • Loan
  • Branch

 Step-1-Entities-Bank-ER-Diagram

    Back to top

    2. Figure out Relationships and Participation Constraints

    Step-2-Relationships-Participation-Constraints-Bank-ER-Diagram

    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.

     

     

    Back to top

    3. Specify Cardinality Constraints

    Step-3-Cardinality-Constraints-Bank-ER-Diagram

    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.

     

    Back to top

    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

     

    Back to top

    5. Final ER Diagram

    ER-diagram-commercial-bank: Depositor, Account, Branch and Loan

    Downloads

    PDF

    PDF: Colored

    Image

    Image: Colored

     

    Back to top
    Asked in Year
    2018
    Course
    BBM
    University
    TU