Loan repayment essay validates process.

Generate seven tables and populate them usingthe script provided in the assignment folder.You can optionally create and populate them by yourself, but make sure there is strong alignment between the tables created and rows populated by the script andthose by you. In particular, lookcare fully at how the primary key of the table forrepayment(T_Repayment)is automatically generated,and how row sare inserted toit.

Procedure for Loan Repayment
At Spock Bank, loan repayments are made purely online by transferring fund from another account – typically a savings account-- to the loan account. The customers normally use the bank’s app or webpage and specify from which account they would want to make the repayment,andtowardswhichloan his payment should count.This action of the customer will effectively callastored procedureas:

Loan Repayment Procedure

Procedure Implementation

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `repay_loan`(IN `from_BSB` VARCHAR(10), IN `from_accountNo` VARCHAR(10), IN `to_loan` VARCHAR(10), IN `amount` DECIMAL(8))

BEGIN

declarehasAccount INTEGER;

declareisLoanMatch INTEGER;

declare balance INTEGER;

declareownsAccount INTEGER;

select count(*) into hasAccount from t_account where `BSB` = from_BSB and `AccountNo`=from_accountNo;

select count(*) into ownsAccount from t_own where Customer_ID in (select a.Customer_ID from t_own a, t_loan b where a.Account_BSB = b.Loan_AccountBSB and a.Account_No = b.Loan_AcctNo and b.LoanID = to_loan);

IF (hasAccount>0) THEN

selectAccountBal into balance from t_account where BSB = from_BSB and

AccountNo=from_accountNo;

if(ownsAccount>0) THEN

if (balance>=amount) then

INSERT into t_repayment (RepaymentAmount, Repayment_LoanID, RepaymentDate)

VALUES(amount,to_loan, now());

SELECT 'Loan Repayment Succeeded';

ELSE

SELECT 'Insufficient funds to complete this transaction';

End IF;

ELSE

SELECT 'Paying someones else loan';

END IF;

ELSE

SELECT 'Account does not exist';

END IF;

END$$

DELIMITER ;

Procedure Testing

Successful Repayment

MariaDB [spockbank]> call repay_loan('BSB3','Acct4','L1',1);

+--------------------------+

| Loan Repayment Succeeded |

+--------------------------+

| Loan Repayment Succeeded |

+--------------------------+

1 row in set (0.06 sec)

Unsuccessful Repayment - attempt by a third party

MariaDB [spockbank]> call repay_loan('BSB1','Acct5','L1',1);

+--------------------------+

| Loan Repayment Failed. Account owner not found |

+--------------------------+

| Loan Repayment Failed. Account owner not found |

+--------------------------+

1 row in set (0.06 sec)

Unsuccessful Repayment – insufficient funds

MariaDB [spockbank]> call repay_loan('BSB3','Acct4','L1',10000000);

+-------------------------------------------------+

| Insufficient funds to complete this transaction |

+-------------------------------------------------+

| Insufficient funds to complete this transaction |

+-------------------------------------------------+

TASK 3 DATABASE TRIGGER

Rationale behind the type of trigger used

The trigger is created to operate before insertion and stop saving of the loan when a condition is not met.

How the trigger is tested (methodology)

The trigger is tested by trying to perform a transaction on the t_loan table on which the trigger is created for. Any intended insertion will invoke the trigger whoch

Implementation of the trigger

The trigger is implemented on the t_loan table where any attempt to insert will invoke the trigger. When invoked, the trigger will check all the rules defined and stop insertion if any of the rule condition fails.

Code for implementing the trigger

CREATE TRIGGER `check_before_insert_loan` BEFORE INSERT ON `t_loan`

FOR EACH ROW BEGIN

DECLARE done INT DEFAULT FALSE;

DECLARE id VARCHAR(10);

DECLARE LoanTypeVARCHAR(10);

DECLARE total INT;

DECLARE allLoans INT;

DECLARE personal INT;

DECLARE homeLoan INT;

DECLARE amount INT;

DECLARE OK_INSERT INT DEFAULT 1;

DECLARE LastLoanIDVARCHAR(10);

DECLARE cur CURSOR FOR SELECT Customer_ID FROM t_own where Account_BSB = NEW.Loan_AccountBSB and Account_No = New.Loan_AcctNo;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET done = TRUE;

OPEN cur;

SELECT FOUND_ROWS() INTO total;

ins_loop: LOOP

FETCH cur INTO id;

Stored Procedure Script

SELECT COUNT(*) into allLoans FROM t_loan a, t_own b WHERE a.Loan_AccountBSB = b.Account_BSB and a.Loan_AcctNo=b.Account_No and b.Customer_ID = id;

IF(allLoans>8) THEN

SIGNAL SQLSTATE '45000';

END IF;

SELECT COUNT(*) into homeLoan FROM t_loan a, t_own b WHERE a.Loan_AccountBSB = b.Account_BSB and a.Loan_AcctNo=b.Account_No and b.Customer_ID = id and a.Loan_Type='LT1';

IF(homeLoan>=3 and New.Loan_Type='LT1') THEN

SIGNAL SQLSTATE '45000';

END IF;

SELECT COUNT(*) into personal FROM t_loan a, t_own b WHERE a.Loan_AccountBSB = b.Account_BSB and a.Loan_AcctNo=b.Account_No and b.Customer_ID = id and a.Loan_Type='LT3';

IF(personal>=1 and New.Loan_Type='LT3') THEN

SIGNAL SQLSTATE '45000';

END IF;

SELECT SUM(LoanAmount) into total FROM t_loan a, t_own b WHERE a.Loan_AccountBSB = b.Account_BSB and a.Loan_AcctNo=b.Account_No and b.Customer_ID = id;

IF((total+NEW.LoanAmount) > 10000000) THEN

SIGNAL SQLSTATE '45000';

END IF;

IF done THEN

LEAVE ins_loop;

END IF;

END LOOP;

CLOSE cur;

END

Script for testing the trigger (and test results)

User has more than five personal loans and insertion fails.

MariaDB [spockbank]> INSERT INTO `t_loan` (`LoanID`, `LoanRate`, `LoanAmount`, `Loan_Type`, `Loan_AccountBSB`, `Loan_AcctNo`, `offsetAcct_BSB`, `offsetAcct_No`) VALUES ('L14', '0.02', '1100', 'LT2', 'BSB3', 'Acct2', 'BSB1', 'Acct6');