ISYS224 Database Systems Assignment 2: Loan Management Report

This report presents a comprehensive solution for ISYS224 Assignment 2, focusing on database systems within the context of Spock Bank's loan management. The assignment covers several key areas, starting with the creation and population of database tables, followed by the implementation and testing of a procedure for loan repayment, including scenarios for successful and unsuccessful repayments. The report then delves into database triggers, explaining the rationale behind their use, testing methodologies, and implementation with SQL code. Furthermore, it addresses issues with the relational schema, suggests changes, and implements them with SQL code, including a procedure for interest calculation and its testing. The assignment also includes a section on transaction management theory, covering concepts such as transactions, schedules, serializability, and conflict serializability. The solution provides detailed SQL code for table creation and modification, along with explanations and test results, demonstrating a strong understanding of database principles and practical application.

Database Systems
Table of Contents
Task -1 Table Generation...................................................................................................................2

Task – 2 Procedure..............................................................................................................................3
Task – 3 Triggers.................................................................................................................................7
Task -4 Procedure for Interest Calculation.......................................................................................8
Task -5 Theory...................................................................................................................................10
SQL Code...........................................................................................................................................11
Task -1 Table Generation
The Bank Database Tables are listed in below.
1

For Account Table (Brouard, Bruchez, Soutou & Larrousse, 2012),
For Account Type table,
For customer Table,
For Loan Table,
For loan type table,
For own table,
For repayment table,
2

ER Diagram
The entity relationship diagram for bank database is illustrated as below (Jones, 2013).
Task – 2 Procedure
1.
The procedure for repayment of loan is created sucessfully and it is demostrated as below
(PATHAK, 2011).
3

Output of Procedure is illustrated as below.
4

2.
3.
4.
5

6

Task – 3 Triggers
Bank database triggers is illustrated as below.
Triggers types
Here, we are using the before and after triggers. When characterizing a trigger, you
can indicate the trigger planning regardless of whether the trigger activity is to be pursued
previously or the activating articulation. When apply to both declaration and line triggers.
Triggers testing
BEFORE triggers run the trigger activity before the activating articulation is run. This sort of
trigger is normally utilized in the accompanying circumstances:
 At the point when the trigger activity decides if the activating articulation ought to be
permitted to finish. Utilizing a BEFORE trigger for this reason, you can wipe out
superfluous handling of the activating explanation and its inevitable rollback in
situations where a special case is brought up in the trigger activity.
 To determine particular section esteems before finishing an activating INSERT or
UPDATE explanation.
AFTER triggers pursue the trigger activity the activating proclamation is run.
7

Write and adequately test
CREATE [OR REPLACE ] TRIGGER Loan
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF LoanID]
ON t-loan
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
SELECT * FROM t-loan
END;
Task -4 Procedure for Interest Calculation
The procedure for interest calculation is illustrated as below.
8

Relational schema
Code for modifying the tables and inserting rows
SELECT Date
WHERE Date= 03/01/2018 between 31/08/2018;
FROM t-repayment;
Final Interest Calculation Code
DELIMITER //
CREATE PROCEDURE Interest Calculation()
BEGIN
SELECT Date
WHERE Date= 03/01/2018 between 31/08/2018;
FROM t-repayment;
9

END //
DELIMITER;
Test the procedure
Task -5 Theory
Transaction
A transaction is a consistent unit of work that contains at least one SQL articulations.
A transaction is a nuclear unit. The impacts of all the SQL explanations in a transaction can
be either all dedicated or all moved back. At the point when a bank client transactions cash
from an investment account to a financial records.
Database Schedule
On the off chance that two timetables deliver a similar outcome after execution, they
are said to be result identical. They may yield a similar outcome for some esteem and diverse
outcomes for another arrangement of qualities.
Struggle serializable timetable
Two timetables would strife in the event that they have the accompanying properties −
• Both have a place with independent transactions.
• Both gets to similar information thing.
• At minimum one of them is "express" task.
Two calendars having different transactions with clashing tasks are said to be struggle
proportional if and just if
• Both the timetables contain a similar arrangement of Transactions.
10

• The request of clashing sets of activity is kept up in both the calendars.
Condition of the database
View identical calendars are see serializable and strife proportional timetables are
struggle serializable. All contention serializable timetables are see serializable as well.
Serializability
At the point when different transactions are being executed by the working framework in a
multiprogramming domain, there are conceivable outcomes that directions of one
transactions are interleaved with some other transaction.
• Schedule − A sequential execution grouping of an transaction is known as a
timetable. A calendar can have numerous transactions in it, each including various
directions/assignments.
• Serial Schedule − It is a calendar in which transactions are adjusted so that one
transaction is executed first. At the point when the primary transaction finishes its
cycle, at that point the following transaction is executed. Transactions are
requested in a steady progression. This kind of calendar is known as a sequential
timetable, as transactions are executed in a sequential way.
SQL Code
SQL code is shown below.
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
11