Database System Assignment: Creating Tables and Implementing Triggers

This document presents a comprehensive solution to a database system assignment. The assignment involves creating and populating tables, defining stored procedures for loan repayments, and implementing triggers to enforce business rules, such as limiting the number of loans per account and checking for loan amount thresholds. The solution includes SQL scripts for table creation, data insertion, procedure definitions, and trigger implementation. Furthermore, the assignment explores database concepts such as transactions, schedules, conflict serializability, and view serializability, providing detailed explanations and discussions on each topic. The document also covers the ACID properties of transactions, schedule equivalency, and the differences between conflict and view serializability, culminating in a complete analysis of database concurrency control mechanisms.

Running head: DATABASE SYSTEM
Database System
Name of the Student
Name of the University
Author Note:

1DATABASE SYSTEM
Table of Contents
Task 1:.............................................................................................................................................2
Task 2:...........................................................................................................................................11
Task 3:...........................................................................................................................................13
Task 4:...........................................................................................................................................16
Task 5:...........................................................................................................................................17
Introduction....................................................................................................................................17
Discussion......................................................................................................................................17
Transaction................................................................................................................................17
Schedule.....................................................................................................................................18
Conflict serializable schedule....................................................................................................19
Similarity between Conflict serializable schedule and state of database...................................19
View Serializability...................................................................................................................19
Conclusion.....................................................................................................................................20
References......................................................................................................................................21

2DATABASE SYSTEM
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;

3DATABASE SYSTEM
/**
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 --

4DATABASE SYSTEM
CREATE TABLE IF NOT EXISTS T_Acc_Type (
AccountTypeID VARCHAR(10) NOT NULL,
TypeName VARCHAR(45) NULL,
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, AccountType),
FOREIGN KEY (AccountType)

5DATABASE SYSTEM
REFERENCES T_Acc_Type(AccountTypeID));
-- Loan_Type --
CREATE TABLE IF NOT EXISTS T_Loan_Type (
LoanTypeID VARCHAR(10) NOT NULL,
Loan_TypeName VARCHAR(45) NULL,
Loan_TypeDesc VARCHAR(45) NULL,
Loan_TypeMInRate DECIMAL(4,2) NULL,
PRIMARY KEY (LoanTypeID));
-- Loan --
CREATE TABLE IF NOT EXISTS T_Loan (
LoanID VARCHAR(10) NOT NULL,
LoanRate DECIMAL(4,2) NULL,

6DATABASE SYSTEM
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,

7DATABASE SYSTEM
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)
REFERENCES T_Customer (customerID),
FOREIGN KEY (Account_BSB, Account_No)
REFERENCES T_Account (BSB, AccountNo));

8DATABASE SYSTEM
/*
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.3','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','12.00','AT3');

9DATABASE SYSTEM
INSERT INTO T_Account VALUES ('BSB3','Acct4','12.00','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');
INSERT INTO T_Loan_Type VALUES ('LT3','PL','Personal Loan','0.03');
INSERT INTO T_Loan VALUES ('L1','0.01','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');

10DATABASE SYSTEM
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
**/

11DATABASE SYSTEM
Task 2:
Repay_Loan Procedure:
DELIMITER $$
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), OUT balance_b decimal(10,0))
BEGIN

12DATABASE SYSTEM
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 ;
The payment is Successful:
set @BSB = 'BSB3', @accountNo = 'Acct4', @loan = 'L1', @amount= 2;
CALL `r`.`Repay_loan`(@BSB, @accountNo, @loan, @amount, @balance_b);
SELECT @BSB, @accountNo, @loan, @amount, @balance_b;
The payment is Unsuccessful Due to Payment From Another Account:
set @BSB = 'BSB3', @accountNo = 'Acct4', @loan = 'L3', @amount= 1;
CALL `r`.`Repay_loan`(@BSB, @accountNo, @loan, @amount, @balance_b);
SELECT @BSB, @accountNo, @loan, @amount, @balance_b;
The payment is Unsuccessful due to Irrelevant Payment:

13DATABASE SYSTEM
set @BSB = 'BSB3', @accountNo = 'Acct4', @loan = 'L1', @amount= 100;
CALL `r`.`Repay_loan`(@BSB, @accountNo, @loan, @amount, @balance_b);
SELECT @BSB, @accountNo, @loan, @amount, @balance_b;
Task 3:
Trigger Type: The before insert trigger will be used. It will allow the database to test the
conditions and if the conditions are satisfied then the data will be inserted. On the other hand, if
the conditions are not met then the data insert will not be done.
Testing Trigger: The trigger will be tested by inserting values into the T_Loan table.
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_jl INT;
DECLARE total_loan_amount decimal(8,0);

14DATABASE SYSTEM
DECLARE type_name varchar(45);
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`

15DATABASE SYSTEM
WHere `Loan_AcctNo` = NEW.`Loan_AcctNo` AND `Loan_AccountBSB` =
NEW.`Loan_AccountBSB`;
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 (total_loan_amount + NEW.`Loan_AcctNo`) > 1000000 THEN signal
sqlstate '45000';
END CASE;
END$$
DELIMITER;
Trigger Testing: Insert into T_Loan (LoanID, LoanRate, LoanAmount, Loan_Type,
Loan_AccountBSB, Loan_AcctNo) Values ('L4','0.05','670500.00','LT1','BSB3','Acct4');
Insert into T_Loan (LoanID, LoanRate, LoanAmount, Loan_Type, Loan_AccountBSB,
Loan_AcctNo) Values ('L5','0.05','1100000.00','LT1','BSB3','Acct4');

16DATABASE SYSTEM
Task 4:
1. The current schema can support the requirement if no new table is introduced to the
database.
2. This is because if we only add a column that calculate the provided amount then the
new table is no need to be inserted into the database.
3. ALTER TABLE T_Loan ADD COLUMN paidAmount decimal(8,2);
4. 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;

17DATABASE SYSTEM
Task 5:
Introduction
The following report is all about Spock bank in which the interest is calculated on 25th of
every month (Nagar and Jagannathan 2018). The given interest is added as per day basis on
automatic basis to the account. In how many days are there in a month and the given interest is
calculated on the basis of pro data.
In the coming pages of the report, an idea has been provided regarding schedule and
transaction. Conflict serializable schedule and similarity with transaction has been provided.
View of serializability along with weaker notion has been discussed in details.
Discussion
Transaction
In database, a transaction can be stated as a group of task. A single task can be stated as a
minimum number of processing unit which cannot be anyhow divided further. A transaction in
any database comes up with various aspect like Atomicity, Consistency, Durability and lastly
Isolation (Decker, Muñoz-Escoi and Misra 2015). There four properties of transaction are
abbreviated as ACID. It is mainly done for providing completeness, integrity of data and lastly
accuracy.
Atomicity: This particular property helps in having a proper kind of understanding
regarding the fact that every transaction is required to be treated like a single atomic unit.

18DATABASE SYSTEM
Consistency: The database of this given bank should remain in proper state even after the
competition of the given transaction (Yuan et al. 2016). Any kind of transaction does not create
an effect on the various data residing in database.
Durability: The given database should be very much durable in nature when it tends to
keep all the required updates. It tends to occur when failure is encountered in a system or it even
restarts.
Isolation: In the given database system, more than one transaction can occur at the same
instance. The property of isolation helps in understanding the fact all the transaction can be
carried in proper way.
Schedule
A schedule can be stated as a proper kind of execution of transaction in a proper way. A
schedule comes up with many kinds of transaction in it. It mainly comes up with large number of
instruction. If the two schedules aims to provide same kind of result after execution, then they
are defined to equivalent in nature (Randive et al. 2015). They can easily have same kind of
output of same value along with different set of values in comparison to other.
A schedule in needed in a database due to some kind of transaction when it is executed in
proper way. Then it can easily affect the outcome of the whole result at the time of updating the
whole values in the other kind of transaction (Kizu, Hochin and Nomiya 2016). At this particular
phase, the second kind of transaction can easily take place. So a proper schedule needs to be
created for the execution of the given transaction.

19DATABASE SYSTEM
Conflict serializable schedule
A schedule can be considered to be conflict serializable if and only if it can change into
proper kind of serial schedule. It is mainly done by swapping between the operation that is non-
conflict swapping (Eyal, Birman and van Renesse 2015). Two kinds of operation can be seen in
conflict if all the given condition can be satisfied like
 It tends to belong to various kind of transaction
 It tends to operate on same kind of data item
 Minimum one of them is needed for writing various kind of operation.
Yes, they can guarantee the consistency the schedule of database consistency.
Similarity between Conflict serializable schedule and state of database
A schedule is considered to be conflict serializable as it can easily change or transformed
into proper kind of serial schedule (Brutschy et al. 2016). It is mainly obtained by operation of
non-conflict. The schedule of the conflict serializable is considered to be serializable when
conflict is considered to be equal to serial schedule (Brutschy et al. 2016). No, conflict
serializable schedules are not made up of similar set of transaction which leaves the database at
the given state. It does not provide the same value if they have started with the same state of the
given database.
View Serializability
Serializability can be stated as a proper kind of concurrency where various kind of
transaction in equivalency to one another can be easily executed in serial way (Eyal, Birman and
van Renesse 2015). Serial schedule helps in proper execution of transaction without any kind of
interference from various other transaction (Yuan et al. 2016). The main notion of serializability

20DATABASE SYSTEM
is all about finding new kind of transaction which is need for execution. View serializability is
considered to be another kind of serializability. It can be easily built by developing another kind
of serializability. It is mainly collected from another kind of schedule which is needed for
scheduling the current schedule. It requires similar set of transaction (Randive et al. 2015). If the
concurrent kind of schedule is needed for proper kind of schedule for similar kind of transaction
which is needed for analyzing view serializable. While conflict serializability can be easily stated
which helps in defining two type of transaction (Kizu, Hochin and Nomiya 2016). It is generally
required for getting an access to same kind of data which is needed for both read and write
option.
Conclusion
The left part of the loan needs to be paid and is properly reflected on the loan account. It
can easily vary based on the various kind of repayment made. It can be easily stated that the
given loan amount needs to paid and is properly reflected on saving account which is known as
offset account.

21DATABASE SYSTEM
References
Brutschy, L., Dimitrov, D., Müller, P. and Vechev, M., 2016. Effective serializability for
eventual consistency. ETH Zurich.
Decker, H., Muñoz-Escoí, F.D. and Misra, S., 2015, June. Data Consistency: Toward a
Terminological Clarification. In International Conference on Computational Science and Its
Applications (pp. 206-220). Springer, Cham.
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.
Kizu, F., Hochin, T. and Nomiya, H., 2016, June. Consideration of validity of concurrency
control program generated in genetic programming. In Computer and Information Science
(ICIS), 2016 IEEE/ACIS 15th International Conference on (pp. 1-6). IEEE.
Nagar, K. and Jagannathan, S., 2018. Automated Detection of Serializability Violations Under
Weak Consistency. arXiv preprint arXiv:1806.08416.
Randive, A., Bhumkar, P., Roy, N. and Kinikar, M., 2015. Thread Consistent File Storage in
Databases.
Yuan, Y., Wang, K., Lee, R., Ding, X., Xing, J., Blanas, S. and Zhang, X., 2016. BCC: Reducing
false aborts in optimistic concurrency control with low cost for in-memory
databases. Proceedings of the VLDB Endowment, 9(6), pp.504-515.

22DATABASE SYSTEM