FINAL PROJECT REPORT
docx
keyboard_arrow_up
School
Trine University *
*We aren’t endorsed by this school
Course
CYBERSECUR
Subject
Information Systems
Date
Apr 3, 2024
Type
docx
Pages
18
Uploaded by HighnessMetalMantis41
FINAL PROJECT REPORT
List of Requirements:
1)
MySQL Workbench 8.0 CE.
2)
Anaconda3 Software.
Functional Requirements:
1)
User Login.
2)
Entering the Account ID.
Choosing Options for the Actions like
a)
Checking Balance.
b) Deposit.
c) Withdrawal.
d)
View Transactions.
e)
Change Password.
f)
Find a Branch.
g) Logout.
ER-DIAGRAM Outline:
Do
Run
Have
Have
Customers
Transactions
Branches
(1, *)
(0,*)
(1, *)
(1, *)
(0, *)
(1, *)
(0, *)
(1,1)
(1, *)
(0,*)
have
1)
Branches may have 0 or more Customers.
2)
Branches may have 0 or more Accounts.
3)
Accounts may be in 1 or more Branches.
4)
An Account will have 1 and only 1 Customer.
5)
An Account may perform 0 or more Transactions.
6)
Customers may have 0 or more Transactions.
7)
Customers may have 1 or more Accounts.
8)
Transactions may relate to 1 or more Accounts.
9)
Transactions may be related to 1 or more Customers.
10) Customers may be a holder in 1 or more Branches.
COMPLETE ER-DIAGRAM:
Bank Branch may have 0 or more customers.
Customers may have accounts in 1 or more branches.
Bank Branches may have 0 or more Accounts.
Accounts may be present in 1 or more branches.
An Account should have only one customer.
A Customer may have 1 or more accounts.
A Customer may do 0 or more Transactions.
Accounts
A Transaction may be done by 1 or more customers.
A Transaction may be done for 1 or more accounts.
An Account May have 0 or more Transactions.
NOTE:
In this ER-DIAGRAM I am assuming that accounts is a weak entity because if there are no customers then there are no accounts.
Schema Diagram:
create table BankBranches(
Branch_ID varchar(40),
Name varchar(20),
City Varchar(20) ,
Address varchar(40),
Phone varchar(20) ,
state varchar(20),
primary key (Branch_ID)
);
create table customers(
Customer_ID int,
First_Name varchar(20),
Last_Name varchar(20),
DateOfBirth Date,
PhoneNumber varchar(20),
Email varchar(40),
UserName varchar(10),
Password varchar(10),
Address varchar(40),
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
City varchar(20),
state varchar(20),
Zipcode int,
primary key (
Customer_ID)
);
create table accounts(
Account_ID int,
Account_number int,
Account_type varchar(10),
Balance int,
Open_date date,
Customer_ID int ,
Branch_ID Varchar(20),
foreign key (Customer_ID) references Customers(Customer_ID),
foreign key (Branch_ID) references Branches(Branch_ID)
);
create table Transactions(
Transaction_ID int,
Transaction_Date date,
Transaction_Type varchar(10),
Amount int,
From_Account_ID int,
To_Account_ID int
);
Explanation that the Schema Meets 3
rd
NF:
The third normal form (3NF) demands that a database schema satisfy two conditions:
1)
There cannot be recurring groups, composite keys, or derived data in a table; each column must contain information about the primary key.
2)
There should be no transitive relationships between any non-key columns (i.e., no non-key columns on non-key columns).
Table 1: Branches
Branch_ID serves as the primary key for the Branches database, while the other columns provide
all details about that key. There are no derived data, composite keys, or recurring groupings. There are no transitive dependencies since there are no non-key columns that depend on any other non-key columns.
Table 2: Customers
Customer_ID serves as the primary key for the Customers database, while the other columns provide all details about that key. There are no derived data, composite keys, or recurring groupings. There are no transitive dependencies because the City and State characteristics are independent of all other non-key columns in the table.
Table 3: Accounts
Account_ID serves as the primary key for the Accounts table, while the other columns provide all details about that key. Foreign keys that connect to other tables are the Customer_ID and Branch_ID columns. There are no derived data, composite keys, or recurring groupings. There are no transitive dependencies because the Branch_ID column does not rely on any other non-
key values in the table.
Table 4: Transactions
Transaction_ID serves as the primary key for the Transactions database, while the other columns provide all details about that key. Foreign keys that connect to the Accounts table are the From_Account_ID and To_Account_ID columns. There are no derived data, composite keys, or recurring groupings. In this table, there are no transitive dependencies.
In conclusion, the supplied schema has a primary key for each table, and all other columns are information about that key. There are no derived data, composite keys, or recurring groupings. Furthermore, none of the tables include transitive relationships. The supplied schema is therefore
in third normal form (3NF).
Ethical/Legal Problems:
1)
Potential for data privacy solutions because of the application is dealing with sensitive financial information we need to make sure that the customers data is protected if it is shared illegally with the third parties then the reputation of bank got damaged.
To resolve this ethical concern the bank applications should introduce data privacy policies and encryption techniques for data privacy and provide access to the customer to correct their data.
2)
Legal Point of view the application would need to be follow many regulations such as General Data Protection Regulation (GDPR) or California Consumer Privacy Act (CCPA
) and Bank Secrecy Act (BSA) or USA PATRIOT ACT the application should follow these acts.
3)
The bank should regularly audit the application and its processes to detect and address any potential legal or regulatory concerns to ensure compliance. The bank should also have a group of legal and compliance professionals on staff who can offer advice and guarantee that the application complies with all applicable rules and laws.
Source Code:
Python Code:
import mysql.connector
# establish connection to the MySQL database
db = mysql.connector.connect(
host="127.0.0.1",
user="root",
password="root",
database="bank_db"
)
# define a function to validate user input
def validate_input(prompt, input_type):
while True:
user_input = input(prompt)
try:
validated_input = input_type(user_input)
break
except ValueError:
print("Invalid input. Please try again.")
return validated_input
# define a function to get customer ID based on username and password
def get_customer_id(username, password):
cursor = db.cursor()
query = "SELECT customer_id FROM Customers WHERE username=%s AND password=%s"
cursor.execute(query, (username, password))
result = cursor.fetchone()
if result:
return result[0]
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
else:
return None
# define a function to check balance
def check_balance(account_id):
cursor = db.cursor()
query = "SELECT balance FROM Accounts WHERE account_id=%s"
cursor.execute(query, (account_id,))
result = cursor.fetchone()
if result:
print("Your current balance is:", result[0])
else:
print("Invalid account ID.")
# define a function to deposit money
def deposit(account_id, amount):
cursor = db.cursor()
query = "UPDATE Accounts SET balance=balance+%s WHERE account_id=%s"
cursor.execute(query, (amount, account_id))
db.commit()
print("Deposit successful.")
# define a function to withdraw money
def withdraw(account_id, amount):
cursor = db.cursor()
query = "SELECT balance FROM Accounts WHERE account_id=%s"
cursor.execute(query, (account_id,))
result = cursor.fetchone()
if result:
if result[0] >= amount:
query = "UPDATE Accounts SET balance=balance-%s WHERE account_id=%s"
cursor.execute(query, (amount, account_id))
db.commit()
print("Withdrawal successful.")
else:
print("Insufficient balance.")
else:
print("Invalid account ID.")
# define a function to view transactions
def view_transactions(account_id):
cursor = db.cursor()
query = "SELECT * FROM Transactions WHERE from_account_id=%s OR to_account_id=%s"
cursor.execute(query, (account_id, account_id))
results = cursor.fetchall()
if results:
for result in results:
print(result)
else:
print("No transactions found.")
# define a function to change password
def change_password(customer_id):
cursor=db.cursor()
# ask for old and new password
old_password = input("Enter old password: ")
new_password = input("Enter new password: ")
# verify old password
query = "SELECT password FROM Customers WHERE customer_id=%s"
cursor.execute(query, (customer_id,))
result = cursor.fetchone()
if result[0] != old_password:
print("Incorrect password.")
return
# update password in database
query = "UPDATE Customers SET password=%s WHERE customer_id=%s"
cursor.execute(query, (new_password, customer_id))
db.commit()
print("Password changed successfully. Please log in again with your new password.")
def find_branch(city):
cursor = db.cursor()
query = "SELECT address FROM BankBranches WHERE city=%s"
cursor.execute(query, (city,))
results = cursor.fetchall()
if results:
for result in results:
print(result[0])
else:
print("No branches found in that city.")
# main program loop
while True:
# ask user to login
username = input("Enter username: ")
password = input("Enter password: ")
customer_id = get_customer_id(username, password)
if customer_id:
print("Login successful.")
account_id = validate_input("Enter account ID: ", int)
# validate the account ID
cursor = db.cursor()
query = "SELECT * FROM Accounts WHERE account_id=%s AND customer_id=
%s"
cursor.execute(query, (account_id, customer_id))
result = cursor.fetchone()
if not result:
print("Invalid account ID.")
continue
while True:
print("Please choose an option:")
print("1. Check balance")
print("2. Deposit")
print("3. Withdraw")
print("4. View transactions")
print("5. Change password")
print("6. Find a branch")
print("7. Logout")
option = validate_input("Enter option number: ", int)
if option == 1:
check_balance(account_id)
elif option == 2:
amount = validate_input("Enter deposit amount: ", float)
deposit(account_id, amount)
elif option == 3:
amount = validate_input("Enter withdrawal amount: ", float)
withdraw(account_id, amount)
elif option == 4:
view_transactions(account_id)
elif option == 5:
change_password(customer_id)
break # logout after changing password
elif option == 6:
city = input("Enter city name: ")
find_branch(city)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
elif option == 7:
print("Logout successful.")
break # logout
else:
print("Invalid username or password.")
MYSQL CODE:
create database bank_db;
show databases;
use bank_db;
show tables;
create table BankBranches(
Branch_ID varchar(40),
Name varchar(20),
City Varchar(20) ,
Address varchar(40),
Phone varchar(20) ,
state varchar(20),
primary key (Branch_ID)
);
create table customers(
Customer_ID int,
First_Name varchar(20),
Last_Name varchar(20),
DateOfBirth Date,
PhoneNumber varchar(20),
Email varchar(40),
UserName varchar(10),
Password varchar(10),
Address varchar(40),
City varchar(20),
state varchar(20),
Zipcode int,
primary key (
Customer_ID)
);
create table accounts(
Account_ID int,
Account_number int,
Account_type varchar(10),
Balance int,
Open_date date,
Customer_ID int ,
Branch_ID Varchar(20),
primary key (Account_ID),
foreign key (Customer_ID) references Customers(Customer_ID),
foreign key (Branch_ID) references BankBranches(Branch_ID)
);
create table Transactions(
Transaction_ID int,
Transaction_Date date,
Transaction_Type varchar(10),
Amount int,
From_Account_ID int,
To_Account_ID int,
primary key (Transation_ID)
);
insert into bankbranches values('CH1015' , 'CHASE' , 'RAHWAY' , '1342St,GeorgesAve,Avenel,NJ' , '(732) 726-5699' , 'NewJersey');
insert into bankbranches values('CH2026' , 'CHASE' , 'EDISON' , '46 ParsonageRd,Edison,NJ' , '(732) 549-0707' , 'NewJersey');
insert into bankbranches values('CH2020' , 'CHASE' , 'ISLIEN' , '755 US-1 South,Islien,NJ' , '(732) 283-4730' , 'NewJersey');
insert into bankbranches values('CH2029' , 'CHASE' , 'JERSEYCITY' , '26 JournalSQ,JerseyCity,NJ' , '(201) 216-8665' , 'NewJersey');
insert into bankbranches values('CH2024' , 'CHASE' , 'CHATHAM' , '407 Main st,Chatham,NJ' , '(973) 457-2584' , 'NewJersey');
insert into customers values('101540' , 'JOHN' , 'ARRON' , '1999-10-09' , '(937) 819-7048' , '
johnarron07@gmail.com
' , 'John99' , 'John@99' , '562 Journal Square,NJ' , 'JournalSquare' , 'NJ' , '07306' );
insert into customers values('202641' , 'DAVID' , 'TIM' , '1989-09-05' , '(728) 904-1092' , '
timdavid09@gmail.com
' , 'Tim89' , 'Tim@89' , '1045 Green St,NJ' , 'Islien' , 'NJ' , '08830' );
insert into customers values('202042' , 'SALT' , 'PHIL' , '1987-01-06' , '(987) 098-2076' , '
philsalt01@gmail.com
' , 'Salt87' , 'Salt@87' , '109 FarHills Ave,NJ' , 'Edison' , 'NJ' , '07905' );
insert into customers values('202949' , 'ALI' , 'MHD' , '2000-05-09' , '(765) 562-0978' , '
mhdali05@gmail.com
' , 'Ali00' , 'Ali@00' , '10 Mobile Ave,NJ' , 'WoodBridge' , 'NJ' , '07095' );
insert into customers values('202444' , 'HUGIE' , 'PHILLIPS' , '2021-10-23' , '(723) 765-9072' , '
philipshugie23@gmail.com
' , 'Philip21' , 'Philip@21' , '234 Main St,NJ' , 'Middlesex' , 'NJ' , '07193' );
insert into accounts values('10151' , '10151342' , 'Savings' , '10000' , '2021-05-22' , '101540' , 'CH1015');
insert into accounts values('20262' , '20262342' , 'Savings' , '10000' , '2022-08-21' , '202641' , 'CH2026');
insert into accounts values('20203' , '20203342' , 'Savings' , '10000' , '2021-05-22' , '202042' , 'CH2020');
insert into accounts values('20294' , '20294342' , 'Savings' , '10000' , '2023-01-17' , '202949' , 'CH2029');
insert into accounts values('20245' , '20245342' , 'Savings' , '10000' , '2018-08-21' , '202444' , 'CH2024');
insert into transactions values('100234' , '2023-02-18' , 'Deposit' , '170' , '10151' , '10151');
insert into transactions values('202635' , '2022-09-22' , 'Withdrawl' , '1200' , '20262' , NULL);
insert into transactions values('202045' , '2021-07-16' , 'OnlineTran' , '30' , '20203' , '20262');
insert into transactions values('108764' , '2023-04-11' , 'CheckDep' , '1000' , '20294' , '20294');
insert into transactions values('129864' , '2019-09-10' , 'Deposit' , '100' , '20245' , '20245');
Output Snapshots:
1)
USER LOGIN
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
2)
CURRENT BALANCE
3)
DEPOSIT
4)
CURRENT BALANCE AFTER DEPOSIT
5)
WITHDRAWL
6)
CURRENT BALANCE AFTER WITHDRAWL
7)
TRANSACTIONS:
8)
CHANGE PASSWORD:
9)
LOGIN AFTER PASSWORD CHANGE:
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
10)
FINDING A BRANCH
11)
LOGGING OUT
12) WRONG USERNAME AND WRONG PASSWORD
13) WRONG WITHDRAWL
14) WRONG CITY BRANCH
15) WRONG OPTION
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help