Bank Database Design and Procedures

This practical assignment involves designing a relational database for a typical bank. The database needs to track customers (with attributes like last name, first name, address, and customer type), various account types (savings, checking, business), and transactions (deposits, withdrawals, transfers). The assignment requires creating several stored procedures to handle tasks such as adding new customers and accounts, processing transactions (with account balance updates and validation), querying account balances by state, updating customer addresses, and retrieving transaction details. Triggers are also required to maintain account balance integrity and log address changes. The assignment emphasizes data integrity through the use of constraints and error handling within the procedures.

Banking: Create a database for a typical bank. We will track customers of the bank to include;
last name, first name, address, and customer type (small business, large business, personal). The
bank has various types of accounts that we will track (savings, checking, business). A customer
may have multiple accounts of each type. Accounts also have a balance. An account may be
owned by one customer only. For each account you must keep track of all the transactions on the
account. Transactions types consist of deposits, withdrawals, and transfers. Each transaction
consists of the amount, type (deposit, withdrawal, transfer), date, and account(s) for the
transaction.
Requirements:
1. (Using a procedure) System should be able to add new customers ensuring that no
duplicates are entered, and type is valid. A message should be returned with the success
or failure of the action.
2. (Using a procedure) System should be able to add a new account for an existing
customer. Given a customer ID, and an account type, the system should verify that the
customer id, and account type are valid, then create a new account. A message should be
returned with the success or failure of the action.
3. (Using a procedure and trigger) System should be able to handle valid transactions on
valid accounts for valid customers maintaining the integrity of the account balances.
Specifically, the system should allow deposits to accounts (must have account number,
amount), withdrawals (must have account number, amount) from accounts and transfers
between accounts (must have 2 account numbers and an amount). When these
transactions are made, the account balance must be updated (via trigger). No account
balance may go below zero. A message should be returned with the success or failure of
the action.
4. (Using a procedure) Given a state, display the sum of the balances by state.
5. (Using a procedure) Given a customer id and a new address, update the customer’s
address. A message should be returned with the success or failure of the action.
6. (Using a procedure) Given an amount, display the account number, last name, and
amount of all transactions with an amount greater than the one passed into the procedure.
7. [EXTRA CREDIT]: Write a trigger so that anytime a customer changes their address,
the old address, new address, date, customer id and user ID are stored in a log.
NOTE: You must use at least 2 triggers, one to maintain the account balance and one for
validation. The validation trigger may be to validate the account type, customer type, the balance
not going below zero, or not allowing duplicates. A check constraint is also required. Use other
table constraints as appropriate.
Important: Each requirement should translate into at least one procedure depending on how you
decide to implement the requirement. #3 will require at least one procedure and one trigger.