Database Transaction: Case Study of Loan Payment

Running head: DATABASE TRANSACTION
Database Transaction: Case Study of Loan Payment
Name of the Student
Name of the University
Author’s note:

1DATABASE TRANSACTION
Table of Contents
Task 1:........................................................................................................................................2
Task 2:........................................................................................................................................6
Task 3:........................................................................................................................................7
Task 4:........................................................................................................................................8
Task 5:........................................................................................................................................9
Introduction................................................................................................................................9
Discussion..................................................................................................................................9
Transaction.............................................................................................................................9
Schedule...............................................................................................................................10
Conflict serializable schedule..............................................................................................10
Similarity between Conflict serializable schedule and state of database.............................10
View Serializability..............................................................................................................10
Conclusion................................................................................................................................10
References:...............................................................................................................................11

2DATABASE TRANSACTION
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;
/**
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 --

3DATABASE TRANSACTION
CREATE TABLE IF NOT EXISTS T_Acc_Type (
AccountTypeID VARCHAR(10) NOT NULL,
TypeName SET('SAV','CHK','LON'),
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),
FOREIGN KEY (AccountType)
REFERENCES T_Acc_Type(AccountTypeID));
-- Loan_Type --
CREATE TABLE IF NOT EXISTS T_Loan_Type (
LoanTypeID VARCHAR(10) NOT NULL,
Loan_TypeName SET('HL','IL','PL'),
Loan_TypeDesc VARCHAR(45) NULL,
Loan_TypeMInRate DECIMAL(4,2) NULL,
PRIMARY KEY (LoanTypeID));
-- Loan --