Spock Bank: Triggers, Procedures, And Interest Calculation

Seven Tables and Repay_loan Procedure

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. 

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;

Procedure for Interest Calculation

The procedure for interest calculation is illustrated as below.

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;

   END //

 DELIMITER;

Test the procedure 

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.
• 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 is shown below.

— MySQL Workbench Forward Engineering

SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @[email protected]@SQL_MODE, SQL_MODE=’ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’;

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

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

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

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

SET [email protected]_SQL_MODE;

SET [email protected]_FOREIGN_KEY_CHECKS;

SET [email protected]_UNIQUE_CHECKS;