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.

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.

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)

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.

Lock Types

9. Deadlock: The Standoff

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';

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! 🕶️

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?