Lecture 4: Relationships & Data Analysis

Joining the Dots: From Tables to Insights

🎓 Concept Overview

Normalization: The art of splitting data to reduce redundancy. "One Fact, One Place."
Relationships: Connecting split tables via Foreign Keys (1:1, 1:N, N:M).
JOINs: The mechanism to reconstruct split data.
Aggregation: Collapsing thousands of rows into a single summary (Sum, Average, Count).

4.1 Relationships (Normalization)

Why do we split data into Authors and Books? Why can't we just have one big excel sheet? This process is called Normalization.

🧠 Mental Model: One Fact, One Place

If you misspelled an author's name in 1,000 book rows, you'd have to fix it 1,000 times. By splitting tables, you fix it in one place (the Authors table), and all books automatically point to the corrected name via ID.

4.1.1 Types of Relationships

Type	Description	Example
One-to-One (1:1)	Row A matches exactly one Row B. Often used to split heavy data or security.	User -> UserProfile (Bio, Avatar)
One-to-Many (1:N)	Row A matches many Row Bs. The most common relationship.	Author -> Books, Customer -> Orders
Many-to-Many (N:M)	Row A matches many Row Bs, AND Row B matches many Row As. Requires a "Junction Table".	Students -> Classes (A student has many classes; a class has many students).

4.1.2 The Junction Table (Many-to-Many)

You cannot link specialized N:M directly. You need a middleman.

4.2 JOINs: Connecting Data

JOIN clauses link tables together based on a shared key (PK = FK).

🧠 Mental Model: The Zipper vs The Stapler

JOIN is a Zipper: It connects two pieces of fabric (tables) sideways using teeth (keys).
UNION is a Stapler: It stacks two pages (result sets) on top of each other.

4.2.1 Join Types

INNER JOIN

Only matches

LEFT JOIN

All Left + Matches

RIGHT JOIN

All Right + Matches

-- Get Books AND their Author names
SELECT books.title, authors.name 
FROM books
INNER JOIN authors ON books.author_id = authors.author_id;

Join Type	Result	Scenario
`INNER JOIN`	Matching rows only.	Show me orders that have a customer.
`LEFT JOIN`	All rows from Left table, plus matches.	Show me ALL customers, even if they haven't ordered yet (NULL order).
`RIGHT JOIN`	All rows from Right table.	Rarely used (Left Join is preferred for readability).
`FULL OUTER JOIN`	Everything from Both.	MySQL Missing Feature: You must emulate this using `LEFT JOIN ... UNION ... RIGHT JOIN`.
`CROSS JOIN`	Cartesian Product. All vs All.	Usually an error unless generating test data.

4.3 Aggregation (The Analysis)

GROUP BY is like putting rows into buckets. Use Aggregate Functions to summarize the bucket.

4.3.1 Aggregate Functions

COUNT(*): Count rows.
SUM(column): Add up values.
AVG(column): Calculate average.
MIN() / MAX(): Find extremes.

-- How many books per author?
SELECT author_id, COUNT(*) as book_count
FROM books 
GROUP BY author_id;

4.3.2 WHERE vs HAVING

Confusion point: Both filter data, but when?

Clause	Timing	Filters...
`WHERE`	Before Grouping	Individual Rows (e.g., "Price > 10")
`HAVING`	After Grouping	The Aggregate Result (e.g., "Count > 5")

-- Find authors with more than 5 books
SELECT author_id, COUNT(*) 
FROM books 
GROUP BY author_id
HAVING COUNT(*) > 5;

4.4 Sorting and Limits

4.4.1 ORDER BY

Sorts the final result. Can be multiple columns.

-- Sort by expensive first, then by name alphabetically
ORDER BY price DESC, title ASC;

4.4.2 LIMIT & OFFSET

Essential for pagination (Page 1, Page 2...).

-- Get Page 2 (Items 11-20)
SELECT * FROM products 
LIMIT 10 OFFSET 10;

4.5 Teaching Notes

🎓 Instructor Guide

Key Visuals:

Venn Diagrams: Always draw Circles for JOINs. Shading the middle = INNER. Shading the Left + Middle = LEFT.
The "Buckets" Analogy: For GROUP BY, imagine sorting Lego bricks into buckets by color. You can count the bricks in each bucket, but you can't hold up "The Single Lego" that represents the whole red bucket unless you aggregate it.
The Cartesian Explosion: Warn students that SELECT * FROM A, B is valid syntax but dangerous. It multiplies rows (100 rows * 100 rows = 10,000).

4.6 Debugging Workshop

Find the logic errors in these advanced queries.

1. Infinite Rows

I joined tables but my computer froze. I got millions of results.

SELECT * FROM books, authors; -- Old style syntax

SELECT * FROM books 
JOIN authors ON books.author_id = authors.author_id;

Explanation: Without an ON clause, you create a Cartesian Product (All rows × All rows).

2. The Ambiguous Group

Error: Select list is not in GROUP BY clause.

SELECT author_id, title, COUNT(*) 
FROM books 
GROUP BY author_id;

-- You can only select the group key or an aggregate
SELECT author_id, COUNT(*) 
FROM books 
GROUP BY author_id;
-- OR include title in the group (if it makes sense)
GROUP BY author_id, title;

3. Subquery Overload

Error: Subquery returns more than 1 row.

SELECT * FROM authors 
WHERE author_id = (SELECT author_id FROM books WHERE price > 50);

SELECT * FROM authors 
WHERE author_id IN (SELECT author_id FROM books WHERE price > 50);

Explanation: = is for single values. IN is for lists.

4.7 Practice Tasks

Task 1: The Unsold Properties

Scenario: You are a Real Estate agent. You have a Properties table and a Sales table. You want to see properties that have never been sold.

Activity: Use a LEFT JOIN to find records in A that are NOT in B.

Show Answer

SELECT p.address 
FROM properties p
LEFT JOIN sales s ON p.id = s.property_id
WHERE s.id IS NULL;

Trick: Left Join keeps all properties. If no matching sale exists, the sales columns will be NULL.

Task 2: The High Rollers

Scenario: You want to find customers who have spent more than $1,000 in total across all their orders.

Activity: Use GROUP BY and HAVING.