Database Transaction: Case Study of Loan Payment

This report provides details of the transaction feature of a database management system. The main concern of the report is serializability. It includes a case study of loan payment and covers ACID properties, consistency, isolation, and more.

Running head: DATABASE TRANSACTION
Database Transaction: Case Study of Loan Payment
Name of the Student
Name of the University
Author’s note:

1DATABASE TRANSACTION
Table of Contents
Task 1:........................................................................................................................................2
Task 2:........................................................................................................................................6
Task 3:........................................................................................................................................7
Task 4:........................................................................................................................................8
Task 5:........................................................................................................................................9
Introduction................................................................................................................................9
Discussion..................................................................................................................................9
Transaction.............................................................................................................................9
Schedule...............................................................................................................................10
Conflict serializable schedule..............................................................................................10
Similarity between Conflict serializable schedule and state of database.............................10
View Serializability..............................................................................................................10
Conclusion................................................................................................................................10
References:...............................................................................................................................11

2DATABASE TRANSACTION
Task 1:
/*******
Sample script for creating and populating tables for Assignment 2, ISYS224, 2018
*******/
/**
Drop old Tables
**/
DROP TABLE IF EXISTS T_Repayment;
DROP TABLE IF EXISTS T_Loan;
DROP TABLE IF EXISTS T_Own;
DROP TABLE IF EXISTS T_Customer;
DROP TABLE IF EXISTS T_Account;
DROP TABLE IF EXISTS T_Loan_Type;
DROP TABLE IF EXISTS T_Acc_Type;
/**
Create Tables
**/
-- Customer --
CREATE TABLE T_Customer (
CustomerID VARCHAR(10) NOT NULL,
CustomerName VARCHAR(45) NULL,
CustomerAddress VARCHAR(45) NULL,
CustomerContactNo INT NULL,
CustomerEmail VARCHAR(45) NULL,
CustomerJoinDate DATETIME NULL,
PRIMARY KEY (CustomerID));
-- Acc_Type --

3DATABASE TRANSACTION
CREATE TABLE IF NOT EXISTS T_Acc_Type (
AccountTypeID VARCHAR(10) NOT NULL,
TypeName SET('SAV','CHK','LON'),
TypeDesc VARCHAR(45) NULL,
TypeRate DECIMAL(4,2) NULL,
TypeFee DECIMAL(2) NULL,
PRIMARY KEY (AccountTypeID));
-- Account --
CREATE TABLE IF NOT EXISTS T_Account (
BSB VARCHAR(10) NOT NULL,
AccountNo VARCHAR(10) NOT NULL,
AccountBal DECIMAL(10) NULL,
AccountType VARCHAR(10) NOT NULL,
PRIMARY KEY (BSB, AccountNo),
FOREIGN KEY (AccountType)
REFERENCES T_Acc_Type(AccountTypeID));
-- Loan_Type --
CREATE TABLE IF NOT EXISTS T_Loan_Type (
LoanTypeID VARCHAR(10) NOT NULL,
Loan_TypeName SET('HL','IL','PL'),
Loan_TypeDesc VARCHAR(45) NULL,
Loan_TypeMInRate DECIMAL(4,2) NULL,
PRIMARY KEY (LoanTypeID));
-- Loan --

4DATABASE TRANSACTION
CREATE TABLE IF NOT EXISTS T_Loan (
LoanID VARCHAR(10) NOT NULL,
LoanRate DECIMAL(4,2) NULL,
LoanAmount DECIMAL(8) NULL,
Loan_Type VARCHAR(10) NOT NULL,
Loan_AccountBSB VARCHAR(10) NOT NULL,
Loan_AcctNo VARCHAR(10) NOT NULL,
PRIMARY KEY (LoanID),
FOREIGN KEY (Loan_Type)
REFERENCES T_Loan_Type (LoanTypeID),
FOREIGN KEY (Loan_AccountBSB , Loan_AcctNo)
REFERENCES T_Account (BSB, AccountNo));
-- Repayment --
CREATE TABLE IF NOT EXISTS T_Repayment (
RepaymentNo int NOT NULL AUTO_INCREMENT,
Repayment_LoanID VARCHAR(10) NOT NULL,
RepaymentAmount DECIMAL(6) NULL,
RepaymentDate DATETIME NULL,
PRIMARY KEY (RepaymentNo),
FOREIGN KEY (Repayment_LoanID)
REFERENCES T_Loan (LoanID));
-- Own --
CREATE TABLE IF NOT EXISTS T_Own (
Customer_ID VARCHAR(10) NOT NULL,
Account_BSB VARCHAR(10) NOT NULL,
Account_No VARCHAR(10) NOT NULL,
PRIMARY KEY (Customer_ID, Account_BSB, Account_No),
FOREIGN KEY (Customer_ID)

5DATABASE TRANSACTION
REFERENCES T_Customer (customerID),
FOREIGN KEY (Account_BSB, Account_No)
REFERENCES T_Account (BSB, AccountNo));
/*
Populate Tables
*/
INSERT INTO T_Customer VALUES
('C1','Adam','AdamHouse','234567891','aMail','2015-10-10');
INSERT INTO T_Customer VALUES
('C2','Badshah','BadshahPalace','234567892','bMail','2015-10-11');
INSERT INTO T_Customer VALUES
('C3','Chandni','ChandniBar','234567893','cMail','2015-10-12');
INSERT INTO T_Acc_Type VALUES ('AT1','SAV','Savings','0.1','15');
INSERT INTO T_Acc_Type VALUES ('AT2','CHK','Checking','0.2','16');
INSERT INTO T_Acc_Type VALUES ('AT3','LON','Loan','0','17');
INSERT INTO T_Account VALUES ('BSB1','Acct1','10.00','AT1');
INSERT INTO T_Account VALUES ('BSB2','Acct2','11.00','AT3');
INSERT INTO T_Account VALUES ('BSB3','Acct3','-5000','AT3');
INSERT INTO T_Account VALUES ('BSB3','Acct4','-7000','AT3');
INSERT INTO T_Account VALUES ('BSB1','Acct5','10.00','AT1');
INSERT INTO T_Account VALUES ('BSB1','Acct6','10.00','AT1');
INSERT INTO T_Loan_Type VALUES ('LT1','HL','Home Loan','0.01');
INSERT INTO T_Loan_Type VALUES ('LT2','IL','Investment Loan','0.02');

6DATABASE TRANSACTION
INSERT INTO T_Loan_Type VALUES ('LT3','PL','Personal Loan','0.03');
INSERT INTO T_Loan VALUES ('L1','0.05','5000.00','LT3','BSB3','Acct4');
INSERT INTO T_Loan VALUES ('L2','0.02','16200.00','LT2','BSB2','Acct2');
INSERT INTO T_Loan VALUES ('L3','0.03','670500.00','LT1','BSB3','Acct3');
INSERT INTO T_Repayment (Repayment_LoanID, RepaymentAmount,
RepaymentDate)
VALUES ('L1','1.00','2017-10-10');
INSERT INTO T_Repayment (Repayment_LoanID, RepaymentAmount,
RepaymentDate)
VALUES ('L2','2.00','2018-02-11');
INSERT INTO T_Repayment (Repayment_LoanID, RepaymentAmount,
RepaymentDate)
VALUES ('L3','2.00','2018-02-11');
INSERT INTO T_Own VALUES ('C1','BSB2','Acct2');
INSERT INTO T_Own VALUES ('C2','BSB3','Acct3');
INSERT INTO T_Own VALUES ('C3','BSB3','Acct4');
INSERT INTO T_Own VALUES ('C1','BSB3','Acct4');
INSERT INTO T_Own VALUES ('C1','BSB1','Acct1');
INSERT INTO T_Own VALUES ('C2','BSB1','Acct5');
INSERT INTO T_Own VALUES ('C3','BSB1','Acct6');
/**
End Script
**/
Task 2:
SELECT * FROM student.t_loan;DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `loan_repayment`(IN
from_BSB VARCHAR(10), IN from_accountNo VARCHAR(10), IN to_loan
VARCHAR(10), IN amount DECIMAL(8), OUT balance_current decimal(10,0))
BEGIN
SELECT `AccountBal` INTO balance_current 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;

7DATABASE TRANSACTION
IF(balance_current > amount) THEN
INSERT INTO t_repayment (`Repayment_LoanID`, `RepaymentAmount`,
`RepaymentDate`) Values(to_loan, amount, NOW());
END IF;
END$$
DELIMITER ;
Task 3:
DROP TRIGGER IF EXISTS INSERT_LOAN;
DELIMITER $$
CREATE TRIGGER INSERT_LOAN
before INSERT ON `t_loan`
FOR EACH ROW
BEGIN
DECLARE Entire_Loan_Amount INT;
DECLARE Joint_Loan INT;
DECLARE Entire_Loan_Amount_amount decimal(8,0);
DECLARE type_name varchar(45);
INSERT INTO Entire_Loan_Amount Select count(`LoanID`) From `t_loan` inner join
`t_account`
On `t_loan`.`Loan_AccountBSB` = `t_account`.`BSB` AND `t_loan`.`Loan_AcctNo` =
`t_account`.`AccountNo`
inner join `t_loan_type` ON `Loan_Type` = `LoanTypeID`
WHere `Loan_AcctNo` = NEW.`Loan_AcctNo` AND `Loan_AccountBSB` =
NEW.`Loan_AccountBSB`;
INSERT INTO type_name Select `Loan_TypeName` From `t_loan` inner join `t_account`
On `t_loan`.`Loan_AccountBSB` = `t_account`.`BSB` AND `t_loan`.`Loan_AcctNo` =
`t_account`.`AccountNo`
inner join `t_loan_type` ON `Loan_Type` = `LoanTypeID`
WHere `Loan_AcctNo` = NEW.`Loan_AcctNo` AND `Loan_AccountBSB` =
NEW.`Loan_AccountBSB`;
INSERT INTO Entire_Loan_Amount_amount Select SUM(`LoanAmount`) From `t_loan`
inner join `t_account`
On `t_loan`.`Loan_AccountBSB` = `t_account`.`BSB` AND `t_loan`.`Loan_AcctNo` =
`t_account`.`AccountNo`
inner join `t_loan_type` ON `Loan_Type` = `LoanTypeID`

8DATABASE TRANSACTION
WHere `Loan_AcctNo` = NEW.`Loan_AcctNo` AND `Loan_AccountBSB` =
NEW.`Loan_AccountBSB`;
INSERT INTO Joint_Loan select Count(`Customer_ID`) From `t_own` Where
`Account_BSB` = NEW.`Loan_AccountBSB` AND `Account_No` = NEW.`Loan_AcctNo`;
CASE
WHEN Entire_Loan_Amount > 4 THEN signal sqlstate '45000';
WHEN Entire_Loan_Amount > 0 && type_name = 'PL' THEN signal sqlstate '45000';
WHEN Entire_Loan_Amount > 3 && type_name = 'HL' THEN signal sqlstate '45000';
WHEN (Entire_Loan_Amount_amount + NEW.`Loan_AcctNo`) > 1000000 THEN
signal sqlstate '45000';
WHEN Joint_Loan > 1 && Entire_Loan_Amount > 7 THEN signal sqlstate
'45000';
END CASE;
END$$
Task 4:
The current schema cannot support the new requirements of the database. As per the
requirement, the database must have a separate table associated with both the loan and
account table. It can act as the bridge table as all the loan must be associated with the offset
account.
A new table named offset account will be created which will have columns named
amount paid and loan id.
Create Table Offset (
loanId varchar(20),
amountPaid decimal(8,2),
Primary Key (loanId),
FOREIGN KEY fk_13(loanId) REFERENCES T_Loan(LoanID)
);
CREATE EVENT IF NOT EXISTS loan_Payment
ON SCHEDULE EVERY 1 DAY STARTS '2015-06-21 00:20:00'
Do
Update `offset_account`, `t_loan` Set `paidAmount` =
CASE
When
`offset_account`.`loanID` = `t_loan`.`LoanID`
THEN

9DATABASE TRANSACTION
(Select ((`LoanAmount`-`paidAmount`)*(`LoanRate`/365)) From `offset_account`
inner join `t_loan` on `offset_account`.`loanID` = `t_loan`.`LoanID`)
End;
Task 5:
Introduction
The database management system is the software application that allows the database
developers to manipulate the original raw database. The developers can store, retrieve or
update data in the database along with creating procedures, triggers and events. The database
allows the developers to transfer the database through a raw file, individual for different
management system.
The report is based on providing the details of the transaction feature of a database
management system. The brief details of the transaction properties and features has been
provided into the report. The main concern of the report is serializability.
Discussion
Transaction
The transition can be considered as the singular logical element of process which
evaluates as well as updates the database contents. The transaction has mainly two operations
named as read and write. These read and write operation accesses the data stored into the
database. The database consistency is a very important factor in maintaining proper
functioning of the database at all times. In order to maintain database consistency before a
transaction begins or its ends, some properties of tractions are followed. These properties are
called ACID properties.
Atomicity: The atomicity refers to the situation when a transaction is either fully
completed or rejected by the database. This means that the database will consider all the
processes defined a transaction as atomic or one single process. If the transaction is cancelled
before it is committed, the database will rollback all the previously executed processes and
restore to the stage when the transaction was not began. The abort operation suggests that all
the changes will be undone. On the other hand, the commit operation will save all the
changes done in transaction part permanently in the database itself.
Consistency: The consistency property is used for maintaining the integrity of the
database so that the database can be consistent after and before the transaction. This implies
that of consistency is maintained properly then the database will maintain correctness. It
suggests that if total amount in all the accounts is 5000 then after all the transactions the total
amount should be 5000.
Isolation: The isolation makes sure that simultaneously occurring transactions do not
lead the database to inconsistent state. Individual transactions can be executed indecently
without any interference. The isolation does not allow any other transaction to see the
updated changes in the database until the write operation is committed.
Durability: The durability property makes sure that after the transactions are done
then modifications to the database are done and updates are written to the disk. The updates
written into the disks become permanent and can be accessed by all the other transactions.

10DATABASE TRANSACTION
Schedule
The schedule is considered as the operations that are executed in series and conveys
from one transaction to another transactions. The schedule preserves the operation execution
order for each individual transactions. Three types of schedule are available in database
transaction such as serial schedule, non-serial schedule and serializable schedule.
The schedule is a part of the transaction that assist in transaction concurrent operation
execution. A transaction can consist of only one type of schedule. The schedule is completely
based on the transaction type and properties but a transaction is never decided based on
schedule.
Conflict serializable schedule
The conflict serializability states that a schedule can be altered into a serial schedule
through adjusting its non-conflicting operations. Two operations of a transaction will be
called conflicting operations if both the operations are from different transactions, same data
item is considered and at least one of those are write.
Similarity between Conflict serializable schedule and state of database
A schedule is viewed as strife serializable as it can without much of a stretch change
or changed into appropriate sort of sequential schedule. It is chiefly gotten by activity of non-
struggle. The schedule of the contention serializable is viewed as serializable when struggle is
viewed as equivalent to sequential schedule. No, contention serializable schedules are not
comprised of comparative arrangement of exchange which leaves the database at the given
state. It doesn't give a similar esteem in the event that they have begun with a similar
condition of the given database.
View Serializability
The view Serializability is view equivalent to serial schedule, it must be a confilict
serializable serializable and it does not have any bind write operations.
Conclusion
From the above discussion it can be stated that the transaction in the database is a very
important factor. The database must be following the transaction protocol for maintaining the
consistency and other ACID properties of the database. The ACID property is a very
important factor database that ensures integrity of the database.

11DATABASE TRANSACTION
References:
Ahmed, T.M., Bezemer, C.P., Chen, T.H., Hassan, A.E. and Shang, W., 2016, May. Studying
the effectiveness of application performance management (APM) tools for detecting
performance regressions for web applications: an experience report. In Proceedings of the
13th International Conference on Mining Software Repositories (pp. 1-12). ACM.
Arulraj, J. and Pavlo, A., 2017, May. How to build a non-volatile memory database
management system. In Proceedings of the 2017 ACM International Conference on
Management of Data (pp. 1753-1758). ACM.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Elmasri, R. and Navathe, S., 2016. Fundamentals of database systems. London: Pearson.
Goel, A.K., Pound, J., Auch, N., Bumbulis, P., MacLean, S., Färber, F., Gropengiesser, F.,
Mathis, C., Bodner, T. and Lehner, W., 2015. Towards scalable real-time analytics: an
architecture for scale-out of OLxP workloads. Proceedings of the VLDB Endowment, 8(12),
pp.1716-1727.
Gurajada, A., Gala, D., Zhou, F., Pathak, A. and Ma, Z.F., 2018. BTrim: hybrid in-memory
database architecture for extreme transaction processing in VLDBs. Proceedings of the
VLDB Endowment, 11(12), pp.1889-1901.
Kim, K., Wang, T., Johnson, R. and Pandis, I., 2016, June. Ermia: Fast memory-optimized
database system for heterogeneous workloads. In Proceedings of the 2016 International
Conference on Management of Data (pp. 1675-1687). ACM.
Kvet, M., Matiako, K. and Kvet, M., 2014, March. Transaction management in fully temporal
system. In Computer Modelling and Simulation (UKSim), 2014 UKSim-AMSS 16th
International Conference on (pp. 148-153). IEEE.
Lin, Q., Chang, P., Chen, G., Ooi, B.C., Tan, K.L. and Wang, Z., 2016, June. Towards a non-
2pc transaction management in distributed database systems. In Proceedings of the 2016
International Conference on Management of Data (pp. 1659-1674). ACM.
Padhye, V. and Tripathi, A., 2015. Scalable transaction management with snapshot isolation
for NoSQL data storage systems. IEEE Transactions on Services Computing, 8(1), pp.121-
135.
Pavlo, A., Angulo, G., Arulraj, J., Lin, H., Lin, J., Ma, L., Menon, P., Mowry, T.C., Perron,
M., Quah, I. and Santurkar, S., 2017. Self-Driving Database Management Systems. In CIDR.
Zhang, H., Chen, G., Ooi, B.C., Tan, K.L. and Zhang, M., 2015. In-memory big data
management and processing: A survey. IEEE Transactions on Knowledge and Data
Engineering, 27(7), pp.1920-1948.
Zimmermann, O., 2015. Architectural refactoring: A task-centric view on software evolution.
IEEE Software, 32(2), pp.26-29.