A Bank Management System (BMS) is a critical application used by banks to handle various services, such as managing customer accounts, processing transactions, managing loans, and overseeing employees. An effective database design ensures smooth operations, secure data handling, and efficient query execution.

In this case study, we’ll explore how to model a database for a Bank Management System (BMS) using Entity-Relationship (ER) diagrams and then map it to a relational database schema. By following this guide, you’ll gain the skills to design and implement a database for any banking system.

Requirements Analysis

Understanding the system's requirements is the first and most crucial step. It ensures that the database will support all necessary functionalities.

For our case study, let's outline the key functionalities that the HMS should support:

• Customer Management: Storing and retrieving customer information.
• Account Management: Maintaining account details (e.g., savings, current).
• Transaction Processing: Handling deposits, withdrawals, and fund transfers.
• Loan Management: Managing loan applications, approvals, and repayments.
• Branch Management: Tracking bank branch details.
• Employee Management: Maintaining employee roles and work details.
• Card Management: Issuing and tracking credit/debit cards.
• Account and Card Linking: Associating accounts with issued cards.

Step-by-Step ER Diagram Creation

We will build the ER diagram for the Hospital Management System through the following four steps.

Step 1: Identify Entities

Entities represent objects or concepts in the system that have data stored about them.

Primary Entities

1. Customer
2. Account
3. Transaction
4. Loan
5. Loan_Repayment
6. Branch
7. Employee
8. Card

Step 2: Detailing Attributes

We'll define attributes for each entity, including primary keys (PK) and foreign keys (FK).

1. Customer

• Customer_ID (PK)
• Full_Name
• Address (Composite)
  • City
  • State
  • Area
• Phone_Number
• Email
• Date_of_Birth
• Identification_Type (e.g., Passport, ID Card)
• Identification_Number

2. Account

• Account_ID (PK)
• Account_Type (Savings, Current)
• Balance
• Date_Opened
• Branch_ID (FK)
• Customer_ID (FK)

3. Transaction

• Transaction_ID (PK)
• Transaction_Type (Deposit, Withdrawal, Transfer)
• Amount
• Transaction_Date
• Notes
• Account_ID (FK)

4. Loan

• Loan_ID (PK)
• Loan_Type (Home, Personal, Car)
• Loan_Amount
• Interest_Rate
• Start_Date
• End_Date
• Account_ID (FK)

5. Loan_Repayment

• Repayment_ID (PK)
• Repayment_Date
• Amount
• Loan_ID (FK)

6. Branch

• Branch_ID (PK)
• Branch_Name
• Address
• Phone_Number

7. Employee

• Employee_ID (PK)
• Name
• Role
• Email
• Branch_ID (FK)

8. Card

• Card_ID (PK)
• Card_Type (Credit, Debit)
• Expiry_Date
• Card_Limit
• Customer_ID (FK)

Step 3: Defining Relationships

Let’s define the relationships between entities in the system.

1. Customer and Account

• A customer can have multiple accounts.
• Each account belongs to one customer.
• Type: One-to-Many (Customer to Account)

2. Account and Transaction

• An account can have multiple transactions.
• Each transaction is linked to one account.
• Type: One-to-Many (Account to Transaction)

3. Account and Loan

• An account can be associated with multiple loans.
• Each loan is linked to one account.
• Type: One-to-Many (Account to Loan)

4. Loan and Loan_Repayment

• A loan can have multiple repayments.
• Each repayment is linked to one loan.
• Type: One-to-Many (Loan to Loan_Repayment)

5. Customer and Card

• A customer can have multiple cards.
• Each card belongs to one customer.
• Type: One-to-Many (Customer to Card)

6. Account and Card

• An account can be linked to multiple cards.
• A card can be linked to multiple accounts.
• Type: Many-to-Many (Account to Card)

7. Branch and Account

• A branch manages multiple accounts.
• Each account is associated with one branch.
• Type: One-to-Many (Branch to Account)

8. Branch and Employee

• A branch can have multiple employees.
• Each employee works in one branch.
• Type: One-to-Many (Branch to Employee)

Here is the final ER diagram.

Mapping the ER Diagram to a Relational Schema

When converting the ER diagram into a relational schema for our Bank Management System, we need to carefully handle various components to ensure data integrity and optimal performance. Here are the key considerations:

• Entity Tables and Attributes:

  • Create a table for each entity identified in the ER diagram, such as Customer, Employee, Branch, etc.
  • Define all attributes for each table, specifying appropriate data types and constraints. Also, create seprate table for multi-valued attributes like Phone_Number.
  • Set primary keys (PK) for unique identification of records in each table.

• Foreign Keys and Relationships:

  • Establish foreign keys (FK) to represent relationships between entities, linking tables through common attributes.
  • Ensure referential integrity by enforcing foreign key constraints, which maintain consistent and valid references between related tables.

• Handling Many-to-Many Relationships:

  • Introduce associative (junction) tables to resolve many-to-many relationships into two one-to-many relationships.
    • For example, Card_Account connects Card and Account.
  • Include composite primary keys in associative tables, typically combining the primary keys of the related entities.

Here is the Relational Schema diagram.

Now, let's translate the ER model into SQL tables.

1. Customer Table

CREATE TABLE Customer (
    Customer_ID INT PRIMARY KEY,
    Full_Name VARCHAR(100),
    City VARCHAR(50),
    State VARCHAR(50),
    Area VARCHAR(50),
    Phone_Number VARCHAR(15),
    Email VARCHAR(50),
    Date_of_Birth DATE,
    Identification_Type VARCHAR(20), -- e.g., Passport, ID Card
    Identification_Number VARCHAR(50)
);

2. Account Table

CREATE TABLE Account (
    Account_ID INT PRIMARY KEY,
    Account_Type VARCHAR(20), -- e.g., Savings, Current
    Balance DECIMAL(10, 2),
    Date_Opened DATE,
    Branch_ID INT,
    Customer_ID INT,
    FOREIGN KEY (Branch_ID) REFERENCES Branch(Branch_ID),
    FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)
);

3. Transaction Table

CREATE TABLE Transaction (
    Transaction_ID INT PRIMARY KEY,
    Transaction_Type VARCHAR(20), -- e.g., Deposit, Withdrawal
    Amount DECIMAL(10, 2),
    Transaction_Date DATE,
    Notes TEXT,
    Account_ID INT,
    FOREIGN KEY (Account_ID) REFERENCES Account(Account_ID)
);

4. Loan Table

CREATE TABLE Loan (
    Loan_ID INT PRIMARY KEY,
    Loan_Type VARCHAR(20), -- e.g., Home, Personal
    Loan_Amount DECIMAL(10, 2),
    Interest_Rate DECIMAL(5, 2),
    Start_Date DATE,
    End_Date DATE,
    Account_ID INT,
    FOREIGN KEY (Account_ID) REFERENCES Account(Account_ID)
);

5. Loan_Repayment Table

CREATE TABLE Loan_Repayment (
    Repayment_ID INT PRIMARY KEY,
    Repayment_Date DATE,
    Amount DECIMAL(10, 2),
    Loan_ID INT,
    FOREIGN KEY (Loan_ID) REFERENCES Loan(Loan_ID)
);

6. Branch Table

CREATE TABLE Branch (
    Branch_ID INT PRIMARY KEY,
    Branch_Name VARCHAR(50),
    Address VARCHAR(100),
    Phone_Number VARCHAR(15)
);

7. Branch_Phone_Number Table

CREATE TABLE Branch_Phone_Number (
    Branch_ID INT,
    Phone_Number VARCHAR(15),
    PRIMARY KEY (Branch_ID, Phone_Number),
    FOREIGN KEY (Branch_ID) REFERENCES Branch(Branch_ID)
);

8. Employee Table

CREATE TABLE Employee (
    Employee_ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Role VARCHAR(50),
    Email VARCHAR(50),
    Branch_ID INT,
    FOREIGN KEY (Branch_ID) REFERENCES Branch(Branch_ID)
);

9. Card Table

CREATE TABLE Card (
    Card_ID INT PRIMARY KEY,
    Card_Type VARCHAR(20), -- e.g., Credit, Debit
    Expiry_Date DATE,
    Card_Limit DECIMAL(10, 2),
    Customer_ID INT,
    FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)
);

10. Account_Card Table

CREATE TABLE Account_Card (
    Account_ID INT,
    Card_ID INT,
    PRIMARY KEY (Account_ID, Card_ID),
    FOREIGN KEY (Account_ID) REFERENCES Account(Account_ID),
    FOREIGN KEY (Card_ID) REFERENCES Card(Card_ID)
);