Database Systems and Design Assignment: Bank Database Implementation

This assignment focuses on the design and implementation of a database system for a bank, encompassing various aspects of database management. It begins with the selection of a bank case study, detailing its operations, including customer information, transactions, accounts, credit cards, and loans. An Entity-Relationship (ER) diagram is created to visually represent the database structure, highlighting entities such as bankers, customers, accounts, loans, and credit cards, along with their attributes and relationships. The assignment then delves into logical database design, considering factors like primary and foreign keys. Subsequently, database tables are created, and data is populated into these tables. The student also includes four useful indexes in tables. The assignment also includes the writing of SQL queries to retrieve and manipulate data, demonstrating proficiency in database querying. The assignment concludes with a successful database creation, incorporating six tables, data population, and the utilization of primary and foreign keys, along with SQL queries to demonstrate functionality. References to relevant books and journals are also provided.

DATABASE SYSTEMS AND
DESIGN

Table of Contents
INTRODUCTION...........................................................................................................................1
TASK 1............................................................................................................................................1
1.1 Selection of a case study for designing and implementation of a data base.....................1
1.2 Creation of ER diagram with details................................................................................2
TASK 2............................................................................................................................................3
2.1 Logical database design according to the case study.......................................................3
2.2 Creation of database table................................................................................................7
2.3 Four useful indexes in tables............................................................................................9
2.4 Data population..............................................................................................................10
2.5 SQL query writing..........................................................................................................10
CONCLUSION..............................................................................................................................15

INTRODUCTION
A database can simply be defined as a collection of data that is generally accessed or
stored electronically from a computer systems. There are many types of database available out of
which here oracle SQL developer has been used. In this assignment a database will be created
based on operations of a bank. According to bank case study here six tables will be created with
some data inserted in it. After insertion of data some SQL queries will be fired to achieve the
result.
TASK 1
1.1 Selection of a case study for designing and implementation of a data base
For this database designing and implementation assignment a bank and its operations
case study has been taken which consist of banker and their customer information (Nguyen and
et.al., 2018). It also consists of Transaction, account details, credit card details and loan details
(if taken by customers). It has one generalisation/specialization structure i.e. customer has a
credit card and an account.
Generalisation is a process of extracting shared characteristics from more than one classes.
Those classes can also be combined into a generalized super class. It occurs when differences
between two classes is ignored and similarities between lower entities are acknowledged to form
a higher entity. It is a bottom to up process (Eastman, 2018).
Specialisation is a process of creating sub classes from an existing class. It is a top to down
process. It also splits an entity to form multiple new entities that inherit some features of splitting
entity. It is applied on single entity.
(a) Relevant background information on the organization:
A bank is an organization which involves saving, borrowing and lending of money to its
customers. They provide customers loan of a fixed amount, offers to save their deposits in return
for paying an annual interest payment to customers. A bank has many operations but its main
principle operations are cash withdraw, cash deposit, providing loan and saving deposit of
customers. Its target customers are lower value, middle value and higher vale customers, also all
the customers who has closed their accounts in the bank (Pei and et.al., 2018).
(b) operations supported by customers
This database support transaction, account, providing credit card, loan related operation
of all the customers.
1

1.2 Creation of ER diagram with details
In this ER diagram there are five strong entities and one weak entity. Banker, customer, account,
loan and credit card are the strong entities and transaction is the weak entity as it is dependent on
the customer(Zhang and et.al., 2018). There are many entities in this diagram that consist of a
primary key like
Entities Attributes
Banker  banker_id ( Primary Key )
 Banker_email
 banker-name
Customers  cust_id ( Primary key )
 cust_name
 cust_address
 account_no
2

Account  account no( Primary key )
 balance
 account_type
Credit card  card_no ( Primary key )
 cust_id
 limit
Loan  loan_id ( Primary key )
 loan_amount
 account_no
Transaction  atm_no ( Primary key )
 withdraw_cash
 cust_id
Relationship between all the entities are shown in the ER diagram above.
TASK 2
2.1 Logical database design according to the case study
Logical database designing is a process to decide the arrangement of attributes of all the
entities into database (Hoang and et.al., 2018). It reflects proper structured tables of database that
reflect companies environment. In this database all the logical database design factors have been
considered like primary key and foreign keys in all the tables.
3

4

5

6

2.2 Creation of database table
All the tables in the database has been created according to the logical database design.
Appropriate order of table creation has been considered like tables with primary keys have been
created first and then tables with foreign keys have been created.
 Create table banker
 Create table account
7

 Create table customers
 Create table credit_card
 create table loan
8

 Create table transaction
2.3 Four useful indexes in tables
9

2.4 Data population
Each table has some set of data stored in it. Each row in a set is a member of set and
those sets have some data stored in it. Populate in database simply means to add record into the
tables in database (Hung and et.al., 2018). It can be any kind of data i.e. whether inserting actual
data into the tables for implementation or some dummy data for testing. That large amount of
data present in database is called data population. In this database each row has five rows with
some data inserted into them.
2.5 SQL query writing
 Selection of particular table columns
10

 Inner Join of at least 2 tables
11

 Outer Join of at least 2 tables
 Use of count and/or another similar mathematical expression
12

 Use of a sorting/ordering facility
13

 A condition using “<”, “>”, LIKE etc.
14

15

 A condition using IN, NOT NULL, or similar.
CONCLUSION
In this assignment database is created successfully with six different tables inserted in it.
In each table five rows had been added. Each table has a primary key and some table's primary
keys has been used as a foreign key in another tables.
16

REFERENCES
Books and journals:
Eastman, C.M., 2018. Building product models: computer environments, supporting design and
construction. CRC press.
Hoang, V.D and et.al., 2018, March. Improving Traffic Signs Recognition Based Region
Proposal and Deep Neural Networks. In Asian Conference on Intelligent Information and
Database Systems (pp. 604-613). Springer, Cham.
Hung, N.V. and et.al., 2018. Database Design For E-Governance Applications: A Framework
For The Management Information Systems Of The Vietnam Commitee For Ethnic
Minority Affairs (CEMA). International Journal of Civil Service Reform and
Practice, 3(1).
Nguyen, N.T. And et.al., 2018. Intelligent Information and Database Systems: 10th Asian
Conference, ACIIDS 2018, Dong Hoi City, Vietnam, March 19-21, 2018,
Proceedings (Vol. 10751). Springer.
Pei, J and et.al., 2018. Database Systems for Advanced Applications. Springer.
Zhang, M. and et.al., 2018. Workload Management in Database Management Systems: A
Taxonomy. IEEE Transactions on Knowledge and Data Engineering, 30(7), pp.1386-
1402.
17