Prerequisites: Understand SQL Server architecture and indexing. See DB 01 Networking Fundamentals and DB 03 B-Tree Index.

We’ve covered the hardware layer (disk, memory) and the search engine (B-Tree). Now let’s explore the software logic layer — the magic developers work with every day.

graph LR
    subgraph "Hardware Layer"
        DISK[Hard Disk]
        RAM[Memory]
    end
    
    subgraph "Search Engine"
        BTREE[B-Tree Index]
    end
    
    subgraph "Software Layer"
        NORM[Normalization]
        JOIN[Joins]
        ACID[Transactions]
        LOCK[Locks]
        SP[Stored Procedures]
        VIEW[Views]
    end
    
    DISK --> BTREE
    RAM --> BTREE
    BTREE --> NORM
    NORM --> JOIN
    JOIN --> ACID
    ACID --> LOCK
    
    style NORM fill:#3498db,color:#fff
    style JOIN fill:#3498db,color:#fff
    style ACID fill:#e74c3c,color:#fff
    style LOCK fill:#e74c3c,color:#fff
    style SP fill:#27ae60,color:#fff
    style VIEW fill:#27ae60,color:#fff

Part A: Normalization & Joins — The Art of Organization

This is the essence of Relational in Relational Database.

1. The Problem: The Giant Messy Spreadsheet

Imagine storing all your data in ONE giant Excel sheet:

Problems:

• ❌ Redundancy: Alice’s phone appears 3 times
• ❌ Update Anomaly: If Alice changes phone, must update 3 rows
• ❌ Delete Anomaly: If we delete all Alice’s orders, we lose her contact info
• ❌ Insert Anomaly: Can’t add a customer without an order

2. Normalization: Don’t Put All Eggs in One Basket

Normalization = Split data into separate, focused tables.

graph TD
    subgraph "Before: One Giant Table"
        BIG[Orders + Customers + Products All Mixed]
    end
    
    subgraph "After: Normalized Tables"
        CUST[Customers Table]
        PROD[Products Table]
        ORD[Orders Table]
    end
    
    BIG -->|Split| CUST
    BIG -->|Split| PROD
    BIG -->|Split| ORD
    
    CUST -.->|CustomerID| ORD
    PROD -.->|ProductID| ORD
    
    style BIG fill:#e74c3c,color:#fff
    style CUST fill:#27ae60,color:#fff
    style PROD fill:#27ae60,color:#fff
    style ORD fill:#27ae60,color:#fff

Normal Forms (1NF → 2NF → 3NF)

Why 3NF Matters: The Update Anomaly

Transitive Dependency is the trickiest concept. Here’s a concrete example:

┌─────────────────────────────────────────────────────────────────┐
│              3NF Violation: Update Anomaly                       │
├─────────────────────────────────────────────────────────────────┤ 
│                                                                 │
│   BAD: Customer table with City stored directly                 │
│   ┌──────────────────────────────────────────────────────┐      │
│   │ CustomerID │ Name  │ ZipCode │ City                  │      │
│   │ 1          │ Alice │ 90210   │ Beverly Hills         │      │
│   │ 2          │ Bob   │ 90210   │ Beverly Hills         │      │
│   │ 3          │ Carol │ 90210   │ Beverly Hills         │      │
│   │ 4          │ Dave  │ 90210   │ Beverly Hills         │      │
│   │ 5          │ Eve   │ 90210   │ Beverly Hills         │      │
│   └──────────────────────────────────────────────────────┘      │
│                                                                 │
│   Problem: City depends on ZipCode, not CustomerID!             │
│   (CustomerID → ZipCode → City = Transitive Dependency)         │
│                                                                 │
│   Scenario: Post office renames "Beverly Hills" to "Los Angeles"│
│   ❌ Must update 5 rows (error-prone, slow)                     │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

3NF Solution: Separate ZipCode table

-- ZipCodes table
CREATE TABLE ZipCodes (
    ZipCode CHAR(5) PRIMARY KEY,
    City NVARCHAR(100)
);

-- Customers only stores ZipCode (FK)
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100),
    ZipCode CHAR(5) REFERENCES ZipCodes(ZipCode)
);

After Normalization (3 Tables)

Customers Table:

Products Table:

Orders Table:

Benefits:

• ✅ Alice’s phone stored ONCE
• ✅ Update one place, done everywhere
• ✅ No data anomalies

3. Joins: Gluing the Tables Back Together

Since data is split, we need JOIN to combine them for reports.

Types of Joins

INNER JOIN: Both Sides Must Match

SELECT Orders.OrderID, Customers.Name, Products.Name, Orders.Quantity
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Products ON Orders.ProductID = Products.ProductID;

Result: Only rows where CustomerID and ProductID exist in both tables.

LEFT JOIN: Left Table Always Included

SELECT Customers.Name, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result: ALL customers, even those with no orders (OrderID = NULL).

Part B: Transactions & ACID — Life or Death Together

This is database’s greatest invention — why banks trust SQL Server.

4. The Problem: Bank Transfer Gone Wrong

Scenario: Alice transfers $100 to Bob.

Step 1: Deduct $100 from Alice's account
Step 2: Add $100 to Bob's account

What if power fails after Step 1?

• Alice: -$100 ✓
• Bob: +$0 ✗
• $100 vanished into thin air!

5. Solution: Transaction

Wrap both steps into ONE transaction:

BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 'Alice';
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 'Bob';

-- If both succeed:
COMMIT;

-- If anything fails:
-- ROLLBACK; (automatically reverts everything)

sequenceDiagram
    participant App as Application
    participant DB as SQL Server
    
    App->>DB: BEGIN TRANSACTION
    App->>DB: UPDATE Alice -100
    DB-->>DB: Tentatively done (not permanent)
    App->>DB: UPDATE Bob +100
    DB-->>DB: Tentatively done
    
    alt Both Succeed
        App->>DB: COMMIT
        DB-->>App: ✅ Permanent!
    else Any Failure
        App->>DB: ROLLBACK
        DB-->>App: ❌ Reverted everything
    end

6. ACID Properties

Atomicity in Action

┌─────────────────────────────────────────────────────────────────┐
│              Transaction Atomicity                              │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   BEGIN TRANSACTION                                             │
│   ┌───────────────────────────────────────────────────────┐     │
│   │  Step 1: Alice -100  ✓                                │     │
│   │  Step 2: Bob +100    ✓                                │     │
│   │  Step 3: Log entry   ✗ (disk error!)                  │     │
│   └───────────────────────────────────────────────────────┘     │
│                                                                 │
│   Result: ROLLBACK (all 3 steps undone)                         │
│   Alice still has original balance                              │
│   Bob still has original balance                                │
│   As if nothing happened                                        │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

Part C: Locks & Concurrency — The Queue System

What happens when 1000 people try to buy the last train ticket?

7. The Problem: Race Condition

Time 1: User A reads TicketsRemaining = 1
Time 2: User B reads TicketsRemaining = 1
Time 3: User A updates TicketsRemaining = 0 (bought it!)
Time 4: User B updates TicketsRemaining = 0 (also bought it!)

Result: 2 tickets sold, but only 1 existed! 💀

8. Solution: Locks

When User A modifies data, SQL Server puts a Lock on it.

sequenceDiagram
    participant A as User A
    participant DB as SQL Server
    participant B as User B
    
    A->>DB: SELECT Tickets (gets Shared Lock)
    B->>DB: SELECT Tickets (gets Shared Lock)
    A->>DB: UPDATE Tickets (requires Exclusive Lock)
    DB-->>A: ✅ Granted (converts to Exclusive)
    B->>DB: UPDATE Tickets (requires Exclusive Lock)
    DB-->>B: ⏳ WAIT (blocked by A's lock)
    A->>DB: COMMIT (releases lock)
    DB-->>B: ✅ Now you can proceed
    B->>DB: UPDATE... but Tickets = 0!
    DB-->>B: ❌ No tickets left

Lock Types

9. Deadlock: The Standoff

graph LR
    A[User A] -->|Holds Lock on| TableX[Table X]
    A -->|Waiting for| TableY[Table Y]
    B[User B] -->|Holds Lock on| TableY
    B -->|Waiting for| TableX
    
    style A fill:#e74c3c,color:#fff
    style B fill:#e74c3c,color:#fff

Deadlock:

• User A holds Table X, waiting for Table Y
• User B holds Table Y, waiting for Table X
• Neither can proceed — stuck forever!

SQL Server’s Solution: Detect deadlock, pick a victim, kill their transaction, let the other proceed.

-- Error 1205: Transaction was deadlocked and has been chosen as the victim.

Avoiding Deadlocks

Isolation Levels: Controlling Lock Behavior

Developers control locking through Isolation Levels. This is the “dial” between speed and safety:

-- Set isolation level for current session
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Or for a specific transaction
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Your queries here (maximum locking)
COMMIT;

Practical Rule: Stick with READ COMMITTED (default) unless you have a specific reason to change it. SERIALIZABLE guarantees consistency but can cause severe blocking.

Part D: Stored Procedures — The Menu Button

Instead of writing complex SQL every time, pre-save it on the server.

10. The Problem: Sending Long SQL Across Network

-- Client sends this 500-character query every time:
SELECT o.OrderID, c.Name, c.Phone, p.ProductName, p.Price, o.Quantity,
       (p.Price * o.Quantity) AS Total
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN Products p ON o.ProductID = p.ProductID
WHERE o.OrderDate > '2024-01-01'
AND c.Country = 'Taiwan'
ORDER BY o.OrderDate DESC;

Problems:

• ❌ Network traffic: 500 bytes every call
• ❌ Security risk: Exposes table structure
• ❌ Performance: SQL must be parsed/compiled each time

11. Solution: Stored Procedure

-- Create once, store on server:
CREATE PROCEDURE GetRecentOrders
    @Country NVARCHAR(50),
    @StartDate DATE
AS
BEGIN
    SELECT o.OrderID, c.Name, c.Phone, p.ProductName, p.Price, o.Quantity,
           (p.Price * o.Quantity) AS Total
    FROM Orders o
    INNER JOIN Customers c ON o.CustomerID = c.CustomerID
    INNER JOIN Products p ON o.ProductID = p.ProductID
    WHERE o.OrderDate > @StartDate
    AND c.Country = @Country
    ORDER BY o.OrderDate DESC;
END

-- Client only sends this:
EXEC GetRecentOrders @Country = 'Taiwan', @StartDate = '2024-01-01';

sequenceDiagram
    participant C as Client
    participant S as SQL Server
    
    Note over C,S: Without Stored Procedure
    C->>S: SELECT o.OrderID, c.Name... (500 bytes)
    S-->>S: Parse → Compile → Execute
    S-->>C: Results
    
    Note over C,S: With Stored Procedure
    C->>S: EXEC GetRecentOrders (30 bytes)
    S-->>S: Already compiled! Just execute
    S-->>C: Results

Benefits Summary

Modern Perspective: While SPs remain powerful, modern ORMs (Entity Framework, Dapper, Hibernate) often generate SQL dynamically at the application layer. Today, SPs are primarily used for:

• 📊 Complex reporting with many JOINs and aggregations
• ⏰ Batch jobs running overnight (ETL, data cleanup)
• 🏦 High-security systems (banking) where direct table access is forbidden
• ⚡ Performance-critical hot paths where every millisecond counts

Part E: Views — The Filter / Virtual Table

A View is a fake table that shows only what you want to show.

12. The Problem: Sensitive Data Exposure

Employees Table:

Problem: Intern needs to look up employee phone numbers, but shouldn’t see salaries or SSN.

13. Solution: View

-- Create a View that hides sensitive columns:
CREATE VIEW EmployeeDirectory AS
SELECT EmployeeID, Name, Department, Phone
FROM Employees;

-- Intern queries the View:
SELECT * FROM EmployeeDirectory;

What intern sees:

Salary and SSN? Never existed! 🕶️

graph LR
    subgraph "Real Table (Hidden)"
        RT[Employees: ID, Name, Dept, Phone, Salary, SSN]
    end
    
    subgraph "View (Visible to Intern)"
        VW[EmployeeDirectory: ID, Name, Dept, Phone]
    end
    
    RT -->|Filter| VW
    
    style RT fill:#e74c3c,color:#fff
    style VW fill:#27ae60,color:#fff

14. Types of Views

Complex View Example

-- Instead of writing complex JOIN every time:
CREATE VIEW SalesReport AS
SELECT 
    c.Name AS CustomerName,
    p.ProductName,
    o.Quantity,
    (p.Price * o.Quantity) AS TotalAmount,
    o.OrderDate
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN Products p ON o.ProductID = p.ProductID;

-- Now anyone can query easily:
SELECT * FROM SalesReport WHERE OrderDate > '2024-01-01';

Summary: Quick Reference

The 5 Core Concepts

When to Use What

Common Mistakes

💡 Practice Questions

Conceptual

1. Explain the difference between 1NF, 2NF, and 3NF. What problems does each normalization level solve?

2. What is an ACID transaction? Describe each property with a real-world banking example.

3. Compare INNER JOIN vs LEFT JOIN. When would you use each?

4. What is the difference between a View and a Stored Procedure?

Hands-on

-- Given this denormalized table, normalize it to 3NF:
-- Orders(OrderID, CustomerName, CustomerPhone, ProductName, ProductPrice, Quantity)
-- 
-- HINT: Consider that one order may contain MULTIPLE products.
-- You'll need a junction table to handle the many-to-many relationship.

-- Write a transaction that transfers $100 from Account A to Account B
-- Include proper error handling with ROLLBACK

-- Customers table (eliminates customer data redundancy)
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100),
    CustomerPhone NVARCHAR(20)
);

-- Products table (eliminates product data redundancy)
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    ProductPrice DECIMAL(10,2)
);

-- Orders table (references Customers)
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID)
);

-- OrderItems table (junction for Order-Product relationship)
CREATE TABLE OrderItems (
    OrderID INT FOREIGN KEY REFERENCES Orders(OrderID),
    ProductID INT FOREIGN KEY REFERENCES Products(ProductID),
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

BEGIN TRY
    BEGIN TRANSACTION;
    
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 'A';
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 'B';
    
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;
END CATCH

Scenario

1. Design Decision: A junior developer wants to store customer orders as a comma-separated list in a single column (e.g., “Laptop,Mouse,Keyboard”). Explain why this violates 1NF and what problems it will cause.

2. Debugging: A user reports that their UPDATE statement changed more rows than expected. The table has no primary key. What happened and how would you prevent this?