ADBMS Group Project 1 (PDF)




File information


Author: Saurabh Jeet Singh

This PDF 1.7 document has been generated by Microsoft® Word 2016, and has been sent on pdf-archive.com on 08/01/2018 at 08:18, from IP address 97.96.x.x. The current document download page has been viewed 563 times.
File size: 4.84 MB (79 pages).
Privacy: public file
















File preview


ISM 6218
ADVANCED DATABASE MANAGEMENT SYSTEM
Banking Database of ABC Bank
with High Security Features

Submitted By: Shashank Swami
Ayush Sood
Saurabh Jeet Singh
Himanshu Ranjan
Shubham Mishra

U23024116
U58981286
U94231818
U65004929
U25655892

Table of contents
1) Project overview
2) Database Overview
3) Logical database design
4) Physical database design
5) Table Structure Definitions
6) Data generation and loading
7) Performance tuning
8) Database Programming
9) DBA Scripts
10) Database Security Features
11) Data Visualization and Business Insights

1|Page

Project Overview
The project is focused on security of ABC bank database. In this, we will
implement the high security features to overcome few problems that are
encountered in recent past. ABC bank user data has been compromised.
Important details of users such as SSN, date of birth, first name, last name,
zip code has been hacked due to security breach. Because of this security
breach fraud transactions have happened in past.
To overcome this problem, we are proposing extra layer of security in the
database at account login and transaction level. For this we will store
network details, secure IP addresses for last 10 logins and transactions, and
providing a limit on transactions amount until the user requests for
increasing the limit. When considering ways to protect sensitive database
information, it's important to ensure that the privacy protection process
doesn't prevent authorized people from obtaining the right data at the
appropriate time. It is also important that a database security solution be
application-transparent. This means there is no need to make any changes
to the underlying applications. The benefits of deploying applicationtransparent database security include faster implementation and lower
support costs.
In this project, we will be considering account balance, ATM withdrawal
details, bank account details, branch detail, credit card information, internet
banking activity details, transaction data, transaction limit details, network
properties, online transaction details, user session information and user
passwords. These data are required to incorporate more features in security
to mask the transactions with more layers of authentication.
Things which are covered in this project are bank database, on top of it we
are implementing our security features.

2|Page

First, we created out logical and physical database designs. After this, we
created tables structures and we have created datasets using excel and
http://yandataellan.com. We loaded the data in the tables in Oracle SQL
Developer. Few challenges were faced during creation and loading the data
in tables. Few interesting queries has been created based on different use
cases on the security features which is used for making the bank user data
safer.

3|Page

Database Overview
There are 18 tables which has been created and tried to maintain 3rd
normalization form in our database. Main table “CUSTOMER” is used to store
basic information of the customer like first name, last name, SSN, address,
gender, phone number etc. We have identified Customer ID as primary key
for this table and will be linked with other important tables such as deposit,
credit card, bank account, account balance, ATM, ip address, loan properties,
money transfer, net banking, security_qa, user passwords, and user session
as a foreign key to fetch data from database. IP address and network
properties table are used for applying new security features.
Each table contains unique set of data which are used for providing different
details about each aspect related to banking. Network properties, user
password tables are playing key role in providing extra security for the
database.
Table names and descriptions are as below:
S.N
O
Table Name

Table Description
Account Balance - Contains information of
1 ACCOUNT_BALANCE balance amount per customer
ATM - Contains information of transactions
2 ATM
done by a customer through ATM
ATM_TRANSACTION ATM Transaction Limit - contains information
3 _LIMIT
about ATM withdrawal transaction limit
Bank Account - contains information about
4 BANK_ACCOUNT
customer account
Branch Details - contains information about
5 BRANCH_DETAILS
bank's branch detail with address of branch
Credit Card - Contains credit card information
6 CREDIT_CARD
of customer

4|Page

Customer - contains information about
7 CUSTOMER
customer personal details
Deposit - contains information about deposits
8 DEPOSIT
done by customer
Ip Address - contains information about IP
9 IP_ADDRESS
address of the user's system
Money Transfer - contains information about
10 MONEY_TRANSFER customer transactions related to money
Net Banking - contains information about
11 NET_BANKING
customer's internet banking details
Network Properties - contains information
NETWORK_PROPERT about network properties of the internet
12 IES
service provider
ONLINE_TRANSACTI Online Transaction - contains information
13 ON
about customer online transaction details
Securityqa - contains information about
14 SECURITYQA
customer security questions and answers
User Passwords - contains information about
15 USER_PASSWORDS customer's internet banking passwords
User Session - contains information about
16 USER_SESSION
customer internet banking session

5|Page

Logical Database Design
A logical design is a conceptual, abstract design. In any database design, the
first step to implement is Logical database design. Here, we define how data
is arranged into a series of logical relationships called entities and attributes.
An entity is nothing but a chunk of information. An entity is often mapped to
a table in RDBMS. The uniqueness of an entity is defined by its component
called attribute. The logical design is more conceptual and abstract than a
physical design. The logical design’s output yields a set of entities and
attributes with respect to fact tables and dimension tables.
The process of logical design involves arranging data into a series of logical
relationships called entities and attributes. An entity represents a chunk of
information. In relational databases, an entity often maps to a table.
An attribute is a component of an entity and helps define the uniqueness of
the entity. In relational databases, an attribute maps to a column.
Entities which are created are:
ACCOUNT_BALANCE, ATM, ATM_TRANSACTION_LIMIT, BANK_ACCOUNT,
BRANCH_DETAILS, COLLATERAL, CREDIT_CARD, CUSTOMER, DEPOSIT,
IP_ADDRESS, LOAN_PROPERTIES, MONEY_TRANSFER, NET_BANKING,
NETWORK_PROPERTIES
ONLINE_TRANSACTION,
SECURITYQA,
USER_PASSWORDS, USER_SESSION
Based on these entities we have maintained different attributes which are
used in the database.

6|Page

7|Page

Physical Database Design

8|Page






Download ADBMS Group Project-1



ADBMS Group Project-1.pdf (PDF, 4.84 MB)


Download PDF







Share this file on social networks



     





Link to this page



Permanent link

Use the permanent link to the download page to share your document on Facebook, Twitter, LinkedIn, or directly with a contact by e-Mail, Messenger, Whatsapp, Line..




Short link

Use the short link to share your document on Twitter or by text message (SMS)




HTML Code

Copy the following HTML code to share your document on a Website or Blog




QR Code to this page


QR Code link to PDF file ADBMS Group Project-1.pdf






This file has been shared publicly by a user of PDF Archive.
Document ID: 0000718172.
Report illicit content