Limited-time offer! Save up to 50% Off | Solutions starting at $6 each  

Database Transaction Assignment PDF

Added on - 19 Oct 2021

Trusted by +2 million users,
1000+ happy students everyday
Showing pages 1 to 4 of 13 pages
Running head:DATABASE TRANSACTION
Database Transaction
Name of the Student
Name of the University
DATABASE TRANSACTION1
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
Conflictserializableschedule..................................................................................................10
Similarity between Conflict serializable schedule and state of database.................................10
View Serializability..................................................................................................................11
Conclusion................................................................................................................................11
References:...............................................................................................................................12
DATABASE TRANSACTION2
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);
DATABASE TRANSACTION3
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);
desklib-logo
You’re reading a preview
Preview Documents

To View Complete Document

Click the button to download
Subscribe to our plans

Download This Document