Database System for Desklib

Running head: DATABASE SYSTEM
Database System
Name of the Student
Name of the University
Author Note:

1DATABASE SYSTEM
Table of Contents
Task 1:.............................................................................................................................................2
Task 2:...........................................................................................................................................11
Task 3:...........................................................................................................................................13
Task 4:...........................................................................................................................................16
Task 5:...........................................................................................................................................17
Introduction....................................................................................................................................17
Discussion......................................................................................................................................17
Transaction................................................................................................................................17
Schedule.....................................................................................................................................18
Conflict serializable schedule....................................................................................................19
Similarity between Conflict serializable schedule and state of database...................................19
View Serializability...................................................................................................................19
Conclusion.....................................................................................................................................20
References......................................................................................................................................21

2DATABASE SYSTEM
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;

3DATABASE SYSTEM
/**
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 --

4DATABASE SYSTEM
CREATE TABLE IF NOT EXISTS T_Acc_Type (
AccountTypeID VARCHAR(10) NOT NULL,
TypeName VARCHAR(45) NULL,
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, AccountType),
FOREIGN KEY (AccountType)

5DATABASE SYSTEM
REFERENCES T_Acc_Type(AccountTypeID));
-- Loan_Type --
CREATE TABLE IF NOT EXISTS T_Loan_Type (
LoanTypeID VARCHAR(10) NOT NULL,
Loan_TypeName VARCHAR(45) NULL,
Loan_TypeDesc VARCHAR(45) NULL,
Loan_TypeMInRate DECIMAL(4,2) NULL,
PRIMARY KEY (LoanTypeID));
-- Loan --
CREATE TABLE IF NOT EXISTS T_Loan (
LoanID VARCHAR(10) NOT NULL,
LoanRate DECIMAL(4,2) NULL,