Skip to content
Zeni Study
Share
Explore

Day 2 — Relationships & Foreign Keys (Thinking in Connections)

⁠

🟢 Day 2 — Relationships & Foreign Keys (Thinking in Connections)

🎯 Day 2 Outcome

By the end of Day 2, the student should be able to:
Understand why tables are split
Model one-to-many relationships
Use foreign keys correctly
Write SQL queries that involve multiple tables
Explain why a schema is designed a certain way
If Day 2 is strong, JOINs (Day 4) become easy.
⁠

1️⃣ Why One Table Is Not Enough (Teaching Script)

Start with This Scenario

“One user places multiple orders.”
Ask:
Should we store orders inside the users table?

❌ Bad Design (Do NOT Do This)

users:
user_id | name | order1 | order2 | order3
Problems:
How many order columns?
What if user has 100 orders?
What if no orders?
❌ This design breaks immediately.
⁠

2️⃣ The Core Rule of Databases

One row = one real-world object
So:
One user → one row in users
One order → one row in orders
⁠

3️⃣ Understanding Relationships (Key Concept)

One-to-Many Relationship

Real World
Database
One user
Many orders
One product
Many reviews
One department
Many employees
There are no rows in this table
⁠
👉 This is called one-to-many.
⁠

4️⃣ How Databases Represent Relationships

Answer:
By storing the parent’s ID in the child table
This is called a foreign key.
⁠

5️⃣ Designing the Schema (Interview-Style)

Step 1: Users Table (Parent)

CREATE TABLE users (
user_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
⁠

Step 2: Orders Table (Child)

CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
total_amount INT,
created_at TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
⁠

6️⃣ Explain Foreign Key in Simple Words

A foreign key says: “This value must exist in the parent table.”
So:
You cannot create an order for a non-existing user
Database helps maintain correctness
⁠

7️⃣ Insert Data (Very Important to Try)

Insert Users

INSERT INTO users VALUES
(1, 'Amit', 'amit@gmail.com'),
(2, 'Neha', 'neha@gmail.com');
⁠

Insert Orders

INSERT INTO orders VALUES
(101, 1, 500, '2024-01-10'),
(102, 1, 300, '2024-01-11'),
(103, 2, 700, '2024-01-12');
⁠

❌ What This Prevents (Interview Question)

INSERT INTO orders VALUES (104, 999, 100, '2024-01-13');
❌ ERROR — user_id 999 does not exist
That’s foreign key doing its job.
⁠

8️⃣ Writing Real Interview Queries

Problem 1 — Get All Orders of a User

SELECT * FROM orders
WHERE user_id = 1;
Read in English:
“Give me all orders placed by user 1”
⁠

Problem 2 — Count Orders Per User

SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id;
⁠

Problem 3 — Users With No Orders (Thinking Question)

We cannot solve this fully yet (JOIN needed). But conceptually:
“Users whose user_id is not present in orders table”
We’ll solve this properly on Day 4.
⁠

9️⃣ Why We Do NOT Duplicate Data

❌ Bad Design

orders:
order_id | user_name | user_email | amount
Problems:
If user name changes → update everywhere
Data inconsistency
Wasted storage
❌ Never duplicate parent data in child table.
⁠

🔟 Common Interview Questions (Verbal)

You MUST be able to answer:
What is a foreign key?
Why do we need foreign keys?
What happens if foreign key is removed?
Can a foreign key be NULL? (Yes, sometimes)
Difference between primary key and foreign key?
⁠

📝 Practice Problems (DO THESE)

Problem 1 — Schema Design

Design tables for:
customers
orders
Fields:
Customer: id, name, city
Order: id, customer_id, amount, order_date
⁠

Problem 2 — Insert Data

Insert:
2 customers
3 orders (2 for one customer, 1 for another)
⁠

Problem 3 — Queries

Write SQL to:
Get all orders for customer_id = 1
Count orders per customer
Get total order amount per customer
⁠

🔍 HINTS (Read Only If Stuck)

Child table stores parent ID
Use GROUP BY with aggregates
One row = one entity
⁠

✅ Solutions (Read After Attempting)

Solution 1 — Tables

CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
city VARCHAR(50)
);

CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
amount INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
⁠

Solution 2 — Insert

INSERT INTO customers VALUES
(1, 'Rahul', 'Delhi'),
(2, 'Sneha', 'Mumbai');

INSERT INTO orders VALUES
(201, 1, 500, '2024-02-01'),
(202, 1, 300, '2024-02-02'),
(203, 2, 700, '2024-02-03');
⁠

Solution 3 — Queries

SELECT * FROM orders WHERE customer_id = 1;

SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id;

SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id;
⁠

🧠 Reflection Questions (Must Answer)

Why do we split users and orders?
What problem does foreign key solve?
What breaks if we duplicate user data?
Why is one-to-many so common?
⁠

⭐ Day 2 Golden Rule

Databases model relationships, not nested objects.
⁠
Gallery
 
Want to print your doc?
This is not the way.
Try clicking the ··· in the right corner or using a keyboard shortcut (
CtrlP
) instead.