Data Warehousing & SQL – Assignment Study Guide

DATA WAREHOUSING & SQL

Comprehensive Assignment Study Guide

Prepared for: Students of Dr. Mahadia Tunga

Purpose: Marked Assignment Preparation (Friday)

Topics Covered in This Guide

1. Writing SQL Queries
2. Identifying OLAP Operations
3. Drawing Star Schema
4. Drawing Snowflake Schema
5. Differences Between Schemas
6. Using Aggregation Functions Correctly

1. Writing SQL Queries

SQL (Structured Query Language) is the standard language for managing and querying relational
databases. Mastering SQL is fundamental to data analysis and database management.

1.1 Core SQL Clauses

Every SQL query is built from a combination of clauses. Understanding each clause and its purpose
is essential:
Clause Purpose Example Usage
SELECT Specifies which columns to retrieve SELECT name, salary
from the database
FROM Identifies the table(s) to query FROM employees
WHERE Filters rows based on a condition WHERE salary > 50000
(before grouping)
JOIN Combines rows from two or more JOIN departments ON dept_id
related tables
GROUP BY Groups rows that share a property GROUP BY department
for aggregation
HAVING Filters groups after aggregation (like HAVING COUNT(*) > 5
WHERE for groups)

Prepared for students of Dr. Mahadia Tunga | Page 1 of 9

Data Warehousing & SQL – Assignment Study Guide

Clause Purpose Example Usage

ORDER BY Sorts the final result set ascending ORDER BY salary DESC
or descending
LIMIT Restricts the number of rows LIMIT 10
returned

1.2 Types of JOINs

JOINs are used to combine data from multiple tables. There are four main types:
• INNER JOIN – Returns only rows where there is a match in both tables
• LEFT JOIN – Returns all rows from the left table, and matched rows from the right
• RIGHT JOIN – Returns all rows from the right table, and matched rows from the left
• FULL OUTER JOIN – Returns all rows when there is a match in either table

1.3 Complete SQL Query Example

The following query demonstrates multiple clauses working together:
SELECT
d.department_name,
COUNT(e.employee_id) AS total_employees,
AVG([Link]) AS avg_salary,
MAX([Link]) AS highest_salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE e.hire_date >= '2020-01-01'
GROUP BY d.department_name
HAVING COUNT(e.employee_id) > 3
ORDER BY avg_salary DESC;

What this query does:

• Retrieves department names and employee statistics
• Joins employees with departments using a matching dept_id
• Filters to only employees hired from 2020 onwards
• Groups results by department and filters to departments with more than 3 employees
• Sorts output by average salary from highest to lowest
💡 Tip: Always use table aliases (e, d) when joining multiple tables — it makes your code
shorter and easier to read.

2. Identifying OLAP Operations

OLAP stands for Online Analytical Processing. It is a technology that allows users to analyze large
volumes of data from multiple dimensions quickly and interactively. OLAP is the analytical engine
behind most data warehouses.

Prepared for students of Dr. Mahadia Tunga | Page 2 of 9

Data Warehousing & SQL – Assignment Study Guide

2.1 The OLAP Data Cube Concept

Think of data as a multi-dimensional cube. Each face of the cube represents a different dimension
(e.g., Time, Product, Region). OLAP operations are different ways of "looking at" or "slicing through"
this cube.

2.2 The Five OLAP Operations

Operation Definition Direction Example
Roll-Up Aggregates data by ⬆ Less detail Daily Sales → Monthly Sales
moving up a dimension → Annual Sales
hierarchy. Combines
detailed data into
summaries.
Drill-Down Breaks data into finer ⬇ More detail Annual Sales → Quarterly
levels of detail by moving Sales → Monthly Sales
down a hierarchy. The
opposite of Roll-Up.
Slice Selects a single layer ➡ One Show all data for Year = 2024
(two-dimensional "slice") dimension fixed only
from the data cube by
fixing one dimension to a
specific value.
Dice Selects a sub-cube by ⬛ Multiple Sales where Year = 2024 AND
applying conditions on dimensions fixed Region = 'East'
two or more dimensions
simultaneously.
Pivot (Rotate) Rotates the data cube to 🔄 View angle Switch Product columns to
view it from a different changes become rows
angle. Swap rows and
columns in a report.

2.3 How to Identify OLAP Operations

When reading a question, look for these keywords:
• Roll-Up → words like 'summarize', 'total', 'combine', 'aggregate up', 'higher level'
• Drill-Down → words like 'break down', 'detailed view', 'expand', 'lower level'
• Slice → fixing ONE dimension to a single value ('only for 2024', 'only for East region')
• Dice → fixing TWO or MORE dimensions simultaneously ('2024 AND East AND
Electronics')
• Pivot → rotating, transposing, or changing the orientation of a table/report
💡 Tip: Remember: Slice = one constraint. Dice = two or more constraints. A dice is like slicing
in multiple directions at once.

Prepared for students of Dr. Mahadia Tunga | Page 3 of 9

Data Warehousing & SQL – Assignment Study Guide

3. Drawing Star Schema

A Star Schema is the most widely used schema in data warehousing. It gets its name from its visual
appearance — a central table surrounded by dimension tables, resembling a star.

3.1 Components of a Star Schema

Fact Table (Centre)
The Fact Table is the core of the Star Schema. It stores measurable, quantitative business data —
these are the 'facts' you want to analyze.
• Contains foreign keys that link to all dimension tables
• Contains numeric measures (e.g., sales_amount, quantity_sold, revenue)
• Typically has millions of rows — it is the largest table in the schema

Dimension Tables (Surrounding)

Dimension Tables surround the fact table and provide context for the facts. They answer the 'who,
what, where, when, why' questions.
• DimTime – When did the sale occur? (Year, Quarter, Month, Day)
• DimProduct – What was sold? (Product Name, Category, Brand)
• DimCustomer – Who bought it? (Name, Age, Location)
• DimStore – Where was it sold? (Store Name, City, Region)

3.2 Star Schema Diagram

[DimTime]
(TimeID PK)
(Year)
(Quarter)
(Month)
|
|
[DimProduct] ---- [FactSales] ---- [DimCustomer]
(ProductID PK) (SaleID PK) (CustomerID PK)
(ProductName) (TimeID FK) (CustomerName)
(Category) (ProductID FK) (City)
(Brand) (CustomerID FK) (Age)
(StoreID FK) |
(SalesAmount) |
(Quantity) [DimStore]
(StoreID PK)
(StoreName)
(Region)

3.3 Characteristics of Star Schema

Feature Description
Normalization Denormalized — dimension tables are not split further
Redundancy Higher — data may be repeated in dimension tables

Prepared for students of Dr. Mahadia Tunga | Page 4 of 9

Data Warehousing & SQL – Assignment Study Guide

Feature Description
Query Performance Fast — fewer table joins needed
Complexity Low — easy to understand and implement
Storage Uses more storage due to redundancy
Best For Simple queries and fast reporting environments

💡 Tip: In an exam, draw the Fact Table in the centre and connect all Dimension Tables directly
to it with straight lines. Label Primary Keys (PK) and Foreign Keys (FK).

4. Drawing Snowflake Schema

A Snowflake Schema is an extension of the Star Schema. In a Snowflake Schema, the dimension
tables are normalized — meaning they are broken down into multiple related sub-dimension tables.
This creates a structure that resembles a snowflake.

4.1 How Snowflake Extends Star Schema

In a Star Schema, the DimProduct table might store Category and Brand directly. In a Snowflake
Schema, Category and Brand are moved to their own separate tables and linked via foreign keys.
This normalization eliminates redundancy.

4.2 Snowflake Schema Diagram

[DimProductCategory] [DimYear]
(CategoryID PK) (YearID PK)
(CategoryName) (YearValue)
| |
[DimProduct] [DimMonth]
(ProductID PK) (MonthID PK)
(ProductName) (MonthName)
(CategoryID FK) (YearID FK)
| |
| [DimTime]
| (TimeID PK)
| (Day)
| (MonthID FK)
| |
[DimProduct] ---- [FactSales] ---- [DimCustomer] --- [DimCity]
(SaleID PK) (CustomerID PK) (CityID PK)
(ProductID FK) (CustomerName) (CityName)
(CustomerID FK) (CityID FK) (CountryID FK)
(TimeID FK) |
(SalesAmount) [DimCountry]
(CountryID PK)
(CountryName)

Prepared for students of Dr. Mahadia Tunga | Page 5 of 9

Data Warehousing & SQL – Assignment Study Guide

4.3 Characteristics of Snowflake Schema

Feature Description
Normalization Fully normalized — dimension tables are split into sub-tables
Redundancy Low — data is stored only once, reducing duplication
Query Performance Slower — more joins are needed to retrieve data
Complexity Higher — more tables and relationships to manage
Storage Uses less storage due to normalization
Best For Large databases where storage efficiency and data integrity
are priorities

5. Differences Between Star and Snowflake Schemas

Being able to clearly explain the differences between these two schemas is a key requirement. The
table below provides a comprehensive side-by-side comparison:
Criteria Star Schema Snowflake Schema
Structure Simple — one level of Complex — multiple levels of
dimension tables sub-tables
Normalization Denormalized Normalized (3NF or higher)
Data Redundancy Higher — data is repeated Lower — data is not repeated
Number of Tables Fewer tables More tables
Number of JOINs Fewer joins needed More joins required
Query Speed Faster query performance Slower query performance
Storage Space Requires more storage Requires less storage
Design Complexity Easier to design and More complex to design
understand
Maintenance Harder to update (data in Easier to update (data in one
many places) place)
Best Use Case When speed and simplicity When storage efficiency and
matter most integrity matter most

5.1 When to Use Each Schema

Use Star Schema when:
• Query performance and speed are the top priority
• The database is used primarily for reporting and dashboards
• The team prefers simplicity and ease of understanding

Prepared for students of Dr. Mahadia Tunga | Page 6 of 9

Data Warehousing & SQL – Assignment Study Guide

• Storage cost is not a concern

Use Snowflake Schema when:

• Storage efficiency is important (large datasets)
• Data integrity and consistency must be maintained
• Updates to dimension data are frequent
• The database must adhere to normalization rules
💡 Tip: A common exam question asks: 'Which schema is better?' The answer is: it depends on
the use case. Star Schema is faster; Snowflake Schema is more storage-efficient. Always justify
your answer.

6. Using Aggregation Functions Correctly

Aggregation functions perform calculations on a group of values and return a single summary value.
They are used with the GROUP BY clause to summarize data.

6.1 The Five Core Aggregation Functions

Function What It Does Syntax Example Returns
SUM() Adds up all numeric values SUM(sales_amount Total sum
in a column )
COUNT() Counts the number of rows. COUNT(order_id) Number of
COUNT(*) counts all rows; rows
COUNT(col) skips NULLs
AVG() Calculates the arithmetic AVG(salary) Average value
mean (sum ÷ count)
MIN() Returns the smallest value MIN(price) Lowest value
in the column
MAX() Returns the largest value in MAX(score) Highest value
the column

6.2 Using Aggregation with GROUP BY

The GROUP BY clause is always used alongside aggregation functions when you want to calculate
summaries per group (e.g., per department, per region, per year).
SELECT
region,
SUM(sales_amount) AS total_sales,
AVG(sales_amount) AS average_sale,
COUNT(order_id) AS number_of_orders,
MAX(sales_amount) AS highest_sale,
MIN(sales_amount) AS lowest_sale
FROM fact_sales
GROUP BY region
ORDER BY total_sales DESC;

Prepared for students of Dr. Mahadia Tunga | Page 7 of 9

Data Warehousing & SQL – Assignment Study Guide

This query calculates five different aggregations for each region and sorts the results by total sales.

6.3 WHERE vs HAVING — A Critical Distinction

This is a very common source of errors. Knowing when to use WHERE vs HAVING is essential:
Clause Used For Works With Example
WHERE Filter individual rows Regular column WHERE salary > 50000
BEFORE grouping values
HAVING Filter groups AFTER Aggregated/ HAVING AVG(salary) >
aggregation calculated values 50000

Correct Example Using Both:

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01' -- Filter rows first
GROUP BY department
HAVING AVG(salary) > 60000; -- Then filter groups

6.4 Common Mistakes to Avoid

• Using WHERE instead of HAVING to filter aggregated results — this causes a SQL error
• Forgetting to include non-aggregated columns in GROUP BY — every column in SELECT
that is not aggregated must appear in GROUP BY
• Confusing COUNT(*) and COUNT(column_name) — COUNT(*) counts all rows including
NULLs; COUNT(col) skips NULL values
💡 Tip: Rule: If you can calculate it without aggregation, use WHERE. If you are filtering on the
result of SUM, COUNT, AVG, MIN, or MAX — always use HAVING.

7. Revision Checklist
Use this checklist to track your preparation before Friday's assignment. You should be comfortable
with every item:

SQL Queries
☐ I can write a SELECT query with WHERE, GROUP BY, HAVING, and ORDER BY
☐ I can write an INNER JOIN query between two tables
☐ I know the difference between WHERE and HAVING
☐ I can write a query using multiple aggregation functions at once

OLAP Operations
☐ I can define and identify all five OLAP operations: Roll-Up, Drill-Down, Slice, Dice, Pivot
☐ I can distinguish between Slice (one dimension) and Dice (multiple dimensions)
☐ I can give a real-world example of each operation

Prepared for students of Dr. Mahadia Tunga | Page 8 of 9

Data Warehousing & SQL – Assignment Study Guide

Star Schema
☐ I can draw a complete Star Schema from scratch with a Fact Table and at least 3 Dimension
Tables
☐ I can label Primary Keys (PK) and Foreign Keys (FK) correctly
☐ I can explain what the Fact Table contains vs. what Dimension Tables contain

Snowflake Schema
☐ I can draw a Snowflake Schema with normalized dimension sub-tables
☐ I can explain why normalization is used in Snowflake Schema

Differences Between Schemas

☐ I can list at least 5 differences between Star and Snowflake schemas
☐ I can recommend the appropriate schema for a given use case and justify my answer

Aggregation Functions
☐ I can correctly use SUM, COUNT, AVG, MIN, and MAX in a SQL query
☐ I know when to use GROUP BY alongside aggregation functions
☐ I know the difference between WHERE and HAVING

Good luck on your assignment! You've got this. 🎓

Prepared for students of Dr. Mahadia Tunga | Page 9 of 9