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.

 

Work to be submitted:

All SQL scripts created for this assignment.

All PL/SQL code created for this assignment.

Any other artifacts created for this assignment.

 

 

 

 

 

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.

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.

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.

PL/SQL code (10 marks)

Code the PL/SQL module for each of the following:

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

 

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

 

• Trigger to update the Account balance for each new transaction entered (assume that a transaction will never be updated or deleted). (3 marks)

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)

 

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)

 

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)

 

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.

CLIENT Client Nbr First Name wwwwwwwwwwwwww Last Name Street City Prov_State Postal Code Phone Email OWNS CLIENT_FK OWNS Client Nbr Account Nbr OWNS ACCOUNT FK TRANSACTION ACCOUNT FK TRANSACTION Tx Nbr Account Nbr Tx Type Code Tx Date Tx Amount Ref Nbr BANK BRANCH Branch Nbr Branch Name TX_TYPE_LOOKUP_FK MERCHANT Merchant Nbr Merchant Name ACCOUNT Account Nbr Balance TX_TYPE Tx Type Code Tx Type Descript wwwwwwwwwww