Relational Databases, SQL, Normalization & ACID: A Beginner-Friendly Guide 🗄️

🗄️ Relational Databases, SQL, Normalization & ACID

This is one of the most important topics to understand before working with SQLAlchemy, PostgreSQL, FastAPI and backend development. 💪

We'll learn relational database concepts, SQL, normalization and ACID properties from absolute zero. No prior database knowledge is assumed, no skipped fundamentals, and no terminology without explanation.

Along the way we'll connect everything to a real Instagram-like Social Feed project (React + FastAPI + PostgreSQL), so you can see how these ideas are actually used. 🚀

---

📚 Table of Contents

1. What is a database?
2. Tables, rows and columns
3. Keys (Primary, Foreign, Composite...)
4. Relationships (1:1, 1:N, N:N)
5. SQL (DDL, DML, DQL)
6. JOINs
7. Aggregations, GROUP BY and HAVING
8. Normalization (1NF, 2NF, 3NF)
9. ACID properties and transactions
10. Constraints
11. Designing an Instagram database
12. SQL → SQLAlchemy → FastAPI
13. Interview questions
14. Quiz and final revision

---

1️⃣ First, what is a database?

Imagine you're building Instagram 📸. Your app needs to store:

• 👤 Users
• 🖼️ Posts
• 💬 Comments
• ❤️ Likes
• 👥 Followers
• 🔑 Login sessions

Where does all this go? Into a database.

📦 A database is an organized collection of data that lets applications store, retrieve, update and delete information.

React (Frontend)         → user clicks "Like"
      │  HTTP request / response
      ▼
FastAPI (Backend)        → validates, applies business logic
      │  SQL via SQLAlchemy
      ▼
PostgreSQL (Database)    → stores users, posts, likes, comments...

When a user creates a post:

1. User types a caption in React
2. React sends a request to FastAPI
3. FastAPI validates it
4. FastAPI talks to PostgreSQL
5. PostgreSQL stores the post
6. The backend returns a response
7. React shows the new post ✅

⚠️ The frontend does not normally talk directly to your database. The backend is the middleman.

1.1 🧩 What is a Relational Database?

A relational database stores data in tables and defines relationships between those tables.

Popular ones:

• 🐘 PostgreSQL
• 🐬 MySQL
• 🪶 SQLite
• 🏢 Microsoft SQL Server
• 🏛️ Oracle Database

We'll use PostgreSQL in our examples.

Why "relational"? Because tables connect to each other:

• One user can create many posts
• One post can have many comments
• One post can receive many likes

Instead of one giant table, we split information into logically related tables. That's the central idea of the relational model.

---

2️⃣ Tables, Rows and Columns

Here's a simple users table:

user_id	username	email	age
1	ishu	[e-mail]	22
2	rahul	[e-mail]	23
3	priya	[e-mail]	21

Term	Meaning	Example
📋 Table	Collection of related data	users, posts, comments
➡️ Row (record / tuple)	One complete entry	One user's information
⬇️ Column (attribute)	One property of the data	username, email, age
🔲 Cell	A single value at a row/column intersection	[e-mail]
📐 Schema	The blueprint: tables, columns, types, constraints, relationships	The whole structure

2.1 🔤 Data types

Every column has a data type that decides what it can store.

Data type	Meaning	Example
INTEGER	Whole numbers	25
VARCHAR(n)	Variable-length text (max n)	'Ishu'
TEXT	Text of any length	A post caption
BOOLEAN	True or false	TRUE
DATE	Calendar date	2026-11-11
TIMESTAMP	Date and time	2026-11-11 12:30:00
DECIMAL	Exact decimal numbers	99.50
UUID	Unique identifier	550e8400-...

CREATE TABLE users (
    user_id   INTEGER,
    username  VARCHAR(50),
    email     VARCHAR(100),
    age       INTEGER,
    is_active BOOLEAN
);

• users → the table name
• username → text, max 50 characters
• email → text, max 100 characters
• is_active → a Boolean

2.2 🕳️ What is NULL?

NULL means a value is missing or unknown. It is NOT the same as:

• ❌ 0
• ❌ an empty string ''
• ❌ FALSE

INSERT INTO users (user_id, username, email, age)
VALUES (1, 'ishu', '[e-mail]', NULL);

To find users whose age is missing:

SELECT *
FROM users
WHERE age IS NULL;

🚨 You cannot write age = NULL. NULL is an unknown value, not an ordinary comparable value. Always use IS NULL / IS NOT NULL.

---

3️⃣ Keys in Relational Databases 🔑

Imagine two users with the same name:

user_id	username
1	Rahul
2	Rahul

If you want to update the first Rahul's email, how does the database know which one? We need something that uniquely identifies each row. That's what keys do.

3.1 🆔 Primary Key (PK)

A Primary Key is a column (or combination of columns) that uniquely identifies every row in a table.

CREATE TABLE users (
    user_id  INTEGER PRIMARY KEY,
    username VARCHAR(50),
    email    VARCHAR(100)
);

Rules:

1. Every primary key value must be unique
2. A primary key can never be NULL
3. A table has only one primary key constraint (though it may span multiple columns)

user_id (PK)	username
1	Rahul
2	Rahul
3	Priya

Usernames can repeat, but IDs can't.

🎓 Analogy: your college USN. Many students share a name, but every student has a distinct USN.

3.2 🔗 Foreign Key (FK)

A Foreign Key is a column (or group of columns) that references a key in another table (usually its primary key or a UNIQUE key).

Users

user_id (PK)	username
1	Ishu
2	Rahul
3	Priya

Posts

post_id (PK)	caption	user_id (FK)
101	My first post	1
102	Travel memories	1
103	Coding journey	2

The user_id in posts tells us who created the post:

• Post 101 → user 1
• Post 102 → user 1
• Post 103 → user 2

CREATE TABLE posts (
    post_id INTEGER PRIMARY KEY,
    caption TEXT,
    user_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

🛡️ Why is a Foreign Key important?

What if someone tries this?

INSERT INTO posts VALUES (104, 'Hello', 999);

User 999 doesn't exist, so the foreign key constraint blocks the insert. This is called Referential Integrity: relationships between tables always stay valid.

3.3 🗝️ Other important keys

Key	Meaning
Super Key	Any set of columns that uniquely identifies a row
Candidate Key	A minimal super key (no unnecessary columns)
Alternate Key	A candidate key not chosen as the primary key
Composite Key	A key made of multiple columns
Unique Key	A constraint that prevents duplicate values

CREATE TABLE users (
    user_id  INTEGER PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email    VARCHAR(100) UNIQUE
);

Here user_id is the primary key, while username and email are unique (and are also candidate keys).

3.4 🧱 Composite Primary Key

Think of a likes table:

user_id	post_id
1	101
1	102
2	101

One user can like many posts, and one post can be liked by many users. But the same user shouldn't like the same post twice.

CREATE TABLE likes (
    user_id INTEGER,
    post_id INTEGER,
    PRIMARY KEY (user_id, post_id)
);

The combination (user_id, post_id) is unique. Neither column needs to be unique on its own.

---

4️⃣ Relationships in Relational Databases 🤝

There are three main types:

1. One-to-One (1:1)
2. One-to-Many (1:N)
3. Many-to-Many (N:N)

4.1 1️⃣ One-to-One (1:1)

One record in Table A relates to at most one record in Table B, and vice versa. Example: one user has one profile.

users

user_id	username
1	Ishu
2	Rahul

profiles

profile_id	user_id (FK)	bio
101	1	Developer
102	2	Student

CREATE TABLE profiles (
    profile_id INTEGER PRIMARY KEY,
    user_id    INTEGER UNIQUE REFERENCES users(user_id),
    bio        TEXT
);

Why UNIQUE on user_id? Without it, multiple profiles could point to the same user. UNIQUE guarantees at most one profile per user.

Common uses: user profiles, employee details, passport info, account settings.

4.2 🌳 One-to-Many (1:N)

One record in Table A can relate to many records in Table B, but each B record belongs to one A record. Example: one user creates many posts.

users

user_id	username
1	Ishu
2	Rahul

posts

post_id	user_id
101	1
102	1
103	1
104	2

📌 Golden rule: the foreign key goes on the MANY side. So posts.user_id references users.user_id.

More examples:

• One teacher → many students
• One customer → many orders
• One post → many comments
• One department → many employees

4.3 🕸️ Many-to-Many (N:N)

Many records in A relate to many records in B. Example: students and courses.

• One student can enroll in many courses
• One course can have many students

A single foreign key in either table can't model this. So we add a third table called a junction table (also: bridge table or associative table).

students

student_id	name
1	Ishu
2	Rahul

courses

course_id	course_name
10	Python
20	SQL

enrollments (junction)

student_id	course_id
1	10
1	20
2	10

CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(student_id),
    course_id  INTEGER REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

This turns one many-to-many relationship into two one-to-many relationships. 🎯

Instagram example: Users ↔ Posts is many-to-many through Likes. One user can like many posts, and one post can be liked by many users.

4.4 ⚖️ Quick comparison

Relationship	Example	Implementation
1:1	User → Profile	FK + UNIQUE
1:N	User → Posts	FK on the many side
N:N	Students ↔ Courses	Junction table

---

5️⃣ SQL: Structured Query Language 💬

How do we talk to a relational database? With SQL (Structured Query Language). With SQL we can:

• Create tables
• Insert, retrieve, update and delete records
• Create relationships and apply constraints
• Manage transactions

SQL commands are grouped into categories.

5.1 🏗️ DDL: Data Definition Language

DDL defines or changes the structure of database objects.

Command	Purpose
CREATE	Creates a table or other object
ALTER	Modifies an existing table
DROP	Deletes an object
TRUNCATE	Removes all rows, keeps the table

-- CREATE
CREATE TABLE users (
    user_id  INTEGER PRIMARY KEY,
    username VARCHAR(50),
    email    VARCHAR(100)
);

-- ALTER: add an age column
ALTER TABLE users
ADD COLUMN age INTEGER;

-- TRUNCATE: remove all rows, keep structure
TRUNCATE TABLE users;

-- DROP: remove the table itself (structure + data)
DROP TABLE users;

5.2 ✏️ DML: Data Manipulation Language

DML manipulates the data inside tables: INSERT, UPDATE, DELETE.

-- INSERT: add data
INSERT INTO users (user_id, username, email)
VALUES (1, 'ishu', '[e-mail]');

-- UPDATE: modify existing data
UPDATE users
SET username = 'deepika'
WHERE user_id = 1;

-- DELETE: remove data
DELETE FROM users
WHERE user_id = 1;

🚨 Never forget WHERE! Without it, UPDATE changes every row and DELETE removes every row.

5.3 🔎 DQL: Data Query Language

DQL retrieves data. The main command is SELECT.

-- all columns
SELECT * FROM users;

-- specific columns
SELECT username, email
FROM users;

-- filter rows
SELECT *
FROM users
WHERE age > 20;

Useful comparison operators:

Operator	Meaning
=	Equal
!= or <>	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal

More filtering tools:

-- AND / OR
SELECT * FROM users WHERE age >= 18 AND is_active = TRUE;

-- IN
SELECT * FROM users WHERE username IN ('ishu', 'rahul');

-- BETWEEN
SELECT * FROM users WHERE age BETWEEN 18 AND 25;

-- LIKE (pattern matching)
SELECT * FROM users WHERE email LIKE '[e-mail]';

-- ORDER BY + LIMIT
SELECT * FROM users
ORDER BY age DESC
LIMIT 5;

🧠 % in LIKE means "any number of characters".

5.4 🔐 Other SQL categories

Category	Purpose	Examples
DDL	Define structure	CREATE, ALTER, DROP
DML	Change data	INSERT, UPDATE, DELETE
DQL	Query data	SELECT
TCL	Control transactions	BEGIN, COMMIT, ROLLBACK
DCL	Control permissions	GRANT, REVOKE

---

6️⃣ SQL JOINs 🔀

Data lives in separate tables, so how do we combine it? With JOINs.

We'll use this sample data throughout:

users

user_id	username
1	Ishu
2	Rahul
3	Priya
4	Arjun

posts

post_id	caption	user_id
101	Hello World	1
102	My Travel	1
103	Coding	2
104	Photography	NULL

💡 Post 104 has no owner (user_id is NULL). In a well-designed schema posts.user_id would be NOT NULL, but this orphan row helps us see how each JOIN behaves.

6.1 🤝 INNER JOIN

Returns only rows that have a match in both tables.

SELECT
    users.username,
    posts.caption
FROM users
INNER JOIN posts
    ON users.user_id = posts.user_id;

username	caption
Ishu	Hello World
Ishu	My Travel
Rahul	Coding

• Why no Priya or Arjun? They have no matching posts.
• Why no post 104? Its user_id matches no user.

🧠 INNER JOIN = only matching records.

6.2 ⬅️ LEFT JOIN (LEFT OUTER JOIN)

Returns:

• All rows from the left table
• Matching rows from the right table
• NULL for right-side columns when there's no match

SELECT
    users.username,
    posts.caption
FROM users
LEFT JOIN posts
    ON users.user_id = posts.user_id;

username	caption
Ishu	Hello World
Ishu	My Travel
Rahul	Coding
Priya	NULL
Arjun	NULL

Priya and Arjun appear because LEFT JOIN protects the left table (users).

🧠 LEFT JOIN = everything from the left + matching data from the right.

Real use case: "Show all users, including those who haven't posted anything."

6.3 ➡️ RIGHT JOIN (RIGHT OUTER JOIN)

The mirror image of LEFT JOIN. Returns all rows from the right table plus matching left rows (NULL where there's no match).

SELECT
    users.username,
    posts.caption
FROM users
RIGHT JOIN posts
    ON users.user_id = posts.user_id;

username	caption
Ishu	Hello World
Ishu	My Travel
Rahul	Coding
NULL	Photography

"Photography" is included because RIGHT JOIN protects every row of posts.

6.4 ↔️ FULL OUTER JOIN

Returns all matches + all unmatched rows from both sides.

SELECT
    users.username,
    posts.caption
FROM users
FULL OUTER JOIN posts
    ON users.user_id = posts.user_id;

username	caption
Ishu	Hello World
Ishu	My Travel
Rahul	Coding
Priya	NULL
Arjun	NULL
NULL	Photography

6.5 📊 JOIN comparison

JOIN	Returns
INNER JOIN	Only matching rows
LEFT JOIN	All left rows + matching right rows
RIGHT JOIN	All right rows + matching left rows
FULL OUTER JOIN	All rows from both tables

Easy memory trick:

• INNER = match only
• LEFT = protect the left side
• RIGHT = protect the right side
• FULL = protect both sides

💡 PostgreSQL supports all four. Some other databases have limited support or different syntax (for example, MySQL has no FULL OUTER JOIN).

6.6 🧮 Multiple-table JOINs

Real apps often need three or more tables. Let's show the username, post caption and comment:

SELECT
    users.username,
    posts.caption,
    comments.content
FROM users
INNER JOIN posts
    ON users.user_id = posts.user_id
INNER JOIN comments
    ON posts.post_id = comments.post_id;

1. Users connect to Posts
2. Posts connect to Comments
3. We retrieve information from all three tables ✅

---

7️⃣ SQL Aggregations 📈

Your mentor might ask:

• How many users are registered?
• How many posts exist?
• What's the average number of likes?
• Which user has the most posts?

For this we need aggregate functions: calculations that turn many rows into a single result.

Function	Purpose
COUNT()	Counts rows / non-NULL values
SUM()	Adds numeric values
AVG()	Calculates the average
MIN()	Finds the smallest value
MAX()	Finds the largest value

7.1 🔢 COUNT()

SELECT COUNT(*) FROM users;

Difference:

• COUNT(*) → counts all rows
• COUNT(age) → counts only rows where age is NOT NULL

7.2 ➕ SUM, AVG, MIN, MAX

Imagine a payments table:

payment_id	amount
1	500
2	1000
3	1500

SELECT SUM(amount) FROM payments;  -- 3000
SELECT AVG(amount) FROM payments;  -- 1000
SELECT MIN(amount) FROM payments;  -- 500
SELECT MAX(amount) FROM payments;  -- 1500

7.3 🗂️ GROUP BY

How many posts has each user created?

post_id	user_id
101	1
102	1
103	1
104	2
105	2
106	3

SELECT
    user_id,
    COUNT(*) AS total_posts
FROM posts
GROUP BY user_id;

user_id	total_posts
1	3
2	2
3	1

GROUP BY user_id puts rows with the same user_id into one group, and COUNT(*) counts rows in each group. AS total_posts gives the result a readable name (an alias).

7.4 🚧 HAVING

We only want users with more than two posts:

SELECT
    user_id,
    COUNT(*) AS total_posts
FROM posts
GROUP BY user_id
HAVING COUNT(*) > 2;

user_id	total_posts
1	3

❓ WHERE vs HAVING (a classic interview question!)

• WHERE filters individual rows, before grouping
• HAVING filters groups, after GROUP BY

SELECT
    user_id,
    COUNT(*) AS total_posts
FROM posts
WHERE post_id > 100
GROUP BY user_id
HAVING COUNT(*) > 2;

Logical processing order:

1. FROM: get the source table
2. WHERE: filter rows
3. GROUP BY: form groups
4. HAVING: filter groups
5. SELECT: produce columns
6. ORDER BY: sort
7. LIMIT: restrict output

📝 This is the logical order, not necessarily the database's physical execution order.

---

8️⃣ Database Normalization 🧹 (VERY IMPORTANT!)

First, the problem normalization solves. A beginner might design one giant table:

user_id	username	post_id	caption	comment	commenter
1	Ishu	101	Hello	Nice!	Rahul
1	Ishu	101	Hello	Amazing!	Priya
1	Ishu	102	Travel	Beautiful!	Rahul
2	Rahul	103	Coding	Great!	Ishu

See the problems? 👀

• 🔁 Data redundancy: Ishu's username and her post caption repeat in multiple rows. This wastes storage and complicates maintenance.
• ✏️ Update anomaly: if Ishu changes her username, you must update every row. Miss one and your data becomes inconsistent.
• ➕ Insertion anomaly: a new user who hasn't posted yet can't be stored cleanly, because the table expects post data.
• 🗑️ Deletion anomaly: if Rahul's only post is deleted, you may accidentally lose his user details too.

These are called database anomalies, and normalization prevents them.

8.1 What is Normalization?

Normalization is organizing data into well-structured tables to:

• ✅ Reduce unnecessary duplication
• ✅ Prevent insertion, update and deletion anomalies
• ✅ Keep data consistent
• ✅ Improve data integrity
• ✅ Represent relationships correctly

We split large, poorly structured tables into smaller related ones. The foundational normal forms are 1NF, 2NF and 3NF.

8.2 1️⃣ First Normal Form (1NF)

Rule: every cell holds a single atomic value. No repeating groups or multi-valued columns.

❌ Not in 1NF

student_id	name	phone_numbers
1	Ishu	9876, 8765
2	Rahul	7654, 6543

Multiple phone numbers are crammed into one cell.

✅ Fixed (1NF)

students

student_id	name
1	Ishu
2	Rahul

student_phones

student_id (FK)	phone_number
1	9876
1	8765
2	7654
2	6543

Now every cell has exactly one value.

🧠 1NF = one value per cell, no repeating groups.

8.3 2️⃣ Second Normal Form (2NF)

Rule: the table must be in 1NF, and every non-key attribute must depend on the whole candidate key, not just part of it.

Two terms first:

• Non-key attribute: a column that isn't part of the candidate key
• Partial dependency: a non-key attribute depends on only part of a composite key

❌ Not in 2NF — enrollment with composite key (student_id, course_id):

student_id	course_id	student_name	course_name
1	101	Ishu	Python
1	102	Ishu	SQL
2	101	Rahul	Python

• student_name depends only on student_id
• course_name depends only on course_id

Neither depends on the complete key. That's a partial dependency, and it causes repeated names.

✅ Fixed (2NF)

students

student_id (PK)	student_name
1	Ishu
2	Rahul

courses

course_id (PK)	course_name
101	Python
102	SQL

enrollments

student_id (PK, FK)	course_id (PK, FK)
1	101
1	102
2	101

🧠 2NF = 1NF + no partial dependency.

💡 Nuance: if a table's candidate key is a single column, partial dependency is impossible.

8.4 3️⃣ Third Normal Form (3NF)

Rule: the table must be in 2NF, and have no transitive dependency of non-key attributes on the key.

A transitive dependency means a non-key attribute depends on another non-key attribute instead of directly on the key.

❌ Not in 3NF — employees (PK: employee_id):

employee_id	employee_name	department_id	department_name
1	Ishu	10	Engineering
2	Rahul	10	Engineering
3	Priya	20	HR

Dependencies:

employee_id   → employee_name
employee_id   → department_id
department_id → department_name

So employee_id → department_id → department_name. The department name depends on the department, not directly on the employee. If "Engineering" becomes "Software Engineering", you must update many rows and might miss one.

✅ Fixed (3NF)

employees

employee_id (PK)	employee_name	department_id (FK)
1	Ishu	10
2	Rahul	10
3	Priya	20

departments

department_id (PK)	department_name
10	Engineering
20	HR

Now the department name is stored once.

🧠 3NF = 2NF + no transitive dependency.

📖 Formal definition: for every non-trivial functional dependency X → Y, either X is a superkey, or Y is a prime attribute (part of some candidate key).

8.5 🧬 Functional Dependency (FD)

A functional dependency means one attribute determines another. It's written:

A → B        (read: "A determines B")

Examples:

employee_id   → employee_name
department_id → department_name

If we know employee_id, we can determine the employee_name (assuming employee_id is unique). FDs are the foundation for spotting partial and transitive dependencies.

8.6 📊 1NF vs 2NF vs 3NF

Normal form	Main rule	Problem eliminated
1NF	Atomic values, no repeating groups	Multi-valued cells
2NF	No partial dependency	Partial dependency
3NF	No transitive dependency of non-key attributes	Transitive dependency

Think of it as three stages:

1. 1NF: organize the cells. Store single values, not lists.
2. 2NF: organize the dependencies. Every non-key column depends on the whole key.
3. 3NF: remove indirect dependencies. Each entity gets its own table.

8.7 🛡️ Why normalization is a data-integrity principle

Normalization isn't just about saving space. It's about storing each fact in exactly one appropriate place:

• A username belongs to a user
• A caption belongs to a post
• A department name belongs to a department
• A like belongs to a user-post combination

When each fact lives in one place, inconsistent data becomes much harder to create.

⚖️ Note: normalization doesn't mean "split everything always". Real systems sometimes use controlled denormalization for performance, but only when the trade-offs are understood.

---

9️⃣ ACID Properties and Transactions ⚛️

Imagine a banking app. You transfer ₹1,000 to a friend. Two operations must happen:

1. Deduct ₹1,000 from your account
2. Add ₹1,000 to your friend's account

What if the first succeeds, but the app crashes before the second? 😱 Your money is gone and your friend never receives it.

We need guarantees. That's what ACID provides:

• A: Atomicity
• C: Consistency
• I: Isolation
• D: Durability

9.1 📦 What is a transaction?

A transaction is a group of database operations treated as one logical unit of work.

BEGIN;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;

COMMIT;

• BEGIN → starts the transaction
• first UPDATE → deducts money
• second UPDATE → adds money
• COMMIT → makes the changes permanent

If something goes wrong:

ROLLBACK;

ROLLBACK cancels all uncommitted changes in the transaction.

9.2 ⚛️ A: Atomicity ("all or nothing")

A transaction either fully completes or has no effect at all.

✅ SUCCESS (both operations succeed)
   Account A: ₹5,000 → ₹4,000
   Account B: ₹2,000 → ₹3,000

❌ FAILURE (everything is rolled back)
   Account A: ₹5,000 → ₹5,000
   Account B: ₹2,000 → ₹2,000

We must never end up with money deducted from A but not added to B.

9.3 ✅ C: Consistency ("valid state to valid state")

A transaction must keep the database's defined integrity rules intact. The database is valid before and after.

CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    balance    DECIMAL(12,2) CHECK (balance >= 0)
);

If account A has ₹500 and someone runs:

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;

the balance would become -₹500, so the database rejects it because it violates the CHECK constraint.

Consistency rules include primary key uniqueness, foreign key validity, NOT NULL, UNIQUE and CHECK constraints.

⚠️ ACID consistency doesn't mean the database magically understands every business rule. Developers must define the constraints and transaction logic.

9.4 🚦 I: Isolation ("transactions don't interfere incorrectly")

Concurrent transactions should behave correctly without corrupting each other's intermediate work.

Your balance is ₹5,000. Two requests arrive at nearly the same time:

• Transaction A withdraws ₹4,000
• Transaction B withdraws ₹4,000

If both read ₹5,000 and both proceed, you could withdraw more than you have. That's a concurrency problem, and isolation mechanisms control it.

PostgreSQL supports these isolation levels:

Isolation level	General protection
READ UNCOMMITTED	Lowest level; PostgreSQL treats it like Read Committed
READ COMMITTED	Prevents dirty reads (PostgreSQL default)
REPEATABLE READ	Consistent snapshot for the whole transaction; prevents non-repeatable reads
SERIALIZABLE	Strongest; concurrent transactions behave as if run one after another

Key terms:

• 🧟 Dirty read: reading another transaction's uncommitted changes
• 🔄 Non-repeatable read: reading the same row twice and getting different committed values
• 👻 Phantom read: repeating a query and getting extra or missing rows because another transaction inserted/deleted matching rows

PostgreSQL uses MVCC (Multi-Version Concurrency Control), which keeps multiple row versions so readers and writers don't block each other unnecessarily.

Isolation matters most in banking, ticket booking and inventory systems.

9.5 💾 D: Durability ("committed means persisted")

Once a transaction is committed, its changes survive crashes and restarts.

You transfer ₹1,000, the database confirms the commit, and the server crashes right after. After recovery, the transfer must still be there.

PostgreSQL supports durability through Write-Ahead Logging (WAL): changes are recorded in a log before the corresponding data pages are necessarily written to their final location, so the database can recover after a crash.

9.6 🧾 ACID together

Property	Simple meaning	Banking example
Atomicity	All or nothing	Both debit and credit happen
Consistency	Rules remain valid	Balance can't go negative
Isolation	Concurrent operations are controlled	Two withdrawals don't interfere incorrectly
Durability	Committed changes survive failures	A successful transfer stays recorded

Easy memory aid:

• A: All or nothing
• C: Correct database state
• I: Independent execution
• D: Data stays committed

---

🔟 Database Constraints 🚧

A constraint is a rule on a column or table that blocks invalid data.

Imagine a table that allows this:

user_id	email
1	[e-mail]
1	[e-mail]
2	NULL

Duplicate IDs and missing emails! Constraints prevent this.

Constraint	Purpose	Example
PRIMARY KEY	Unique row identifier, no NULL	user_id INTEGER PRIMARY KEY
FOREIGN KEY	Referenced record must exist	user_id INTEGER REFERENCES users(user_id)
UNIQUE	No duplicate values	email VARCHAR(100) UNIQUE
NOT NULL	A value is required	username VARCHAR(50) NOT NULL
CHECK	Enforces a condition	age INTEGER CHECK (age >= 18)
DEFAULT	Value used when none is given	is_active BOOLEAN DEFAULT TRUE

All together:

CREATE TABLE users (
    user_id   INTEGER PRIMARY KEY,
    username  VARCHAR(50)  NOT NULL UNIQUE,
    email     VARCHAR(100) NOT NULL UNIQUE,
    age       INTEGER CHECK (age >= 18),
    is_active BOOLEAN DEFAULT TRUE
);

This is how we enforce data quality at the database level. 🛡️

🧹 Bonus: ON DELETE behavior

What happens to a user's posts when the user is deleted? You decide:

CREATE TABLE posts (
    post_id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL
        REFERENCES users(user_id) ON DELETE CASCADE,
    caption TEXT
);

• ON DELETE CASCADE → deleting a user also deletes their posts
• ON DELETE RESTRICT / NO ACTION → block the delete while posts exist
• ON DELETE SET NULL → keep posts but clear user_id (column must be nullable)

---

1️⃣1️⃣ Putting it together: design an Instagram database 📸

Your mentor says: "Design the database for an Instagram-like app." How do you approach it?

Step 1: identify the entities (real-world things we store data about):

• 👤 User
• 🖼️ Post
• 💬 Comment
• ❤️ Like
• 👥 Follow

Step 2: create the tables.

1. users

Column	Type	Constraint
user_id	INTEGER	PK
username	VARCHAR	UNIQUE, NOT NULL
email	VARCHAR	UNIQUE, NOT NULL
password_hash	TEXT	NOT NULL

2. posts

Column	Type	Constraint
post_id	INTEGER	PK
user_id	INTEGER	FK, NOT NULL
caption	TEXT
created_at	TIMESTAMP

3. comments

Column	Type	Constraint
comment_id	INTEGER	PK
post_id	INTEGER	FK, NOT NULL
user_id	INTEGER	FK, NOT NULL
content	TEXT	NOT NULL

4. likes

Column	Type	Constraint
user_id	INTEGER	PK, FK
post_id	INTEGER	PK, FK
created_at	TIMESTAMP

5. follows

Column	Type	Constraint
follower_id	INTEGER	PK, FK
following_id	INTEGER	PK, FK
created_at	TIMESTAMP

Notice that in follows, both follower_id and following_id reference the users table. That's a self-referencing relationship.

follower_id	following_id
1	2
1	3
2	1

• User 1 follows users 2 and 3
• User 2 follows user 1

The composite primary key prevents following the same person twice, and we add a CHECK so nobody follows themselves:

CHECK (follower_id <> following_id)

1️⃣1️⃣.1 🗺️ How the tables relate

USERS (PK: user_id)
   │ 1 : N
   ▼
POSTS (FK: user_id)
   │ 1 : N
   ▼
COMMENTS (FK: post_id, user_id)

USERS ◄── N : N via LIKES (user_id + post_id) ──► POSTS

USERS ◄── self N : N via FOLLOWS (follower_id + following_id) ──► USERS

• Users → Posts: one-to-many
• Posts → Comments: one-to-many
• Users → Comments: one-to-many
• Posts → Likes: one-to-many
• Users ↔ Posts through Likes: many-to-many
• Users ↔ Users through Follows: self-referencing many-to-many

1️⃣1️⃣.2 🧪 Real SQL queries

Display a post with its creator:

SELECT
    posts.post_id,
    posts.caption,
    users.username
FROM posts
INNER JOIN users
    ON posts.user_id = users.user_id;

Count posts per user (including users with zero posts):

SELECT
    users.username,
    COUNT(posts.post_id) AS total_posts
FROM users
LEFT JOIN posts
    ON users.user_id = posts.user_id
GROUP BY users.user_id, users.username;

🤔 Why LEFT JOIN? To include users with no posts.
🤔 Why COUNT(posts.post_id) and not `COUNT()?* For a user with no posts, LEFT JOIN still creates a row with NULL post columns. COUNT(*) would count that row as 1, but COUNT(posts.post_id) correctly returns 0`.

Top 5 most-liked posts:

SELECT
    posts.post_id,
    posts.caption,
    COUNT(likes.user_id) AS total_likes
FROM posts
LEFT JOIN likes
    ON posts.post_id = likes.post_id
GROUP BY posts.post_id, posts.caption
ORDER BY total_likes DESC
LIMIT 5;

1. Start with posts
2. Join likes
3. Group likes by post
4. Count likes per post
5. Sort by like count
6. Return the top five 🏆

Who does user 1 follow? (bonus)

SELECT users.username
FROM follows
INNER JOIN users
    ON follows.following_id = users.user_id
WHERE follows.follower_id = 1;

This is the kind of query you'll meet in real backend apps.

---

1️⃣2️⃣ How this connects to SQLAlchemy and FastAPI 🔌

You might wonder: "If SQL talks to PostgreSQL, why learn SQLAlchemy?"

• SQL → the language for querying relational databases
• PostgreSQL → the database system that stores and manages the data
• SQLAlchemy → a Python library that lets us work with databases using Python objects and SQL expressions

The SQL table:

CREATE TABLE users (
    user_id  INTEGER PRIMARY KEY,
    username VARCHAR(50)
);

becomes this SQLAlchemy 2.0 ORM model (simplified):

from sqlalchemy import Integer, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "users"

    user_id: Mapped[int] = mapped_column(Integer, primary_key=True)
    username: Mapped[str] = mapped_column(String(50))

SQL concept	SQLAlchemy equivalent
Table	ORM model (a class)
Column	mapped_column
Primary Key	primary_key=True
Foreign Key	ForeignKey()
UNIQUE	unique=True
NOT NULL	nullable=False
Relationship	relationship()
INSERT	session.add()
UPDATE	Modify object attributes
DELETE	session.delete()
COMMIT	session.commit()
ROLLBACK	session.rollback()

Creating a user (with an async session):

new_user = User(username="ishu")
session.add(new_user)
await session.commit()

• session.add() registers the object with the session
• session.commit() commits the transaction (that's ACID in action! ⚛️)
• SQLAlchemy generates and runs the SQL for you

🧠 SQLAlchemy doesn't replace SQL. It's a Pythonic way to work with relational databases. Understand the underlying SQL, because SQLAlchemy still talks to the database using SQL.

🔄 How a request flows through your backend

React frontend       → user clicks "Create Post"
      ▼
FastAPI endpoint     → receives and validates the request
      ▼
Service layer        → applies business logic
      ▼
Repository layer     → runs database operations via SQLAlchemy
      ▼
PostgreSQL           → stores the row in the posts table

That's why relational database fundamentals come before SQLAlchemy and Alembic. 🎯

---

1️⃣3️⃣ Common interview questions 🎤

Q1. Primary Key vs Foreign Key?
A primary key uniquely identifies a row in its own table. A foreign key references a key in another table and maintains referential integrity.

Q2. Can a table have multiple primary keys?
Only one PRIMARY KEY constraint, but it can span multiple columns (a composite primary key).

Q3. Can a foreign key contain NULL?
Yes, unless the column is NOT NULL. A NULL foreign key means no related record is specified.

Q4. DELETE vs TRUNCATE vs DROP?

DELETE	TRUNCATE	DROP
Removes selected rows	Removes all rows	Removes the whole table
Supports WHERE	No WHERE	Removes structure too
Table remains	Table remains	Table is gone

Q5. WHERE vs HAVING?
WHERE filters rows before grouping. HAVING filters groups after grouping.

Q6. Why do we need normalization?
To reduce redundancy, prevent insertion/update/deletion anomalies and improve data integrity.

Q7. What is a junction table?
A table implementing a many-to-many relationship using foreign keys to the two related tables.

Q8. 2NF vs 3NF?
2NF removes partial dependencies. 3NF removes transitive dependencies of non-key attributes.

Q9. What happens when a transaction fails?
Uncommitted changes can be rolled back, preventing partial application.

Q10. Atomicity vs Durability?
Atomicity: all operations succeed together or none do. Durability: once committed, changes survive failures.

Q11. What is referential integrity?
A guarantee that foreign key values always point to existing rows (or are NULL).

Q12. INNER JOIN vs LEFT JOIN?
INNER JOIN returns only matching rows. LEFT JOIN returns all left rows plus matches, with NULLs where there's no match.

---

1️⃣4️⃣ Practice quiz 🧪

Answer without scrolling up! (Answers are at the bottom.)

1. Which key uniquely identifies each row in a table?

• A) Foreign Key
• B) Primary Key
• C) Alternate Table
• D) Index

2. One user creates multiple posts. What relationship is this?

• A) One-to-One
• B) One-to-Many
• C) Many-to-Many
• D) None

3. Which JOIN returns all records from the left table?

• A) INNER JOIN
• B) RIGHT JOIN
• C) LEFT JOIN
• D) CROSS JOIN

4. Which normal form eliminates partial dependency?

• A) 1NF
• B) 2NF
• C) 3NF
• D) None

5. Which normal form eliminates transitive dependencies of non-key attributes?

• A) 1NF
• B) 2NF
• C) 3NF
• D) BCNF only

6. Which SQL clause filters aggregated groups?

• A) WHERE
• B) ORDER BY
• C) HAVING
• D) LIMIT

7. Which ACID property means all operations happen or none happen?

• A) Consistency
• B) Atomicity
• C) Isolation
• D) Durability

8. What does COMMIT do?

• A) Deletes a table
• B) Starts a transaction
• C) Makes transaction changes permanent
• D) Reads records

9. What implements a many-to-many relationship?

• A) Only a Primary Key
• B) A junction table
• C) A single text column
• D) DROP TABLE

10. Why do we use a Foreign Key?

• A) To sort records
• B) To maintain referential integrity
• C) To encrypt passwords
• D) To create indexes automatically in every database

👀 Click to reveal answers

1. B
2. B
3. C
4. B
5. C
6. C
7. B
8. C
9. B
10. B

---

🧾 Final revision sheet

Concept	What to remember
Relational model	Data organized into related tables
Table	Collection of related records
Row	One record
Column	One attribute
Primary Key	Unique row identifier
Foreign Key	References another table's key
1:1	One record relates to at most one other (FK + UNIQUE)
1:N	One record relates to many (FK on the many side)
N:N	Many relate to many through a junction table
INNER JOIN	Only matching rows
LEFT JOIN	All left rows + matching right rows
RIGHT JOIN	All right rows + matching left rows
FULL JOIN	All rows from both sides
GROUP BY	Groups similar records
HAVING	Filters grouped results
Normalization	Organizes data and reduces anomalies
1NF	Atomic values
2NF	No partial dependency
3NF	No transitive dependency of non-key attributes
Atomicity	All or nothing
Consistency	Preserves database rules
Isolation	Controls concurrent transactions
Durability	Committed changes persist

---

🚀 What should you learn next?

You now have the conceptual foundation of relational database development. Learn in this order:

1. SQL CRUD operations and filtering
2. Joins and subqueries
3. GROUP BY, HAVING and aggregate queries
4. Constraints and indexes
5. Transactions and isolation levels
6. SQLAlchemy Core and ORM
7. Alembic migrations

💡 My biggest advice: don't just memorize definitions of INNER JOIN, 1NF, 2NF, 3NF and ACID. Create real tables, insert rows, join them and watch what happens. That's when it all clicks. ❤️

---

🙌 Enjoyed this post?

• ❤️ Drop a reaction
• 🦄 Save it for revision
• 💬 Comment with questions, and I'll answer them!

Follow me so you don't miss it! 🔔
Happy coding! 👩‍💻✨