Assignment 3 Banking App Solution

Directions: This assignment requires both MySQL and Python. It is broken into 2 steps:

Using your MySQL instance on GCP, create a database called bank

Create the following tables in bank

Customer(customer_id, ssn, first_name, last_nam)
Account_xref(customer_id, account_num)
Account(account_num, account_type, balance)
Transaction_log(timestamp, trans_id, account_num, trans_type, trans_amount)
You are in charge of data types and keys. Make sure to specify them in a way that makes sense and can be used to link these tables together
Create an app in python that is able to connect to and modify the bank database, and has the following functionality

Create new customers

Should allow customer input for the ssn/name fields, and should prompt them to input information for (b)
ID can be randomly generated, but make sure it does not already exist
New customers can create an account

They will have a choice for the type of account (checking, savings)
Should ask if they want to add money to their newly created account if they wish (c)
Account_num can be randomly generated, but make sure it does not already exist
Login

If a customer already has an account, they should be able to input the account_num and access their information
Customers can see the balance of their account(s)
Customers can add money to any of their accounts
Customers can take out money from any of their accounts
Customers can transfer money between accounts (up to $5000)
Logs transactions

Any transaction from above that happens should have a row inserted into the transaction_log table with the corresponding information
In the case of transferring money between accounts, 2 rows should be written – one for the debit of one account, and one for the addition to the other
The trans_id can be randomly generated


Each piece of the python app should be its own function, with a main() method that continuously takes user input until they decide to quit. The user should have a menu of each “function” of banking available to them at each input step. An example menu is below (yours does not have to match exactly, do whatever you think is best):




Hello, welcome to <bank_name! You can do the following:

1. new customer

2. create account

3. check balance

4. deposit money

5. withdraw money

6. transfer money

7. quit




Please select an option:




All python functionality will modify the information held in the tables made in Step 1 – the data should persist. If a transaction is done, and the user quits, they should be able to run the app again and see the information for the transaction. Your app should include a light-level of error checking. Some examples that your app should be able to handle are below:

if a customer tries to create an account without having ever given any customer info
if a customer tries to modify an account that doesn’t exist
if a customer does not have enough balance to complete a transaction



You may work individually or with one other person (teams of 2 MAX). If you work with someone, put it in a comment at the top of your files – both of you still need to submit.




Turning In: Please submit two files – one .py file that includes your python code to manipulate the database tables, and one .sql file that includes your SQL code for creating the database/tables.