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.
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.
Code the PL/SQL module for each of the following:
-
Trigger to enforce the referential integrity for the Transaction Ref_Nbr:
-
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).
-
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.
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
| TX_TYPE | |||
| Code | Description | ||
| D | Deposit | ||
| W | Withdrawal | ||
| B | Bill Payment | ||
| P | Purchase | ||
| R | Return | ||
| BRANCH | |||
| Nbr | Name | ||
| 101 | … make up your own name | ||
| 102 | … make up your own name | ||
| 103 | … make up your own name | ||
| 104 | … make up your own name | ||
| MERCHANT | |||
| Nbr | Name | ||
| 301 | … make up your own name | ||
| 302 | … make up your own name | ||
| 303 | … make up your own name | ||
| 304 | … make up your own name |
Step by step
Solved in 2 steps
