DBMS Interview Questions — Complete Notes#

Source set: 18 questions from the InterviewBit DBMS PDF (answered here in my own words, expanded with examples), plus extra topics that commonly come up in DBMS interviews/coursework but weren't in that PDF (joins, indexing, concurrency control, distributed DBs, etc).

---

📑 Table of Contents#

• [[#1. Foundations]]
• [[#2. Data Modeling - ER Model & Abstraction]]
• [[#3. Keys, Constraints & Normalization]]
• [[#4. Transactions & Concurrency Control]]
• [[#5. Architecture]]
• [[#6. SQL - Joins, Subqueries & Practical Query Patterns]]
• [[#7. Indexing, Storage & File Organization]]
• [[#8. Distributed Databases & NoSQL (Bonus/Advanced)]]
• [[#9. Quick-Fire Revision Table]]

Legend: 🟩 = from the original PDF (re-explained in my own words) · 🟦 = added by me, common in interviews/coursework but not in the original set.

---

1. Foundations#

🟩 Q1. What is a DBMS? What is RDBMS? (with examples)

A DBMS (Database Management System) is software that sits between an application and the actual stored data, giving you a controlled way to create, read, update, and delete data instead of working with raw files directly. It handles concerns like consistency, security, concurrent access, and backup/recovery so application code doesn't have to reinvent all of that.

A RDBMS (Relational DBMS) is a specific type of DBMS that organizes data into tables (relations) made of rows and columns, with relationships between tables enforced through keys. Almost every DBMS you'll touch day-to-day in a job (MySQL, PostgreSQL, Oracle, SQL Server) is actually an RDBMS — "DBMS" is the umbrella term, "RDBMS" is the relational flavor of it (as opposed to hierarchical, network, or NoSQL document/graph/key-value models).

sqlCopy
-- RDBMS: data lives in structured tables with explicit relationships
CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(50),
  course_id INT REFERENCES courses(course_id)
);

[!tip] Interview Angle Q: Is every DBMS an RDBMS? A: No — DBMS is the general category (any system that manages databases, including non-relational ones like MongoDB). RDBMS specifically means the data model is relational (tables + keys + SQL). MongoDB is a DBMS but not an RDBMS.

---

🟩 Q2. What is a database?

A database is an organized, persistent collection of related data, structured so it can be efficiently stored, queried, and updated. In the relational world this means a set of tables, where each row (tuple) is one record and each column (attribute) holds one specific piece of information about that record.

codeCopy
students table
+------------+----------+------------+
| student_id | name     | course_id  |   <- attributes / columns
+------------+----------+------------+
| 1          | Rakshit  | 101        |   <- one tuple / row
| 2          | Aman     | 102        |
+------------+----------+------------+

---

🟩 Q3. Why is DBMS better than a traditional file-based system?

File-based systems (data stored directly in flat files, managed by individual applications) run into several structural problems that a DBMS is specifically designed to solve:

[!tip] Interview Angle Q: Give one concrete failure mode of a file-based system a DBMS prevents. A: Two processes appending to the same flat file simultaneously can corrupt it or silently lose one write — there's no built-in locking/transaction model. A DBMS's transaction + locking mechanism guarantees one write completes cleanly before/after the other, never interleaved destructively.

---

🟩 Q4. Advantages of a DBMS

• Data sharing — many users/apps can access the same database concurrently and safely.
• Reduced redundancy — normalization centralizes data instead of duplicating it across files.
• Data independence — you can change how data is physically stored without rewriting the applications that use it (see logical vs physical independence below).
• Integrity constraints — NOT NULL, UNIQUE, CHECK, foreign keys enforce valid data automatically.
• Backup & recovery — built-in tooling for automated backups and crash recovery.
• Security — authentication, role-based permissions (GRANT/REVOKE), and encryption for sensitive fields.
• Concurrent access control — locking/MVCC lets multiple transactions run safely at the same time without corrupting data.

[!warning] Gotcha — Logical vs Physical Data Independence Physical independence: you can change storage details (e.g. switch storage engine, add an index, move to different disks) without changing the schema applications see. Logical independence: you can change the logical schema (e.g. add a new table/column) without breaking existing applications querying the old structure — generally harder to achieve than physical independence.

---

🟩 Q5. Languages in DBMS (DDL, DML, DCL, TCL)

sqlCopy
-- DDL
CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50));
ALTER TABLE employees ADD COLUMN salary DECIMAL(10,2);

-- DML
INSERT INTO employees VALUES (1, 'Rakshit', 50000);
UPDATE employees SET salary = 55000 WHERE id = 1;

-- DCL
GRANT SELECT, INSERT ON employees TO 'analyst_user';
REVOKE INSERT ON employees FROM 'analyst_user';

-- TCL
BEGIN;
UPDATE employees SET salary = salary * 1.1;
SAVEPOINT before_bonus;
UPDATE employees SET salary = salary + 1000;
ROLLBACK TO before_bonus; -- undoes only the bonus update
COMMIT;

[!tip] Interview Angle Q: Is TRUNCATE DDL or DML? Why does that matter? A: It's DDL (even though it removes data, like DELETE) — because it actually resets the table's storage/structure internally (deallocates pages) rather than removing rows one at a time. That's also why TRUNCATE typically can't be selectively WHERE-filtered and historically couldn't be rolled back in some databases (modern PostgreSQL/SQL Server can roll it back within a transaction, but it's still classified as DDL because of how it operates).

---

🟩 Q6. ACID Properties

sqlCopy
-- Classic atomicity example: bank transfer — both updates must succeed, or neither should
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A'; -- debit
UPDATE accounts SET balance = balance + 100 WHERE id = 'B'; -- credit
COMMIT; -- if the system crashes between the two UPDATEs, atomicity guarantees
        -- the whole transaction rolls back rather than leaving A debited but B not credited

[!tip] Interview Angle Q: Which ACID property is most directly enforced by locks/concurrency control, and which by the transaction log? A: Isolation is primarily enforced via locking/MVCC (controlling what concurrent transactions can see/touch). Durability is primarily enforced via the write-ahead/transaction log (changes are logged to durable storage before being confirmed, so they can be replayed after a crash).

---

🟩 Q7. Is NULL the same as blank space or zero?

No — these are three fundamentally different things:

sqlCopy
SELECT * FROM students WHERE phone_number IS NULL;  -- correct way to check for NULL
SELECT * FROM students WHERE phone_number = '';      -- different! checks for an actual empty string
-- phone_number = NULL  ❌ always evaluates to UNKNOWN, never TRUE — classic SQL gotcha

[!warning] Gotcha NULL = NULL evaluates to NULL (unknown), not TRUE — you can never compare for NULL using =. Always use IS NULL / IS NOT NULL. This also means NOT IN subqueries containing a NULL can silently return zero rows — a very common real bug.

---

🟦 Bonus — OLTP vs OLAP

🟦 Bonus — Schema vs Instance

• Schema — the overall design/structure of the database (table definitions, columns, constraints) — changes rarely.
• Instance — the actual data present in the database at one specific moment in time — changes constantly as rows are inserted/updated/deleted.

[!tip] Interview Angle Q: If you ALTER TABLE to add a column, did you change the schema or the instance? A: The schema — you changed the structural definition. The data already in the table is an instance that now reflects the updated schema (often with NULL/default values in the new column for existing rows).

---

2. Data Modeling — ER Model & Abstraction#

🟩 Q8. What is Data Warehousing?

A data warehouse is a large, centralized store that pulls data from multiple source systems (different operational databases, logs, external feeds), cleans/transforms it, and stores it in one place optimized for analysis and reporting rather than day-to-day transactions. The pipeline that moves data into it is called ETL (Extract, Transform, Load) — or ELT when transformation happens after loading, which is more common with modern cloud warehouses.

codeCopy
[Orders DB] ─┐
[Users DB]  ─┼─► ETL (Extract, Transform, Load) ─► Data Warehouse ─► BI dashboards, reporting, OLAP
[Logs/CDN]  ─┘

A data warehouse typically stores historical data (years of records) and is optimized for big aggregate queries ("total revenue by region per quarter"), unlike operational databases which are optimized for fast, small, frequent transactions.

[!tip] Interview Angle Q: Why not just run analytics queries directly on the production database? A: Heavy analytical queries (large scans, joins, aggregations) would compete for resources with live transactional traffic, potentially slowing down or locking the production system. A separate data warehouse with a denormalized, analytics-friendly schema avoids that and can be scaled/tuned independently.

---

🟩 Q9. Levels of Data Abstraction

DBMS architecture is typically described in 3 levels, each hiding implementation details from the layer "above" it:

1. Physical level — the lowest level. Describes how data is actually stored on disk (file structures, indexes, block layout). Hidden from everyone except the DB engine itself.
2. Logical / Conceptual level — describes what data is stored and the relationships between it (tables, columns, constraints) — this is what schema designers and backend developers work with.
3. View / External level — describes only the part of the database a specific user/application needs to see, hiding the rest. A SQL VIEW is a literal implementation of this level.

sqlCopy
-- View level in action: hides the full schema, exposes only what's needed
CREATE VIEW student_public_info AS
SELECT name, course_id FROM students; -- hides student_id, ssn, etc. from whoever queries this view

[!tip] Interview Angle Q: If you change the physical storage engine (e.g. switch indexing strategy) without changing table definitions, which level of abstraction were you working at, and what principle does this demonstrate? A: The physical level — and this demonstrates physical data independence: the logical/view levels above remain completely unaffected.

---

🟩 Q10. Entity-Relationship (E-R) Model — Entity, Entity Type, Entity Set

The E-R model is a diagrammatic way to design a database by modeling real-world things as entities and the connections between them as relationships, before translating that design into actual tables.

• Entity — a single real-world object with attributes, e.g. one specific student.
• Entity Type — the general category/template, e.g. "Student" as a concept with attributes like student_id, name. This is what eventually becomes a table.
• Entity Set — the actual collection of all entities of one entity type that currently exist in the database, e.g. all the rows currently in the students table.

codeCopy
Entity Type: Student (student_id, name, dob)
Entity Set:  { (1, "Rakshit", ...), (2, "Aman", ...), (3, "Priya", ...) }
Entity:      (1, "Rakshit", ...)   <- just one of them

[!tip] Interview Angle Q: Entity Type vs Entity Set — aren't they basically the same thing? A: Entity Type is the schema/template (column definitions) — it's static. Entity Set is the actual data conforming to that template at a point in time — it changes as rows are added/removed. Same relationship as table-definition vs table-contents.

---

🟩 Q11. Types of Relationships Between Tables

sqlCopy
-- M:N requires a junction table (no way to model it with FKs on just the two main tables)
CREATE TABLE enrollments (
  student_id INT REFERENCES students(student_id),
  course_id  INT REFERENCES courses(course_id),
  PRIMARY KEY (student_id, course_id)
);

-- Self-referencing FK
CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  name VARCHAR(50),
  manager_id INT REFERENCES employees(emp_id)
);

[!tip] Interview Angle Q: Why can't you implement a many-to-many relationship with just a foreign key column on one of the two tables? A: A single FK column can only point to one row in the other table, so it can express "many A → one B" (or vice versa), not "many A → many B" simultaneously. A junction/bridge table with two FKs (one to each side) is required so each pairing gets its own row.

---

🟩 Q12. Intension vs Extension

• Intension (= the database schema) — the structural definition: table names, column names/types, constraints. Set once at design time, changes rarely.
• Extension (= a snapshot/instance) — the actual set of tuples present at a given moment. Changes constantly with every INSERT/UPDATE/DELETE.

This is conceptually identical to the Schema vs Instance distinction from Section 1 — "intension/extension" is just the more formal/academic terminology for the same idea, often used interchangeably with it in textbooks.

---

🟦 Bonus — ER Diagram Notation Cheat Sheet

🟦 Bonus — Strong vs Weak Entity

• Strong entity — has its own primary key and can exist independently. E.g. Student.
• Weak entity — has no primary key of its own; it's identified only in combination with the primary key of a related ("owner") strong entity, via a partial key + identifying relationship. E.g. a Dependent of an Employee — a dependent only makes sense in the context of "whose dependent."

sqlCopy
CREATE TABLE dependents (
  emp_id INT REFERENCES employees(emp_id), -- owner entity's key
  dependent_name VARCHAR(50),               -- partial key
  PRIMARY KEY (emp_id, dependent_name)       -- composite key = owner key + partial key
);

🟦 Bonus — Generalization, Specialization & Aggregation

• Specialization — top-down: starting from one general entity (Employee) and splitting it into specialized sub-types (Manager, Engineer) with extra attributes each.
• Generalization — bottom-up: the reverse — noticing common attributes across separate entities (Car, Truck) and factoring them into a shared parent entity (Vehicle).
• Aggregation — treating a relationship itself as a higher-level entity so it can participate in further relationships. E.g. "an employee works on a project" (a relationship) can itself be related to "a manager monitors that work" — the works on relationship is abstracted/aggregated into something monitors can point to.

🟦 Bonus — Attribute Types & Participation Constraints

• Simple — atomic, can't be divided further (age).
• Composite — can be broken into sub-parts (name → first_name + last_name).
• Derived — computed from another attribute (age derived from dob), often not stored directly.
• Multivalued — can hold more than one value for a single entity (phone_numbers).

Participation constraint (in a relationship): Total participation means every entity in the set must participate in the relationship (e.g. every employee must belong to a department). Partial means participation is optional (e.g. not every employee manages a project).

---

3. Keys, Constraints & Normalization#

🟩 Q13. DELETE vs TRUNCATE (+ bonus: DROP)

sqlCopy
DELETE FROM students WHERE student_id = 5; -- removes just one row, logged, slow-ish
TRUNCATE TABLE students;                     -- removes ALL rows fast, no WHERE allowed
DROP TABLE students;                          -- removes the table definition itself, data + schema gone

[!tip] Interview Angle Q: Why does TRUNCATE typically reset an auto-increment counter while DELETE doesn't? A: TRUNCATE deallocates the table's storage and effectively recreates an empty table internally (not logging row-by-row), which resets associated sequence/identity state as a side effect. DELETE removes individual rows via normal logged operations, leaving the sequence counter untouched so the next insert continues from where it left off.

---

🟩 Q15. Normalization vs Denormalization

• Normalization — splitting data into multiple related tables to eliminate redundancy and keep each piece of data stored in exactly one place. Improves data integrity, reduces storage waste and the risk of inconsistent updates — but requires more JOINs to reassemble data for reads.
• Denormalization — deliberately reintroducing redundancy (merging tables back, duplicating columns) to make reads faster, at the cost of more complex/riskier writes (multiple places to keep in sync). Common in analytics/reporting schemas and read-heavy systems.

[!tip] Interview Angle Q: When would you deliberately denormalize a normalized schema? A: When read performance matters far more than write performance/storage efficiency — e.g. a reporting dashboard querying millions of rows where joining 5 normalized tables on every page load is too slow; pre-joining/duplicating some data into a wider, flatter table trades some redundancy for much faster reads.

---

🟩 Q16. Normal Forms (1NF → BCNF) + Functional Dependency Concepts

First, the dependency vocabulary these forms are built on:

• Functional Dependency (A → B) — the value of attribute B is fully determined by the value of attribute A (given A, there's only ever one possible B).
• Partial Dependency — a non-key attribute depends on only part of a composite primary key (not the whole key).
• Transitive Dependency — a non-key attribute depends on another non-key attribute, rather than directly on the primary key (A → B → C, where C depends on A only through B).
• Multivalued Dependency — one attribute determines a set of values for another, independent of any third attribute (used for 4NF).

The normal forms themselves:

sqlCopy
-- Violates 1NF: phone column holds multiple values in one field
-- student_id | name | phones
-- 1          | Amit | "9999999999, 8888888888"

-- Fixed (1NF): one phone per row
-- student_id | phone
-- 1          | 9999999999
-- 1          | 8888888888

codeCopy
-- 2NF example: composite key (student_id, course_id), but course_name only depends on course_id (partial dependency)
-- student_id | course_id | course_name | grade
-- Splitting course_name into its own `courses` table removes the partial dependency.

-- 3NF example: student_id -> zip_code -> city (transitive dependency)
-- student_id | zip_code | city
-- Splitting zip_code -> city into its own table removes the transitive dependency.

[!tip] Interview Angle Q: Why does BCNF exist when 3NF already removes transitive dependencies — what extra case does it catch? A: 3NF can still allow anomalies when a table has multiple overlapping candidate keys, where a non-candidate-key attribute determines part of a candidate key. BCNF tightens the rule to "every determinant must be a candidate key," closing that edge case 3NF leaves open. In practice this only shows up with somewhat unusual key overlaps, which is why 3NF is the common "good enough" target in most real schemas.

Q: Most schemas in practice stop at which normal form, and why? A: 3NF — it removes the vast majority of redundancy/anomalies with manageable complexity. BCNF/4NF/5NF address increasingly rare edge cases and can hurt read performance (more joins) for diminishing integrity benefit, so they're more of an academic/edge-case concern than a default target.

---

🟩 Q17. Types of Keys

sqlCopy
CREATE TABLE enrollments (
  student_id INT,
  course_id  INT,
  enrolled_on DATE,
  PRIMARY KEY (student_id, course_id),       -- composite primary key
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE,  -- unique key, NULL allowed (e.g. for users without email yet)
  username VARCHAR(50)
);

[!tip] Interview Angle Q: Primary Key vs Unique Key — if both enforce uniqueness, why have both? A: A table can have only one primary key (the chosen main identifier, no NULLs allowed, usually clustered/indexed as the main lookup path), but it can have multiple unique keys for other columns that also need uniqueness (like email or username) while still tolerating NULL in cases where the value might not be known yet.

---

🟦 Bonus — Constraints & Referential Integrity Actions

sqlCopy
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,                          -- NOT NULL constraint
  status VARCHAR(20) DEFAULT 'pending',                -- DEFAULT constraint
  quantity INT CHECK (quantity > 0),                    -- CHECK constraint
  customer_ref INT REFERENCES customers(customer_id)
    ON DELETE CASCADE                                   -- referential integrity action
    ON UPDATE CASCADE
);

ON DELETE / ON UPDATE referential actions — what happens to child rows when the referenced parent row is deleted/updated:

• CASCADE — automatically delete/update the matching child rows too.
• SET NULL — set the foreign key column in child rows to NULL.
• RESTRICT / NO ACTION — block the delete/update entirely if matching child rows exist.

[!tip] Interview Angle Q: When would ON DELETE CASCADE be dangerous? A: When the "child" data is actually independently valuable and shouldn't disappear just because a parent record was removed — e.g. cascading deletes from users to orders would silently destroy order history (often needed for accounting/legal reasons) just because a user account was deleted. SET NULL or a soft-delete pattern is usually safer for such cases.

---

4. Transactions & Concurrency Control#

🟩 Q14. Locks — Shared vs Exclusive

A lock is how a DBMS prevents two transactions from stepping on each other's changes to the same piece of data at the same time.

• Shared Lock (S-lock) — taken for reading data. Multiple transactions can hold a shared lock on the same item simultaneously (everyone can read together), but no one can write while any shared lock is held.
• Exclusive Lock (X-lock) — taken for writing data. Only one transaction can hold it, and no other transaction (reader or writer) can access that data until it's released.

codeCopy
Transaction A: SELECT * FROM accounts WHERE id = 1;  -- takes Shared lock, fine to share with other readers
Transaction B: SELECT * FROM accounts WHERE id = 1;  -- also Shared lock, both A and B can read together

Transaction C: UPDATE accounts SET balance = 100 WHERE id = 1; -- needs Exclusive lock
-- C must WAIT until A and B release their shared locks before it can acquire the exclusive lock

[!tip] Interview Angle Q: Can a transaction "upgrade" a shared lock to an exclusive lock? A: Yes — this is common (e.g. SELECT ... FOR UPDATE patterns: read first, then decide to write). The transaction must wait until it's the only one holding a shared lock on that item before it can upgrade, which is actually a classic cause of deadlocks if two transactions try to upgrade simultaneously while both holding shared locks on each other's target.

---

🟦 Bonus — Transaction States

A transaction moves through these states during its lifecycle:

codeCopy
Active → Partially Committed → Committed
   ↓              ↓
 Failed  ───→  Aborted

• Active — currently executing.
• Partially committed — finished executing, but changes not yet permanently saved.
• Committed — successfully finished, changes are durable.
• Failed — something went wrong (constraint violation, system error) before commit.
• Aborted — transaction was rolled back, database restored to its state before the transaction started.

---

🟦 Bonus — Schedules: Serial vs Serializable vs Concurrent

• Serial schedule — transactions run one completely after another, no interleaving. Always correct, but slow (no concurrency benefit at all).
• Concurrent schedule — operations from multiple transactions interleave for better throughput — but risks anomalies if not controlled properly.
• Serializable schedule — a concurrent schedule whose end result is guaranteed equivalent to some serial execution of the same transactions — the gold standard for correctness while still allowing concurrency.

---

🟦 Bonus — Concurrency Control Techniques

1. Two-Phase Locking (2PL) — each transaction has a growing phase (only acquiring locks, never releasing) followed by a shrinking phase (only releasing locks, never acquiring more). Guarantees serializability. Strict 2PL (the common real-world variant) holds all exclusive locks until commit/rollback, avoiding cascading rollbacks.
2. Timestamp Ordering — each transaction gets a timestamp at start; conflicting operations are ordered/rejected based on timestamp order rather than locks, avoiding deadlocks entirely (a transaction trying to violate timestamp order is aborted and restarted with a new timestamp).
3. Optimistic Concurrency Control (OCC) — assume conflicts are rare: let transactions run freely, then validate at commit time that no conflicting changes happened; if a conflict is detected, abort and retry. Good for low-contention, read-heavy workloads.
4. Multiversion Concurrency Control (MVCC) — keep multiple versions of a row; readers see a consistent snapshot without blocking writers, and writers create a new version rather than locking the old one. Used by PostgreSQL, MySQL (InnoDB), Oracle for high concurrency without heavy reader/writer blocking.

[!tip] Interview Angle Q: Why might MVCC be preferred over strict locking for a high-traffic read-heavy app? A: With MVCC, readers never block writers and writers never block readers (each reads/writes its own version/snapshot) — only writer-vs-writer conflicts need resolving. Strict locking would force readers to wait on writers (and vice versa) far more often, hurting throughput under heavy concurrent load.

---

🟦 Bonus — Isolation Levels & Concurrency Anomalies

• Dirty Read — reading uncommitted data from another transaction that might later be rolled back.
• Non-Repeatable Read — re-reading the same row within one transaction gives a different value because another transaction updated and committed it in between.
• Phantom Read — re-running the same query within one transaction returns a different set of rows (not just different values) because another transaction inserted/deleted matching rows in between.

sqlCopy
-- Dirty read scenario (Read Uncommitted only):
-- T1: UPDATE accounts SET balance = 0 WHERE id = 1;          (not committed yet)
-- T2: SELECT balance FROM accounts WHERE id = 1;  -- reads 0, the "dirty" uncommitted value
-- T1: ROLLBACK;                                                -- balance reverts, but T2 already acted on bad data!

[!tip] Interview Angle Q: Why isn't Serializable the default isolation level in most production databases? A: It gives the strongest correctness guarantees but at the cost of the most locking/blocking (or most validation overhead with OCC/MVCC-based serializable implementations), hurting throughput under concurrent load. Most systems default to Read Committed (PostgreSQL, Oracle, SQL Server default) as a practical balance, escalating to stricter levels only for operations that specifically need it.

---

🟦 Bonus — Deadlocks

A deadlock occurs when two (or more) transactions each hold a lock the other needs, and each is waiting for the other to release it — neither can proceed.

codeCopy
T1: locks row A, wants row B
T2: locks row B, wants row A
-- both wait forever, unless something intervenes

Handling strategies:

• Detection — periodically check the "wait-for graph" for cycles; if found, abort one transaction (the "victim," often the one with least work done) to break the cycle.
• Prevention — force transactions to acquire locks in a consistent global order, or use timestamp-based schemes (wait-die / wound-wait) so a cycle can never form in the first place.
• Timeout — simplest approach: if a transaction waits too long for a lock, abort it and retry.

---

🟦 Bonus — Recovery Techniques (Crash Recovery)

• Write-Ahead Logging (WAL) — before any change is applied to the actual database, it's first written to a durable log. After a crash, the log can be replayed to redo committed work and undo uncommitted work.
• Checkpointing — periodically flushing in-memory state + writing a checkpoint marker to the log, so recovery only needs to replay the log from the last checkpoint forward (instead of from the very beginning of time).
• Redo / Undo — on recovery: redo all committed transactions found in the log (to make sure their effects are actually applied), undo all uncommitted ones (to make sure their partial effects are removed) — this redo-then-undo approach is the basis of the widely-used ARIES recovery algorithm.

---

5. Architecture#

🟩 Q18. 2-Tier vs 3-Tier Architecture

• 2-Tier — the client application talks directly to the database server, no middle layer. Simple, but business logic ends up either duplicated across clients or crammed into the database (stored procedures), and it doesn't scale well to many concurrent users.

Client ──────────────► Database

• 3-Tier — adds an application/server layer between the client and the database. The client only talks to the application server (handles business logic, auth, validation), which then talks to the database. More secure (DB never directly exposed to clients), easier to scale, and the standard architecture for virtually all modern web apps.

Client (browser/app) ──► Application Server (business logic, API) ──► Database

[!tip] Interview Angle Q: In a typical full-stack web app (e.g. React frontend + Node/Express backend + Postgres), which tier does each part map to? A: React frontend = presentation tier (client). Express/Node backend = application/logic tier (handles routes, auth, validation, talks to DB). Postgres = data tier. This 3-tier separation is exactly why the frontend never gets direct DB credentials — all data access is mediated through the backend's controlled API.

---

🟦 Bonus — Query Processing Pipeline (How a SQL Query Actually Runs)

When you submit a SQL query, the DBMS engine processes it through roughly these stages:

1. Parser — checks SQL syntax, builds a parse tree, verifies table/column names exist (semantic checks).
2. Query Optimizer — generates multiple possible execution plans (e.g. different join orders, index usage vs full scan) and estimates the cost of each using statistics about the data (row counts, index selectivity), then picks the cheapest plan.
3. Query Executor — actually runs the chosen execution plan against the storage engine, fetching/joining/filtering data and returning results.

sqlCopy
EXPLAIN ANALYZE
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'India';
-- shows you the actual execution plan the optimizer picked: index scan vs seq scan, join algorithm used, estimated vs actual rows, etc.

[!tip] Interview Angle Q: Why might the same query run fast one day and slow another, with no code changes? A: The optimizer's choice of execution plan depends on table statistics (row counts, data distribution) which change as data grows/changes. If stats go stale or the data distribution shifts significantly, the optimizer might pick a worse plan (e.g. a full table scan instead of an index scan) — this is why running ANALYZE/updating statistics periodically matters in production.

---

6. SQL — Joins, Subqueries & Practical Query Patterns#

🟦 Entirely bonus material — the original PDF doesn't cover SQL syntax/joins, but this is consistently one of the heaviest-weighted areas in actual DBMS/SQL interviews.

Joins

sqlCopy
-- Sample tables
-- students(student_id, name, course_id)
-- courses(course_id, course_name)

-- INNER JOIN — only rows that match in BOTH tables
SELECT s.name, c.course_name
FROM students s
INNER JOIN courses c ON s.course_id = c.course_id;

-- LEFT (OUTER) JOIN — all rows from the left table, matched data from right (NULL if no match)
SELECT s.name, c.course_name
FROM students s
LEFT JOIN courses c ON s.course_id = c.course_id; -- students with no course still appear, course_name = NULL

-- RIGHT (OUTER) JOIN — mirror of LEFT JOIN, all rows from the right table
SELECT s.name, c.course_name
FROM students s
RIGHT JOIN courses c ON s.course_id = c.course_id; -- courses with no students still appear

-- FULL OUTER JOIN — all rows from both sides, NULLs where there's no match either way
SELECT s.name, c.course_name
FROM students s
FULL OUTER JOIN courses c ON s.course_id = c.course_id;

-- CROSS JOIN — Cartesian product: every row of A paired with every row of B
SELECT s.name, c.course_name FROM students s CROSS JOIN courses c; -- rarely intentional, watch for accidental ones (missing ON clause)

-- SELF JOIN — joining a table to itself (e.g. find employees and their managers, both from the same `employees` table)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

-- NATURAL JOIN — auto-joins on columns with the SAME NAME in both tables (implicit, no ON clause)
SELECT * FROM students NATURAL JOIN courses; -- risky in practice: silently breaks if column names change

[!warning] Gotcha NATURAL JOIN looks convenient but is fragile in real codebases — if someone adds an unrelated column with a matching name to either table later, the join silently changes behavior with no error. Most style guides ban it in favor of explicit ON.

[!tip] Interview Angle Q: LEFT JOIN with a WHERE filter on the right table's column vs the same filter in the ON clause — same result? A: No, and this is a classic interview trap. Putting the filter in ON only affects which right-side rows match (left rows still all appear, with NULL for unmatched). Putting it in WHERE filters the final joined result, which can silently turn your LEFT JOIN into behaving like an INNER JOIN by dropping left rows that had NULL for that column.

sqlCopy
-- Keeps ALL students, course_name only populated for CS101 matches
SELECT * FROM students s LEFT JOIN courses c ON s.course_id = c.course_id AND c.course_id = 'CS101';
-- Drops students with no CS101 course entirely — behaves like INNER JOIN!
SELECT * FROM students s LEFT JOIN courses c ON s.course_id = c.course_id WHERE c.course_id = 'CS101';

---

Subqueries vs Joins, EXISTS / NOT EXISTS / IN

sqlCopy
-- Subquery in WHERE
SELECT name FROM students
WHERE course_id IN (SELECT course_id FROM courses WHERE course_name = 'DBMS');

-- Same result via JOIN (often more efficient, optimizer can use indexes more flexibly)
SELECT s.name FROM students s
JOIN courses c ON s.course_id = c.course_id
WHERE c.course_name = 'DBMS';

-- Correlated subquery — inner query references the OUTER query's row, re-evaluated per row
SELECT name FROM students s
WHERE EXISTS (
  SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id AND e.grade = 'A'
);

-- NOT EXISTS — students with NO 'A' grade enrollments at all
SELECT name FROM students s
WHERE NOT EXISTS (
  SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id AND e.grade = 'A'
);

[!warning] Gotcha WHERE x NOT IN (SELECT y FROM t) returns zero rows if even one row in the subquery has y IS NULL — because x <> NULL evaluates to UNKNOWN for every comparison, and UNKNOWN propagates through the whole NOT IN check. NOT EXISTS doesn't have this problem — always prefer it over NOT IN when the subquery's column might contain NULLs.

---

WHERE vs GROUP BY vs HAVING

sqlCopy
SELECT course_id, COUNT(*) AS num_students
FROM students
WHERE enrolled_year = 2025      -- filters INDIVIDUAL rows, BEFORE grouping
GROUP BY course_id                -- groups remaining rows by course_id
HAVING COUNT(*) > 10;             -- filters GROUPS, AFTER aggregation

• WHERE filters raw rows before grouping — cannot reference aggregate functions.
• GROUP BY collapses rows sharing the same value(s) into one group per distinct value, for use with aggregate functions.
• HAVING filters groups after aggregation — this is the only place you can filter on an aggregate like COUNT(*) or SUM(...).

[!tip] Interview Angle Q: Why can't you write WHERE COUNT(*) > 10 instead of using HAVING? A: WHERE is evaluated before grouping/aggregation happens — at that point, COUNT(*) doesn't have a value yet per row, only per group, which doesn't exist until after GROUP BY runs. HAVING exists specifically to filter on the result of aggregation, which logically happens later in query execution order.

---

Aggregate Functions

sqlCopy
SELECT
  COUNT(*) AS total_rows,
  COUNT(DISTINCT course_id) AS unique_courses,
  AVG(grade_points) AS avg_gpa,
  SUM(credits) AS total_credits,
  MIN(enrolled_year) AS earliest_year,
  MAX(enrolled_year) AS latest_year
FROM enrollments;

[!warning] Gotcha COUNT(*) counts all rows including those with NULL in every column; COUNT(column_name) only counts rows where that specific column is non-NULL. These can give different numbers on the same table.

---

🟦 Views vs Materialized Views

• View — a saved query, no data stored — runs the underlying query fresh every time it's selected from. Always reflects live data, but adds the underlying query's full cost every time.
• Materialized View — the query result is actually stored physically, like a cached table. Much faster to read, but goes stale until manually/scheduled-ly refreshed.

sqlCopy
CREATE VIEW active_students AS
SELECT * FROM students WHERE status = 'active'; -- always live, recomputed on every SELECT

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders GROUP BY 1; -- stored physically; needs REFRESH MATERIALIZED VIEW to update

[!tip] Interview Angle Q: When would you pick a materialized view over a regular view? A: When the underlying query is expensive (big aggregations/joins) and slightly-stale data is acceptable — e.g. a daily sales dashboard. Refresh it on a schedule (hourly/nightly) instead of recomputing the full aggregation on every single page load.

---

🟦 Stored Procedure vs Function vs Trigger

sqlCopy
-- Trigger example: auto-maintain an updated_at timestamp on every row update
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
SET NEW.updated_at = NOW();

---

🟦 Window Functions (bonus — increasingly common in interviews)

Unlike GROUP BY (which collapses rows), window functions compute a value per row while still having access to a "window" of related rows.

sqlCopy
SELECT
  student_id, course_id, grade_points,
  RANK() OVER (PARTITION BY course_id ORDER BY grade_points DESC) AS rank_in_course,
  AVG(grade_points) OVER (PARTITION BY course_id) AS course_avg
FROM enrollments;
-- Each row keeps its own identity, but also gets a rank/average computed within its course group

[!tip] Interview Angle Q: How is a window function different from GROUP BY + aggregate? A: GROUP BY collapses multiple rows into one summary row per group — you lose row-level detail. A window function (OVER (...)) keeps every original row intact while also attaching a group-aware computed value (rank, running total, moving average) to each one — useful for "top N per category" or "compare this row to its group's average" type queries that GROUP BY alone can't express.

---

7. Indexing, Storage & File Organization#

🟦 Entirely bonus material — not in the original PDF, but indexing in particular is one of the most commonly asked practical DBMS topics.

File Organization Methods

How rows are physically arranged on disk within a table:

• Heap (unordered) file — rows stored in no particular order, typically insertion order. Fast inserts (just append), but lookups require a full scan unless an index exists.
• Sequential (ordered) file — rows physically sorted by some key. Fast range queries on that key, but inserts/deletes are expensive (may require shifting/reordering rows).
• Hash file — rows placed into "buckets" based on a hash of the key. Very fast exact-match lookups, but poor for range queries (hashing destroys ordering).
• Clustered file — rows physically grouped/sorted to match a clustering index (see below) — related rows end up physically near each other on disk.

---

Indexing

An index is an auxiliary data structure that lets the DB find rows without scanning the whole table — trading some extra storage + slower writes for much faster reads on indexed columns.

Clustered vs Non-Clustered:

• Clustered Index — determines the actual physical order of rows on disk. A table can have only one (since rows can only be physically sorted one way). In many RDBMS, the primary key is the clustered index by default.
• Non-Clustered (Secondary) Index — a separate structure that stores pointers to where the actual rows live, without reordering the table itself. A table can have many non-clustered indexes.

sqlCopy
CREATE INDEX idx_students_name ON students(name);  -- non-clustered, speeds up lookups/sorts by name
CREATE UNIQUE INDEX idx_students_email ON students(email);

-- Composite index — order of columns matters a lot
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
-- Efficiently supports: WHERE customer_id = ?  AND  WHERE customer_id = ? AND order_date = ?
-- Does NOT efficiently support: WHERE order_date = ?  alone (leftmost column must be used first)

The underlying structure (B+ Tree) — most RDBMS indexes (MySQL InnoDB, PostgreSQL default, SQL Server) use a B+ tree: a balanced tree where all actual data/pointers live in leaf nodes (linked together for fast range scans), and internal nodes only hold navigation keys. This gives O(log n) lookups and efficient range queries (BETWEEN, >, ORDER BY) — unlike a plain hash index, which only gives fast exact-match lookups.

codeCopy
                [50]
              /      \
          [20,35]    [70,90]
          /  |  \      /  |  \
       leaf nodes (sorted, linked) → actual row pointers / data

Covering Index — an index that includes all the columns a particular query needs, so the engine can answer the query directly from the index without ever touching the actual table rows ("index-only scan") — the fastest possible read path.

[!warning] Gotcha Indexes aren't free — every INSERT/UPDATE/DELETE has to also update every index on that table, so over-indexing a write-heavy table can seriously hurt write performance. Index columns you actually filter/sort/join on frequently, not every column "just in case."

[!tip] Interview Angle Q: You add an index on email but WHERE email = 'x' is still slow — what could be wrong? A: A few common culprits: (1) a function/transformation is applied to the column in the query (WHERE LOWER(email) = 'x') which prevents the plain index from being used unless it's a functional index; (2) the column has low selectivity (e.g. mostly the same value) so the optimizer decides a full scan is actually cheaper; (3) stale table statistics are causing the optimizer to mis-estimate costs; (4) the index wasn't actually created successfully, or is on the wrong combination of columns for a composite-key query. EXPLAIN ANALYZE is the first tool to check which of these is happening.

---

🟦 Hashing in Databases (beyond hash indexes)

• Static hashing — fixed number of buckets; simple but doesn't handle a growing dataset well (some buckets overflow while others stay empty).
• Dynamic/Extendible hashing — the bucket structure grows dynamically as data grows, avoiding the "fixed bucket count" problem of static hashing, at the cost of slightly more complex bookkeeping (a directory of bucket pointers that doubles when needed).

---

8. Distributed Databases & NoSQL (Bonus/Advanced)#

🟦 Entirely bonus material — increasingly common in interviews for full-stack/backend roles, since most real systems aren't single-node relational databases anymore.

CAP Theorem

In a distributed database (data spread across multiple nodes), you can only fully guarantee 2 of these 3 at the same time, in the presence of a network partition:

• Consistency — every read sees the most recent write (all nodes agree).
• Availability — every request gets a response, even if some nodes are down.
• Partition Tolerance — the system keeps working even if network communication between nodes breaks.

Since network partitions will happen eventually in any real distributed system, the practical choice is really between CP (consistent but may refuse requests during a partition) and AP (always responds, but might return stale/inconsistent data during a partition).

[!tip] Interview Angle Q: Does CAP theorem mean a normal single-server MySQL database has to "give up" Consistency or Availability? A: No — CAP only applies once your data is distributed across multiple nodes that need to agree with each other over a network. A single-node database has no partition tolerance concern in the first place, so the trade-off doesn't apply until you scale out (replication, sharding, clustering).

---

ACID vs BASE

• ACID (traditional RDBMS) — prioritizes strict correctness: every transaction is atomic, consistent, isolated, durable, even under concurrency/failures.
• BASE (common in many distributed NoSQL systems) — Basically Available, Soft state, Eventually consistent. Prioritizes availability and partition tolerance over immediate consistency — the system will converge to a consistent state eventually, but might briefly show stale data right after a write.

[!tip] Interview Angle Q: Give a real product scenario where "eventually consistent" (BASE) is an acceptable trade-off. A: A social media "like" counter — if it briefly shows 1,042 likes on one server and 1,041 on another for a fraction of a second after a new like, no real harm is done, and it converges quickly. Compare that to a bank balance, where even momentary inconsistency between replicas could lead to a double-spend — that needs ACID-style strong consistency instead.

---

Sharding vs Partitioning vs Replication

• Partitioning — splitting a large table into smaller pieces (partitions), usually within the same database server, based on some key (range, list, hash). Mainly for manageability/performance on one node.
• Sharding — splitting data across multiple separate servers/nodes, each shard holding a subset of the data — used specifically to scale horizontally beyond what one machine can handle.
• Replication — keeping copies of the same data on multiple nodes (not splitting it) — for fault tolerance and read scalability, not for handling more total data volume.

codeCopy
Partitioning (one server):      Sharding (multiple servers):        Replication (multiple servers):
[Table: years 2020-2025]        [Shard A: users 1-1M]                [Primary: full dataset]
  ├─ partition 2020-2021             on Server 1                          │  (writes go here)
  ├─ partition 2022-2023        [Shard B: users 1M-2M]              ┌─────┼─────┐
  └─ partition 2024-2025             on Server 2                [Replica1][Replica2]
                                                                  (read copies, fault tolerance)

[!tip] Interview Angle Q: Sharding vs Replication — do you use one or the other, or both? A: Both, typically together in large-scale systems — shard to spread data volume + write load across many machines, and replicate each shard for fault tolerance and to spread read load. They solve different problems: sharding = "too much data/write traffic for one machine," replication = "need redundancy/more read capacity for the data each machine already holds."

---

SQL vs NoSQL

Types of NoSQL databases:

• Document (MongoDB, CouchDB) — stores semi-structured documents (JSON/BSON-like), great for nested/flexible data.
• Key-Value (Redis, DynamoDB) — simplest model, extremely fast lookups by key, often used for caching/session storage.
• Column-Family (Cassandra, HBase) — optimized for very wide tables and fast writes/scans on specific columns across huge datasets.
• Graph (Neo4j) — optimized for traversing relationships (friend-of-friend, recommendation engines) — relational JOINs become expensive at depth, graph DBs handle this natively.

[!tip] Interview Angle Q: You're building a chess platform with real-time games, move history, and a leaderboard — where might SQL vs NoSQL each fit? A: A relational DB (Postgres/MySQL) fits well for structured data with clear relationships — users, games, move history, ratings — where you want strong consistency (e.g. rating updates) and the ability to run relational queries (leaderboards, joins across users/games). A key-value store like Redis fits well alongside it for ephemeral, latency-sensitive data — active game state, session/presence info, real-time leaderboard caching — exactly the kind of split many real production systems use rather than picking one model exclusively.

---

9. Quick-Fire Revision Table#

One-liner recall — good for a last-pass scan before an interview. Full explanations are in the sections above.

---

Coverage Note#

All 18 questions from the InterviewBit DBMS PDF are answered above (marked 🟩), rewritten in my own words/structure with added examples — not copied from the source. Everything marked 🟦 is additional material covering topics that come up often in DBMS interviews and coursework (joins, indexing internals, concurrency control, distributed systems/NoSQL) but weren't in the original 18.

Suggested next step: pair this with hands-on practice — running these joins/subqueries/window functions against a real schema (e.g. the Silberschatz-style academic schema) cements this far better than reading alone.