Relational Databases Deep Dive

What is a Relational Database?

A relational database organizes data into tables (relations) with rows (tuples) and columns (attributes). Tables are connected through relationships defined by keys.

Simple Analogy

Think of a relational database like a filing cabinet with multiple drawers (tables). Each drawer contains folders (rows) with information (columns). The folders are connected by reference numbers (keys) that link related information across drawers.

---

ACID Properties: The Foundation

ACID is an acronym for four essential properties that guarantee reliable database transactions:

---

A: Atomicity

Atomicity means a transaction is all-or-nothing. Either all operations succeed, or all fail (rollback).

Example: Transferring money between accounts. If deducting from Account A succeeds but adding to Account B fails, both operations are rolled back. You can’t have money disappear!

---

C: Consistency

Consistency ensures the database always remains in a valid state. All constraints, rules, and relationships are maintained.

Example: If a table has a constraint “age must be > 0”, the database will reject any transaction that tries to insert a negative age, keeping the database consistent.

---

I: Isolation

Isolation ensures concurrent transactions don’t interfere with each other. Each transaction sees a consistent snapshot of the data.

Example: Two users updating the same account balance simultaneously. Isolation ensures each transaction sees a consistent view and updates don’t interfere.

---

D: Durability

Durability guarantees that once a transaction is committed, it persists even if the system crashes.

Example: After you commit a bank transfer, even if the server crashes immediately after, your transaction is safely stored and will be restored when the system recovers.

---

Database Normalization

Normalization is the process of organizing data to reduce redundancy and eliminate anomalies. It involves splitting tables and creating relationships.

Why Normalize?

---

Normal Forms

Normalization follows normal forms (NF), each eliminating specific types of redundancy:

---

Normalization Example

Before Normalization (Problems):

order_id	customer_name	customer_email	product_name	price	quantity
1	Alice	[email protected]	Laptop	1000	1
2	Alice	[email protected]	Mouse	20	2
3	Bob	[email protected]	Keyboard	50	1

Problems:

• ❌ Customer info repeated (redundancy)
• ❌ If Alice changes email, need to update multiple rows
• ❌ If we delete order #2, we lose Alice’s info

After Normalization (3NF):

Customers Table:

customer_id	name	email
1	Alice	[email protected]
2	Bob	[email protected]

Orders Table:

order_id	customer_id	product_name	price	quantity
1	1	Laptop	1000	1
2	1	Mouse	20	2
3	2	Keyboard	50	1

Benefits:

• ✅ Customer info stored once
• ✅ Update email in one place
• ✅ Delete order without losing customer info

---

Database Indexing

An index is a data structure that speeds up data retrieval. It’s like a book’s index—instead of scanning every page, you look up the index to find the exact location.

How Indexes Work

Analogy: Finding a word in a dictionary:

• Without index: Read every page (full table scan)
• With index: Use alphabetical index to jump directly (index lookup)

---

Types of Indexes

---

Index Trade-offs

Aspect	Benefit	Cost
Read Speed	✅ Much faster queries	❌ Slower writes (must update index)
Storage	-	❌ Extra storage space
Maintenance	-	❌ Index must be maintained

Index Best Practices

• Index frequently queried columns (WHERE, JOIN, ORDER BY)
• Don’t over-index (too many indexes slow down writes)
• Use composite indexes for multi-column queries
• Monitor index usage and remove unused indexes

---

LLD ↔ HLD Connection

How relational database concepts affect your class design:

Entity Classes Map to Tables

Python

Entity Class Mapping to Table

from dataclasses import dataclass
from typing import Optional
from datetime import datetime

@dataclass
class User:
    """Maps to 'users' table"""
    id: Optional[int] = None  # Primary key
    name: str
    email: str  # Has unique index
    created_at: datetime = datetime.now()

@dataclass
class Order:
    """Maps to 'orders' table"""
    id: Optional[int] = None  # Primary key
    user_id: int  # Foreign key -> users.id
    total: float
    created_at: datetime = datetime.now()

    # Relationship (not in DB, but in code)
    user: Optional[User] = None

Java

Entity Class Mapping to Table

import java.time.LocalDateTime;

public class User {
    // Maps to 'users' table
    private Integer id;  // Primary key
    private String name;
    private String email;  // Has unique index
    private LocalDateTime createdAt = LocalDateTime.now();

    // Getters and setters...
}

public class Order {
    // Maps to 'orders' table
    private Integer id;  // Primary key
    private Integer userId;  // Foreign key -> users.id
    private Double total;
    private LocalDateTime createdAt = LocalDateTime.now();

    // Relationship (not in DB, but in code)
    private User user;

    // Getters and setters...
}

Repository Pattern for Data Access

Python

Repository Pattern

from abc import ABC, abstractmethod
from typing import List, Optional

class UserRepository(ABC):
    @abstractmethod
    def find_by_id(self, user_id: int) -> Optional[User]:
        """Find user by primary key"""
        pass

    @abstractmethod
    def find_by_email(self, email: str) -> Optional[User]:
        """Find user by email (uses index)"""
        pass

    @abstractmethod
    def save(self, user: User) -> User:
        """Save user (handles insert/update)"""
        pass

class SQLUserRepository(UserRepository):
    def __init__(self, db_connection):
        self.db = db_connection

    def find_by_email(self, email: str) -> Optional[User]:
        # Uses email index for fast lookup
        query = "SELECT * FROM users WHERE email = %s"
        # Execute query...

Java

Repository Pattern

import java.util.*;

public interface UserRepository {
    Optional<User> findById(Integer userId);  // Uses primary key index
    Optional<User> findByEmail(String email);  // Uses email index
    User save(User user);  // Handles insert/update
}

public class SQLUserRepository implements UserRepository {
    private Connection dbConnection;

    public Optional<User> findByEmail(String email) {
        // Uses email index for fast lookup
        String query = "SELECT * FROM users WHERE email = ?";
        // Execute query...
    }
}

ACID Transactions in Code

Python

ACID Transaction

class AccountService:
    def __init__(self, db_connection):
        self.db = db_connection

    def transfer(self, from_account_id: int, to_account_id: int, amount: float):
        # Atomicity: All or nothing
        with self.db.transaction():  # BEGIN TRANSACTION
            try:
                # Deduct from source
                self.db.execute(
                    "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                    (amount, from_account_id)
                )

                # Add to destination
                self.db.execute(
                    "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                    (amount, to_account_id)
                )

                # Consistency: Check constraints
                if amount < 0:
                    raise ValueError("Amount must be positive")

                # Commit (Durability: persisted)
                self.db.commit()  # COMMIT
            except Exception:
                # Rollback on any error (Atomicity)
                self.db.rollback()  # ROLLBACK
                raise

Java

ACID Transaction

import java.sql.*;

public class AccountService {
    private Connection dbConnection;

    public void transfer(int fromAccountId, int toAccountId, double amount) {
        // Atomicity: All or nothing
        try {
            dbConnection.setAutoCommit(false);  // BEGIN TRANSACTION

            // Deduct from source
            PreparedStatement stmt1 = dbConnection.prepareStatement(
                "UPDATE accounts SET balance = balance - ? WHERE id = ?"
            );
            stmt1.setDouble(1, amount);
            stmt1.setInt(2, fromAccountId);
            stmt1.executeUpdate();

            // Add to destination
            PreparedStatement stmt2 = dbConnection.prepareStatement(
                "UPDATE accounts SET balance = balance + ? WHERE id = ?"
            );
            stmt2.setDouble(1, amount);
            stmt2.setInt(2, toAccountId);
            stmt2.executeUpdate();

            // Consistency: Check constraints
            if (amount < 0) {
                throw new IllegalArgumentException("Amount must be positive");
            }

            // Commit (Durability: persisted)
            dbConnection.commit();  // COMMIT
        } catch (SQLException e) {
            // Rollback on any error (Atomicity)
            dbConnection.rollback();  // ROLLBACK
            throw new RuntimeException(e);
        }
    }
}

---

Key Takeaways

Remember

• ACID Properties: Atomicity (all-or-nothing), Consistency (valid state), Isolation (concurrent transactions), Durability (persistent)
• Normalization: Organize data to eliminate redundancy and anomalies (1NF, 2NF, 3NF, BCNF)
• Indexing: Data structures that speed up queries (primary, unique, composite, full-text)
• Trade-offs: Indexes speed reads but slow writes; normalization reduces redundancy but increases joins
• LLD Implementation: Entity classes map to tables, repository pattern for data access, transactions ensure ACID
• Choose wisely: Balance normalization vs performance, indexes vs write speed

---

What’s Next?

Now that you understand relational databases, let’s explore how isolation levels control what concurrent transactions can see:

Next up: Database Isolation Levels — Learn how read committed, repeatable read, and serializable isolation levels prevent race conditions.

Practice Exercise

Design a normalized database schema for an e-commerce system. What tables do you need? What are the relationships? What indexes would you create?