Data Warehousing and Data Mining Lab Practicals
Enterprise: Banking Management System
Software: MySQL 8.0, MySQL Workbench and WEKA
This practical covers source table identification, data warehouse creation, multidimensional data modelling, and data mining applications using WEKA.
Practical 1: Identify Source Tables and Populate Sample Data
Aim
To identify the source tables required for a banking data warehouse.
| Table | Purpose | Attributes |
|---|---|---|
| Branch | Branch information | BranchID, BranchName, City |
| Customer | Customer details | CustomerID, CustomerName, Gender, Age, BranchID |
| Account | Account details | AccountID, CustomerID, AccountType, Balance |
| BankTransaction | Transaction records | TransactionID, AccountID, TransactionDate, TransactionType, Amount |
| Loan | Loan information | LoanID, CustomerID, LoanAmount, InterestRate, LoanStatus |
Relationships: Branch serves customers, customers own accounts and loans, and accounts contain transactions.
Result: Source tables for the banking data warehouse have been identified.
Practical 2: Create Source Tables and Populate Them
Aim
To create source tables and insert sample banking records.
SQL Code
CREATE DATABASE IF NOT EXISTS BankingSource; USE BankingSource; CREATE TABLE Branch ( BranchID INT PRIMARY KEY, BranchName VARCHAR(50), City VARCHAR(50) ); CREATE TABLE Customer ( CustomerID INT PRIMARY KEY, CustomerName VARCHAR(100), Gender VARCHAR(10), Age INT, BranchID INT, FOREIGN KEY (BranchID) REFERENCES Branch(BranchID) ); CREATE TABLE Account ( AccountID INT PRIMARY KEY, CustomerID INT, AccountType VARCHAR(20), Balance DECIMAL(12,2), FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) ); CREATE TABLE BankTransaction ( TransactionID INT PRIMARY KEY, AccountID INT, TransactionDate DATE, TransactionType VARCHAR(20), Amount DECIMAL(12,2), FOREIGN KEY (AccountID) REFERENCES Account(AccountID) ); CREATE TABLE Loan ( LoanID INT PRIMARY KEY, CustomerID INT, LoanAmount DECIMAL(12,2), InterestRate DECIMAL(5,2), LoanStatus VARCHAR(20), FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) ); INSERT INTO Branch VALUES (1,'Main Branch','Delhi'), (2,'City Branch','Gurugram'), (3,'Market Branch','Faridabad'); INSERT INTO Customer VALUES (101,'Amit Sharma','Male',35,1), (102,'Neha Verma','Female',29,2), (103,'Rohit Kumar','Male',42,1), (104,'Priya Singh','Female',31,3), (105,'Karan Mehta','Male',25,2); INSERT INTO Account VALUES (1001,101,'Savings',50000), (1002,102,'Current',80000), (1003,103,'Savings',65000), (1004,104,'Savings',40000), (1005,105,'Current',95000); INSERT INTO BankTransaction VALUES (1,1001,'2026-01-05','Deposit',10000), (2,1001,'2026-01-10','Withdrawal',3000), (3,1002,'2026-01-12','Deposit',20000), (4,1003,'2026-02-02','Withdrawal',5000), (5,1004,'2026-02-10','Deposit',15000), (6,1005,'2026-02-15','Deposit',25000), (7,1002,'2026-03-01','Withdrawal',8000), (8,1003,'2026-03-05','Deposit',12000); INSERT INTO Loan VALUES (501,101,200000,8.50,'Approved'), (502,102,150000,9.00,'Approved'), (503,104,300000,8.75,'Pending'), (504,105,100000,10.00,'Approved'); SELECT * FROM Branch; SELECT * FROM Customer; SELECT * FROM Account; SELECT * FROM BankTransaction; SELECT * FROM Loan;
Verification Queries
SELECT COUNT(*) AS TotalCustomers FROM Customer;
SELECT COUNT(*) AS TotalAccounts FROM Account;
SELECT COUNT(*) AS TotalTransactions FROM BankTransaction;
SELECT TransactionType,
COUNT(*) AS NumberOfTransactions,
SUM(Amount) AS TotalAmount
FROM BankTransaction
GROUP BY TransactionType;
Expected counts: 3 branches, 5 customers, 5 accounts, 8 transactions and 4 loans. Deposits total 82000 and withdrawals total 16000.
Result: Source tables have been created and populated successfully.
Practical 3: Create a Data Warehouse
Aim
To create and populate a banking data warehouse for historical analysis.
Star Schema
The Star Schema contains a central fact table connected directly to dimension tables.
↓ ↓ ↓ ↓
Measures: Amount, TransactionCount
Create the Warehouse Tables
CREATE DATABASE IF NOT EXISTS BankingDW; USE BankingDW; CREATE TABLE DimCustomer ( CustomerKey INT AUTO_INCREMENT PRIMARY KEY, CustomerID INT, CustomerName VARCHAR(100), Gender VARCHAR(10), Age INT ); CREATE TABLE DimBranch ( BranchKey INT AUTO_INCREMENT PRIMARY KEY, BranchID INT, BranchName VARCHAR(50), City VARCHAR(50) ); CREATE TABLE DimAccount ( AccountKey INT AUTO_INCREMENT PRIMARY KEY, AccountID INT, AccountType VARCHAR(20) ); CREATE TABLE DimDate ( DateKey INT PRIMARY KEY, FullDate DATE, DayNumber INT, MonthNumber INT, MonthName VARCHAR(20), QuarterNumber INT, YearNumber INT ); CREATE TABLE FactTransaction ( FactKey INT AUTO_INCREMENT PRIMARY KEY, TransactionID INT, CustomerKey INT, BranchKey INT, AccountKey INT, DateKey INT, TransactionType VARCHAR(20), Amount DECIMAL(12,2), TransactionCount INT DEFAULT 1, FOREIGN KEY (CustomerKey) REFERENCES DimCustomer(CustomerKey), FOREIGN KEY (BranchKey) REFERENCES DimBranch(BranchKey), FOREIGN KEY (AccountKey) REFERENCES DimAccount(AccountKey), FOREIGN KEY (DateKey) REFERENCES DimDate(DateKey) );
Populate the Dimension Tables
INSERT INTO BankingDW.DimCustomer (CustomerID,CustomerName,Gender,Age) SELECT CustomerID,CustomerName,Gender,Age FROM BankingSource.Customer; INSERT INTO BankingDW.DimBranch (BranchID,BranchName,City) SELECT BranchID,BranchName,City FROM BankingSource.Branch; INSERT INTO BankingDW.DimAccount (AccountID,AccountType) SELECT AccountID,AccountType FROM BankingSource.Account; INSERT INTO BankingDW.DimDate (DateKey,FullDate,DayNumber,MonthNumber, MonthName,QuarterNumber,YearNumber) SELECT DISTINCT CAST(DATE_FORMAT(TransactionDate,'%Y%m%d') AS UNSIGNED), TransactionDate, DAY(TransactionDate), MONTH(TransactionDate), MONTHNAME(TransactionDate), QUARTER(TransactionDate), YEAR(TransactionDate) FROM BankingSource.BankTransaction;
Populate the Fact Table
INSERT INTO BankingDW.FactTransaction (TransactionID,CustomerKey,BranchKey,AccountKey, DateKey,TransactionType,Amount,TransactionCount) SELECT t.TransactionID, dc.CustomerKey, db.BranchKey, da.AccountKey, CAST(DATE_FORMAT(t.TransactionDate,'%Y%m%d') AS UNSIGNED), t.TransactionType, t.Amount, 1 FROM BankingSource.BankTransaction t JOIN BankingSource.Account a ON t.AccountID=a.AccountID JOIN BankingSource.Customer c ON a.CustomerID=c.CustomerID JOIN BankingDW.DimCustomer dc ON c.CustomerID=dc.CustomerID JOIN BankingDW.DimBranch db ON c.BranchID=db.BranchID JOIN BankingDW.DimAccount da ON a.AccountID=da.AccountID;
Analyze the Warehouse
SELECT b.BranchName,
SUM(f.Amount) AS TotalTransactionAmount,
SUM(f.TransactionCount) AS TotalTransactions
FROM BankingDW.FactTransaction f
JOIN BankingDW.DimBranch b ON f.BranchKey=b.BranchKey
GROUP BY b.BranchName
ORDER BY TotalTransactionAmount DESC;
SELECT d.YearNumber,d.MonthName,f.TransactionType,
SUM(f.Amount) AS TotalAmount,
COUNT(*) AS NumberOfTransactions
FROM BankingDW.FactTransaction f
JOIN BankingDW.DimDate d ON f.DateKey=d.DateKey
GROUP BY d.YearNumber,d.MonthNumber,d.MonthName,f.TransactionType
ORDER BY d.YearNumber,d.MonthNumber;
SELECT COUNT(*) AS WarehouseFactRows
FROM BankingDW.FactTransaction;
Expected result: The fact table should contain 8 rows. Branch transaction totals are City Branch = 53000, Main Branch = 30000, and Market Branch = 15000.
Result: The banking data warehouse has been created and populated.
Practical 4: Design Star, Snowflake and Fact Constellation Schemas
Aim
To design three multidimensional models for banking data analysis.
A. Star Schema
A Star Schema has one central fact table connected directly to dimension tables. It is easy to understand and suitable for analytical reports.
Structure: FactTransaction connects to DimCustomer, DimBranch, DimAccount and DimDate.
USE BankingDW;
SELECT b.BranchName,
SUM(f.Amount) AS TotalDeposits
FROM FactTransaction f
JOIN DimBranch b ON f.BranchKey=b.BranchKey
WHERE f.TransactionType='Deposit'
GROUP BY b.BranchName;
B. Snowflake Schema
A Snowflake Schema normalizes dimensions into related tables. For example, branch information can be separated from city information.
CREATE DATABASE IF NOT EXISTS BankingSnowflake;
USE BankingSnowflake;
CREATE TABLE DimCity (
CityKey INT PRIMARY KEY,
CityName VARCHAR(50),
StateName VARCHAR(50)
);
CREATE TABLE DimBranch (
BranchKey INT PRIMARY KEY,
BranchID INT,
BranchName VARCHAR(50),
CityKey INT,
FOREIGN KEY (CityKey) REFERENCES DimCity(CityKey)
);
CREATE TABLE DimCustomer (
CustomerKey INT PRIMARY KEY,
CustomerID INT,
CustomerName VARCHAR(100),
Gender VARCHAR(10),
Age INT
);
CREATE TABLE DimAccount (
AccountKey INT PRIMARY KEY,
AccountID INT,
AccountType VARCHAR(20)
);
CREATE TABLE DimDate (
DateKey INT PRIMARY KEY,
FullDate DATE,
MonthNumber INT,
YearNumber INT
);
CREATE TABLE FactTransaction (
FactKey INT AUTO_INCREMENT PRIMARY KEY,
TransactionID INT,
CustomerKey INT,
BranchKey INT,
AccountKey INT,
DateKey INT,
TransactionType VARCHAR(20),
Amount DECIMAL(12,2),
FOREIGN KEY (CustomerKey) REFERENCES DimCustomer(CustomerKey),
FOREIGN KEY (BranchKey) REFERENCES DimBranch(BranchKey),
FOREIGN KEY (AccountKey) REFERENCES DimAccount(AccountKey),
FOREIGN KEY (DateKey) REFERENCES DimDate(DateKey)
);
INSERT INTO DimCity VALUES
(1,'Delhi','Delhi'),
(2,'Gurugram','Haryana'),
(3,'Faridabad','Haryana');
INSERT INTO DimBranch VALUES
(1,1,'Main Branch',1),
(2,2,'City Branch',2),
(3,3,'Market Branch',3);
INSERT INTO DimCustomer
SELECT CustomerKey,CustomerID,CustomerName,Gender,Age
FROM BankingDW.DimCustomer;
INSERT INTO DimAccount
SELECT AccountKey,AccountID,AccountType
FROM BankingDW.DimAccount;
INSERT INTO DimDate
SELECT DateKey,FullDate,MonthNumber,YearNumber
FROM BankingDW.DimDate;
INSERT INTO FactTransaction
(TransactionID,CustomerKey,BranchKey,AccountKey,
DateKey,TransactionType,Amount)
SELECT TransactionID,CustomerKey,BranchKey,AccountKey,
DateKey,TransactionType,Amount
FROM BankingDW.FactTransaction;
Snowflake Query
SELECT city.CityName,b.BranchName,
SUM(f.Amount) AS TotalAmount
FROM BankingSnowflake.FactTransaction f
JOIN BankingSnowflake.DimBranch b
ON f.BranchKey=b.BranchKey
JOIN BankingSnowflake.DimCity city
ON b.CityKey=city.CityKey
GROUP BY city.CityName,b.BranchName;
Advantage: Reduces repeated dimension data. Disadvantage: Requires additional joins.
C. Fact Constellation Schema
A Fact Constellation, or Galaxy Schema, contains multiple fact tables that share dimensions. In banking, FactTransaction analyzes transactions and FactLoan analyzes loans.
↓ ↓
Shared dimensions allow related business processes to be analyzed.
Create and Populate the Loan Fact Table
USE BankingDW;
CREATE TABLE FactLoan (
LoanFactKey INT AUTO_INCREMENT PRIMARY KEY,
LoanID INT,
CustomerKey INT,
BranchKey INT,
LoanAmount DECIMAL(12,2),
InterestRate DECIMAL(5,2),
LoanStatus VARCHAR(20),
FOREIGN KEY (CustomerKey) REFERENCES DimCustomer(CustomerKey),
FOREIGN KEY (BranchKey) REFERENCES DimBranch(BranchKey)
);
INSERT INTO FactLoan
(LoanID,CustomerKey,BranchKey,LoanAmount,InterestRate,LoanStatus)
SELECT l.LoanID,dc.CustomerKey,db.BranchKey,
l.LoanAmount,l.InterestRate,l.LoanStatus
FROM BankingSource.Loan l
JOIN BankingSource.Customer c ON l.CustomerID=c.CustomerID
JOIN BankingDW.DimCustomer dc ON c.CustomerID=dc.CustomerID
JOIN BankingDW.DimBranch db ON c.BranchID=db.BranchID;
SELECT b.BranchName,
COUNT(*) AS NumberOfLoans,
SUM(l.LoanAmount) AS TotalLoanAmount
FROM BankingDW.FactLoan l
JOIN BankingDW.DimBranch b ON l.BranchKey=b.BranchKey
GROUP BY b.BranchName;
Expected loan totals: Main Branch = 200000, City Branch = 250000, Market Branch = 300000.
Comparison of Multidimensional Schemas
| Feature | Star | Snowflake | Fact Constellation |
|---|---|---|---|
| Fact tables | Usually one | Usually one per model | Multiple |
| Dimensions | Directly linked | Normalized | Can be shared |
| Joins | Fewer | More | Depends on query |
| Use | Simple reports | Hierarchical dimensions | Multiple business processes |
Result: Star, Snowflake and Fact Constellation schemas have been designed for the banking enterprise.
Practical 5: Install WEKA for Data Mining Applications
Aim
To install WEKA and perform classification, clustering and association rule mining.
What is WEKA?
WEKA stands for Waikato Environment for Knowledge Analysis. It is a machine learning and data mining software package.
Installation Steps
- Visit the official WEKA download page.
- Download the appropriate Windows installer.
- Run the installer and follow the installation instructions.
- Install a compatible Java version if required by the selected WEKA package.
- Launch WEKA and open Explorer.
Create a Sample Dataset
Open Notepad and save the following as bank_customers.csv. Choose All Files in the Save As dialog.
Age,Income,ExistingLoan,LoanAccepted Young,Low,Yes,No Young,Medium,No,Yes Middle,High,No,Yes Middle,Medium,Yes,No Senior,High,No,Yes Senior,Low,Yes,No Young,High,No,Yes Middle,Low,No,No Senior,Medium,Yes,No Young,Medium,Yes,No Middle,High,No,Yes Senior,High,Yes,No Young,Low,No,No Middle,Medium,No,Yes Senior,Medium,No,Yes
A. Classification using J48
- Open WEKA Explorer.
- Select Preprocess and click Open file.
- Choose bank_customers.csv.
- Open the Classify tab.
- Click Choose and select trees, then J48.
- Select Cross-validation with 5 folds.
- Choose LoanAccepted as the class attribute.
- Click Start to run the classifier.
WEKA displays a decision tree, predicted classes, evaluation statistics and a confusion matrix. Results depend on the data and settings.
B. Clustering using SimpleKMeans
- Load the dataset in WEKA Explorer.
- Open the Cluster tab.
- Choose SimpleKMeans.
- Set the number of clusters to 2.
- Click Start.
Clustering groups records with similar characteristics without requiring a target class.
C. Association Rule Mining using Apriori
- Load the CSV dataset in WEKA Explorer.
- Open the Associate tab.
- Choose Apriori if available.
- Click Start.
Apriori discovers associations among attributes. For example, a rule may associate an existing loan with loan acceptance patterns. Any rule found in this small illustrative dataset should not be treated as a reliable banking prediction.
Result
WEKA has been installed and can be used to experiment with classification, clustering and association rule mining.
Conclusion
In these five practicals, the banking source database was designed and populated, a data warehouse was created, Star, Snowflake and Fact Constellation models were implemented, and WEKA was introduced for data mining.
Important Notes
- Execute the SQL scripts in order: Practical 2, Practical 3, and Practical 4.
- Run each script only once on a fresh database, or reset the sample databases before rerunning insert statements.
- Execute the source database creation before loading the warehouse dimensions and facts.
- The sample banking records are fictional and intended only for educational purposes.
- Capture your own output screenshots from MySQL Workbench and WEKA if your lab record requires them.
