Analyze DBMS Schema of a a Bank Management System

Analyzing a Bank Management System Database Schema

Mr. Karim has developed a bank management system for a reputed bank. As part of this system, he designed a relational database with the following schema:

  branch(branch_name, branch_city, assets)

  customer(ID, customer_name, customer_street, customer_city)

  loan(loan_number, branch_name, amount)

  borrower(ID, loan_number)

  account(account_number, branch_name, balance)

  depositor(ID, account_number)

Based on this schema, here are some relational queries to answer specific information needs:

1. Retrieve the names of all branches located in the city of “Rangpur”
2. Find the IDs of borrowers who have taken a loan from the “Dhaka” branch

1. Find the names of all branches located in the city of "Rangpur"

Schema involved:

- branch(branch_name, branch_city, assets)

Analysis:

• The branch table contains information about each branch, including its name (branch_name) and location (branch_city).
• To find branches in "Rangpur", filter rows where branch_city = 'Rangpur'.
• The output should be the names of these branches.

SQL Query:

SELECT branch_name
FROM branch
WHERE branch_city = 'Rangpur';

2. Find the IDs of borrowers who have taken out a loan from the "Dhaka" branch

Schema involved:

- loan(loan_number, branch_name, amount)
- borrower(ID, loan_number)

Analysis:

• The loan table links loans to branches via branch_name.
• The borrower table links borrowers to loans via loan_number.
• To find borrowers from "Dhaka", identify loans where branch_name = 'Dhaka', then find associated borrowers by joining loan with borrower.

SQL Query:

SELECT DISTINCT b.ID
FROM borrower b
JOIN loan l ON b.loan_number = l.loan_number
WHERE l.branch_name = 'Dhaka';