CPSC;Assignment 3 Banking App Solution

Description

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

1. Using your MySQL instance on GCP, create a database called bank
   1. Create the following tables in bank
      1. Customer(customer_id, ssn, first_name, last_nam)
      2. Account_xref(customer_id, account_num)
      3. Account(account_num, account_type, balance)
      4. Transaction_log(timestamp, trans_id, account_num, trans_type, trans_amount)
   2. 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
2. Create an app in python that is able to connect to and modify the bank database, and has the following functionality
   1. Create new customers
      1. Should allow customer input for the ssn/name fields, and should prompt them to input information for (b)
      2. ID can be randomly generated, but make sure it does not already exist
   2. New customers can create an account
      1. They will have a choice for the type of account (checking, savings)
      2. Should ask if they want to add money to their newly created account if they wish (c)
      3. Account_num can be randomly generated, but make sure it does not already exist
   3. Login
      1. If a customer already has an account, they should be able to input the account_num and access their information
   4. Customers can see the balance of their account(s)
   5. Customers can add money to any of their accounts
   6. Customers can take out money from any of their accounts
   7. Customers can transfer money between accounts (up to $5000)
   8. Logs transactions
      1. Any transaction from above that happens should have a row inserted into the transaction_log table with the corresponding information
      2. 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
      3. 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:

1. if a customer tries to create an account without having ever given any customer info
2. if a customer tries to modify an account that doesn’t exist
3. 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.