Department of Computer Science and Information Technology
Database Systems and Applications: SSWTZC337
Lab Work Sheet-3
Name – Rohit Singh
Bits ID – 2023wd86475
Problem Statement: Implement a comprehensive database solution that
includes a stored procedure for transferring funds between accounts (ensuring
sufficient balance and using transaction properties), a trigger to automatically
update account balances upon new transactions, and a cursor to generate a
report of transactions with amounts greater than 100
Step1: Setup the Database Schema: Created the tables with the provided
schema. DATABASE: “Bank_Account_Management”
TABLE: ‘’Accounts”, “Transactions” and “Overdrafts”.
-- Create Accounts Table
CREATE TABLE Accounts (
AccountID INT PRIMARY KEY,
AccountNumber VARCHAR (10),
AccountHolder VARCHAR (20),
Balance DECIMAL (10,2)
);
-- Create Transactions Table
CREATE TABLE Transactions (
TransactionID INT PRIMARY KEY,
AccountID INT,
TransactionDate DATE,
TransactionAmount DECIMAL (10,2),
Transactiontype VARCHAR (10), -- 'Debit or Credit'
FOREIGN KEY (AccountID) REFERENCES Accounts (AccountID) );
-- Create Overdrafts Table
CREATE TABLE Overdrafts (
OverdraftID INT PRIMARY KEY,
AccountID INT,
OverdraftDate DATE,
OverdraftAmount DECIMAL (10,2),
FOREIGN KEY (AccountID) REFERENCES Accounts (AccountID));
Note: Insert 4 records in the relation (table) Accounts and Overdrafts.
So, here sharing a Snapshot of data which I have entered in the table.
-- Insert sample data into Accounts Table
INSERT INTO Accounts (AccountID, AccountNumber, AccountHolder, Balance)
VALUES (1, '9876543210', 'Hrithik Kumar', 1000.00),
(2, '8976543218', 'Kanhaiya Singh', 2000.00),
(3, '6543217890', 'Tiger Singh', 1500.00),
(4, '3456789201', 'Sourav Das', 800.00);
-- Check Table Data
SELECT * FROM Accounts;
-- Insert sample data into Overdrafts Table
INSERT INTO Overdrafts (OverdraftID, AccountID, OverdraftDate, OverdraftAmount)
VALUES (1, 1, '2025-02-01', 200.00),
(2, 2, '2025-02-10', 300.00),
(3, 3, '2025-02-15', 150.00),
(4, 4, '2025-02-20', 100.00);
-- Check Overdrafts Data
SELECT * FROM Overdrafts;
-- Modify the TransactionID field to Auto Increment
ALTER TABLE Transactions MODIFY COLUMN TransactionID
INT AUTO_INCREMENT;
Step2: Implement a Stored Procedure: Create a stored procedure to transfer
funds between two accounts. The procedure should check if the source account
has sufficient balance before making the transfer and handle the transaction
accordingly.
Display this with the help of an example test scenario.
Code: DELIMITER $$
CREATE PROCEDURE TransferFunds(
IN sourceAccountNumber BIGINT,
IN destinationAccountNumber BIGINT,
IN transferAmount DECIMAL(10, 2))
BEGIN
DECLARE currentBalance DECIMAL (10, 2);
DECLARE sourceAccountID BIGINT;
DECLARE destinationAccountID BIGINT;
-- Start transaction
START TRANSACTION;
-- Get the balance of the source account
SELECT Balance INTO currentBalance FROM Accounts WHERE AccountNumber =
sourceAccountNumber;
SELECT AccountID INTO sourceAccountID FROM Accounts WHERE AccountNumber =
sourceAccountNumber;
SELECT AccountID INTO destinationAccountID FROM Accounts WHERE
AccountNumber =
sourceAccountNumber;
-- Check if the source account has sufficient balance
IF currentBalance >= transferAmount THEN
-- Deduct from source account
UPDATE Accounts
SET Balance = Balance - transferAmount
WHERE AccountNumber = sourceAccountNumber;
-- Add to destination account
UPDATE Accounts
SET Balance = Balance + transferAmount
WHERE AccountNumber = destinationAccountNumber;
-- Insert a debit transaction for source account
INSERT INTO Transactions (AccountID, TransactionDate, TransactionAmount,
TransactionType)
VALUES (sourceAccountID, CURDATE (), transferAmount, 'Debit');
-- Insert a credit transaction for destination account
INSERT INTO Transactions (AccountID, TransactionDate, TransactionAmount,
TransactionType)
VALUES (destinationAccountID, CURDATE(), transferAmount, 'Credit');
-- Commit the transaction
COMMIT;
SELECT 'Transfer successful' AS Message;
ELSE
-- Rollback the transaction if insufficient funds
ROLLBACK;
SELECT 'Insufficient balance in source account' AS Message;
END IF;
END$$
DELIMITER;
Step3: Implement a Trigger: To ensure the Balance in the Accounts table is
updated automatically when a new transaction occurs, create a trigger. The
trigger should update the balance based on the transaction type ('Debit' or
'Credit').
Display the output with an example test scenario
Code: DELIMITER $$
CREATE TRIGGER UpdateAccountBalance
AFTER INSERT ON Transactions
FOR EACH ROW
BEGIN
IF [Link] = 'Debit' THEN
UPDATE Accounts
SET Balance = Balance - [Link]
WHERE AccountID = [Link];
ELSEIF [Link] = 'Credit' THEN
UPDATE Accounts
SET Balance = Balance + [Link]
WHERE AccountID = [Link];
END IF;
END $$
DELIMITER;
Step4: Implement a Cursor: You need to generate a report of all high-value
transactions (greater than 100). Use a cursor to iterate through the Transactions
table and display the details of such transactions.
Display this with the help of example test scenarios for transactions:
a. greater than 100 and
b. not greater than 100
Code: DELIMITER $$
CREATE PROCEDURE GetHighValueTransactions()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE transID INT;
DECLARE transDate DATE;
DECLARE transAmount DECIMAL(10, 2);
DECLARE transType VARCHAR(10);
DECLARE cur CURSOR FOR
SELECT TransactionID, TransactionDate, TransactionAmount, TransactionType
FROM Transactions
WHERE TransactionAmount > 100;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO transID, transDate, transAmount, transType;
IF done THEN
LEAVE read_loop;
END IF;
-- Display each transaction
SELECT transID AS TransactionID, transDate AS TransactionDate,
transAmount AS TransactionAmount, transType AS TransactionType;
END LOOP;
CLOSE cur;
END $$
DELIMITER ;
Stored Procedures Problem Statement:
• Create a stored procedure to transfer funds between two accounts.
• The procedure should check if the source account has sufficient balance before
making the transfer.
• And handle the transaction accordingly.
Solution: (refer step 2):
Triggers Problem Statement:
• To ensure the Balance in the Accounts table is updated automatically when a
new transaction occurs, create a trigger.
• The trigger should update the balance based on the transaction type ('Debit' or
'Credit').
Solution: (refer step 3)
Cursors Problem Statement:
• You need to generate a report of all high-value transactions (greater than 100).
• Use a cursor to iterate through the Transactions table and display the details of
such transactions.
Solution: (refer step 4)
Test the Stored Procedure created.
♦ Test 1: Transfer funds between two accounts. (Source account has
sufficient balance)
a) Use select query to view accounts details.
Screenshot:
Before doing b), first we must disable Safe Update Mode.
For Disabling, Go to Edit > Preferences, then navigate to SQL Editor.
Find the option “Safe Updates” and uncheck it.
Then reconnect to Dbms.
b) Click on highlighted symbol in front of “TransferFunds” stored procedure.
Screenshot:
c) Enter the details by referring account details. click on Execute. (in below
image I am sending Rs 200 from 8976543218 to 9876543210)
Screenshot:
♦ Below Screenshot show that transfer has been successfully completed.
♦ Test 2: Transfer funds between two accounts. (Source account has
insufficient balance) Refer Test1 till the c.
Screenshot:
♦ Below Screenshot showing that 3456789201 account number has insufficient
balance to transfer.