DMW Assignment – 1
Question 1
A separate Data Warehouse is required because operational databases (OLTP systems) are designed
for daily transactions and fast updates, while analytical queries require large historical datasets and
complex aggregations. A data warehouse stores integrated historical data from multiple sources and
supports decision making and business analysis.
In the three-tier data warehouse architecture, OLAP servers are placed in the middle tier.
OLAP servers are useful because they allow multidimensional analysis of data and support
operations such as roll■up, drill■down, slice, dice and pivot. These operations help analysts
explore large datasets quickly.
Difference between OLAP and OLTP:
OLAP systems are used for analysis, contain historical data, handle complex queries and are mostly
read■only.
OLTP systems are used for transactions, contain current data, support many users and involve
frequent insert, update and delete operations.
Question 2
Example: An e■commerce company uses data mining to analyze customer behavior and
purchasing patterns.
Data mining is crucial because it helps the business recommend products, understand customer
preferences and improve marketing strategies.
Required data mining functionalities include association rule mining to find products that are
frequently bought together, classification to categorize customers, clustering to group similar
customers and prediction to forecast future purchases.
These patterns cannot be effectively generated by simple SQL queries or basic statistical analysis
because those methods mainly summarize data rather than discover hidden relationships.
Data Cleaning:
Data cleaning is the process of detecting and correcting errors, missing values and inconsistencies
in data.
Common data cleaning methods include handling missing values, smoothing noisy data, identifying
and removing outliers and resolving inconsistencies between datasets.
Question 3
KDD (Knowledge Discovery in Databases) is the complete process of discovering useful
knowledge from large datasets.
Main steps in the KDD process:
1. Data Cleaning – removing noise and inconsistent data
2. Data Integration – combining data from different sources
3. Data Selection – selecting relevant data for analysis
4. Data Transformation – converting data into suitable format
5. Data Mining – applying algorithms to extract patterns
6. Pattern Evaluation – identifying meaningful patterns
7. Knowledge Presentation – presenting knowledge using visualization
Example: A supermarket analyzes transaction data and discovers that customers who buy bread
often buy butter as well. This knowledge can be used for product placement and marketing
strategies.
Question 4
(a) Snowflake Schema:
The fact table contains the measures count and avg_grade. The dimension tables include Student,
Course, Semester and Instructor. Each dimension can be further normalized into additional tables
such as department, program, year and so on.
(b) OLAP Operations:
First perform a slice operation to select courses belonging to the CS category. Then roll■up the
semester dimension to year if needed and aggregate results by student. This produces the average
grade of CS courses for each student.
(c) If each dimension has five hierarchy levels including ALL, then the total number of cuboids is:
5 × 5 × 5 × 5 = 625 cuboids.
Question 5
Major challenges in data mining include poor data quality, missing or noisy data, integrating data
from multiple sources, handling very large datasets, privacy and security concerns, high
dimensional data and difficulty in interpreting discovered patterns.
Question 6
Data mining primitives define the basic components used to specify a data mining task.
These components include the task■relevant data, the type of knowledge to be mined such as
association rules or classification, background knowledge like concept hierarchies, interestingness
measures such as support and confidence and the way results will be presented.
Example: In a supermarket database we may search for association rules with minimum support of
30 percent and minimum confidence of 70 percent to discover relationships between purchased
items.
Question 7
Example: Fraud detection in banking systems.
Banks analyze customer transaction data to detect unusual patterns that may indicate fraudulent
activities.
Data mining functionalities required include classification to distinguish fraudulent transactions,
outlier detection to find abnormal behavior and sequential pattern analysis to detect suspicious
transaction sequences.
These patterns cannot be reliably discovered using only traditional queries or basic statistical
methods because fraud patterns are often hidden and complex.
Question 8
Given Data:
12, 15, 18, 19, 21, 23, 25, 26, 28, 30, 33, 35, 36, 38, 42
Equal Width Binning (4 bins):
Range = 42 − 12 = 30
Bin width = 30 / 4 = 7.5
Bins:
Bin 1: 12, 15, 18
Bin 2: 19, 21, 23, 25
Bin 3: 26, 28, 30, 33
Bin 4: 35, 36, 38, 42
Equal Frequency Binning (3 bins):
Bin 1: 12, 15, 18, 19, 21
Bin 2: 23, 25, 26, 28, 30
Bin 3: 33, 35, 36, 38, 42
Smoothing by Mean:
For the first bin the mean is (12+15+18+19+21)/5 = 17. All values are replaced by 17.
Smoothing by Boundary:
Each value in a bin is replaced with the closest boundary value of that bin.
Question 9
Star Schema Design:
Fact Table: Sales_Fact
Attributes: product_id, customer_id, store_id, time_id, total_sales
Dimension Tables:
Product Dimension:
product_id, product_name, category, brand
Customer Dimension:
customer_id, customer_name, gender, age, city
Store Dimension:
store_id, store_name, location, region
Time Dimension:
time_id, day, month, quarter, year