Database Transaction Project: Implementing SpockBank Database

This database project encompasses the creation and population of database tables, specifically within the context of a 'SpockBank'. The project involves the implementation of a procedure designed for loan repayment, complete with testing scenarios for successful and unsuccessful repayments. A crucial element is the development of a database trigger to enforce data integrity, with a detailed rationale provided for the chosen trigger type and thorough testing. Furthermore, the assignment addresses interest calculation, highlighting the limitations of the relational schema and suggesting modifications, including the creation of an 'offset' table and associated triggers. The project concludes with a comprehensive exploration of transaction management, encompassing key concepts such as ACID properties, schedules, conflict serializability, and view serializability, all within the framework of database systems and SQL.

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);

4DATABASE TRANSACTION
Task 3: Database Trigger
a) Rationale behind the Type of Trigger Used: Before insert trigger will be used for
this solution. The before insert will invoke the conditions before the loan data is
inserted. This implies that the database will prevent data insertion into loan table if
the conditions are not met.
b) Trigger Testing:
INSERT INTO T_LOAN VALUES ('L4','0.07','670500.00','LT1','BSB1','ACCT5');
c) Implementation of the Trigger:
DROP TRIGGER IF EXISTS LOAN_INSERT;
DELIMITER $$
CREATE TRIGGER LOAN_INSERT
BEFORE INSERT ON `T_LOAN`
FOR EACH ROW
BEGIN
DECLARE TOTAL_LOAN INT;
DECLARE TOTAL_JOINTLOAN INT;
DECLARE TOTAL_LOAN_AMOUNT DECIMAL(8,0);
DECLARE TYPE_NAME VARCHAR(45);

5DATABASE TRANSACTION
INSERT INTO TOTAL_LOAN 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 TOTAL_LOAN_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`
WHERE `LOAN_ACCTNO` = NEW.`LOAN_ACCTNO` AND
`LOAN_ACCOUNTBSB` = NEW.`LOAN_ACCOUNTBSB`;

6DATABASE TRANSACTION
INSERT INTO TOTAL_JL SELECT COUNT(`CUSTOMER_ID`) FROM
`T_OWN` WHERE `ACCOUNT_BSB` = NEW.`LOAN_ACCOUNTBSB` AND
`ACCOUNT_NO` = NEW.`LOAN_ACCTNO`;
CASE
WHEN TOTAL_JL > 1 && TOTAL_LOAN > 7 THEN SIGNAL SQLSTATE
'45000';
WHEN TOTAL_LOAN > 4 THEN SIGNAL SQLSTATE '45000';
WHEN TOTAL_LOAN > 0 && TYPE_NAME = 'PL' THEN SIGNAL
SQLSTATE '45000';
WHEN TOTAL_LOAN > 3 && TYPE_NAME = 'HL' THEN SIGNAL
SQLSTATE '45000';
WHEN (NEW.`LOAN_ACCTNO`+TOTAL_LOAN_AMOUNT) > 1000000
THEN SIGNAL SQLSTATE '45000';
END CASE;
END$$
DELIMITER;
Task 4: Interest Calculation
a) Issues with Relational Schema: The relational schema cannot support the new
requirement. The database does not have the offset account table within it. The
offset table will need to be connected to t_loan table. As the offset accounts are

7DATABASE TRANSACTION
savings account, all the offset accounts are savings account. A trigger will be used
for setting the account type as savings.
b) Suggested Changes to the Relational Schema: A new tables named offset must
be created first. This table will have same keys and structure of that t_account
table. Then a trigger will be created to store the account type as savings account
for every insert value.
c) Implementation of the Suggested Changes:
CREATE TABLE offset LIKE `t_account`;
Alter Table offset
ADD CONSTRAINT FK_offset
FOREIGN KEY (`AccountType`) REFERENCES `t_acc_type`(`AccountTypeID`);
Alter Table offset
ADD CONSTRAINT FK_offset_BSB
FOREIGN KEY (`BSB`) REFERENCES `t_own`(`Account_BSB`);
DROP TRIGGER IF EXISTS Offset_Insert;
DELIMITER $$
CREATE TRIGGER Offset_Insert
AFTER INSERT ON `offset`
FOR EACH ROW
BEGIN
Insert Into Offset (AccountType) values ('AT1');

8DATABASE TRANSACTION
END$$
d) Interest Calculation Procedures: as
CREATE EVENT IF NOT EXISTS loan_Payment
ON SCHEDULE EVERY 1 DAY STARTS '2015-06-21 00:20:00'
Do
Update `t_loan` Set `paidAmount` =
CASE
When
`offset_account`.`loanID` = `offset_account`.`loanID`
THEN
(Select ((`LoanAmount`-`paidAmount`)*(`LoanRate`/365)) From `t_loan`)
End;
e) Procedure Testing: Event cannot be testedmanually.
Task 5: Transaction Management
Introduction:
The structured query language or SQL sands for standard language that allows the
database to manipulate existing data. The extension of the SQL is known as the PL/SQL.
The database management system allows the developer to connect with the database. Such a
DBMS is MySQL database which can be accessed through Workbench.

9DATABASE TRANSACTION
The report consists of the trigger, event and procedures created using workbench. The
concept of the transactions in the database has also been described in general.
Transaction
In case of database system, group task can be performed in terms of transaction in
between the system. The task that is ti be performed is diversified in smaller units until and
unless the entire tsk can be diversified in smaller parts. Database transaction can be
performed with the help of the atomicity, durability and isolation testing processing. In case
this testing are not performed the functioning of the database gets inefficient in nature. The
properties that are taken into consideration for better performance are the ACID properties.
 Atomicity: In case of the functioning of the data management system the main
concern is that the entire unit is considered to be a single unit.
 Consistency: the consistency of the main project inject the fact that the
commencement of the project includes the fact that the functioning of the database
must remain in a proper track despite of several ongoing transactions (Hababeh,
Khalil and Khreishah, 2015).
 Isolation: during the course of transaction the main aspect that is present is the
commencing of more than one task at a time. With the help of the isolation property
it gets easy for the completion of the project as it takes into consideration every
single step in a distinct manner (Dziadek, Ferraccioli and Gohl 2018)
 Durability: Durability of the database can be measured by checking the process of
completion of the project and this affects the completion of the project in positive
manner.

10DATABASE TRANSACTION
Schedule
A schedule is required fir commencing of the project for the better management of the
entire project. A schedule is termed as one of the major reason that affects the processing of
the project in a positive manner. By following the schedule that is created the main benefit
that can be affected in a better process as the estimation of the process can be done and the
execution of the entire process will be completed with ease.
In order to effectively execute the transactions in database it is recommended to create
a proper schedule. It helps to define which transaction is executed at which phase which helps
the transaction to be executed effectively and efficiently. If the transaction is not executed
properly the outcome of one transaction has effect on another transaction (Neumann,
Mühlbauer and Kemper 2015, May). However a schedule ensures that if second transaction is
intended to be executed it will only be performed after he first transaction is completed
properly as per the schedule.
Conflict serializable schedule
A schedule is only termed as serializable schedule if and only if it is possible to
change the schedule into serial schedule and that too in proper type of serial schedule. In
order to do that a non-conflict swapping is considered (Eyal Birman and van Renesse 2015,
June). Two operation are considered as conflict if it follows the following criteria:
 It is associated with different kind of transaction
 It includes data set that is similar
 One of them at minimum is required for writing different types of operation
Similarity between Conflict serializable schedule and state of database
The conflict serializable schedule if the conflicting schedule can be converted into
serial schedule. Non-conflicting operation is mainly the source of the conflict serializability.
The schedules are not created from similar transaction collection which has the tendency of

11DATABASE TRANSACTION
leaving the database at the given state. It is not possible to get the same for a schedule every
time.
View Serializability
The view serializability states that the schedules are defined through similar to the
serial schedule along with the same set of transactions in which two schedules are read and
other one is write and all the schedules operate on same data.
Conclusion
The report concludes that the database has been updated properly using triggers,
procedures, events and tables. The transaction details of the database has been provided with
brief and exact answers.

12DATABASE TRANSACTION
References:
Dziadek, R., Ferraccioli, F. and Gohl, K., 2018. Linking GHF to crustal structures and DBMS
estimates in the Amundsen Sea Sector.
Eyal, I., Birman, K. and van Renesse, R., 2015, June. Cache serializability: Reducing
inconsistency in edge transactions. In Distributed Computing Systems (ICDCS), 2015 IEEE
35th International Conference on (pp. 686-695). IEEE.
Hababeh, I., Khalil, I. and Khreishah, A., 2015. Designing high performance web-based
computing services to promote telemedicine database management system. IEEE
transactions on services computing, 8(1), pp.47-64.
Neumann, T., Mühlbauer, T. and Kemper, A., 2015, May. Fast serializable multi-version
concurrency control for main-memory database systems. In Proceedings of the 2015 ACM
SIGMOD International Conference on Management of Data(pp. 677-689). ACM.