Database Report: Banking System: Data Structure and Design

This report delves into the development of a relational database for a banking organization. It begins by identifying the organization's background and key stakeholders (customers and employees), followed by a detailed analysis of information system requirements. The report then analyzes existing data sources, defining tables for accounts, customers, deposits, transactions, interest, loans, managers, and officers. A conceptual data structure, including an entity-relationship diagram, is presented to illustrate the relationships between these entities. The core of the report involves the creation of a relational database, including table definitions with primary and foreign keys. Additionally, the report focuses on developing reports and queries to extract meaningful insights from the data. The report also considers complementary data stores and concludes with an overview of the developed database system. The assignment showcases a practical application of database design principles within a banking context.

Databases

Table of Contents
1. Introduction.......................................................................................................................1
1.1 Workplace Background............................................................................................1
2. Information System Requirement Identification...........................................................4
3. Analyse Existing Data Sources........................................................................................7
4. Conceptual Data Structure............................................................................................10
5. Develop a Relational Database......................................................................................11
5.1 Table Creation.........................................................................................................11
5.2 Entity Relationship Diagram..................................................................................14
6. Develop Reports, and Queries View..............................................................................16
6.1 Query View Creation..............................................................................................16
6.2 Report Creation.......................................................................................................19
7. Develop Complementary Data Stores...........................................................................22
8. Conclusion.......................................................................................................................24
References...............................................................................................................................25

1. Introduction
A report about building a bank organization is represented. A real world organization
is considered in this assignment. A bank refers to an institution which offers financial
services for the governments, businesses, and consumers. In contrast to the other bank types,
the commercial bank is a main bank type that comprises of less restrictions on its services.
Banks are interconnected with the economic growth of a nation, thus banking system is a
powerful instrument that is essential for a country. Any bank's quality services helps to
drastically increase its growth. However, the quality services to the customers ensures to
provide effective organization.
The profit of the commercial banks come by taking deposits from the customers, for
this the bank pays somewhat less interest rates, but it lends deposit to the borrowers with a
high interest rate. The customers’ purpose of lending money might include to purchase cars,
homes, and other things. However, it is also possible that the borrowers might be the
businesses who intend to finance their working capital requirements, equipment purchasing
and so on. On the other hand, the banks can even generate their revenue from the services
like mortgage loan maintenance, investment sales, and asset management (Banks and
Banking - type, cost, Commercial bank organization, Banks and small businesses, 2020).
In a democratic society, rapid banking development along with balanced development
is essential. Sound banking policies and effective organization are vital to have a sound
banking system. For expanding and deepening the banking services the main thing required
is “Specialised skills” and “Integration of effective and sound organisation”. Therefore, it is
crucial to have an organized banking system to achieve the expected goal of bank. An
organisation pools different means and activates them to accomplish their set goals.
1.1 Workplace Background
The objective of the commercial banks includes promoting people's economic status,
whereas its implicit objectives refer to effective utilization of resources, implementing
technology, diversifying the credits, and function as a financial investor based on national
priorities.
Today, the functions of the banks have been expanded and diversified. The below
depicted chart provides a gist of the structure followed in banks.
1

Figure: Bank Organization’s Chart (Bank Organizational Chart Example, 2016)
The banks function as an intermediary part in the financial transactions. It also
facilitates its customers with financial services and financial products. Itis evident that the
banks carry out various activities like making investments, money lending, selling of
financial products and so on, so it is essential to develop a strong and clear organizational
structure that ensures effective functioning.
With a help of mapping process shareholder groups are selected, which uses internal
survey for determining the respective shareholder groups' influence in the bank and on the
shareholder groups.
A bank's key stakeholders are as follows (Bank's Stakeholders, 2015):
2

1) Customers
2) Employees
3) Investors/Stakeholders
4) Business partners
5) Social environment
6) Market Regulators and Supervision
7) Natural Environment
Thus, the other shareholders can be as listed below:
1) Customers
2) Suppliers
3) Employees
4) Shareholders
5) Environment
6) Market regulators and supervision
7) Media
8) Competitors
9) Auditors
10) NGOs
11) CSR
12) Analysts
13) Society
14) Financial Analysts
The bank must have documented dialogue with the key stakeholders, just for knowing their
expectations and opinion by cooperating with the bank.
2. Information System Requirement Identification
3

The selected stakeholders are the customers and employees of the bank (Here, the
bank manager is also an employee of the bank).
The customers need branches of banks, bank website, the internet banking option,
info line, satisfaction surveys, meetings and claims for establishing communication channel.
Similarly, the manager requires intranet, in-house magazine, community discussion
board, internal meetings, satisfaction surveys, corporate events and evaluation systems for
establishing communication channel.
The frequency of dialogue can be an ongoing, monthly, quarterly or yearly.
The customers mainly raise the topics like:
1) Quality service or product
2) Ethical marketing practices
3) Ethical selling practices
4) Safety deposit funds
5) Easy bank communication
6) Product or process's improvements
The employees mainly raise the topics like:
1) Employment stability
2) Opportunities of professional development
3) Responding to the submitted queries
4) Fair salary and rewarding system
Respectively, the bank must take appropriate actions for the stakeholder’s raised
topics.
In banking information system, customer identification is the first step followed by
monitoring their transactions. Here, customer identification refers to a significant step which
must be handled carefully (Sam Waterson, 2020).
The following section determines the complete data application entities for banking
organization:
Account Table
The account table is essential to give customer’s account details.
4

Account No
Account Type
Customer ID
Balance
Interest Rate
Open date
Interest Amount
Customer Table
The customer table is essential to give customer’s personal details.
Deposit Table
The deposit table is essential to give customer’s deposited amount details.
Transaction Table
The transaction table is essential to give customer’s account transaction details.
5
Customer ID
Name
Address
DOB
Gender
Contact Number
PAN
Nationality
Account No
Closure Type
Customer ID
Deposit Account
No
Deposit Account
Type
Amount
Transaction ID
Account No
Transaction Type
Amount
Total Balance
Transaction Date

Interest Table
The interest table is essential to give customer’s interest details.
Loan Amount Table
The loan amount table is essential to give customer’s loan details.
Manager Table
The manager table is essential to give manager’s personal details.
Officer Table
The officer table is essential to give officer’s personal details.
6
Interest ID
Savings Interest
Loan Interest
Current Interest
Loan ID
Account No
Loan Type
Loan Date
Duration
Total Amount
Remaining Amount
Status
Interest
Manager Employee ID
Name
Address
Contact Number
Gender
Salary
Email
DOB
Officer Employee ID
Name
Address
Contact Number
Gender
Salary
DOB

3. Analyse Existing Data Sources
This section is used to analyse the existing data sources, structures and stores. Based
on the selected organization, the information system requirements are already identified, thus
now collect, process and analyse the data. The below tables provide the details of analyzing
the data (Beynon-Davies, 2017).
For Account Table,
Account_
No
Account_t
ype
Custome
r_ID
Balance Inter
est_
Rate
Open_date Interest_Amount
540501111 SB 4589632 15,42,333.00₹ 2 05-12-2019 4,782.00₹
540501112 SB 1478223 1,47,85,230.00₹ 5 03-12-2019 7,856.00₹
540501113 Joint 1478523 1,47,852.00₹ 4 30-10-2019 3,562.00₹
540501114 SB 4785236 1,57,852.00₹ 4 05-11-2019 2,596.00₹
540501115 Joint 1477859 14,785.00₹ 2 07-10-2019 1,452.00₹
For Customer Table,
Customer
_ID
Name Addr
ess
DOB Gender ContactNu
mber
PAN Nationality
1477859 JESSICA USA 18-07-1853 Female 725478963 NJU7856NJ USA
1478223 JOSEPH USA 04-02-1990 Male 896523410 JUN9876GN USA
1478523 MICHEAL USA 17-01-1992 Male 785412369 HYU9876JN USA
4589632 JOHN USA 06-03-1994 Male 854126370 CUN1234BN USA
4785236 RACHEL USA 05-09-1895 Female 781478526 BHY1475FV USA
For Deposit table,
Deposi
t_ID
Account_N
o
Closure_
Type
Customer
_ID
Deposit_Accoun
t_No
Deposit_Acc
ount_Type
Amount
14527 540501112 NO 1478223 785236900 SB 85,236.00₹
14782 540501111 NO 1477859 750222145 SB 1,475.00₹
14785 540501115 NO 4785236 147852369 SB 25,893.00₹
14789 540501113 NO 1478523 859632140 SB 1,47,852.00₹
15263 540501114 NO 4589632 147852369 SB 14,563.00₹
7

For Interest Table,
Interest_ID Savings_Interest Loan_Interest Current_Interest
1000 2.00% 5.00% 2.00%
1001 2.00% 5.00% 2.00%
1002 2.00% 5.00% 2.00%
1003 2.00% 5.00% 2.00%
1004 2.00% 5.00% 2.00%
For Loan Amount table,
Loan_I
D
Account_
No
Loan_type Loan_Date Duration Total_Amount Remaining_Amou
nt
125963 540501114 PERSONAL 09-08-2018 3 YEARS 1,50,000.00₹ 45,000.00₹
145236 540501111 PERSONAL 01-03-2019 2 YEARS 1,50,000.00₹ 75,000.00₹
145896 540501113 EDUCATION 16-02-2019 4 YEARS 1,00,000.00₹ 60,000.00₹
174852 540501112 HOME 26-07-2019 10 YEARS ₹
15,50,000.00
13,00,000.00₹
589632 540501115 BIKE 17-10-2019 3 YEARS 1,50,000.00₹ 90,000.00₹
For Manager Table,
Emp_I
D
Name Add
ress
Contac
tNumb
er
Gender Salary Email DOB
14523 ROBERY SAM US
A
859214
701
Male 50,000.00₹ SAM123@GMAI
L.COM
21-03-1988
14785 SAM JOSEPH UK 523967
410
Male 50,000.00₹ JOSEPH@YAHO
O.COM
28-08-1975
14789 JENELIA UK 147852
639
Female 50,000.00₹ JENELIA@GMAI
L.COM
05-12-1986
45632 JOEL WASAM UK 785236
941
Male 50,000.00₹ JOELWASM@YA
HOO.COM
19-11-1982
85963 CHUNG SHAN US
A
147852
336
Female 50,000.00₹ CHUNGSHAN@
GMAL.COM
25-07-1990
For Officer Table,
Emp_ID Name Addr Gender DOB Salary Contact_Number
8

ess
12365 RACHEAL USA Female 08-01-1980 1,54,222.00₹ 785412369
12589 JOSEPH IMMAN USA Male 18-12-1982 1,47,852.00₹ 147852369
14784 JOEL SAM UK Male 23-04-1973 1,42,000.00₹ 526369874
14785 IMMAN SAM UK Male 04-09-1974 1,45,263.00₹ 145236987
78569 JESSICCA UK Female 06-11-1985 1,35,000.00₹ 125987463
For Transaction table,
Transaction_I
D
Account_
No
Transaction_Ty
pe
Amount Total_Balance Transaction_Date
12563 540501112 CHEQUE ₹
14,58,962.00
52,00,000.00₹ 16-01-2020
14852 540501113 CASH 14,522.00₹ 7,85,412.00₹ 10-12-2019
25638 540501114 CHEQUE 12,596.00₹ 14,78,523.00₹ 04-12-2019
25968 540501115 DD ₹
14,52,368.00
₹
1,47,85,236.00
06-03-2020
47859 540501111 CASH 1,50,000.00₹ 30,00,000.00₹ 08-01-2020
9

4. Conceptual Data Structure
The conceptual data structure for the Banking organization is illustrated below (Castano,
2012).
10
Bank
Database
Account
Customer
Deposit
Account Number
Account Type
Balance
Interest Rate
Open Date
Loan Amount
Transaction
Officer
Manager
Customer ID
Name
Address
DOB
PAN
Deposit ID
Deposit Account No
Account Type
Amount
Customer ID
Transaction ID
Account No
Amount
Total Balance
Employee ID
Name
Address
DOB
Salary
Employee ID
Name
Address
DOB
Salary
Loan ID
Type
Account No
Duration
Loan Amount