Database Transaction Assignment PDF

Added on -2021-10-19

| 13 pages| 2240 words| 186 views

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
Conflict serializable schedule..................................................................................................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);

Found this document preview useful?

You are reading a preview
Upload your documents to download
or
Become a Desklib member to get accesss

Premium

$45

Q&A Library Access

Chat support

12

Document Unlocks

4

Answer Unlocks

Students who viewed this