Database Design and Implementation: Banking Scenario Assignment

This assignment involves designing and implementing a database for a banking system. The task requires creating an Entity-Relationship Diagram (ERD) to model customers, accounts, and transactions. Students are expected to use procedures to add new customers, accounts, and handle transactions (deposits, withdrawals, and transfers), ensuring data integrity and validation. Triggers are needed to update account balances, log customer address changes, and enforce constraints like preventing negative balances. The solution includes table statements, procedures, and examples demonstrating the functionality of adding customers, handling transactions, and updating customer addresses. The assignment emphasizes database design principles, procedural programming, and the use of triggers to maintain data consistency and integrity within the banking system. The solution provided demonstrates the creation of database objects like tables, sequences, and procedures to meet the specified requirements of the assignment.

Banking Scenario
Banking: Create a database for a typical bank ERD. 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. You should avoid putting FKs in ER diagrams.
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.

Sample examples of the work process:
Step 1 : create ERD diagram
Step 2 : create Physical diagram
Step 3 : tables statements
Step 4 : requirements, one example out of the seven requirements
(Using a procedure) System should be able to add new customers ensuring that no
duplicates are entered, and Gender is valid. A message should be returned with the
success or failure of the action.
CREATE OR REPLACE PROCEDURE add_new_customer
(
input_fname CUSTOMER.CUSTOMER_FIRST_NAME%type,
input_lname CUSTOMER.CUSTOMER_LAST_NAME%type,
input_addr CUSTOMER.CUSTOMER_ADDR_STREET%type,
input_city CUSTOMER.CUSTOMER_ADDR_CITY%type,
input_zip CUSTOMER.CUSTOMER_ADDR_ZIP%type,
input_state CUSTOMER.CUSTOMER_ADDR_STATE%type,
input_gender CUSTOMER.CUSTOMER_GENDER%type
)
IS
record_count NUMBER;
duplicate_exception EXCEPTION;
invalid_gender_exception EXCEPTION;

CURSOR customer_exists IS
SELECT COUNT (*)
FROM customer
WHERE UPPER (input_fname) = UPPER(CUSTOMER_FIRST_NAME)
AND UPPER(input_lname) = UPPER(CUSTOMER_LAST_NAME)
AND UPPER(input_addr) = UPPER(CUSTOMER_ADDR_STREET)
AND UPPER(input_city) = UPPER(CUSTOMER_ADDR_CITY)
AND input_zip = CUSTOMER_ADDR_ZIP
AND UPPER (input_state) = UPPER(CUSTOMER_ADDR_STATE)
AND UPPER (input_gender) = UPPER(CUSTOMER_GENDER);
BEGIN
IF (input_gender not in ('M', 'F') and input_gender is not null) THEN
raise invalid_gender_exception;
END IF;
record_count := 0;
OPEN customer_exists;
FETCH customer_exists INTO record_count;
CLOSE customer_exists;
IF record_count > 0 THEN
RAISE duplicate_exception;
END IF;
INSERT INTO customer
(CUSTOMER_ID,CUSTOMER_FIRST_NAME, CUSTOMER_LAST_NAME,
CUSTOMER_ADDR_STREET, CUSTOMER_ADDR_CITY,
CUSTOMER_ADDR_ZIP,
CUSTOMER_ADDR_STATE, CUSTOMER_GENDER)
VALUES
(CUSTOMER_ID_SEQUENCE.nextval,input_fname,
input_lname,input_addr,input_city,input_zip,input_state,input_gender);
COMMIT;
dbms_output.put_line ('New Customer inserted');
EXCEPTION
WHEN duplicate_exception THEN
dbms_output.put_line ('Customer exists already. Please try again');
WHEN invalid_gender_exception THEN
dbms_output.put_line ('Invalid gender entered. Please try again');
WHEN OTHERS THEN
dbms_output.put_line ('Transaction Failed, customer not inserted ');

END add_new_customer;
Requirement 1 – success:
Requirement 1 – Failure – Duplicate:
Requiremnt 1 – Failure – Invalid Gender: