Database Systems: Tables, Procedures, Triggers, Interest Calculation, Theory and SQL Code

This article covers the creation of tables, procedures, triggers, and interest calculation in a bank database. It also discusses transaction, database schedule, and serializability. SQL code is included.

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

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE,
SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_D
ATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITU
TION';
-- -----------------------------------------------------
-- Schema Bank Database
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema Bank Database
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `Bank Database` DEFAULT CHARACTER SET
utf8 ;
USE `Bank Database` ;
-- -----------------------------------------------------
-- Table `Bank Database`.`T-AccountType`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Bank Database`.`T-AccountType` (
`AccountTypeID` INT NOT NULL,
`TypeName` VARCHAR(45) NULL,
`TypeDescription` VARCHAR(45) NULL,
`TypeRate` DECIMAL(45) NULL,
`TypeFee` DECIMAL(45) NULL,
`OwnID` INT NULL,
`T-Own_OwnID` INT NULL,
PRIMARY KEY (`AccountTypeID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
12

-- Table `Bank Database`.`T-LoanType`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Bank Database`.`T-LoanType` (
`LoanTypeID` INT NOT NULL,
`LoanTypeName` VARCHAR(45) NULL,
`LoanTypeDescription` VARCHAR(45) NULL,
`LoanTypeAmount` DECIMAL(45) NULL,
`T-LoanTypecol` VARCHAR(45) NULL,
`LoanID` INT NULL,
PRIMARY KEY (`LoanTypeID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Bank Database`.`T-Loan`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Bank Database`.`T-Loan` (
`LoanID` INT NOT NULL,
`LoanRate` VARCHAR(45) NULL,
`LoanAmount` VARCHAR(45) NULL,
`LoanType` VARCHAR(45) NULL,
`LoanAccountNumber` VARCHAR(45) NULL,
`LoanAccountBSB` VARCHAR(45) NULL,
`AccountID` INT NULL,
`T-LoanType_LoanTypeID` INT NOT NULL,
PRIMARY KEY (`LoanID`),
INDEX `fk_T-Loan_T-LoanType1_idx` (`T-LoanType_LoanTypeID` ASC) VISIBLE,
CONSTRAINT `fk_T-Loan_T-LoanType`
FOREIGN KEY (`T-LoanType_LoanTypeID`)
REFERENCES `Bank Database`.`T-LoanType` (`LoanTypeID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
13

-- -----------------------------------------------------
-- Table `Bank Database`.`T-Account`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Bank Database`.`T-Account` (
`AccountID` INT NOT NULL,
`BSB` VARCHAR(45) NULL,
`AccountNo` VARCHAR(45) NULL,
`AccountBalance` DECIMAL(45) NULL,
`AccountTypeID` INT NULL,
`T-Loan_LoanID` INT NOT NULL,
`T-AccountType_AccountTypeID` INT NOT NULL,
`T-Loan_LoanID1` INT NOT NULL,
PRIMARY KEY (`AccountID`),
INDEX `fk_T-Account_T-Loan1_idx` (`T-Loan_LoanID1` ASC) VISIBLE,
CONSTRAINT `fk_T-Account_T-Loan1`
FOREIGN KEY (`T-Loan_LoanID1`)
REFERENCES `Bank Database`.`T-Loan` (`LoanID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Bank Database`.`T-Own`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Bank Database`.`T-Own` (
`OwnID` INT NOT NULL,
`Account_BSB` VARCHAR(45) NULL,
`AccountNo` VARCHAR(45) NULL,
`CustomerID` INT NULL,
`T-Customer_CustomerID` INT NOT NULL,
PRIMARY KEY (`OwnID`))
ENGINE = InnoDB;
14

-- -----------------------------------------------------
-- Table `Bank Database`.`T-Customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Bank Database`.`T-Customer` (
`CustomerID` INT NOT NULL,
`CustomerName` VARCHAR(45) NULL,
`CustomerAddress` VARCHAR(45) NULL,
`CustomerContactNumber` VARCHAR(45) NULL,
`CustomerEmail` VARCHAR(45) NULL,
`CustomerJoinDate` DATETIME NULL,
PRIMARY KEY (`CustomerID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Bank Database`.`T-Repayment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Bank Database`.`T-Repayment` (
`RepaymentID` INT NOT NULL,
`RepaymentLoanID` VARCHAR(45) NULL,
`RepaymentAmount` DECIMAL(45) NULL,
`RepaymentDate` DATETIME NULL,
`LoanID` INT NULL,
`T-Loan_LoanID` INT NOT NULL,
PRIMARY KEY (`RepaymentID`),
INDEX `fk_T-Repayment_T-Loan1_idx` (`T-Loan_LoanID` ASC) VISIBLE,
CONSTRAINT `fk_T-Repayment_T-Loan`
FOREIGN KEY (`T-Loan_LoanID`)
REFERENCES `Bank Database`.`T-Loan` (`LoanID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
15

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
16