Hey I need help with this programming assignment. It must be done in Oracle. A s
ID: 3678024 • Letter: H
Question
Hey I need help with this programming assignment. It must be done in Oracle. A similar answer was posted on this website but it doesn't meet the requirements for this assignment because it was done in PHP rather than command line Oracle. http://www.sci.csueastbay.edu/~yu/cs4660/ For context of the course. CS 4660 Program Assignment #2 For this assignment you are to 1. Create an accounts table using the following cracct.sql 2. Complete the following statements to create 5 store procedures for the table accounts. a. Create or replace procedure Deposit(amt in number, br in char, acc in char) as b. Create or replace procedure Withdrawal(amt in number, br in char, acc in char) as c. Create or replace procedure transfer(amt in number, br1 in char, acc1 in char, br2 in char, acc2 in char) as d. Create or replace procedure New_account(amt in number, br in char, acc in char, nam in char, type in char, mat date) as e. Create or replace procedure Close_account(br in char, acc in char, nam in char) as For Withdrawal, there is a service charge of 50 cents for checking or saving accounts if the new balance of an account is below $100. There is a penalty of $50 for withdrawing from a CD account before the maturity date. The minimum balance in a CD account is $1,000. For transfer, the amt is transferred from (br1, acc1) to (br2, acc2). There is a service charge of 50 cents if the new balance of an account is below $100. There is a penalty of $50 for withdrawing from a CD account before the maturity date. The minimum balance in a CD account is $1,000. For New_account, the minimum amount to open a checking or savings account is $100, and the minimum for CD is $1000. For Close_account, the branch, acct, and name must match. 3. create a log table using the command drop table lab2_log; create table lab2_log(branch char(1), acct char(4), message varchar2(60)); Your procedures should insert a row into LAB2_LOG for each call, with the branch, acct, and a message indicating whether the transaction is completed or a particular error has occurred. 4. You can test your code by creating your own accounts table. (Create accounts table) insert the data from my accounts table to your accounts table. Then Run the command file lab2.sql The database tables can be restored with a ROLLBACK command. Be sure to use the original data each time you run the lab2.sql file to get the correct output. You should email me and attach your txt file that creates the 5 procedures and log table contents.
Explanation / Answer
1. Create an accounts table using the following cracct.sql
create table accounts
(accountNumber varchar2(10) PRIMARY KEY,
accountName varchar2(60),
accType varchar2(5)
)
/
2. Complete the following statements to create 5 store procedures for the table accounts.
a. Create or replace procedure Deposit
(amt in number, br in char, acc in char)
as
update DepositsTable
SET DepositAmount = :amt
where AccountNumber = :acc and
Branch = :br
/
b. Create or replace procedure
Withdrawal(amt in number, br in char, acc in char)
as
update table AccountsTable
set amount = amount - :amt
where
accountNumber = :acc
and
branch = :br
/
c. Create or replace procedure
transfer(amt in number, br1 in char, acc1 in char,
br2 in char, acc2 in char) as
select branch1, accountNumber1 from Table1
INTO :br1, :acc1
/
INSERT into Table2 values(:br1, :acc1)
/
d. Create or replace procedure
New_account(amt in number, br in char, acc in char,
nam in char, type in char, mat date)
as
Insert Into TableNewAccounts
values
("amt, :br, :acc, :nam, :type, :matdate)
/