Instructions

1. 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.

2. 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)