Banking Scenario Procedures: ER Diagram, SQL, and Procedures

The assignment content includes four procedures in Oracle PL/SQL. The first procedure, `add_new_account`, adds a new account to the database if the input is valid. If not, it raises an exception and displays an error message. The second procedure, `state_balance`, displays the sum of balances for a given state. The third procedure, `address_update`, updates a customer's address if the customer ID exists in the database. If the customer ID does not exist, it raises an exception and displays an error message. The fourth procedure, `transac_history`, displays all transactions with amounts greater than or equal to a specified amount. Finally, the extra credit task involves creating a trigger that logs changes to a customer's address.

BANKING SCENARIOPROCEDURES
Faisal Jahdlai
08 December, 2016

Faisal Jahdali
Second Draft
1 | Page

Contents
ER DIAGRAM
CREATE AND INSERT TABLE COMMANDS
SQL PROCEDURES
2 | Page

ER DIAGRAM
CREATE AND INSERT TABLE COMMANDS
CREATE SEQUENCE c1
MINVALUE 1
MAXVALUE 999999
START WITH 1
INCREMENT BY 1
CACHE 20;
3 | Page
Has 1,10,*
Has 0,*
1
,
Transaction
Trans_ID {PK}
Acct_ID {FK}
Amount
Date
Trans_Type
Cust_ID {PK}
Cust_Type
Last_Name
First_Name
Street
City
State
ZipCode
Sex
Customer
Acct_ID {PK}
Cust_ID {FK}
Acct_Num
Balance
Account

CREATE SEQUENCE T1
MINVALUE 1
MAXVALUE 999999
START WITH 1
INCREMENT BY 1
CACHE 20;
4 | Page

create table customer
(
cust_id int primary key,
cust_type varchar2(20),
fname varchar2(20),
lname varchar2(20),
street varchar2(30),
city varchar2(30),
zip number(6,0),
state varchar2(2),
gender char(1),
constraint chk_1 check (cust_type IN ('small business', 'large business', 'personal')),
constraint chk_2 check (gender IN ('M', 'F'))
);
5 | Page

INSERT INTO CUSTOMER VALUES (C1.NEXTVAL, 'small business', 'JIA', 'STEVEN',
'21 PARK STREET', 'ASPEN', 889988, 'CA', 'F');
INSERT INTO CUSTOMER VALUES (C1.NEXTVAL, 'large business', 'DIA', 'STONES',
'212 PARK VIEW ROAD', 'ASPEN', 889908, 'CA', 'F');
INSERT INTO CUSTOMER VALUES (C1.NEXTVAL, 'personal', 'LIA', 'DAVID', '41
GREEN PARK STREET', 'ASPEN', 889918, 'CA', 'F');
create table account
( acct_id int primary key,
cust_id int,
acct_type varchar2(20) not null,
acct_num number(16,0)unique ,
balance number(10,2),
constraint fk_1 foreign key(cust_id) references customer(cust_id),
constraint chk22 CHECK (acct_type IN ('savings', 'checking' , 'business'))
);
6 | Page

INSERT INTO ACCOUNT VALUES (1, 1, 'savings', 567890000007890, 1500.90);
INSERT INTO ACCOUNT VALUES (2, 2, 'savings', 567890000009990, 2000.90);
INSERT INTO ACCOUNT VALUES (3, 3, 'checking', 567890000001090, 15000.90);
create table transaction
(
transc_id int primary key,
acct_id int,
amount number(10,2),
trans_date date,
trans_type varchar(20),
constraint fk2 foreign key(acct_id) references account(acct_id),
constraint chk_t1 CHECK (trans_type IN ('deposit', 'withdrawal', 'transfer'))
);
7 | Page

INSERT INTO TRANSACTION VALUES (T1.NEXTVAL, 1, 678.00, '10-11-2015',
'deposit');
INSERT INTO TRANSACTION VALUES (T1.NEXTVAL, 2, 118.00, '11-11-2015',
'withdrawal');
INSERT INTO TRANSACTION VALUES (T1.NEXTVAL, 3, 500.00, '12-11-2015',
'deposit');
SQL PROCEDURES
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. (
Answer:
create or replace PROCEDURE add_new_customer
(
input_type customer.cust_type%type,
input_fname CUSTOMER.fname%type,
8 | Page

input_lname CUSTOMER.lname%type,
input_addr CUSTOMER.STREET%type,
input_city CUSTOMER.CITY%type,
input_zip CUSTOMER.ZIP%type,
input_state CUSTOMER.STATE%type,
input_gender CUSTOMER.GENDER%type
)
IS
record_count NUMBER;
duplicate_exception EXCEPTION;
invalid_type_exception EXCEPTION;
CURSOR customer_exists IS
SELECT COUNT (*)
FROM customer
WHERE UPPER (input_fname) = UPPER(fname)
AND UPPER(input_lname) = UPPER(lname)
AND UPPER(input_addr) = UPPER(street)
AND UPPER(input_city) = UPPER(CITY)
AND input_zip = ZIP
AND UPPER (input_state) = UPPER(STATE)
AND UPPER (input_gender) = UPPER(GENDER)
AND lower(input_type) = cust_type;
SET SERVEROUTPUT ON
BEGIN
IF (input_type not in ('small business', 'large business', 'personal') and input_type is not null) THEN
raise invalid_type_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
(CUST_ID,cust_type, FNAME, LNAME, STREET, CITY, ZIP, STATE, GENDER)
9 | Page

VALUES
(c1.nextval,input_type, 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_type_exception THEN
dbms_output.put_line ('Invalid type entered. Please try again');
WHEN OTHERS THEN
dbms_output.put_line ('Transaction Failed, customer not inserted ');
END add_new_customer;
10 | Page

1. (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
11 | Page

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.
create or replace PROCEDURE add_new_account
(
input_cid account.cust_id%type,
input_actype account.acct_type%type,
input_acctid account.acct_id%type,
input_num account.acct_num%type,
input_bal account.balance%type
)
IS
not_found_exception EXCEPTION;
invalid_actype_exception EXCEPTION;
cnumber number;
cursor c1 is
SELECT cust_id
FROM customer
WHERE cust_id = input_cid;
SET SERVEROUTPUT ON
BEGIN
IF (input_actype not in ('savings', 'checking', 'business') and input_actype is not null) THEN
raise invalid_actype_exception;
END IF;
open c1;
FETCH c1 into cnumber;
if c1%notfound then
raise not_found_exception;
END IF;
INSERT INTO account (acct_id , cust_id, acct_type, acct_num, balance) VALUES
(input_acctid , input_cid, input_actype, input_num, input_bal);
COMMIT;
dbms_output.put_line ('New account inserted');
12 | Page

EXCEPTION
WHEN not_found_exception THEN
dbms_output.put_line ('Customer data not found. Please try again');
WHEN invalid_actype_exception THEN
dbms_output.put_line ('Invalid account type entered. Please try again');
WHEN OTHERS THEN
dbms_output.put_line ('Transaction Failed, data not inserted ');
END add_new_account;
13 | Page

2. (Using a procedure) Given a state, display the sum of the balances by state.
create or replace PROCEDURE state_balance
(
input_states customer.state%type
)
IS
not_found_exception EXCEPTION;
cstate varchar2(2);
balances number(10,2);
14 | Page

cursor c1 is
SELECT state
FROM customer
WHERE state= input_states;
SET SERVEROUTPUT ON
BEGIN
open c1;
fetch c1 into cstate;
if c1%notfound then
raise not_found_exception;
else
select sum(a.balance) into balances from account a, customer c where c.cust_id = a.cust_id
and c.state = cstate;
dbms_output.put_line ('the balance is: '||balances);
END IF;
EXCEPTION
WHEN not_found_exception THEN
dbms_output.put_line ('state not found. Please try again');
WHEN OTHERS THEN
dbms_output.put_line ('Transaction Failed, enter a valid state');
END state_balance;
15 | Page

3. (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.
CREATE OR REPLACE PROCEDURE create or replace PROCEDURE
address_update
(
input_id customer.cust_id%type,
input_addr CUSTOMER.STREET%type,
input_city CUSTOMER.CITY%type,
input_zip CUSTOMER.ZIP%type,
input_state CUSTOMER.STATE%type
)
16 | Page

IS
cid int;
not_found_cust_exception EXCEPTION;
CURSOR customer_exists IS
SELECT cust_id
FROM customer
WHERE cust_id = input_id;
SET SERVEROUTPUT ON
BEGIN
OPEN customer_exists;
FETCH customer_exists INTO cid;
IF customer_exists%notfound THEN
RAISE not_found_cust_exception;
END IF;
update customer set street = input_addr, city = input_city,zip = input_zip, state =
input_state where cust_id = cid;
COMMIT;
dbms_output.put_line ('New Customer address updated');
EXCEPTION
WHEN not_found_cust_exception THEN
dbms_output.put_line ('data not found for update. Please try again');
WHEN OTHERS THEN
dbms_output.put_line ('Transaction Failed, customer not found ');
END address_update;
execute ADDRESS_UPDATE ('1','washington','towosn','21204','md');
17 | Page

4. (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.
create or replace PROCEDURE transac_history
(
input_amt transaction.amount%type
)
IS
acnum account.acct_num%type;
lastname customer.lname%type;
amts transaction.amount%type;
cursor c1 is
SELECT a.acct_num, c.lname, t.amount from account a, customer c , transaction t where
a.cust_id = c.cust_id and t.acct_id = a.acct_id and t.amount > = input_amt;
SET SERVEROUTPUT ON
BEGIN
open c1;
loop
fetch c1 into acnum, lastname, amts;
18 | Page

dbms_output.put_line ('account number: ' ||acnum);
dbms_output.put_line ('last name : ' ||lastname);
dbms_output.put_line ('amount: ' ||amts);
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ('Transaction Failed, data not inserted ');
END transac_history;
5. [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.
19 | Page