Friday, 23 October 2026

Dataware housing and data mining complete manual file(DWDM)

 

XXX

 UNIVERSITY

Laboratory Manual

 

DATA WAREHOUSING

AND

DATA MINING

 

Practical Manual for Diploma Students

20 Practicals  |  MySQL Workbench  |  Orange Data Mining

 

Faculty


Student Name

 

Roll No.

 



INDEX

S.No.

Title

Date

Remark

PART A – DBMS

1

Introduction to DBMS and MySQL Workbench

 

 

2

SQL and Its Categories (DDL, DML, DQL, DCL, TCL)

 

 

3

CREATE, ALTER and DROP Tables (DDL)

 

 

4

INSERT Data (DML)

 

 

5

SELECT Queries (DQL)

 

 

6

UPDATE and DELETE (DML)

 

 

7

Constraints

 

 

8

Joins

 

 

9

Aggregate Functions and GROUP BY

 

 

10

Transactions (TCL) and User Permissions (DCL)

 

 

PART B - DATA MINING

11

Data Exploration

 

 

12

Classification

 

 

13

Clustering

 

 

14

Association Rules (Market Basket Analysis)

 

 

15

Model Evaluation

 

 

PART C - DATA WAREHOUSING

16

OLTP vs OLAP

 

 

17

Star Schema Design

 

 

18

Creating Fact and Dimension Tables

 

 

19

ETL (Extract, Transform, Load)

 

 

20

Simple Analytical Reports

 

 

PART A - DATABASE MANAGEMENT SYSTEM (MYSQL WORKBENCH)

Practical 1: Introduction to DBMS and MySQL Workbench

Aim: Understand what a DBMS is, know its types, and connect to MySQL using Workbench.

Theory

•     Data is raw facts (for example, Aman, 78). Information is data with meaning (Aman scored 78).

•     A database is an organised collection of related data. A DBMS (Database Management System) is software that creates, stores, protects and retrieves that data. Examples: MySQL, Oracle, PostgreSQL, MongoDB.

•     Before DBMS, people used separate files. Problems: duplicate data, hard searching, no security, and inconsistent updates. A DBMS solves these.

•     In a relational database, data is stored in tables. A table has rows (records) and columns (fields).

Type of DBMS

Idea

Example

Hierarchical

Data in a tree (parent - child)

IBM IMS

Network

Data in a graph; a child can have many parents

IDMS

Relational (RDBMS)

Data in tables linked by keys

MySQL, Oracle, PostgreSQL

Object-oriented

Data stored as objects

db4o

NoSQL

Flexible, non-table storage (documents, key-value)

MongoDB, Redis

 

Procedure

1.    Open MySQL Workbench. Next to MySQL Connections, click the + icon.

2.    Connection Name: Local. Hostname: 127.0.0.1. Port: 3306. Username: root. Click Test Connection, enter your password, then OK.

3.    Double-click the new connection to open the SQL Editor. Identify its four areas: Navigator (left), Query Editor (middle), Result Grid (below), Action Output (bottom).

4.    Type and run:

SELECT VERSION();

SHOW DATABASES;

SELECT NOW();

 

Expected Result: The first query shows the MySQL version (for example 8.0.x). SHOW DATABASES lists system databases such as information_schema, mysql, performance_schema and sys. The last query shows today's date and time. Action Output shows green ticks.

Practice Questions: (a) Write three differences between a file system and a DBMS. (b) Name one real-life system that uses a database (bank, railway booking, hotel).

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART A - DATABASE MANAGEMENT SYSTEM (MYSQL WORKBENCH)

Practical 2: SQL and Its Categories (DDL, DML, DQL, DCL, TCL)

Aim: Learn what SQL is, its five command categories, and create the practice database.

Theory: SQL (Structured Query Language) is the standard language for talking to a relational database. Its commands are grouped by purpose:

Category

Full form

Purpose

Commands

DDL

Data Definition Language

Create or change structure

CREATE, ALTER, DROP, TRUNCATE, RENAME

DML

Data Manipulation Language

Change the data

INSERT, UPDATE, DELETE

DQL

Data Query Language

Read the data

SELECT

DCL

Data Control Language

Permissions

GRANT, REVOKE

TCL

Transaction Control Language

Save or undo work

START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT

 

Procedure: Create and select the database we will use for Part A.

CREATE DATABASE college_db;

SHOW DATABASES;

USE college_db;

SELECT DATABASE();

 

Expected Result: college_db appears in the database list. SELECT DATABASE(); returns college_db, confirming it is the active database. Click the refresh icon in the Navigator to see it on the left.

Practice Questions: Classify each as DDL / DML / DQL / DCL / TCL: DROP TABLE, INSERT, COMMIT, SELECT, GRANT, UPDATE, ALTER.

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART A - DATABASE MANAGEMENT SYSTEM (MYSQL WORKBENCH)

Practical 3: CREATE, ALTER and DROP Tables (DDL)

Aim: Create tables, view their structure, modify and delete them.

Theory: A table needs a name, column names and a data type for each column. Common types: INT (whole numbers), DECIMAL(10,2) (exact decimals), VARCHAR(n) (text up to n characters), CHAR(1) (fixed text), DATE (YYYY-MM-DD).

Procedure

USE college_db;

 

CREATE TABLE department (

  dept_id   INT,

  dept_name VARCHAR(50)

);

 

CREATE TABLE student (

  roll_no INT,

  name    VARCHAR(50),

  gender  CHAR(1),

  city    VARCHAR(30),

  marks   INT,

  dept_id INT

);

 

SHOW TABLES;

DESCRIBE student;

 

-- ALTER: change the structure

ALTER TABLE student ADD email VARCHAR(60);

ALTER TABLE student MODIFY email VARCHAR(80);

ALTER TABLE student DROP COLUMN email;

 

-- Practice with a throw-away table

CREATE TABLE temp_demo (id INT, note VARCHAR(20));

TRUNCATE TABLE temp_demo;   -- removes all rows, keeps the table

DROP TABLE temp_demo;       -- removes the whole table

 

Expected Result: SHOW TABLES lists department and student. DESCRIBE student shows six columns with their types. After the ALTER lines the email column is added, widened, then removed. temp_demo disappears after DROP.

Important: DROP and TRUNCATE cannot be undone. Use them carefully.

Practice Questions: Create a table course(course_id, course_name, credits). Add a column duration, then rename the table to courses using RENAME TABLE course TO courses;.

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART A - DATABASE MANAGEMENT SYSTEM (MYSQL WORKBENCH)

Practical 4: INSERT Data (DML)

Aim: Add rows to tables.

Theory: INSERT INTO table VALUES (...) adds a row. Text and dates go inside single quotes. The values must match the column order, or you can name the columns.

Procedure

INSERT INTO department VALUES (1, 'Computer');

INSERT INTO department VALUES (2, 'Mechanical'), (3, 'Electrical');

 

INSERT INTO student (roll_no, name, gender, city, marks, dept_id)

VALUES (101, 'Aman', 'M', 'Delhi', 78, 1);

 

INSERT INTO student VALUES

(102, 'Riya',   'F', 'Jaipur', 85, 1),

(103, 'Karan',  'M', 'Delhi',  62, 2),

(104, 'Neha',   'F', 'Agra',   91, 1),

(105, 'Rahul',  'M', 'Jaipur', 55, 3),

(106, 'Priya',  'F', 'Delhi',  73, 2),

(107, 'Vikas',  'M', 'Agra',   48, 3),

(108, 'Sneha',  'F', 'Delhi',  88, 1),

(109, 'Arjun',  'M', 'Jaipur', 67, 2),

(110, 'Pooja',  'F', 'Agra',   81, 3);

 

SELECT * FROM department;

SELECT * FROM student;

 

Expected Result: The department table shows 3 rows and the student table shows 10 rows (roll numbers 101 to 110). Action Output reports 9 row(s) affected for the bulk insert.

Common error: Column count doesn't match value count means you gave too few or too many values.

Practice Questions: Insert two rows into your own courses table. Which values need quotes and which do not?

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART A - DATABASE MANAGEMENT SYSTEM (MYSQL WORKBENCH)

Practical 5: SELECT Queries (DQL)

Aim: Retrieve data using SELECT with filters, sorting and patterns.

Theory: SELECT reads data without changing it. WHERE filters rows, ORDER BY sorts, LIMIT restricts the number of rows, LIKE searches patterns (% means any characters), DISTINCT removes duplicates.

Procedure and Expected Results

SELECT * FROM student;                                   -- all 10 rows

SELECT name, marks FROM student;                         -- 2 columns

SELECT * FROM student WHERE marks > 75;                  -- 5 rows

SELECT * FROM student WHERE city = 'Delhi';              -- 4 rows

SELECT * FROM student WHERE marks BETWEEN 60 AND 80;     -- 4 rows

SELECT * FROM student WHERE city IN ('Agra', 'Jaipur');  -- 6 rows

SELECT * FROM student WHERE name LIKE 'S%';              -- 1 row: Sneha

SELECT * FROM student WHERE gender = 'F' AND marks > 80; -- 4 rows

SELECT DISTINCT city FROM student;                       -- 3 rows

SELECT name, marks FROM student ORDER BY marks DESC;     -- Neha 91 first

SELECT name, marks FROM student

ORDER BY marks DESC LIMIT 3;                             -- Neha, Sneha, Riya

 

Practice Questions: (a) Show students whose name ends with a. (b) Show students not from Delhi. (c) Show the 2 lowest scorers.

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART A - DATABASE MANAGEMENT SYSTEM (MYSQL WORKBENCH)

Practical 6: UPDATE and DELETE (DML)

Aim: Change and remove existing rows safely.

Theory: UPDATE ... SET ... WHERE changes data; DELETE FROM ... WHERE removes rows. Always write a WHERE clause. Without it, every row is changed or deleted.

Procedure: To keep your Part A data unchanged for later practicals, we use a temporary test row.

INSERT INTO student VALUES (111, 'Test', 'M', 'Delhi', 40, 3);

 

UPDATE student SET marks = 45 WHERE roll_no = 111;

UPDATE student SET marks = marks + 5, city = 'Agra' WHERE roll_no = 111;

SELECT * FROM student WHERE roll_no = 111;   -- marks 50, city Agra

 

DELETE FROM student WHERE roll_no = 111;

SELECT COUNT(*) FROM student;                -- back to 10

 

Expected Result: After the updates the test row shows marks 50 and city Agra. After DELETE, COUNT(*) returns 10.

If you see Error 1175 (Safe Update mode): MySQL Workbench blocks UPDATE/DELETE that do not use a key column. Use the roll number in WHERE as above, or switch off safe mode in Edit > Preferences > SQL Editor, then reconnect.

Practice Questions: Insert another test row and (a) change its city, (b) delete it using its name.

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART A - DATABASE MANAGEMENT SYSTEM (MYSQL WORKBENCH)

Practical 7: Constraints

Aim: Protect data quality using constraints.

Theory: Constraints are rules a column must obey.

Constraint

Rule

PRIMARY KEY

Unique and not empty; identifies each row

FOREIGN KEY

Value must exist in another table (links tables)

NOT NULL

Value cannot be empty

UNIQUE

No duplicate values

CHECK

Value must satisfy a condition (enforced in MySQL 8.0.16+)

DEFAULT

Value used when none is given

 

Procedure: Add constraints to the existing tables.

ALTER TABLE department ADD PRIMARY KEY (dept_id);

ALTER TABLE department ADD CONSTRAINT uq_dept UNIQUE (dept_name);

ALTER TABLE department MODIFY dept_name VARCHAR(50) NOT NULL;

 

ALTER TABLE student ADD PRIMARY KEY (roll_no);

ALTER TABLE student MODIFY name VARCHAR(50) NOT NULL;

ALTER TABLE student ADD CONSTRAINT chk_marks

  CHECK (marks BETWEEN 0 AND 100);

ALTER TABLE student ALTER city SET DEFAULT 'Delhi';

ALTER TABLE student ADD CONSTRAINT fk_dept

  FOREIGN KEY (dept_id) REFERENCES department(dept_id);

 

SHOW CREATE TABLE student;

 

Now try to break the rules. Each statement below should fail:

INSERT INTO student VALUES (101, 'Dup', 'M', 'Delhi', 50, 1);   -- duplicate key

INSERT INTO student VALUES (112, 'Bad', 'M', 'Delhi', 150, 1);  -- CHECK fails

INSERT INTO student VALUES (113, 'Ghost', 'M', 'Delhi', 50, 9); -- no dept 9

INSERT INTO student VALUES (114, NULL, 'M', 'Delhi', 50, 1);    -- NOT NULL

 

Expected Result: The ALTER statements succeed. SHOW CREATE TABLE displays the constraints. The four test inserts produce red errors: Duplicate entry, Check constraint violated, Cannot add or update a child row: a foreign key constraint fails, and Column 'name' cannot be null. No rows are added.

Practice Questions: (a) Why can a table have only one primary key? (b) What happens if you try to delete department 1 while students belong to it?

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART A - DATABASE MANAGEMENT SYSTEM (MYSQL WORKBENCH)

Practical 8: Joins

Aim: Combine data from two tables using joins.

Theory: Student names are in student, department names in department. A join matches rows using a common column (dept_id).

Join

Result

INNER JOIN

Only rows that match in both tables

LEFT JOIN

All rows from the left table, plus matches (NULL if none)

RIGHT JOIN

All rows from the right table, plus matches

CROSS JOIN

Every row paired with every row

 

Procedure

INSERT INTO department VALUES (4, 'Civil');   -- a department with no students

 

-- INNER JOIN

SELECT s.roll_no, s.name, d.dept_name

FROM student s

INNER JOIN department d ON s.dept_id = d.dept_id;

 

-- LEFT JOIN (all departments, even empty ones)

SELECT d.dept_name, s.name

FROM department d

LEFT JOIN student s ON d.dept_id = s.dept_id;

 

-- RIGHT JOIN (same idea from the other side)

SELECT s.name, d.dept_name

FROM student s

RIGHT JOIN department d ON s.dept_id = d.dept_id;

 

Expected Result: The INNER JOIN returns 10 rows (Civil does not appear because it has no students). The LEFT JOIN and RIGHT JOIN return 11 rows; the extra row is Civil with NULL as the student name.

Tip: s and d are aliases (short names) that make queries shorter.

Practice Questions: (a) Show names of students from the Computer department only. (b) Show departments that have no students (hint: LEFT JOIN with WHERE s.roll_no IS NULL).

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART A - DATABASE MANAGEMENT SYSTEM (MYSQL WORKBENCH)

Practical 9: Aggregate Functions and GROUP BY

Aim: Summarise data using COUNT, SUM, AVG, MIN, MAX, GROUP BY and HAVING.

Theory: Aggregate functions calculate one value from many rows. GROUP BY makes one summary row per group. HAVING filters groups (WHERE filters rows before grouping).

Procedure and Expected Results

SELECT COUNT(*) AS total, SUM(marks) AS total_marks,

       AVG(marks) AS avg_marks, MAX(marks) AS highest,

       MIN(marks) AS lowest

FROM student;

-- 10, 728, 72.8, 91, 48

 

SELECT city, COUNT(*) AS students

FROM student GROUP BY city;

-- Delhi 4, Jaipur 3, Agra 3

 

SELECT d.dept_name, COUNT(*) AS students, AVG(s.marks) AS avg_marks

FROM student s JOIN department d ON s.dept_id = d.dept_id

GROUP BY d.dept_name;

-- Computer 4 / 85.5, Mechanical 3 / 67.33, Electrical 3 / 61.33

 

SELECT d.dept_name, AVG(s.marks) AS avg_marks

FROM student s JOIN department d ON s.dept_id = d.dept_id

GROUP BY d.dept_name

HAVING AVG(s.marks) > 70;

-- only Computer (85.5)

 

Practice Questions: (a) Find the highest marks in each city. (b) List cities having more than 3 students.

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART A - DATABASE MANAGEMENT SYSTEM (MYSQL WORKBENCH)

Practical 10: Transactions (TCL) and User Permissions (DCL)

Aim: Understand COMMIT, ROLLBACK, SAVEPOINT, and control who can do what.

Theory: A transaction is a group of steps that must succeed together or not at all. Example: moving Rs 1000 from one account to another needs a debit and a credit. A transaction follows the ACID properties: Atomicity (all or nothing), Consistency (rules stay valid), Isolation (transactions do not disturb each other), Durability (committed data is permanent). Transactions need the InnoDB engine (the MySQL default).

Procedure - Part 1 (TCL)

CREATE TABLE accounts (

  acc_no  INT PRIMARY KEY,

  holder  VARCHAR(30),

  balance DECIMAL(10,2)

) ENGINE = InnoDB;

 

INSERT INTO accounts VALUES (1, 'Aman', 5000), (2, 'Riya', 3000);

 

-- A successful transfer

START TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE acc_no = 1;

UPDATE accounts SET balance = balance + 1000 WHERE acc_no = 2;

COMMIT;

SELECT * FROM accounts;      -- Aman 4000, Riya 4000

 

-- A mistake that we undo

START TRANSACTION;

UPDATE accounts SET balance = 0 WHERE acc_no = 1;

SELECT * FROM accounts;      -- Aman shows 0

ROLLBACK;

SELECT * FROM accounts;      -- Aman back to 4000

 

-- SAVEPOINT

START TRANSACTION;

UPDATE accounts SET balance = balance + 500 WHERE acc_no = 1;

SAVEPOINT after_bonus;

UPDATE accounts SET balance = balance - 2000 WHERE acc_no = 2;

ROLLBACK TO after_bonus;     -- undoes only the second update

COMMIT;

SELECT * FROM accounts;      -- Aman 4500, Riya 4000

 

Procedure - Part 2 (DCL)

CREATE USER 'viewer'@'localhost' IDENTIFIED BY 'View@123';

GRANT SELECT ON college_db.* TO 'viewer'@'localhost';

SHOW GRANTS FOR 'viewer'@'localhost';

REVOKE SELECT ON college_db.* FROM 'viewer'@'localhost';

DROP USER 'viewer'@'localhost';

 

Expected Result: The balances match the comments above. SHOW GRANTS lists GRANT SELECT ON college_db.* for the new user. After REVOKE and DROP USER, the user no longer exists.

Note: DDL statements (CREATE, DROP, TRUNCATE) automatically commit in MySQL, so they cannot be rolled back.

Practice Questions: (a) Explain ACID with a bank example. (b) What is the difference between COMMIT and ROLLBACK?

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART B - DATA MINING (ORANGE DATA MINING)

Dataset Files for Part B (Create Before Practical 11)

From Part B onwards we use Orange Data Mining, a visual tool where you build a workflow by dragging widgets onto a canvas and joining them with lines (links). No programming is needed. Data flows from left to right.

Open Notepad and save each file below exactly as named (Save as type: All Files).

students.csv (20 students: attendance %, internal marks out of 30, daily study hours, final result)

Roll,Attendance,Internal,StudyHours,Result

1,85,24,3,Pass

2,90,27,4,Pass

3,60,12,1,Fail

4,75,20,2,Pass

5,55,10,1,Fail

6,95,28,5,Pass

7,70,15,2,Fail

8,80,22,3,Pass

9,50,9,0.5,Fail

10,88,25,3,Pass

11,65,14,1.5,Fail

12,92,26,4,Pass

13,72,18,2,Pass

14,58,11,1,Fail

15,78,19,2.5,Pass

16,68,13,1,Fail

17,83,23,3,Pass

18,62,16,1.5,Fail

19,97,29,5,Pass

20,74,17,2,Fail

 

new_students.csv (3 new students whose result we want to predict)

Roll,Attendance,Internal,StudyHours

21,89,26,4

22,52,10,1

23,71,16,2

 

shopping.basket (10 shopping baskets, one basket per line; used in Practical 14)

Bread, Butter, Milk

Bread, Butter

Milk, Eggs

Bread, Butter, Eggs

Bread, Butter, Milk

Milk, Eggs

Bread, Butter

Bread, Milk

Milk, Eggs

Bread, Butter, Milk

 

PART B - DATA MINING (ORANGE DATA MINING)

Practical 11: Data Exploration

Aim: Load a dataset in Orange and understand it using tables, statistics and charts.

Theory: Data mining means finding useful patterns in large amounts of data. The usual steps are: collect data, clean it, select what matters, mine for patterns, evaluate, and use the result. Key words: an instance is a row (one student); a feature is a column (Attendance); the target (or class) is what we want to predict (Result). Features can be numeric (85) or categorical (Pass / Fail). Always explore data before mining it.

Procedure

1.    Open Orange. Drag the File widget onto the canvas and double-click it. Load students.csv.

2.    In the File widget, double-click the Role cell of Result and set it to target. Set Roll to meta (it is only an ID, not a useful feature). Click Apply.

3.    Drag a Data Table widget and link File to it. Open it and look at all 20 rows.

4.    Add Feature Statistics and link File to it. Read the mean, minimum and maximum of each column.

5.    Add Distributions, link File to it, choose Attendance, and set Split by = Result.

6.    Add Scatter Plot, link File to it, set X = Attendance, Y = Internal, and colour by Result.

Expected Result

•     The File widget reports 20 instances, 3 numeric features, 1 categorical target (Pass = 11, Fail = 9).

•     Feature Statistics: mean Attendance = 74.85 (min 50, max 97); mean Internal = 18.9; mean StudyHours = 2.4.

•     In the scatter plot, Pass students gather at the top-right (high attendance and high marks), Fail students at the bottom-left. This visible pattern is what the algorithms in the next practicals will learn.

Practice Questions: (a) Which feature separates Pass and Fail best? Check with a Box Plot widget. (b) Is there any missing data in the file?

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART B - DATA MINING (ORANGE DATA MINING)

Practical 12: Classification

Aim: Build classification models that predict Pass or Fail for new students.

Theory: Classification predicts a category using examples whose answers are already known (supervised learning). Common methods:

Method

Idea in simple words

Decision Tree

A flowchart of if-else questions (is Attendance above 72?)

Naive Bayes

Uses probabilities of each feature value for each class

kNN

Looks at the k most similar known students and takes a vote

 

Procedure

1.    Keep the File widget from Practical 11 (Result as target).

2.    Add a Tree widget (link File to it), then a Tree Viewer (link Tree to it). Read the tree from the top: each box is a question; each leaf is a prediction.

3.    Add Naive Bayes and kNN widgets, both linked from File.

4.    Add Test and Score. Link File to it (data) and link Tree, Naive Bayes and kNN to it (learners). Choose Cross validation with 5 folds.

5.    Predict new students: add a second File widget loading new_students.csv. Add a Predictions widget. Link the new File to Predictions (Data) and link Tree to Predictions (Predictors).

Expected Result

•     The tree splits mainly on Attendance and/or Internal marks.

•     Test and Score shows a table with CA (accuracy), F1, Precision and Recall for each model. With this small, well-separated dataset, expect accuracies roughly between 0.75 and 1.00. Your exact numbers may differ slightly, and that is normal.

•     Predictions: student 21 (89, 26, 4) should be Pass and student 22 (52, 10, 1) should be Fail. Student 23 (71, 16, 2) is a borderline case, so models may disagree. Discuss why.

Practice Questions: (a) Which model has the highest CA? (b) Why do we test on data the model has not seen?

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART B - DATA MINING (ORANGE DATA MINING)

Practical 13: Clustering

Aim: Group similar students automatically without using the Result column.

Theory: Clustering finds natural groups in data when no answer column is given (unsupervised learning). k-Means picks k centres and assigns each row to the nearest one. Hierarchical clustering repeatedly merges the two closest groups and draws a tree called a dendrogram. Features should be on a similar scale, so we normalise them.

Procedure

1.    In the File widget, set the role of Result to meta (so it is not used for grouping) and keep Roll as meta. Click Apply.

2.    Add k-Means and link File to it. Set the number of clusters from 2 to 5 and keep Normalize columns ticked. Read the Silhouette score for each k (higher means better separated groups). Fix k = 2 for the rest.

3.    Add Scatter Plot linked from k-Means and colour by Cluster.

4.    Add Data Table linked from k-Means and compare the Cluster column with the Result meta column.

5.    Hierarchical method: File → Distances (Euclidean, normalised) → Hierarchical Clustering (Ward linkage). Drag the cut line on the dendrogram to make 2 clusters.

Expected Result: k = 2 gives the clearest separation. One cluster holds the high-attendance, high-marks students (mostly Pass) and the other the low-attendance, low-marks students (mostly Fail). A few borderline students such as Roll 7, 13 and 20 may switch groups depending on the method. The dendrogram cut at 2 clusters gives a very similar grouping.

Practice Questions: (a) Try k = 3. What does the third group look like? (b) How is clustering different from classification?

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART B - DATA MINING (ORANGE DATA MINING)

Practical 14: Association Rules (Market Basket Analysis)

Aim: Find items that are often bought together.

Theory: An itemset is a set of items bought together. For a rule A → B (customers who buy A also buy B):

•     Support = transactions containing A and B / all transactions

•     Confidence = transactions containing A and B / transactions containing A

•     Lift = Confidence / Support of B. Lift above 1 means a real positive link; around 1 means almost no link.

Procedure

1.    Install the add-on if needed: Options > Add-ons > Associate, then restart Orange.

2.    Add File, load shopping.basket. (If Orange does not recognise it, make sure the extension is .basket, not .txt.) Each item becomes a column.

3.    Add Frequent Itemsets, link File to it, and set minimal support to 20%.

4.    Add Association Rules, link File to it, set minimal support 20% and minimal confidence 60%.

Expected Result (you can verify by hand)

Item counts out of 10 baskets: Bread 7, Milk 7, Butter 6, Eggs 4. Frequent itemsets at 20% include Bread + Butter (6 baskets, support 60%) and Bread + Butter + Milk (3 baskets, 30%). The rules you should see:

Rule

Support

Confidence

Lift

Butter → Bread

60%

100%

1.43

Bread → Butter

60%

85.7%

1.43

Butter, Milk → Bread

30%

100%

1.43

Bread, Milk → Butter

30%

75%

1.25

Eggs → Milk

30%

75%

1.07

 

Conclusion: Bread and Butter are strongly linked, so a shop can place them close together or sell them as a combo. Eggs → Milk has lift near 1, so that link is weak, even though its confidence looks high.

Practice Questions: (a) Calculate support and confidence of Milk → Eggs by hand. (b) Why is high confidence alone not enough to trust a rule?

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART B - DATA MINING (ORANGE DATA MINING)

Practical 15: Model Evaluation

Aim: Measure how good a model is and compare several models fairly.

Theory: A confusion matrix counts correct and wrong predictions. Taking Pass as the positive class:

 

Predicted Pass

Predicted Fail

Actually Pass

TP (true positive)

FN (false negative)

Actually Fail

FP (false positive)

TN (true negative)

 

•     Accuracy = (TP + TN) / total

•     Precision = TP / (TP + FP): of those predicted Pass, how many really passed

•     Recall = TP / (TP + FN): of all real Pass students, how many did we find

•     F1 = 2 x Precision x Recall / (Precision + Recall)

Worked example: TP = 10, FN = 1, FP = 2, TN = 7 gives Accuracy = 17/20 = 0.85, Precision = 10/12 = 0.83, Recall = 10/11 = 0.91, F1 = 0.87.

Overfitting happens when a model memorises the training data and fails on new data. To detect it, never judge a model only on the data it learned from. Use a train/test split or cross-validation.

Procedure

1.    Reload students.csv with Result as target and Roll as meta.

2.    Add Test and Score with learners Tree, kNN, Naive Bayes, Logistic Regression and Random Forest.

3.    First choose Cross validation (5 folds) and note CA and F1. Then choose Random sampling (train size 70%, repeat 10 times) and note them again.

4.    Add a Confusion Matrix widget linked from Test and Score. Pick one model at a time.

Expected Result: Test and Score lists AUC, CA, F1, Precision and Recall per model. The two evaluation methods give slightly different numbers, which shows why results depend on how we test. Under cross-validation, every student is predicted exactly once, so the confusion matrix row totals are 11 (Pass) and 9 (Fail).

Practice Questions: (a) Why can 95% accuracy be misleading if 95% of rows belong to one class? (b) Which would you prefer for a dropout-warning system: high precision or high recall, and why?

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART C - DATA WAREHOUSING (MYSQL WORKBENCH)

Practical 16: OLTP vs OLAP

Aim: Understand the difference between operational (OLTP) and analytical (OLAP) systems, and create the source database.

Theory: A data warehouse is a central store of historical data from many sources, built for analysis and reporting. It is subject-oriented (organised by topic such as sales), integrated, time-variant (keeps history) and non-volatile (data is added, rarely changed).

Feature

OLTP

OLAP

Full form

Online Transaction Processing

Online Analytical Processing

Purpose

Run daily business

Analyse the business

Typical task

Insert a new order

Total sales by month

Data

Current, detailed

Historical, summarised

Design

Many small, normalised tables

Few tables in star schema

Users

Clerks, customers

Managers, analysts

Query

Short, touches few rows

Long, scans many rows

Example

ATM, ticket booking

Sales dashboard

 

Procedure: Create the OLTP source database.

CREATE DATABASE sales_oltp;

USE sales_oltp;

 

CREATE TABLE customers (customer_id INT PRIMARY KEY,

  customer_name VARCHAR(50), city VARCHAR(30));

CREATE TABLE products  (product_id INT PRIMARY KEY,

  product_name VARCHAR(50), category VARCHAR(30), price DECIMAL(8,2));

CREATE TABLE orders    (order_id INT PRIMARY KEY, customer_id INT,

  product_id INT, order_date DATE, quantity INT);

 

INSERT INTO customers VALUES (1,'Aman','Delhi'),(2,'Riya','Jaipur'),

  (3,'Karan','Delhi'),(4,'Neha','Agra');

INSERT INTO products VALUES (1,'Pen','Stationery',10.00),

  (2,'Notebook','Stationery',50.00),(3,'Mouse','Electronics',400.00),

  (4,'Keyboard','Electronics',700.00);

INSERT INTO orders VALUES

(1,1,1,'2026-01-05',10),(2,2,2,'2026-01-12',4),(3,3,3,'2026-01-20',1),

(4,1,4,'2026-02-03',1),(5,4,1,'2026-02-10',20),(6,2,3,'2026-02-15',2),

(7,3,2,'2026-02-25',5),(8,4,4,'2026-03-02',1),(9,1,2,'2026-03-10',6),

(10,2,1,'2026-03-18',15),(11,3,4,'2026-03-22',2),(12,4,3,'2026-03-28',1);

 

-- A typical OLTP operation (small, fast, one row). We undo it afterwards.

START TRANSACTION;

INSERT INTO orders VALUES (13,1,3,'2026-03-30',1);

SELECT * FROM orders WHERE order_id = 13;

ROLLBACK;

 

-- A typical OLAP question (reads all rows, needs joins and totals)

SELECT p.category, SUM(o.quantity * p.price) AS revenue

FROM orders o JOIN products p ON o.product_id = p.product_id

GROUP BY p.category;

 

Expected Result: The OLTP insert shows one new row, and ROLLBACK removes it. The OLAP query returns Electronics 4400.00 and Stationery 1200.00. Notice that even this simple summary needs a join across tables. With millions of rows, such queries slow down the live system, which is why warehouses exist.

Practice Questions: Classify as OLTP or OLAP: (a) withdrawing cash, (b) yearly sales trend by region, (c) booking a train seat, (d) finding the best-selling product of the quarter.

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART C - DATA WAREHOUSING (MYSQL WORKBENCH)

Practical 17: Star Schema Design

Aim: Design a star schema on paper for the sales data.

Theory

•     A fact table stores the numbers we measure (quantity, sales amount) plus keys pointing to dimensions. It has many rows.

•     A dimension table stores descriptive details (customer name, city, product category, month). It has fewer rows and is used to filter and group.

•     In a star schema, one fact table sits in the centre and dimension tables surround it, like a star. A snowflake schema further splits dimensions into sub-tables.

•     The grain is what one fact row represents. Ours: one row per order.

•     A surrogate key is a simple generated number (like customer_key) used instead of the business ID.

Procedure: Study the design and copy it into your record.

                  dim_date

                     |

  dim_customer --- fact_sales --- dim_product

 

Table

Type

Columns

fact_sales

Fact

sales_key (PK), date_key (FK), customer_key (FK), product_key (FK), quantity, sales_amount

dim_date

Dimension

date_key (PK), full_date, day_no, month_no, month_name, quarter_no, year_no

dim_customer

Dimension

customer_key (PK), customer_id, customer_name, city

dim_product

Dimension

product_key (PK), product_id, product_name, category, unit_price

 

Expected Result: A neat diagram with 1 fact table in the middle, 3 dimension tables around it, and arrows (foreign keys) from the fact table to each dimension. Mark the measures (quantity, sales_amount).

Practice Questions: (a) Why is sales_amount in the fact table and city in a dimension? (b) Draw a star schema for a college attendance system (what is the grain? what are the dimensions?).

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART C - DATA WAREHOUSING (MYSQL WORKBENCH)

Practical 18: Creating Fact and Dimension Tables

Aim: Build the star schema in MySQL.

Procedure

CREATE DATABASE sales_dw;

USE sales_dw;

 

CREATE TABLE dim_date (

  date_key INT PRIMARY KEY, full_date DATE, day_no TINYINT,

  month_no TINYINT, month_name VARCHAR(10), quarter_no TINYINT,

  year_no SMALLINT

);

CREATE TABLE dim_customer (

  customer_key INT PRIMARY KEY AUTO_INCREMENT, customer_id INT,

  customer_name VARCHAR(50), city VARCHAR(30)

);

CREATE TABLE dim_product (

  product_key INT PRIMARY KEY AUTO_INCREMENT, product_id INT,

  product_name VARCHAR(50), category VARCHAR(30), unit_price DECIMAL(8,2)

);

CREATE TABLE fact_sales (

  sales_key INT PRIMARY KEY AUTO_INCREMENT,

  date_key INT, customer_key INT, product_key INT,

  quantity INT, sales_amount DECIMAL(10,2),

  FOREIGN KEY (date_key)     REFERENCES dim_date(date_key),

  FOREIGN KEY (customer_key) REFERENCES dim_customer(customer_key),

  FOREIGN KEY (product_key)  REFERENCES dim_product(product_key)

);

 

-- Fill the date dimension for January to March 2026

INSERT INTO dim_date

WITH RECURSIVE d AS (

  SELECT DATE('2026-01-01') AS dt

  UNION ALL

  SELECT dt + INTERVAL 1 DAY FROM d WHERE dt < '2026-03-31'

)

SELECT YEAR(dt)*10000 + MONTH(dt)*100 + DAY(dt), dt, DAY(dt),

       MONTH(dt), MONTHNAME(dt), QUARTER(dt), YEAR(dt)

FROM d;

 

SHOW TABLES;

SELECT COUNT(*) FROM dim_date;

SELECT * FROM dim_date LIMIT 3;

 

Expected Result: SHOW TABLES lists four tables. dim_date has 90 rows (31 + 28 + 31 days). The first row is date_key 20260101, January, quarter 1. Optionally use Database > Reverse Engineer in Workbench to see the star diagram drawn automatically.

Note: If the date insert gives a syntax error, your MySQL version is older than 8.0. Install MySQL 8.0 or later.

Practice Questions: (a) Why do we keep a separate date dimension? (b) Add a column is_weekend to dim_date and fill it using DAYOFWEEK(full_date) IN (1,7).

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART C - DATA WAREHOUSING (MYSQL WORKBENCH)

Practical 19: ETL (Extract, Transform, Load)

Aim: Move data from the OLTP database into the warehouse.

Theory: ETL has three steps. Extract: read data from source systems. Transform: clean and reshape it (trim spaces, fix case, calculate new values, replace surrogate keys). Load: write it into the warehouse tables. Always load dimensions first, then the fact table, because the fact table needs their keys.

Procedure

USE sales_dw;

 

-- EXTRACT: look at the source

SELECT * FROM sales_oltp.orders;

 

-- TRANSFORM + LOAD dimensions (clean text while loading)

INSERT INTO dim_customer (customer_id, customer_name, city)

SELECT customer_id, TRIM(customer_name), UPPER(TRIM(city))

FROM sales_oltp.customers;

 

INSERT INTO dim_product (product_id, product_name, category, unit_price)

SELECT product_id, product_name, category, price

FROM sales_oltp.products;

 

-- TRANSFORM + LOAD fact (look up surrogate keys, calculate amount)

INSERT INTO fact_sales

  (date_key, customer_key, product_key, quantity, sales_amount)

SELECT DATE_FORMAT(o.order_date, '%Y%m%d') + 0,

       dc.customer_key, dp.product_key,

       o.quantity, o.quantity * p.price

FROM sales_oltp.orders o

JOIN sales_oltp.products p ON o.product_id  = p.product_id

JOIN dim_customer dc       ON dc.customer_id = o.customer_id

JOIN dim_product  dp       ON dp.product_id  = o.product_id;

 

-- VALIDATE the load

SELECT COUNT(*) FROM dim_customer;            -- 4

SELECT COUNT(*) FROM dim_product;             -- 4

SELECT COUNT(*) FROM fact_sales;              -- 12

SELECT SUM(sales_amount) FROM fact_sales;     -- 5600.00

SELECT COUNT(*) FROM fact_sales

WHERE date_key NOT IN (SELECT date_key FROM dim_date);   -- 0

 

Expected Result: Row counts are 4, 4 and 12. The warehouse total 5600.00 equals the total of quantity x price in the source, which proves nothing was lost. The last query returns 0 (every sale has a valid date). City names now appear in capitals (DELHI, JAIPUR, AGRA) because of the transform step.

If you must run the load twice: first clear the tables in this order, otherwise rows will be duplicated: DELETE FROM fact_sales; DELETE FROM dim_customer; DELETE FROM dim_product;

Practice Questions: (a) Why must the fact table be loaded last? (b) Name two other transformations a real ETL job might do.

Date: ____________     Remark: ________________     Faculty Sign: ____________

PART C - DATA WAREHOUSING (MYSQL WORKBENCH)

Practical 20: Simple Analytical Reports

Aim: Produce management reports from the warehouse using joins and grouping.

Theory: Analysts look at data from different angles. Roll-up moves to a higher summary level (month to quarter). Drill-down goes to more detail. Slice filters one dimension value (only Electronics). Dice filters on several dimensions together. Star schema queries follow one pattern: join the fact table to the needed dimensions, then GROUP BY.

Procedure and Expected Results

USE sales_dw;

 

-- Report 1: Sales by category

SELECT p.category, SUM(f.sales_amount) AS revenue

FROM fact_sales f JOIN dim_product p ON f.product_key = p.product_key

GROUP BY p.category;

-- Electronics 4400, Stationery 1200

 

-- Report 2: Sales by month

SELECT d.month_name, SUM(f.sales_amount) AS revenue

FROM fact_sales f JOIN dim_date d ON f.date_key = d.date_key

GROUP BY d.month_no, d.month_name ORDER BY d.month_no;

-- January 700, February 1950, March 2950

 

-- Report 3: Sales by city

SELECT c.city, SUM(f.sales_amount) AS revenue

FROM fact_sales f JOIN dim_customer c ON f.customer_key = c.customer_key

GROUP BY c.city ORDER BY revenue DESC;

-- DELHI 3150, AGRA 1300, JAIPUR 1150

 

-- Report 4: Top products

SELECT p.product_name, SUM(f.quantity) AS units,

       SUM(f.sales_amount) AS revenue

FROM fact_sales f JOIN dim_product p ON f.product_key = p.product_key

GROUP BY p.product_name ORDER BY revenue DESC;

-- Keyboard 4 / 2800, Mouse 4 / 1600, Notebook 15 / 750, Pen 45 / 450

 

-- Report 5: Slice - Electronics only, by city

SELECT c.city, SUM(f.sales_amount) AS revenue

FROM fact_sales f

JOIN dim_customer c ON f.customer_key = c.customer_key

JOIN dim_product  p ON f.product_key  = p.product_key

WHERE p.category = 'Electronics'

GROUP BY c.city;

-- DELHI 2500, AGRA 1100, JAIPUR 800

 

-- Report 6: Category by month cross-table

SELECT p.category,

 SUM(CASE WHEN d.month_no = 1 THEN f.sales_amount ELSE 0 END) AS jan,

 SUM(CASE WHEN d.month_no = 2 THEN f.sales_amount ELSE 0 END) AS feb,

 SUM(CASE WHEN d.month_no = 3 THEN f.sales_amount ELSE 0 END) AS mar

FROM fact_sales f

JOIN dim_product p ON f.product_key = p.product_key

JOIN dim_date    d ON f.date_key    = d.date_key

GROUP BY p.category;

-- Electronics 400 / 1500 / 2500, Stationery 300 / 450 / 450

 

-- Report 7: Roll-up with subtotals and grand total

SELECT p.category, d.month_no, SUM(f.sales_amount) AS revenue

FROM fact_sales f

JOIN dim_product p ON f.product_key = p.product_key

JOIN dim_date    d ON f.date_key    = d.date_key

GROUP BY p.category, d.month_no WITH ROLLUP;

-- NULL month_no = category subtotal (4400, 1200);

-- NULL category and NULL month_no = grand total (5600)

 

-- Save a report as a reusable view

CREATE VIEW v_monthly_sales AS

SELECT d.month_no, d.month_name, SUM(f.sales_amount) AS revenue

FROM fact_sales f JOIN dim_date d ON f.date_key = d.date_key

GROUP BY d.month_no, d.month_name;

SELECT * FROM v_monthly_sales ORDER BY month_no;

 

Reading the results (write this in your record): Electronics brings about 79% of revenue (4400 of 5600). Sales grew every month, with March the strongest. Keyboard is the top product by revenue, while Pen sells the most units but earns the least. Delhi is the best city.

Optional bridge to Part B: In the Result Grid, use Export to save a report as CSV, then open it in Orange with a File widget and draw a bar chart.

Practice Questions: (a) Write a report showing total sales per customer (expected: Karan 2050, Neha 1300, Riya 1150, Aman 1100). (b) Explain roll-up and drill-down using month and quarter.

Date: ____________     Remark: ________________     Faculty Sign: ____________

Appendix A: Instructions to Students

Software required

•     MySQL Community Server 8.0 or later, and MySQL Workbench (free from mysql.com)

•     Orange Data Mining 3.x (free from orangedatamining.com), with the Associate add-on installed (Options > Add-ons)

•     Any text editor (Notepad) to save CSV files

Learning path: Part A (Practicals 1 to 10) uses MySQL Workbench for DBMS and SQL. Part B (Practicals 11 to 15) uses Orange for Data Mining. Part C (Practicals 16 to 20) returns to MySQL Workbench for Data Warehousing.

Golden rules

1.    Type the commands yourself; do not only copy-paste. Typing builds memory.

2.    Every SQL statement ends with a semicolon ;.

3.    In Workbench, Ctrl + Enter runs the current statement; Ctrl + Shift + Enter runs everything in the editor.

4.    Do the practicals in order. Later practicals use the tables you create earlier.

5.    SQL keywords are not case-sensitive, but writing them in CAPITALS makes code easier to read.

6.    Read the error message in the Action Output panel. It usually tells you exactly what is wrong.

Appendix B: Viva Question Bank

1.    Define data, information, database and DBMS.

2.    Name four advantages of DBMS over a file system.

3.    Differentiate DDL, DML, DQL, DCL and TCL with commands.

4.    What is the difference between DELETE, TRUNCATE and DROP?

5.    What is a primary key? How is it different from a foreign key?

6.    Explain INNER JOIN and LEFT JOIN with an example.

7.    Difference between WHERE and HAVING.

8.    What are ACID properties?

9.    What is data mining? List its main steps.

10.  Difference between classification and clustering.

11.  Define support, confidence and lift.

12.  What is overfitting? How does cross-validation help?

13.  Differentiate OLTP and OLAP.

14.  What are fact tables and dimension tables?

15.  What is ETL? Why are dimensions loaded before facts?

Appendix C: Common Problems and Fixes

Problem

Likely cause

Fix

Error 1046: No database selected

Forgot USE

Run USE college_db; (or the correct database)

Error 1175 on UPDATE / DELETE

Safe Update mode

Use the key column in WHERE, or disable safe mode in Preferences

Error 1451 / 1452

Foreign key blocks the change

Delete or insert the parent row first

Error 1064: syntax error

Missing comma, quote or semicolon

Re-read the line the error points to

Error 1062: Duplicate entry

Primary key or UNIQUE value repeated

Use a different value

CHECK constraint seems ignored

MySQL older than 8.0.16

Upgrade MySQL

Orange shows no association rules

Support or confidence too high, or wrong file format

Lower the thresholds; check the file is .basket

Orange File widget shows wrong types

Roles or types not set

Double-click the Type / Role cell and correct it

 

at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Dataware housing and data mining complete manual file(DWDM)

  XXX  UNIVERSITY Laboratory Manual   DATA WAREHOUSING AND DATA MINING   Practical Manual for Diploma S...