ER Diagram of Bank Management System

ER Diagram for Bank Database

An "entity-relationship diagram" is a kind of flowchart of a database that helps us to analyze the requirements and design of the database. It conveys the relationship between several entities of a specified system and their attributes.

It is a basic diagrammatic structure to represent a database and is considered good to start with an ER diagram before implementing the database system.

In this er diagram of the bank database, we have eight entities,

1. Customer, To represent the customers.
2. Banker, To represent the Banker, who manages the entire branch.
3. Branch, To represent a branch of a bank.
4. Loan, To represent the loan granted by the branch to the customer's account.
5. Account, To represent the bank account of any customer.
6. Transaction, To represent the transactions of customers for any account.
7. Credit Card, To represent the Credit card of any associated customer and account.
8. Loan Payment, To represent the Payment towards the loan.

ER Diagram consists of some shapes which have their significance i.e. Rectangles are used to represent the entities, Rhombus is used to represent the association between the entities, and Oval represents the attributes of an entity.

Schemas

The schema is an analytical layout or blueprint of the database that represents the logical view, like how the tables will look in the actual implementation of the database.

The schema diagram also represents the entity and its relationships but in the form of tables.

Below a schema diagram is shown for the banking system. The arrow outwards the rectangular box represents the relationship between them.

For example, there is an arrow from Banker to Branch which means there is a property branch_id which refers to the Branch.

Creating Bank Database Tables Using MySQL

The design phase and all conceptual discussions have been completed now. The further step is to implement these schemas and relations in the database in form of tables to represent that skeleton structure inside databases. Tables are the collection of related data where each row represents a data entry which is usually called a tuple and reflects the information about any real-world object.

We will store each entity in the form of tables.

Create Database

Initially, we are going to create a database.

Show Databases

We can view databases with show databases statement.

Switch Database

When the database is successfully created we have to switch to it for work.

Create Tables

Here we will create tables corresponding to each entity and their relationship as described in the Schema Diagram.

Before start writing the queries here is a short description of what you will see in most of the queries.

• The NOT NULL written along with the field represents, the value for this field and must be provided at the time of insertion of data into the table.
• The AUTO_INCREMENT written along with the field represents, the value for that field and will be inserted internally and incremented at each insertion.
• The VARCHAR(30) represents that a particular field can store a maximum of 30 characters in that field value.
• PRIMARY KEY(field) represents that field is going to be the primary key.
• FOREIGN KEY(field) REFERENCES other_table(key_from_other_table), this syntax is used to refer to another table by using the field as a foreign key.

Branch

This query will create a table named branch having branch id as primary key, branch name, assets, and branch address.

Banker Info

This query will create a table named banker_info, having banker id as a primary key, banker name, and branch id.

Account

This query will create a table named account, having account id as a primary key, account type, and account balance. This table will refer to the branch table by the foreign key branch id.

Customer

The query written below will create a table named customer which will contain the customer id as primary key, customer name, mobile number, and date of birth. The account id is a foreign key that will be used to refer to the account table and will use to create an association between customers and their accounts.

Transaction

The query will create a table named transaction, having transaction id as a primary key, amount, customer id, and account id. This table will refer to the account and customer table by the foreign key.

Credit Card

The query written below will create a table named customer credit card having credit card id as a primary key, expiry date for that credit card, and card limit. The customer id and account id are foreign and will be used to refer to the associated customer and account for any particular credit card.

Loan

This query will create a table loan having loan id as a primary key, issued amount, and the remaining amount. The branch id will be used as a foreign key to refer to the branch that provided the loan and the account id will refer to the account on which the loan is being borrowed.

Loan Payment

This query will create a table named loan_payment which will have the loan payment id as the primary key and the amount of the payment. The loan id will refer loan table and identify the loan for which payment is being done.

Borrower Table

This query will create a table named borrower which will have the borrower id as the primary key, customer id, customer name, and loan id.

After running all these statements, we can see our tables with the show tables; command. It will show you something like this,

Also, we can view our schema by selecting any table, DESCRIBE banking_system.account;

Perform Querying

In this section, we will start performing the sql queries for the banking database,

Create Branches

To start operating with the banking system the first mandatory thing is the branch so we are inserting a few branches and their information in the database with the help of the Insert statement.

Output : If we select the all inserted data it will look like something this,

Create Bankers

A banker is a person who manages the branch we can use a simple insert statement to store the banker information in the database.

Output :

Create Accounts

To create an account we can insert data in the account table along with providing the necessary information.

Output :

Associate the Customer with the Account

After adding the accounts data we can create associated customers with them.

Output :

Perform Transactions on the Account

To perform any transaction we can insert the details of the transaction, associated account, and customer with that transaction.

After inserting data for the transaction, to keep the database consistent we will need to update the balance in the account.

Output :

Create Loan for Accounts

We can allocate the loan to any account by inserting related information in the loan table.

Output :

Issue Credit Card for Customer's Account

We can issue the credit card by providing the customer id, account id, and other related information.

Output :

Change the Expiry of Credit Card

The updation of expiry_date or limit can be done with the UPDATE clause,

Output :

Make a Payment Toward the Loan

To pay for a loan we can insert the data in the loan payment table and subsequently decrease the remaining amount of the loan.

Output :

Create the Loan Borrower Table

In our database the loan is related to the account and also the customer is related to the account so, consider a situation where we want to fetch a list of all borrowers.

We can use the JOIN in SQL, it is used to join two tables according to any given common field of both tables i.e. account id.

So the query written below will insert the data in the borrower table but this time we will not provide the values, instead, it will be extracted from the existing tables. The select statement in the following query will select some fields from the loan table and customer table. Finally, the INNER JOIN will be done on the account_id.

Output :