PYQ

Unit 1​

1. What do you mean by Data Warehouse? Key Features, Needs, Goals of it.​

A Data Warehouse is a centralized repository that stores large amounts of data collected from different sources of an organization.
It is used for analysis, reporting, and decision-making rather than daily operations.

Key Features:

• Subject-Oriented: Organized by subject (e.g., sales, customers) rather than by department or process.
• Integrated: Combines data from multiple sources into a consistent format.
• Time-Variant: Stores historical data to identify trends over time.
• Non-Volatile: Data is stable and not frequently changed. once entered, it is read-only.

Goals/Needs:

• Integrate Data from Multiple Sources: Combines data from different databases and systems into a single repository.
• Provide Historical Information: Stores past data for trend analysis, forecasting, and strategic planning.
• Support Decision Making: Provides accurate and historical data to help managers make better decisions.
• Enable Fast Query and Analysis: Enables Fast queries for analysis and reporting.

2. Define OLAP.​

OLAP (Online Analytical Processing) is a technology used to analyze large volumes of data from different perspectives.
It helps managers and analysts perform complex queries, reporting, trend analysis, and decision-making.

Example: Analyzing yearly sales data to identify the best-performing products.

3. Differences b/t Operational Database Systems and a Data Warehouse.​

• Also known as OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing).

Examples

• OLTP: A bank's transaction system records deposits, withdrawals, and balance updates every day.

• OLAP: A bank manager analyzes customer transaction data from the last 5 years to identify trends and make business decisions.

5. Briefly explain the Data Warehouse models:​

Enterprise Data Warehouse (EDW)​

A centralized warehouse for the entire organization that stores data from all departments. Used for overall business analysis.

E.g, A company stores sales, HR, and finance data in one system.

Data Mart:​

A small part of a data warehouse focused on one specific department. It is easier and faster to use.

E.g, Only sales department data stored for analysis.

Virtual Warehouse:​

A view of data from operational databases. Data is not physically stored separately. Quick to create but depends on original data sources.

Features:

• Requires less storage space.
• Lower implementation cost.
• Faster to set up.
• Data is retrieved from operational systems in real time.

E.g, Data shown directly from existing databases without full physical storage.

4. Draw and explain the Three-Tier Data Warehousing Architecture with a neat diagram.​

┌─────────────────────┐
│      Top Tier       │
│  Query, Reporting,  │
│  Analysis & OLAP    │
└─────────┬───────────┘
│
┌─────────▼───────────┐
│    Middle Tier      │
│     OLAP Server     │
│ (ROLAP/MOLAP/HOLAP) │
└─────────┬───────────┘
│
┌─────────▼───────────┐
│    Bottom Tier      │
│   Data Warehouse    │
│  + Data Marts       │
└─────────┬───────────┘
│
┌──────────────────┼──────────────────┐
│                  │                  │
┌──────▼──────┐   ┌──────▼──────┐   ┌──────▼──────┐
│ Operational │   │ External    │   │ Legacy      │
│ Databases   │   │ Sources     │   │ Systems     │
└─────────────┘   └─────────────┘   └─────────────┘

1. Bottom Tier – Data Warehouse Database​

It is the foundation of the architecture. Stores integrated and historical data.

• Data is collected from operational databases, external sources, and legacy systems.
• ETL (Extract, Transform, Load) processes clean and load the data into the warehouse.

Functions:

• Data extraction
• Data cleaning
• Data transformation
• Data storage

2. Middle Tier – OLAP Server​

Acts as a bridge between the data warehouse and users.

• Provides fast access to data for analysis.
• Supports multidimensional analysis.

Types of OLAP Servers:

• ROLAP (Relational OLAP)
• MOLAP (Multidimensional OLAP)
• HOLAP (Hybrid OLAP)

Functions:

• Query processing
• Data aggregation
• Analytical operations

3. Top Tier – Front-End Tools​

Used directly by end users, managers, and analysts.

• Provides tools for querying, reporting, data mining, and visualization.

Functions:

• Report generation
• Dashboard creation
• Data analysis
• Decision support

Unit 2​

1. What do you mean by Data Mart? State its advantages and disadvantages.​

A small part of a data warehouse focused on one specific department.

It contains only data required by specific groups of users, making it easier and faster to use.

E.g, Only sales department data stored for analysis.

Advantages of Data Mart

• Faster Access to Data – Smaller size allows quicker queries and reports.
• Lower Cost – Less expensive to implement and maintain than a full Data Warehouse.
• Department-Specific – Provides relevant data for a particular department.
• Easy Implementation – Can be developed in a shorter time.

Disadvantages of Data Mart

• Limited Scope – Contains data for only one department.
• Data Duplication – Same data may be stored in multiple data marts.
• Integration Issues – Difficult to combine data from different data marts.
• Inconsistent Data – Different departments may maintain data differently.

2. Briefly explain the following schemas with examples: (i) Snowflake Schema (ii) Star Schema (iii) Fact Constellation Schema.​

i. Star Schema

A star schema is a data warehouse design where one central fact table is shared by multiple dimension tables, forming a shape like a star.

Structure: A central Fact Table containing numerical data (e.g., sales, profit) surrounded by Dimension Tables containing descriptive data (e.g., customer, product, time).

Use Case: Best for simplicity and fast queries in small/medium systems.

ii. Snowflake Schema

A Snowflake Schema is an extension of the Star Schema where dimension tables are further divided into sub-dimension tables (normalized).

iii. Fact Constellation (Galaxy) Schema

A galaxy schema is a data warehouse design where common dimension tables are shared by multiple fact tables. It looks like a collection of star schemas connected together.

When is it used?

• When a system has multiple related business processes.
• When different facts need to be analyzed together.
• In large and complex organizations (e.g., analyzing sales + shipments + inventory together).

3. Differentiate between Star Schema and Snowflake Schema with diagrams.​

4. Explain ROLAP and MOLAP. Differentiate between them.​

1. ROLAP (Relational Online Analytical Processing)

ROLAP stores data in relational databases (tables with rows and columns) and performs analysis using SQL queries.

Features:

• Uses relational database systems.
• Can handle very large volumes of data.
• Data is stored in tables.
• Queries are executed using SQL.

Example: Sales data stored in relational tables and analyzed through SQL queries.

2. MOLAP (Multidimensional Online Analytical Processing)

MOLAP stores data in multidimensional cubes and provides fast analytical processing.

Features:

• Uses multidimensional data cubes.
• Faster query performance.
• Data is pre-calculated and aggregated.
• Suitable for complex analysis.

Example: A sales cube showing sales by product, region, and time.

Differences between ROLAP and MOLAP​

Unit 3​

1. What is Data Mining? various applications of it​

Data Mining is the process of extracting useful patterns, relationships, and hidden information from large amounts of data using statistical, machine learning, and database techniques.

It helps organizations make better decisions by converting raw data into meaningful information.

Example:

An online shopping company analyzes customer purchase data to recommend products.

Applications​

1. Market Basket Analysis

Identifies which products that are frequently purchased together.
Helps in product placement and promotions.

Example: Customers who buy bread often buy butter.

2. Healthcare

Helps in disease diagnosis and treatment planning.
Predicts patient health risks.

Example: Predicting the risk of diabetes from patient records.

3. Retail and E-Commerce

Analyzes customer buying behavior. Provides personalized product recommendations.

Example: Amazon recommending products based on previous purchases.

4. Education

Analyzes student performance. Identifies students who need additional support.

Example: Predicting students at risk of failing.

5. Manufacturing

Improves product quality and production efficiency. Predicts equipment failures.

Example: Detecting machine maintenance needs before breakdowns.

6. Fraud Detection

Detects suspicious activities in banking, insurance, and online transactions.

Example: Finding fake insurance claims.

2. What is KDD? Steps of KDD. How is it different from the Data Mining?​

KDD or Knowledge Discovery in Databases refers to the complete process of finding knowledge from large datasets.

Steps in the KDD Process​

1. Data Selection: Relevant data is selected from various sources.

2. Data Cleaning: Errors, duplicate records, and missing values are removed.

3. Data Transformation: Data is converted into a format suitable for mining.

4. Data Mining: Various Techniques are applied to discover patterns and relationships.

5. Pattern Evaluation: Interesting and useful patterns are identified and evaluated.

6. Knowledge Presentation: The discovered knowledge is presented to users for decision-making.

Differences between KDD and Data Mining​

3. Differentiate between Data Warehousing and Data Mining.​

4. What is Web Mining? It's applications. Compare Data Mining and Web Mining.​

Web Mining is the process of extracting useful information, patterns, and knowledge from web data such as web pages, hyperlinks, and user browsing behavior.

It applies data mining techniques to data available on the World Wide Web.

Example: Analyzing website visitor behavior to improve user experience.

Applications of Web Mining​

1. E-Commerce: Recommends products based on user browsing and purchase history.
2. Search Engines: Improves search results by analyzing web content and links.
3. Digital Marketing: Studies customer preferences and online behavior.
4. Website Personalization: Provides customized content to users.
5. Fraud Detection: Detects suspicious online activities and transactions.

Data Mining vs Web Mining​

5. Explain each term​

Web Content Mining​

Web Content Mining is the process of extracting useful information from the contents of web pages such as text, images, audio, video, and documents.

Example: A search engine analyzes the content of web pages to provide relevant search results.

Web Structure Mining​

Web Structure Mining is the process of analyzing the structure of hyperlinks to discover how web pages are connected and to determine their importance.

It studies how web pages are connected to each other through links.

Example: Search engines use link analysis to determine the importance of web pages.

Web Usage Mining​

Web Usage Mining is the process of analyzing user behavior from web logs, browser history, cookies, and click data.

It helps understand how users interact with websites.

Example: An e-commerce website tracks pages visited by users to recommend products.

Unit 4​

1. State the Apriori Property. What are the types of knowledge discovered during Data Mining?​

Apriori Property states that If an itemset is frequent, then all of its non-empty subsets must also be frequent.

Explanation:

• If a set of items appears frequently in transactions, then every subset of that set must also appear frequently.
• This property helps reduce the number of candidate itemsets and improves mining efficiency.

Example:

If {Bread, Butter, Milk} is a frequent itemset, then the following subsets must also be frequent:

{Bread, Butter}
{Bread, Milk}
{Butter, Milk}
{Bread}
{Butter}
{Milk}

Types of Knowledge Discovered during Data Mining​

1. Association

Finding things that appear together. Classic market basket analysis.

Example: People who buy chips also buy soda.

2. Classification

Assigning data into predefined classes.

The categories are already decided; we're learning how to sort new data into them.

Example: Is this email spam or not spam?

3. Clustering

Grouping similar things together, but we don't define the groups in advance.
The algorithm figures out the natural groups on its own.

Example: Grouping customers by shopping habits without telling it what groups to create.

4. Prediction/Regression

Predicting values or trends.

Example: Predicting future sales of a product.

5. Sequential patterns

Finds patterns that occur in a sequence. The timing and sequence is part of the pattern.

Example: "People who buy a phone tend to buy a case within a week."

6. Anomaly detection

Spotting the weird stuff that doesn't fit. Used in fraud detection, network security, etc.

Example: Detecting fraudulent credit card transactions.

2. Define the terms Support and Confidence with examples.​

Support

Support measures how frequently an itemset appears in the transaction database.

Confidence

Confidence measures how often item B appears in transactions that already contain item A.

Example: Calculating Support and Confidence (Bread → Milk)​

Transaction Database

Total Transactions = 5

Rule: Bread → Milk

Step 1: Calculate Support​

Transactions containing Bread and Milk:

• T1
• T3
• T5

Count = 3

Step 2: Calculate Confidence​

Transactions containing Bread:

• T1
• T2
• T3
• T5

Count = 4

Result​

• Support (Bread → Milk) = 60%
• Confidence (Bread → Milk) = 75%

Unit 5​

1. What is Clustering? Diff Types of Clustering Methods.​

Clustering is the unsupervised learning technique that groups similar data objects into clusters so that objects within the same cluster are more similar to each other than those in different clusters.

Divides data into a predefined number of clusters.

Example: K-Means Clustering.

Creates a hierarchy of clusters in the form of a tree (dendrogram).

Can be Agglomerative (bottom-up) or Divisive (top-down).

Example: Hierarchical Agglomerative Clustering (HAC).

Forms clusters based on dense regions of data separated by sparse regions.

Can identify clusters of arbitrary shapes and detect outliers.

Example: DBSCAN (used to detect geographical hotspots from GPS location data).

Divides the data space into a finite number of cells (grids) and forms clusters from them.

Example: STING (Used in Geographic Information Systems for analyzing spatial data).

Assumes that data is generated from a statistical model and clusters data accordingly.

Example: Gaussian Mixture Model (GMM) (used for image segmentation and pattern recognition).

2. What are the features of a good cluster? Explain Hierarchical Clustering method.​

A good cluster should have the following characteristics:

1. High Intra-cluster Similarity: Objects within the same cluster should be very similar to each other.

2. Low Inter-cluster Similarity: Objects belonging to different clusters should be as different as possible.

3. Compactness: Data points in a cluster should be closely packed around the cluster center.

4. Meaningfulness: Clusters should represent useful groups that make sense in the application domain.

5. Stability: Small changes in data should not drastically change the clustering result.

6. Scalability: The clustering method should be able to handle large datasets efficiently while maintaining cluster quality.

Creates a hierarchy of clusters in the form of a tree (dendrogram).

Can be Agglomerative (bottom-up) or Divisive (top-down).

Example: Hierarchical Agglomerative Clustering (HAC).

3. Differentiate between K-Means Clustering and K-Medoid Clustering algorithms.​

4. Define and explain Decision Tree with an example. State its advantages and disadvantages.​

A Decision Tree is a supervised machine learning algorithm used for classification and regression tasks. It represents decisions and their possible outcomes in the form of a tree-like structure consisting of root nodes, internal nodes, branches, and leaf nodes.

Root Node
|
Decision
/    \
Yes      No
|         |
Leaf Node  Leaf Node

Example:

Outlook
/    |     \
Sunny Overcast Rain
/          \      \
Humidity       Play   Wind
/    \               /   \
High Normal         Strong Weak
|      |             |      |
Don't   Play        Don't    Play
Play                Play

• If Outlook = Overcast → Play.
• If Outlook = Sunny and Humidity = High → Don't Play.
• If Outlook = Rain and Wind = Weak → Play.

Advantages of Decision Tree

• Easy to understand and interpret.
• Can handle both numerical and categorical data.
• Requires little data preparation.
• Useful for classification and prediction problems.

Disadvantages of Decision Tree

• Can become complex for large datasets.
• Prone to overfitting.
• Small changes in data may produce different trees.

5. What is Naïve Bayesian Classification? How does it differ from Bayesian Classification?​

Naïve Bayesian Classification is a supervised machine learning algorithm based on Bayes' Theorem.
It is mainly used for classification problems such as spam detection, document classification.

It is called "Naïve" because it assumes that all features are independent of each other.

Bayes' Theorem

Where:

• $P(A|B)$ = Probability of A given B
• $P(B|A)$ = Probability of B given A
• $P(A)$ = Prior probability of A
• $P(B)$ = Probability of B

Features:

• Simple and fast classification algorithm.
• Assumes independence among attributes.
• Works well with large datasets.
• Widely used in spam filtering and text classification.

Differences:

6. Differentiate between Supervised Machine Learning and Unsupervised Machine Learning. How do you detect outliers from clusters?​

How to detect outliers from clusters?​

Outliers are data points that are significantly different from other points in a cluster.

Methods to Detect Outliers:

• Distance-Based Method: Objects located far away from cluster centers are considered outliers.
• Density-Based Method: Points in low-density regions compared to neighboring points are treated as outliers.
• Cluster-Based Method: Very small clusters or isolated points may indicate outliers.