Spock Bank: Triggers, Procedures, And Interest Calculation Essay.

Seven Tables and Repay_loan Procedure

Generate seven tables and populate them using the script provided in the assignment folder. You can optionally create and populate them by yourself, but make sure there is strong  alignment between the tables created and rows populated by the script and those by you. In  particular, look carefully at how the primary key of the table for repayment (T_Repayment) is automatically generated, and how rows are inserted to it.

At Spock Bank, loan repayments are made purely online by transferring fund from another account – typically a savings account -- to the loan account. The customers normally use the bank’s app or webpage and specify from which account they would want to make the repayment, and towards which loan this payment should count. This action of the customer will effectively call a stored procedure as:Repay_loan(from_BSB, from_accountNo, to_loan, amount)
with relevant parameters, and make appropriate changes to the relevant tables.

This procedure is intended to transfer the amount of money in question from the account identified by (from_BSB, from_accountNo) to the loan account associated with the to_loan. The procedure must comply with the following business rules:
1. The repayment towards a loan must be made from an account such that at least one of the borrower individually (or jointly with some other entities) holds the account from which the money is being drawn.
2. There must be adequate amount of money in the relevant account for a repayment to be made – if there is not enough money, the repayment transaction cannot be completed.
3. If the repayment goes through, 
a) the balance is accordingly reduced in the account identified by (from_BSB,from_accountNo), and
b) an appropriate new record is inserted into the repayment account.
4. If for some reason the repayment transaction could not be completed, the failure is gracefully handled, and an appropriate message is displayed.

Fro this task, you need to do the following:
a) Write the procedure Repay_loan
b) Test the procedure Repay_loan. Your test should include:
i. one case when the repayment is successful, 
ii. one case where it fails because someone is trying to pay off someone else’s loan, and 
iii. one case where there is not enough money to make the repayment in the relevant account.

Triggers are used to implement complex business rules. Spoc Bank has some rules that have not been outlined earlier. One of them concerns the number of loans customers can have. The rules are:
a) A customer cannot individually have more than five loans.
b) The maximum number of loans that a customer can have, including both individual and joint loans, is eight.
c) A customer cannot have more than one personal loan.
d) Total amount of all the loans that an individual is involved in cannot exceed 10 million dollars. This calculation takes into account the original loan amounts, not the remaining loan balances.
e) An individual cannot be involved in more than three home loans.You have decided to implement these rules by writing a trigger.
(i) Explain what sort of trigger you would use for this purpose, and why.
(ii) Discuss how you will test this trigger.
(iii) write and adequately test this trigger.

At Spock Bank, on the 25th of every month, interest is calculated on every loan and is added to the corresponding loan account. Interest calculation is done on a “daily basis” in the sense that it takes into account how many days are there in that month, and interest is calculated on a pro rata basis. Furthermore, the remaining loan amount left to be paid, as reflected in the corresponding loan account, could vary day to day depending on the repayments made. Also note that, often a loan has an associated special savings account called an offset account. The balance in the savings account offsets the loan in the sense that in calculating the interest on the loan, interest is calculated only on the difference between the remaining loan amount left to be paid and the amount in the offset account. For instance, if the remaining loan amount is $5,000 on a given day, and there is $1,000 in the offset account on that day, interest is charged only on $4,000 (i.e. $5,000 -  $1,000) for that day.
(1) Can the given relational schema (without any modification) support the implementation this procedure? If not, why not? If yes, how? Discuss.
(2) If this schema, as it stands, will not support a procedure for interest calculation as specified above, what modifications in it will you introduce to this schema in order that the above interest calculation procedure can be supported? How will that modification help? Discuss.
(3) Write the code for modifying the tables and inserting rows into them if required.
(4) Write the procedure for interest calculation.
(5) Test the procedure you wrote for March 25, May 25 and August 25 of 2018.

In about one page (single space), with help of examples in the context of the the Spoc Bank domain:
a) Briefly explain what a transaction is;
b) Explain what a schedule is, and how it is different from a transaction;
c) Define and explain the notion of a conflict serializable schedule. Does a conflict 
serializable schedule guarantee database consistency? Explain why or why not.d) Do different conflict-serializable schedules made up of the same set of transactions leave a database in the same state (with same value of the corresponding data items) if they started with the same state of the database? Explain your answer.
e) Explain what view serializability is, and why its being a weaker notion than conflict serializability is also its strength.

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 @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

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_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 SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;