SQL and Transactions

🗄️ Database Theory: SQL and Transactions

The Story of the Library Kingdom

Imagine a magical library where every book has its perfect spot, every reader can find exactly what they need, and nothing ever gets lost or mixed up. That’s what databases do for computers!

Let’s explore this kingdom together and become database wizards! 🧙‍♂️

📚 Relational Algebra: The Secret Language

What is it?

Think of relational algebra as giving instructions to your robot librarian. You tell it exactly what books to get and how to organize them.

Simple Example:

• “Get me all red books” → The robot collects only red books
• “Now from those, keep only the big ones” → Filters again
• This is how databases think!

The Magic Operations

SELECT (σ) = Filter/Pick
PROJECT (π) = Choose columns
JOIN (⋈) = Combine tables
UNION (∪) = Merge together

Real Life:

• σ = “Show me students older than 10”
• π = “Just show names, not ages”
• ⋈ = “Match students with their classes”

🔤 SQL Fundamentals: Speaking to Databases

What is SQL?

SQL is like talking to your database. It’s the language computers understand when you want to find, add, or change information.

Think of it like this:

• You ask: “Hey database, show me all students!”
• Database replies: Here’s your list!

The Basic Building Blocks

SELECT - What do you want to see?
FROM   - Where should I look?
WHERE  - Any specific conditions?

Example - Finding Your Friends:

SELECT name, age
FROM students
WHERE age = 10;

This says: “Show me names and ages from students who are 10 years old.”

Creating and Managing

CREATE TABLE - Build a new shelf
INSERT INTO  - Add new books
UPDATE       - Fix mistakes
DELETE       - Remove items

🔍 SQL Queries: Asking Questions

Simple Questions

-- Find all pets
SELECT * FROM pets;

-- Find only dogs
SELECT * FROM pets
WHERE type = 'dog';

-- Find big dogs
SELECT * FROM pets
WHERE type = 'dog'
AND size = 'large';

Sorting Your Results

-- A to Z by name
SELECT * FROM students
ORDER BY name ASC;

-- Youngest to oldest
SELECT * FROM students
ORDER BY age DESC;

Picking Unique Items

-- What types of pets do we have?
SELECT DISTINCT type
FROM pets;

Result: dog, cat, fish (no repeats!)

🤝 Joins: Connecting Tables

Why Do We Need Joins?

Imagine two lists:

• List 1: Students (name, class_id)
• List 2: Classes (class_id, teacher)

To see “Which teacher does each student have?”, we need to connect these lists!

Types of Joins (The Family Reunion)

graph TD
    A["INNER JOIN"] --> B["Only matching pairs"]
    C["LEFT JOIN"] --> D["All from left + matches"]
    E["RIGHT JOIN"] --> F["All from right + matches"]
    G["FULL JOIN"] --> H["Everyone, matched or not"]

INNER JOIN - Best Friends Only

SELECT students.name, classes.teacher
FROM students
INNER JOIN classes
ON students.class_id = classes.id;

Shows only students who have a class.

LEFT JOIN - Everyone from First Table

SELECT students.name, classes.teacher
FROM students
LEFT JOIN classes
ON students.class_id = classes.id;

Shows ALL students, even if they have no class (shows NULL).

Quick Reference

📊 Aggregation: Counting and Grouping

The Counting Wizards

These functions summarize your data:

COUNT(*) - How many?
SUM()    - Total of numbers
AVG()    - Average
MAX()    - Biggest
MIN()    - Smallest

Examples That Make Sense

Counting Students:

SELECT COUNT(*) as total
FROM students;
-- Result: 50

Average Age:

SELECT AVG(age) as avg_age
FROM students;
-- Result: 11.5

GROUP BY: Organizing Results

How many students in each class?

SELECT class, COUNT(*) as students
FROM students
GROUP BY class;

Result:

HAVING: Filter After Grouping

Show only classes with more than 15 students:

SELECT class, COUNT(*) as students
FROM students
GROUP BY class
HAVING COUNT(*) > 15;

💰 Transactions: All or Nothing

The Bank Story

Imagine moving $100 from your piggy bank to your friend’s:

1. Take $100 from your piggy bank
2. Put $100 in friend’s piggy bank

What if the power goes out after step 1? Your money is GONE! 😱

Transactions prevent this disaster!

How Transactions Work

BEGIN TRANSACTION;
  UPDATE accounts
  SET balance = balance - 100
  WHERE name = 'Me';

  UPDATE accounts
  SET balance = balance + 100
  WHERE name = 'Friend';
COMMIT;

If anything fails → ROLLBACK (undo everything!)

graph TD
    A["Start Transaction"] --> B["Step 1: Take money"]
    B --> C["Step 2: Give money"]
    C --> D{All OK?}
    D -->|Yes| E["COMMIT - Save it!"]
    D -->|No| F["ROLLBACK - Undo all!"]

⚛️ ACID Properties: The Four Promises

The Safety Shield

ACID makes sure your data is ALWAYS safe:

A - Atomicity (All or Nothing)

The Cookie Rule:

• Either eat the WHOLE cookie, or don’t eat it at all
• No half-eaten cookies left behind!

Transaction = Complete fully OR Undo completely

C - Consistency (Rules Always Apply)

The Game Rule:

• If the rule says “maximum 5 players”
• You can NEVER have 6 players
• Database stops you!

I - Isolation (No Peeking)

The Test Room Rule:

• When you’re taking a test
• No one can see your answers until you’re done
• Each person works alone

D - Durability (Saved Forever)

The Diary Rule:

• Once you write with permanent ink
• It stays there even if lights go out
• Committed data survives crashes

graph TD
    A["ACID"] --> B["Atomicity: All or nothing"]
    A --> C["Consistency: Rules enforced"]
    A --> D["Isolation: No interference"]
    A --> E["Durability: Permanent save"]

🚦 Concurrency Control: Managing Traffic

The Library Rush

What if 100 people want the same book at once? Chaos!

Concurrency control = Traffic lights for data

Locking Strategies

Shared Lock (Reading):

• Multiple people can READ the same book
• Like many people looking at a museum painting

Exclusive Lock (Writing):

• Only ONE person can WRITE/MODIFY
• Like only one person editing a document

The Problems We Avoid

graph TD
    A["Without Control"] --> B["Dirty Read: Reading unfinished changes"]
    A --> C[Lost Update: Overwriting others' work]
    A --> D["Phantom Read: Data appearing/disappearing"]

How Isolation Levels Help

Higher level = Safer but slower

🗂️ Indexes: The Speedsters

The Problem

Finding “Harry Potter” in a library with 1 million books…

• Without index: Check EVERY book (slow! 🐢)
• With index: Go straight to “H” section (fast! 🚀)

What is an Index?

CREATE INDEX idx_name
ON students(name);

Now finding students by name is super fast!

Types of Indexes

B-Tree Index (Most Common):

• Like a family tree for your data
• Great for: =, <, >, BETWEEN

        [M]
       /   \
    [D]     [T]
   /   \   /   \
 [A] [G] [P] [Z]

Hash Index:

• Like a magic spell that goes directly to data
• Great for: = (exact matches only)

When to Use Indexes

✅ Good for:

• Columns in WHERE clauses
• Columns used in JOINs
• Columns sorted with ORDER BY

❌ Avoid for:

• Small tables
• Columns that change a lot
• Columns with many duplicates

The Trade-off

More indexes = Faster reads 📖
             = Slower writes ✍️
             = More storage space 💾

Choose wisely! ⚖️

🎯 Quick Summary

🌟 You Did It!

You’ve just learned the foundations of database theory!

These concepts power:

• 🎮 Every game you play
• 📱 Every app on your phone
• 🏪 Every online store
• 🏦 Every bank in the world

Now go forth and organize data like a true wizard! 🧙‍♂️✨