Banking Scenario SQL Procedures and Triggers
VerifiedAdded on  2019/09/18
|20
|1754
|421
Practical Assignment
AI Summary
This document presents a comprehensive solution for a banking scenario using SQL. It includes an ER diagram, SQL commands for creating and inserting data into tables, and several SQL procedures for adding new customers, adding new accounts, displaying balances by state, updating customer addresses, and displaying transaction history. Additionally, it includes a trigger to log customer address changes. The procedures are designed to handle exceptions and provide informative messages about the success or failure of each action. The document also includes sample data and execution examples.

BANKING SCENARIOPROCEDURES
Faisal Jahdlai
08 December, 2016
Faisal Jahdlai
08 December, 2016
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

Faisal Jahdali
Second Draft
1 | Page
Second Draft
1 | Page

Contents
ER DIAGRAM
CREATE AND INSERT TABLE COMMANDS
SQL PROCEDURES
2 | Page
ER DIAGRAM
CREATE AND INSERT TABLE COMMANDS
SQL PROCEDURES
2 | Page
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.

Trusted by 1+ million students worldwide

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 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
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

CREATE SEQUENCE T1
MINVALUE 1
MAXVALUE 999999
START WITH 1
INCREMENT BY 1
CACHE 20;
4 | Page
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
(
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
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.

Trusted by 1+ million students worldwide

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
'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
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

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 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
'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
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.

Trusted by 1+ million students worldwide

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
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
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

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
(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
customer. Given a customer ID, and an account type, the system should verify that
11 | Page
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.

Trusted by 1+ million students worldwide
1 out of 20

Your All-in-One AI-Powered Toolkit for Academic Success.
 +13062052269
info@desklib.com
Available 24*7 on WhatsApp / Email
Unlock your academic potential
Copyright © 2020–2025 A2Z Services. All Rights Reserved. Developed and managed by ZUCOL.