This is database system course. I will need the answer for part 4 . (pL/SQL code). I have highlighted at the bottom. Tha

[answerhappygod]

This is database system course. I will need the answer for part
4 . (pL/SQL code). I have highlighted at the bottom. Thank you.
Narrative Description
The National Bank of Erehwon handles money and maintains bank
accounts on behalf of clients.
A client is a person who does business with the bank.
A client may have any number of accounts, and an account may
belong to multiple clients (e.g., spouses, business partners).
The client record is used for identification and contact
data.
For each account, the bank maintains the current balance on
hand.
Clients are identified by a five digit number starting with
10001.
Accounts are identified by a seven digit number starting with
1000001.
When an account is first opened, its balance is set to zero.
During the course of day-to-day business, Erehwon Bank applies
transactions to accounts, including deposits, withdrawals, bill
payments, and debit purchases or returns. For each transaction, the
date and time, amount, and account are recorded, along with
reference data applicable to that type of transaction:
Deposits and withdrawals require the branch number to be
recorded.
Bill payments, and debit purchases or returns require the
merchant number.
The text in the Notes columns in the spreadsheet is for
reference only, not to be attributes in the tables.
Work to be Submitted
Submit a .zip file named COMP3611_Assignment3 that contains:
All SQL scripts created for this assignment.
All PL/SQL code created for this assignment.
Any other artifacts created for this assignment.
Instructions
Building and Populating the Schema (9 marks)
Develop a database in Oracle to implement the Erehwon Bank
scenario. Code the SQL DDL to build all the tables, complete with
constraints and default values (except for lookup of the Ref_Nbr
for a Transaction ... that will require procedural code in step 4).
Choose appropriate data types for each of the columns. Add those
indexes that you believe would be useful. Include comments in your
SQL script(s) to document any decisions you made.
Populate your database with the test data.
In your SQL INSERT statements, you must make use of sequences
for generating key values for the Client, Account, and Transaction
tables.
You must use the TO_DATE function to get proper date and time
values for the transactions.
Note that trigger code to be written in step 4 will be used to
validate a Transaction Ref_Nbr against either a Bank_Nbr or
Merchant_Nbr as well as update the account balance according to the
type and amount of the transaction. The test data already has
entries for the Owns and Transaction INSERTS that should be
rejected due to invalid FK lookups. Take a screen shot of running
those scripts to show that is the case.
It is not required to demonstrate NOT
NULL constraints.
Views (4 marks)
To facilitate queries, three views are required. Submit your SQL
script(s) as well as a SELECT * FROM … listing of each view.
Join of Transaction to Type description (1 mark)
Join of Client to Account via the Owns table, containing the
client’s number, name, the account number, and balance. (1
mark) (Hint: Use an outer join as one client has no
accounts.)
Join of Deposit and Withdraw transactions to Bank Branch UNION
with join of Bill Payment and Debit Purchase, or Return
transactions to Merchant (i.e., all transactions with appropriate
reference name.) (2 marks)
Queries (7 marks)
There are five queries to be
developed and demonstrated. Submit your SQL script(s) as well as
the query listings for each.
List of only the Accounts that have multiple Clients associated
(1 mark)
Provide an alphabetic list by last name
of all Clients showing their full name
(e.g., Bob Barlow), with the number of Accounts they hold and the
total balance of those Accounts (1 mark)
Provide a count and total amount of Transactions for each Type
description (1 mark)
List of each Account showing the first Transaction date, type,
and amount. (Hint: This is a correlated sub-query.) (2
marks)
Count and Total Amount for each Transaction Type within each
Account (Hint: This is an extended GROUP BY.) (2
marks)
PL/SQL code (10 marks)
Code the PL/SQL module for each of the
following:
Trigger to enforce the referential integrity for the
Transaction Ref_Nbr: (3 marks)
Deposit or Withdrawal transaction to Bank
Branch
Bill Payment, Debit Purchase, or Return transaction to
Merchant
A procedure that displays a nicely formatted audit
statement for a given account number (as a parameter). This will
show each transaction in date / time sequence along with the
running balance. (4 marks)
To test that the triggers are correctly implemented, do
the following:
Truncate the Transaction table
Reset the Tx_Nbr sequence back to 1
Update the Account table, setting the Balance back to
zero
Re-run the INSERT statements for the
transactions
Use simple queries to demonstrate that the results in
the Transaction and Account tables are as expected