02 - DBMS Architecture

🎯 Learning Objectives

• Diagram the major components of a DBMS (storage manager, query processor, transaction manager)
• Describe how a query flows from submission to result
• Explain the role of the storage manager, buffer manager, and file manager
• Differentiate between centralized, client-server, parallel, and distributed architectures
• List database system internals (parsing, optimization, evaluation)

📋 Prerequisites

• 01 - DBMS Introduction — Levels of abstraction, DDL/DML, database engine overview

📖 Core Content

2.1 Intuition: What Happens When You Run a Query?

1. Parsing & Translation: SQL is parsed, validated (table/column existence), and translated to relational algebra
2. Optimization: The optimizer considers multiple evaluation plans and chooses the cheapest (by I/O cost, CPU, etc.)
3. Execution: The executor runs the chosen plan, calling the storage manager to read/write data
4. Result: Results are formatted and returned to the user

Why This Matters: Understanding this pipeline helps you write efficient queries. A poorly written query can be 1000× slower than a good one — even if they return the same results!

2.2 The Query Processor

Parsing and Translation

• Checks SQL syntax
• Validates that tables and columns exist
• Translates SQL into relational algebra (internal representation)
• Relational algebra is procedural — it tells the system how to execute

Optimization

sql
SELECT name
FROM instructor, teaches
WHERE instructor.ID = teaches.ID AND teaches.year = 2018;

• Cartesian product → σ (year=2018) → σ (ID match) → π name
• σ (year=2018) on teaches → ⋈ on ID → π name
• Many other plans The optimizer estimates costs using:
• Number of I/O operations (dominant factor)
• CPU processing time
• Network communication (for distributed systems)

Key insight: The cost difference between a good plan and a bad plan can be enormous (seconds vs. hours).

Evaluation

• Performs selections, projections, joins
• May involve sorting, grouping, aggregation
• Returns result tuples to the user

2.3 The Storage Manager

Buffer Manager

1. Check if the page is already in the buffer (cache hit)
2. If not, read from disk (cache miss)
3. If buffer is full, evict a page using a policy (LRU, Clock, etc.) Buffer management is critical because disk I/O is the dominant cost in database operations.

2.4 Transaction Management

pseudo
1. Read A          -- balance_A = 1000
2. Write A - 500   -- balance_A = 500
3. Read B          -- balance_B = 200
4. Write B + 500   -- balance_B = 700
5. Commit

The Transaction Manager ensures ACID properties:

• Atomicity: Crash after step 2 but before step 4 → $500 disappears (inconsistent)
• Isolation: Two transfers from same account simultaneously → both read $1000, both write$500 → $1000 lost!

The Concurrency-Control Manager

• Locking protocols (shared/exclusive locks, two-phase locking)
• Timestamp ordering (older transactions get priority)
• Optimistic methods (validate at commit time)

2.5 Database Architecture Types

2.6 Database System Internals: The Big Picture

2.7 Database Administrators and Users

• Create and modify schemas
• Grant/revoke permissions
• Monitor performance
• Schedule backups
• Handle crash recovery

📐 Key Formulas / Concepts

⚠️ Common Pitfalls

Pitfall 1: Thinking Query Optimization is Magic

Pitfall 2: Confusing Centralized and Client-Server

Pitfall 3: Underestimating Buffer Manager Impact

📝 Practice Questions

Q1. What are the three components of a query processor? Briefly explain each.

Answer

1. Parser & Translator: Checks SQL syntax, validates table/column names, and translates SQL to relational algebra (internal representation)
2. Optimizer: Generates multiple execution plans and picks the cheapest based on cost estimates (I/O operations, CPU, network)
3. Executor: Runs the chosen plan step-by-step, coordinating with the storage manager to read/write data

Q2. List the ACID properties and what each ensures.

Answer

• Atomicity: Transaction completes fully or not at all (all-or-nothing)
• Consistency: Transaction brings database from one valid state to another
• Isolation: Concurrent transactions appear to run serially (no interference)
• Durability: Committed changes persist even after system failures

Q3. What does the buffer manager do? Why is it important?

Answer

The buffer manager manages data movement between disk and main memory. It:

• Checks if requested data is already in memory (cache hit)
• Loads data from disk if not in memory (cache miss)
• Evicts pages when memory is full using policies like LRU

It's important because disk I/O is the dominant cost in database operations. Effective buffering (high cache hit rate) can make queries 10-100× faster.

Q4. Compare centralized and distributed database architectures.

Answer

Q5. Why is the cost of a bad query plan potentially enormous?

Answer

A naive plan might:

• Read every block of every table (full table scans)
• Compute Cartesian products before filtering
• Not use available indexes

For a 10-million-row table, this could mean reading gigabytes of data from disk, taking minutes or hours. A good plan with index usage and optimal join order might take milliseconds.

Q6. What is the role of the concurrency-control manager?

Answer

The concurrency-control manager ensures isolation by coordinating simultaneous access to data. It:

• Manages locks on data items (shared/exclusive)
• Ensures serializability (concurrent execution produces same result as some serial execution)
• Detects and resolves deadlocks
• Uses protocols like two-phase locking, timestamp ordering, or optimistic concurrency control

Without it, concurrent transactions could interfere (lost updates, dirty reads, incorrect summaries).

Q7. List three tasks performed by a Database Administrator.

Answer

1. Schema definition: Creating and modifying database schemas
2. Security management: Granting/revoking user privileges
3. Backup and recovery: Scheduling backups, handling crash recovery

Other valid answers: Performance monitoring, storage management, user account management.

Q8. A bank transfer transaction debits ₹500 from account A and credits ₹500 to account B. After the debit but before the credit, the system crashes. What does the recovery manager do?

Answer

The recovery manager checks the transaction log:

• It finds a <T_start> record
• It finds a <T, A, old_value, new_value> for the debit
• It does NOT find a <T_commit> or <T_abort> record

Since the transaction didn't complete, the recovery manager performs an undo: it restores account A's balance to its original value (before the debit). This ensures atomicity — the incomplete transaction is fully rolled back.

🔗 Cross-References

• Next Topic: 03 - Relational Model
• Previous Topic: 01 - DBMS Introduction
• Related: BSCS2003 (MAD 1) — Three-tier architecture, web application design
• Related: BSCS4022 (OS) — Storage hierarchy, buffer management
• Textbook: Silberschatz, Korth, Sudarshan — Chapter 1 (Database System Architecture) Join Discord Previous01 - DBMS IntroductionNext03 - Relational Model