I will thumbs up definitely, if you solve this correctly!

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

   • 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)

   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

1. 

TX_TYPE Code D W B R Description Deposit Withdrawal Bill Payment Purchase Return m 2 C 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 II. 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 II II. Street City Prov PostCd Phone Email CLIENT ClientNbr FName LName 10001 ** your own names goes here 10002 ... make up other names, etc. 10003 ... make up other names, etc. 10004 ... make up other names, etc. 10005 ... make up other names, etc. 10006 ... make up other names, etc. ACCOUNT AccountNbr Balance 1000001 1000002 1000003 1000004 1000005 1000006 OOOOOO Notes owner 10002 owner 10003, 10004 owner 10003, 10004 owner 10005 owner 10005 owner 10006 Notes OWNS ClientNbr AccountNbr 10002 10003 10003 10004 10004 10005 10005 10006 10007 10001 1000001 1000002 1000003 1000002 1000003 1000004 1000005 1000006 1000001 1000007 1st shared account 2nd shared account 1st shared account 2nd shared account 1 st account 2 nd account ERROR join non-existing client to account ERROR join client to non-existing account Balance sk * C EK TRANSACTION TxNbr TxCode AccountNbr Amount Date Time RefNbr 1 1000001 123.45 May 1 2019 12:00 101 2 D 1000000 234.56 May 1 2019 12:00 101 3 D 1000001 345.67 May 1 2019 12:00 111 4 D 1000001 100.00 May 1 2019 10:00 101 5 D 1000001 200.00 May 11 2019 11:00 101 6 D 1000001 300.00 May 21 2019 12:00 101 7 W 1000001 50.00 May 29 2009 10:00 102 8 W 1000001 75.00 May 29 2009 11:00 103 9 D 1000001 123.45 Jun 15 2019 13:00 101 100.00 300.00 600.00 550.00 475.00 598.45 10 11 12 13 14 15 16 17 18 D W D W D B P R 1000002 1000002 1000003 1000003 1000003 1000003 1000002 1000002 1000002 1000.00 May 15 2019 456.78 May 15 2019 456.78 May 15 2019 500.00 May 18 2019 100.00 May 20 2019 65.78 May 20 2019 100.00 May 21 2019 200.00 May 21 2019 50.00 May 26 2019 9:00 9:05 9:10 14:00 13:00 14:50 9:00 10:00 12:34 104 104 104 104 104 304 301 302 301 1000.00 543.22 456.78 -43.22 56.78 -9.00 443.22 243.22 293.22 o vo 20 21 22 23 OOO D D D B B B B 1000004 1000005 1000005 1000005 1000005 1000005 1000005 2000.00 Jun 1 2019 2000.00 Jun 1 2019 2000.00 Jun 1 2019 3456.78 Jun 10 2019 432.10 Jun 15 2019 100.00 Jun 20 2019 80.00 Jun 25 2019 13:00 13:00 14:00 12:00 14:30 15:55 16:56 101 101 102 301 302 303 304 2000.00 2000.00 4000.00 543.22 111.12 11.12 -68.88 24 25 26 10007 10001 1000001 1000007 ERROR join non-existing client to account ERROR join client to non-existing account