Database System Assignment: Creating Tables and Implementing Triggers

This document presents a comprehensive solution to a database system assignment. The assignment involves creating and populating tables, defining stored procedures for loan repayments, and implementing triggers to enforce business rules, such as limiting the number of loans per account and checking for loan amount thresholds. The solution includes SQL scripts for table creation, data insertion, procedure definitions, and trigger implementation. Furthermore, the assignment explores database concepts such as transactions, schedules, conflict serializability, and view serializability, providing detailed explanations and discussions on each topic. The document also covers the ACID properties of transactions, schedule equivalency, and the differences between conflict and view serializability, culminating in a complete analysis of database concurrency control mechanisms.

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,

6DATABASE SYSTEM
LoanAmount DECIMAL(8) NULL,
Loan_Type VARCHAR(10) NOT NULL,
Loan_AccountBSB VARCHAR(10) NOT NULL,
Loan_AcctNo VARCHAR(10) NOT NULL,
PRIMARY KEY (LoanID),
FOREIGN KEY (Loan_Type)
REFERENCES T_Loan_Type (LoanTypeID),
FOREIGN KEY (Loan_AccountBSB , Loan_AcctNo)
REFERENCES T_Account (BSB, AccountNo));
-- Repayment --
CREATE TABLE IF NOT EXISTS T_Repayment (
RepaymentNo int NOT NULL AUTO_INCREMENT,
Repayment_LoanID VARCHAR(10) NOT NULL,
RepaymentAmount DECIMAL(6) NULL,
RepaymentDate DATETIME NULL,

7DATABASE SYSTEM
PRIMARY KEY (RepaymentNo),
FOREIGN KEY (Repayment_LoanID)
REFERENCES T_Loan (LoanID));
-- Own --
CREATE TABLE IF NOT EXISTS T_Own (
Customer_ID VARCHAR(10) NOT NULL,
Account_BSB VARCHAR(10) NOT NULL,
Account_No VARCHAR(10) NOT NULL,
PRIMARY KEY (Customer_ID, Account_BSB, Account_No),
FOREIGN KEY (Customer_ID)
REFERENCES T_Customer (customerID),
FOREIGN KEY (Account_BSB, Account_No)
REFERENCES T_Account (BSB, AccountNo));

8DATABASE SYSTEM
/*
Populate Tables
*/
INSERT INTO T_Customer VALUES
('C1','Adam','AdamHouse','234567891','aMail','2015-10-10');
INSERT INTO T_Customer VALUES
('C2','Badshah','BadshahPalace','234567892','bMail','2015-10-11');
INSERT INTO T_Customer VALUES
('C3','Chandni','ChandniBar','234567893','cMail','2015-10-12');
INSERT INTO T_Acc_Type VALUES ('AT1','SAV','Savings','0.1','15');
INSERT INTO T_Acc_Type VALUES ('AT2','CHK','Checking','0.2','16');
INSERT INTO T_Acc_Type VALUES ('AT3','LON','Loan','0.3','17');
INSERT INTO T_Account VALUES ('BSB1','Acct1','10.00','AT1');
INSERT INTO T_Account VALUES ('BSB2','Acct2','11.00','AT3');
INSERT INTO T_Account VALUES ('BSB3','Acct3','12.00','AT3');

9DATABASE SYSTEM
INSERT INTO T_Account VALUES ('BSB3','Acct4','12.00','AT3');
INSERT INTO T_Account VALUES ('BSB1','Acct5','10.00','AT1');
INSERT INTO T_Account VALUES ('BSB1','Acct6','10.00','AT1');
INSERT INTO T_Loan_Type VALUES ('LT1','HL','Home Loan','0.01');
INSERT INTO T_Loan_Type VALUES ('LT2','IL','Investment Loan','0.02');
INSERT INTO T_Loan_Type VALUES ('LT3','PL','Personal Loan','0.03');
INSERT INTO T_Loan VALUES ('L1','0.01','5000.00','LT3','BSB3','Acct4');
INSERT INTO T_Loan VALUES ('L2','0.02','16200.00','LT2','BSB2','Acct2');
INSERT INTO T_Loan VALUES ('L3','0.03','670500.00','LT1','BSB3','Acct3');
INSERT INTO T_Repayment (Repayment_LoanID, RepaymentAmount,
RepaymentDate)
VALUES ('L1','1.00','2017-10-10');
INSERT INTO T_Repayment (Repayment_LoanID, RepaymentAmount,
RepaymentDate)
VALUES ('L2','2.00','2018-02-11');

10DATABASE SYSTEM
INSERT INTO T_Repayment (Repayment_LoanID, RepaymentAmount,
RepaymentDate)
VALUES ('L3','2.00','2018-02-11');
INSERT INTO T_Own VALUES ('C1','BSB2','Acct2');
INSERT INTO T_Own VALUES ('C2','BSB3','Acct3');
INSERT INTO T_Own VALUES ('C3','BSB3','Acct4');
INSERT INTO T_Own VALUES ('C1','BSB3','Acct4');
INSERT INTO T_Own VALUES ('C1','BSB1','Acct1');
INSERT INTO T_Own VALUES ('C2','BSB1','Acct5');
INSERT INTO T_Own VALUES ('C3','BSB1','Acct6');
/**
End Script
**/

11DATABASE SYSTEM
Task 2:
Repay_Loan Procedure:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Repay_loan`(IN from_BSB
VARCHAR(10), IN from_accountNo VARCHAR(10), IN to_loan VARCHAR(10), IN amount
DECIMAL(8), OUT balance_b decimal(10,0))
BEGIN