Database Transaction

Running head: DATABASE TRANSACTION
Database Transaction
Name of the Student
Name of the University

1DATABASE TRANSACTION
Table of Contents
Task 1: Creating and Populating Database Tables.....................................................................2
Task 2: Procedure for Loan Repayment.....................................................................................2
Task 3: Database Trigger...........................................................................................................4
Task 4: Interest Calculation.......................................................................................................6
Task 5: Transaction Management..............................................................................................8
Introduction:...........................................................................................................................8
Transaction.............................................................................................................................9
Schedule.................................................................................................................................9
Conflict serializable schedule..............................................................................................10
Similarity between Conflict serializable schedule and state of database.............................10
View Serializability..............................................................................................................11
Conclusion................................................................................................................................11
References:...............................................................................................................................12

2DATABASE TRANSACTION
Task 1: Creating and Populating Database Tables
No database is created in the script so a database will be created first.
Create Database SpockBank;
Use SpockBank;
Then the script will be run. This script holds the queries for creating and populating
the database. This script will have seven warnings for each drop table query. The example of
the warning is 0 row(s) affected, 1 warning(s): 1051 Unknown table
'spockbank.t_repayment'.
Task 2: Procedure for Loan Repayment
a) Procedure Implementation:
USE `spockbank`;
DROP procedure IF EXISTS `Repay_loan`;
DELIMITER $$
USE `spockbank`$$
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
declare balance_b decimal(10,0);

3DATABASE TRANSACTION
SELECT `AccountBal` INTO balance_b FROM `t_account` inner join `t_loan` ON
`t_account`.`AccountNo` = `t_loan`.`Loan_AcctNo` WHERE `t_loan`.`Loan_AcctNo` =
from_accountNo AND `t_loan`.`LoanID` = to_loan LIMIT 1;
IF(balance_b > amount) THEN
INSERT INTO t_repayment (`Repayment_LoanID`, `RepaymentAmount`,
`RepaymentDate`) Values(to_loan, amount, NOW());
END IF;
END$$
DELIMITER ;
b) Procedure Testing:
(i) Successful Repayment:
set @BSB = 'BSB2', @accountNo = 'Acct2', @loan = 'L2', @amount= 100;
CALL `r`.`Repay_loan`(@BSB, @accountNo, @loan, @amount);
(ii) Unsuccessful Repayment:
set @BSB = 'BSB2', @accountNo = 'Acct1', @loan = 'L2', @amount= 100;
CALL `r`.`Repay_loan`(@BSB, @accountNo, @loan, @amount);
(iii) Unsuccessful Repayment:
set @BSB = 'BSB2', @accountNo = 'Acct2', @loan = 'L2', @amount= 10000000000;
CALL `r`.`Repay_loan`(@BSB, @accountNo, @loan, @amount);