DBMS Interview Questions

ACID Properties

Core Most Asked 4 min

ACID guarantees reliable transactions: Atomicity (all or nothing), Consistency (valid state to valid state), Isolation (transactions don't interfere), Durability (committed = permanent).

⚡ ACID Transaction Simulation

AAtomicity

CConsistency

IIsolation

DDurability

Account A

$1000

→

Account B

$500

Ready — Click a button to start transaction

-- Waiting for transaction...

ACID is the gold standard for relational database transactions. When you transfer money between accounts, you absolutely need ACID — partial transfers or lost data is unacceptable.

Property	What It Means	Real Example	How It's Implemented
Atomicity	Transaction is all-or-nothing	Bank transfer: debit AND credit happen together, or neither happens	Transaction logs, undo logs
Consistency	DB moves from one valid state to another	Account balance can't go negative if rules forbid it	Constraints, triggers, application logic
Isolation	Concurrent transactions don't affect each other	Two transfers happening simultaneously get correct totals	Locks, MVCC (Multi-Version Concurrency Control)
Durability	Committed data survives crashes	After "transfer successful," data persists even if power fails	Write-ahead logging (WAL), fsync

Real-World Analogy

Think of buying a house: Atomicity = money transfers AND ownership transfers, or deal is off. Consistency = you can't buy a house for negative money. Isolation = two people can't buy the same house simultaneously. Durability = once deed is signed, it's permanent record.

Key Interview Points

Atomicity uses undo logs — if transaction fails midway, all changes are rolled back
Durability uses redo logs (WAL) — writes are logged before applying, so recovery is possible
Isolation has different levels — see Isolation Levels section
NoSQL databases often sacrifice some ACID properties for performance (see BASE)

Competitive Edge

Know the difference between ACID (SQL) and BASE (NoSQL): BASE = Basically Available, Soft-state, Eventually consistent. NoSQL trades strict consistency for availability and partition tolerance. Mention this to show you understand the trade-offs.

Follow-up Questions

How does a database ensure atomicity after a crash?

Using Write-Ahead Logging (WAL). Before any change is made to the actual data, it's first written to a log. On crash recovery, the database replays committed transactions from the log and rolls back uncommitted ones using the undo log.

Can you have ACID in distributed systems?

Yes, but it's expensive. Distributed transactions use Two-Phase Commit (2PC): Phase 1 — all nodes vote to commit. Phase 2 — if all voted yes, commit; else rollback. This is slow and blocking. Modern systems often use Saga pattern or eventual consistency instead.

Normalization (1NF to BCNF)

Core Theory 5 min

Normalization organizes data to reduce redundancy and improve integrity. Each normal form builds on the previous: 1NF (atomic values) → 2NF (no partial deps) → 3NF (no transitive deps) → BCNF (stricter 3NF).

Why normalize? Without it, you get anomalies:

Update anomaly: Change data in one place, forget another copy
Insert anomaly: Can't add data without unrelated data
Delete anomaly: Deleting one thing accidentally removes other info

📊 Normalization Step-by-Step

⚠ Unnormalized: Repeating groups, multi-valued cells, no proper key

StudentID	Name	Courses	Phones
1	Alice	Math, Physics	111, 222
2	Bob	Chemistry	333, 444, 555

Problems:
• Multi-valued columns (Courses, Phones)
• Can't query individual courses easily
• No atomic values

✓ 1NF: Atomic values — each cell holds ONE value

StudentID	Name	Course	Phone
1	Alice	Math	111
1	Alice	Physics	111
1	Alice	Math	222
2	Bob	Chemistry	333

Remaining Problem:
• Name depends only on StudentID, not on full key (StudentID, Course)
• Partial dependency → violates 2NF

✓ 2NF: No partial dependencies — every non-key depends on WHOLE key

StudentID	Name
1	Alice
2	Bob

StudentID	CourseID	Grade
1	M101	A
1	P201	B
2	C301	A

Remaining Problem (if exists):
• If we had ZipCode → City, that's a transitive dependency
• Non-key → Non-key violates 3NF

✓ 3NF: No transitive dependencies — non-key depends ONLY on the key

StudentID	Name	ZipCode
1	Alice	10001
2	Bob	90210

ZipCode	City	State
10001	New York	NY
90210	Beverly Hills	CA

The Rule:
"The key, the whole key, and nothing but the key"
• Most databases stop at 3NF ✓

✓ BCNF: Every determinant is a candidate key (stricter 3NF)

Student	Subject	Professor
Alice	DB	Dr. Smith
Bob	DB	Dr. Smith
Alice	OS	Dr. Jones

⚠ Professor → Subject (Prof determines Subject, but Prof is not a candidate key)

Professor	Subject
Dr. Smith	DB
Dr. Jones	OS

Student	Professor
Alice	Dr. Smith
Bob	Dr. Smith
Alice	Dr. Jones

Normal Form	Rule	Simple Explanation	Example Violation
1NF	Atomic values, no repeating groups	Each cell has ONE value. No arrays or lists.	Phone column with "123, 456, 789"
2NF	1NF + no partial dependencies	Every non-key column depends on the WHOLE primary key	Key: (StudentID, CourseID), but StudentName depends only on StudentID
3NF	2NF + no transitive dependencies	Non-key columns don't depend on other non-key columns	ZipCode → City (City depends on ZipCode, not on primary key)
BCNF	For every X→Y, X must be a superkey	Stricter 3NF — every determinant is a candidate key	Complex scenarios with overlapping candidate keys

The Classic Memory Trick

"The key, the whole key, and nothing but the key, so help me Codd."
1NF = "The key" (must have a key, atomic values)
2NF = "The whole key" (depend on ALL of composite key)
3NF = "Nothing but the key" (no transitive deps through non-keys)

Key Interview Points

Most real-world databases aim for 3NF — good balance of normalization vs complexity
BCNF is rarely needed — only when 3NF has specific edge cases with overlapping keys
Higher normalization = more tables = more JOINs = potentially slower reads
Know how to identify violations: "This column depends on X, not on the primary key"

Common Mistake

Don't say "higher normal form is always better." In practice, 3NF is usually sufficient. Over-normalizing creates too many joins and hurts read performance. Know when to stop.

Denormalization

Concept Practical 3 min

Denormalization intentionally adds redundancy to improve read performance. It trades storage space and write complexity for faster queries by reducing JOINs.

Normalization optimizes for writes (no redundancy, easy updates). But in read-heavy systems like analytics dashboards or e-commerce product pages, JOINs across many tables are slow. Denormalization pre-computes and stores data together.

⚖ Normalized vs Denormalized

SELECT o.id, c.name, c.email, p.title, p.price
FROM Orders o
JOIN Customers c ON o.cust_id = c.id
JOIN Products p ON o.prod_id = p.id;

2 JOINs required ✕

id	cust_id	prod_id	qty
1	10	50	2
2	11	50	1

id	name	email
10	Alice	[email protected]
11	Bob	[email protected]

id	title	price
50	Widget	$29

Read Speed

Slow

Write Speed

Fast

Redundancy

None

SELECT id, cust_name, cust_email, prod_title, prod_price
FROM Order_Details
WHERE id = 1;

0 JOINs — single table ✓

id	cust_name	cust_email	prod_title	prod_price	qty
1	Alice	[email protected]	Widget	$29	2
2	Bob	[email protected]	Widget	$29	1

Read Speed

Fast

Write Speed

Slow

Redundancy

High

Aspect	Normalized	Denormalized
Redundancy	Minimal	Intentional duplication
Read Performance	Slower (needs JOINs)	Faster (data together)
Write Performance	Faster (single update)	Slower (update multiple places)
Storage	Less	More
Data Consistency	Easier to maintain	Risk of inconsistency
Use Case	OLTP (transactions)	OLAP (analytics), caching

When to Denormalize

Read-heavy workloads: Analytics, reporting, dashboards
Frequently accessed computed values: Store total instead of calculating
Avoid expensive JOINs: Embed related data in same table
Caching layer: Store pre-computed results
Data warehouses: Star schema, fact tables with denormalized dimensions

Competitive Edge

Mention materialized views as a middle ground — they store query results but can be refreshed automatically. Also mention that NoSQL databases like MongoDB are often denormalized by design (embedding documents instead of referencing).

Primary Key vs Foreign Key

Core 3 min

Primary Key uniquely identifies each row (unique + not null). Foreign Key references a primary key in another table, creating relationships and enforcing referential integrity.

🔗 Primary Key ↔ Foreign Key Relationship

🏢 Department

id

PK

name

🔑 1

PK

Engineering

🔑 2

PK

Marketing

🔑 3

PK

Sales

👤 Employee

id

name

dept_id

🔑 101

Alice

🔗 1

🔑 102

Bob

🔗 1

🔑 103

Charlie

🔗 2

🔑 104

Diana

🔗 3

💡 Hover over any row to see the Primary Key → Foreign Key relationship. The FK in Employee references the PK in Department.

Click an action to see referential integrity in action

Aspect	Primary Key	Foreign Key
Purpose	Uniquely identify rows	Create relationship between tables
Uniqueness	Must be unique	Can have duplicates
NULL allowed	No	Yes (optional relationship)
Count per table	Only 1	Multiple allowed
Auto-creates index	Yes (clustered)	No (but recommended)
References	None	Must reference a primary/unique key

SQL Example

                CREATE TABLE
                Department ( id
                INT PRIMARY KEY,
                -- Unique identifier name
                VARCHAR(100) );

                CREATE TABLE
                Employee ( id
                INT PRIMARY KEY, name
                VARCHAR(100),
                dept_id INT,
                -- Foreign key column
                FOREIGN KEY (dept_id)
                REFERENCES
                Department(id)
                ON DELETE CASCADE
                -- If dept deleted, delete employees
                ON UPDATE CASCADE
                -- If dept id changes, update here too
                );
              

Referential Integrity Actions

CASCADE: Delete/update child rows when parent changes
SET NULL: Set foreign key to NULL when parent deleted
RESTRICT: Prevent delete if children exist (default)
NO ACTION: Similar to RESTRICT, checked at end of transaction

Indexing

Core Performance 4 min

An index is a data structure (usually B+ tree) that speeds up data retrieval. Like a book's index — instead of scanning every page, jump directly to the right location. Trades write speed and storage for read speed.

🐌⚡ Index vs Full Table Scan

Search for "email_15" — see the difference

🐌 No Index (Full Scan)

0

Rows Scanned

—

Time Complexity

Ready

Status

⚡ With Index (B+ Tree)

0

Steps

—

Time Complexity

Ready

Status

SELECT * FROM Users WHERE email = 'email_15'

Without index: Full table scan — read EVERY row to find matches. O(n).
With index: B+ tree lookup — jump directly to relevant rows. O(log n).

SQL — Creating Indexes

                -- Single column index
                CREATE INDEX idx_email
                ON
                Users(email);

                -- Composite index (order matters!)
                CREATE INDEX idx_name_date
                ON
                Orders(customer_name, order_date);

                -- Unique index
                CREATE UNIQUE INDEX idx_username
                ON
                Users(username);

                -- Check if index is used
                EXPLAIN ANALYZE SELECT *
                FROM Users
                WHERE email =
                '[email protected]';
              

When to Create Indexes

Columns in WHERE clauses — frequently filtered columns
JOIN columns — foreign keys used in joins
ORDER BY columns — avoid sorting full result set
High cardinality columns — many unique values (email, ID)

When NOT to Index

Small tables — full scan is fast enough
Frequently updated columns — index maintenance overhead
Low cardinality — boolean columns (only 2 values) won't benefit much
Columns rarely in queries — index unused but still maintained

Competitive Edge

Composite index order matters! Index on (A, B) helps queries on A, or (A AND B). But NOT queries on just B. This is the leftmost prefix rule. Also mention covering indexes — if all columns needed are in the index, the database doesn't need to read the actual table (index-only scan).

Clustered vs Non-Clustered Index

Concept Deep Dive 4 min

Clustered index determines physical row order (table IS the index) — only one per table. Non-clustered index is a separate structure with pointers to rows — multiple allowed.

Aspect	Clustered Index	Non-Clustered Index
Physical order	Data rows sorted by index key	Separate structure, data unordered
Count per table	Only 1	Multiple (up to 999 in SQL Server)
Leaf nodes contain	Actual data rows	Pointers to data rows
Range queries	Faster (data is contiguous)	Slower (need to follow pointers)
Insert performance	Slower (may need to reorder)	Faster
Default on	PRIMARY KEY	Other indexes

🔍 Clustered vs Non-Clustered Index

✦ Clustered Index

Root:30 | 60

↓

Internal:10 | 2040 | 5070 | 80

↓

Leaf:5,Alice10,Bob40,Cara70,Dan

↔ Linked list (sorted order)

Leaf nodes = ACTUAL DATA ROWS

✦ Non-Clustered Index

Root:D | M

↓

Internal:A | BD | E

↓

Leaf:Alice→r1Bob→r2Cara→r3Dan→r4

↓ follow pointer

Heap:r1:Alicer2:Bobr3:Carar4:Dan

Leaf nodes = POINTERS to rows

Click Search to compare the lookup path

Real-World Analogy

Clustered index: A phone book sorted by last name. The data IS sorted — finding "Smith" is fast because all Smiths are together.
Non-clustered index: A book's index at the back. It tells you page numbers, but you still need to flip to those pages. The pages themselves aren't sorted by the index.

Competitive Edge

In MySQL InnoDB, the primary key IS the clustered index. Non-clustered indexes store the primary key value (not row pointer). So if your primary key is a large UUID, ALL your indexes become larger. This is why auto-increment INT is often preferred for primary keys — smaller indexes, better performance.

B+ Trees

Concept Internals 4 min

B+ tree is a self-balancing tree structure used for database indexes. All data is in leaf nodes (connected as linked list), internal nodes only hold keys for navigation. Provides O(log n) search, insert, delete.

Why B+ trees for databases?

Disk-optimized: High fanout (many children) = fewer disk reads
Range queries: Leaf nodes linked = easy sequential scan
Balanced: All leaves at same depth = predictable performance
Full nodes: Typically 70%+ full = space efficient

🌳 B+ Tree — Interactive Search

All data in leaf nodes • Leaves linked for range scans • O(log n) lookups

30

60

↙ ↓ ↘

10

20

40

50

70

80

↓ ↓ ↓

5

8

→

10

15

→

20

25

→

30

35

→

40

45

→

60

65

→

70

75

→

80

90

→ Leaf linked list for sequential scan

🔍 Search:

Enter a value and click Search to traverse the B+ tree

Internal nodes hold keys for navigation. Leaf nodes hold all data and are linked for sequential access.

B+ Tree vs B Tree

B+ tree: Data ONLY in leaves, leaves linked. Better for range queries and disk I/O.
B tree: Data in all nodes. Slightly faster single-key lookup, but worse for ranges.
Databases use B+ trees because disk reads are expensive, and range queries are common.

Competitive Edge

B+ tree node size is typically one disk page (4KB-16KB). This means each node read = one disk I/O. A B+ tree with 1000 keys per node can index billions of rows with just 3-4 levels. Mentioning page size and fanout shows deep understanding.

Joins (All Types)

Core Most Asked 5 min

JOIN combines rows from multiple tables based on a related column. Types: INNER (only matches), LEFT (all from left + matches), RIGHT (all from right + matches), FULL (all from both), CROSS (cartesian product), SELF (table with itself).

SQL — All Join Types

                -- INNER JOIN: Only matching rows
                SELECT e.name, d.name
                FROM Employee e
                INNER JOIN
                Department d
                ON e.dept_id = d.id;

                -- LEFT JOIN: All employees, even without department
                SELECT e.name, d.name
                FROM Employee e
                LEFT JOIN
                Department d
                ON e.dept_id = d.id;

                -- SELF JOIN: Employee with their manager
                SELECT e.name
                AS employee, m.name
                AS manager FROM
                Employee e
                LEFT JOIN
                Employee m
                ON e.manager_id = m.id;

                -- CROSS JOIN: Every combination (careful — big
                  result!)
                SELECT * FROM
                Colors
                CROSS JOIN
                Sizes;
              

Key Interview Points

INNER JOIN is default when you just write JOIN
LEFT JOIN is used to find "missing" records (customers with no orders)
FULL OUTER JOIN not supported in MySQL — use UNION of LEFT and RIGHT
CROSS JOIN is dangerous — 1000 × 1000 rows = 1 million result rows
SELF JOIN needs aliases to distinguish the two "copies" of the table

Transactions

Core 3 min

A transaction is a sequence of operations treated as a single logical unit. Either ALL operations succeed (COMMIT) or ALL are undone (ROLLBACK). Ensures ACID properties.

SQL — Transaction Example

                BEGIN TRANSACTION;

                -- Debit from account A
                UPDATE Accounts
                SET balance = balance -
                100 WHERE id =
                1;

                -- Credit to account B
                UPDATE Accounts
                SET balance = balance +
                100 WHERE id =
                2;

                -- If both succeeded
                COMMIT;

                -- If something went wrong
                -- ROLLBACK;
              

Transaction States

Active: Transaction is executing operations
Partially Committed: All operations done, waiting for final commit
Committed: All changes are permanent
Failed: Error occurred, cannot proceed
Aborted: Rolled back, changes undone

Competitive Edge

Know about savepoints — you can partially rollback within a transaction. Also mention implicit vs explicit transactions: in autocommit mode (default in many DBs), each statement is its own transaction. For multi-statement operations, you need explicit BEGIN/COMMIT.

Locks (Shared/Exclusive)

Concept Concurrency 4 min

Locks control concurrent access to data. Shared lock (S) allows multiple readers. Exclusive lock (X) allows only one writer. Prevents dirty reads, lost updates, and other concurrency issues.

Lock Type	Also Called	Who Can Access	Used For
Shared (S)	Read Lock	Multiple readers, no writers	SELECT queries
Exclusive (X)	Write Lock	Only one holder, no other access	INSERT, UPDATE, DELETE

Lock Compatibility	Shared (S)	Exclusive (X)
Shared (S)	✓ Compatible	✗ Conflict
Exclusive (X)	✗ Conflict	✗ Conflict

Lock Granularity

Row-level: Lock specific rows. Most concurrent, most overhead. (InnoDB default)
Page-level: Lock a page (group of rows). Middle ground.
Table-level: Lock entire table. Least concurrent, least overhead. (MyISAM)
Database-level: Lock entire database. Rare, used for maintenance.

Competitive Edge

Know about MVCC (Multi-Version Concurrency Control) — used by PostgreSQL and MySQL InnoDB. Instead of locking, readers see a snapshot of data at a point in time. Writers create new versions. Readers never block writers, writers never block readers. Much better concurrency than pure locking.

Deadlock

Concept Critical 4 min

Deadlock occurs when two or more transactions are waiting for each other's locks, forming a cycle. No one can proceed. Database must detect and kill one transaction to break the cycle.

Both transactions are stuck waiting for each other — neither can proceed.

Deadlock Handling

Detection: Database periodically checks for cycles in wait-for graph. Kills one transaction (victim selection based on work done, priority, etc.)
Prevention: Acquire all locks upfront, or always acquire in same order
Timeout: If lock not acquired in X seconds, abort transaction
Application handling: Catch deadlock error, retry transaction

Preventing Deadlocks in Your Code

Lock ordering: Always acquire locks in the same order (e.g., by ID ascending)
Keep transactions short: Less time holding locks = less chance of deadlock
Avoid user input mid-transaction: Don't wait for user while holding locks
Use lower isolation levels if possible: Fewer locks needed

Isolation Levels

Concept Tricky 5 min

Isolation levels define how much transactions can see each other's changes. Higher isolation = more correct, but slower. From weakest to strongest: Read Uncommitted → Read Committed → Repeatable Read → Serializable.

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read	Performance
Read Uncommitted	Possible	Possible	Possible	Fastest
Read Committed	Prevented	Possible	Possible	Fast
Repeatable Read	Prevented	Prevented	Possible*	Moderate
Serializable	Prevented	Prevented	Prevented	Slowest

*MySQL InnoDB prevents phantom reads in Repeatable Read using gap locks.

Understanding the Anomalies

Dirty Read: Reading uncommitted data from another transaction. If that transaction rolls back, you read garbage.
Non-Repeatable Read: Read same row twice, get different values (another transaction committed an update).
Phantom Read: Re-run a query, get new rows (another transaction inserted rows that match your WHERE clause).

SQL — Setting Isolation Level

                -- Check current level

                SHOW VARIABLES LIKE
                'transaction_isolation';

                -- Set for session

                SET SESSION TRANSACTION ISOLATION LEVEL
                READ COMMITTED;

                -- Set for next transaction only

                SET TRANSACTION ISOLATION LEVEL
                SERIALIZABLE;

Competitive Edge

Defaults: MySQL InnoDB = Repeatable Read. PostgreSQL = Read Committed. Know why PostgreSQL chose Read Committed: their MVCC implementation handles most anomalies efficiently at that level. Also mention Snapshot Isolation — used by PostgreSQL and Oracle, where each transaction sees a consistent snapshot of the database.

CAP Theorem

Concept Distributed Systems 4 min

CAP theorem: A distributed system can provide only 2 of 3 guarantees: Consistency (all nodes see same data), Availability (system always responds), Partition Tolerance (works despite network failures). Pick 2.

Understanding the Trade-offs

CA (Consistency + Availability): Traditional single-node databases. No network partitions to worry about. (MySQL, PostgreSQL single node)
CP (Consistency + Partition Tolerance): During partition, reject requests to maintain consistency. (MongoDB, HBase, Redis cluster)
AP (Availability + Partition Tolerance): During partition, respond with possibly stale data. (Cassandra, DynamoDB, CouchDB)

Common Misconception

"You pick 2 and never get the third" — Not quite true. When there's no partition, you can have all three. The trade-off only matters DURING a network partition. Since partitions are rare but inevitable in distributed systems, P is usually non-negotiable, so the real choice is between C and A during failures.

Competitive Edge

Know about PACELC — an extension of CAP. If there's a Partition, choose A or C. Else (normal operation), choose Latency or Consistency. This better captures that systems make trade-offs even when running normally. Example: DynamoDB is PA/EL (chooses availability during partition, chooses low latency normally).

Sharding

Concept Scale 4 min

Sharding horizontally partitions data across multiple database servers (shards). Each shard holds a subset of data. Enables scaling beyond single-machine limits, but adds complexity for cross-shard queries and transactions.

Sharding Strategies

Range-based: Shard by value range (e.g., users A-M on shard 1, N-Z on shard 2). Easy range queries, but can cause hotspots.
Hash-based: Hash the shard key (e.g., hash(user_id) mod N). Even distribution, but range queries hit all shards.
Directory-based: Lookup table maps keys to shards. Flexible, but lookup table is a single point of failure.
Geographic: Shard by location (e.g., US data on US shard). Good for latency, compliance.

Sharding Challenges

Cross-shard queries: JOINs across shards are expensive (need to query all shards)
Transactions: ACID across shards requires 2PC (slow) or eventual consistency
Resharding: Adding/removing shards requires data migration
Hotspots: Poor shard key choice leads to uneven load
Complexity: Application must be shard-aware, or use middleware

Competitive Edge

Know Consistent Hashing — used by DynamoDB, Cassandra. When adding/removing nodes, only K/n keys need to move (K = total keys, n = nodes), not all of them. Also mention virtual nodes — each physical node owns multiple positions on the hash ring for better load distribution.

SQL vs NoSQL

Concept Decision Making 5 min

SQL databases are relational, schema-based, ACID-compliant, good for complex queries. NoSQL databases are non-relational, flexible schema, often eventually consistent, good for scale and specific access patterns.

Aspect	SQL (Relational)	NoSQL
Data Model	Tables with rows and columns	Document, Key-Value, Column-family, Graph
Schema	Fixed schema, enforced	Flexible, schema-on-read
Query Language	SQL (standardized)	Varies by database
ACID	Full ACID support	Often BASE (eventual consistency)
Scaling	Vertical (scale up)	Horizontal (scale out)
JOINs	Powerful JOINs	Limited/no JOINs
Best For	Complex queries, transactions, relationships	High volume, simple access patterns, flexible data

NoSQL Types

Document: MongoDB, CouchDB. Store JSON-like documents. Good for content management, catalogs.
Key-Value: Redis, DynamoDB. Simple get/set by key. Good for caching, sessions.
Column-family: Cassandra, HBase. Columns grouped into families. Good for time-series, analytics.
Graph: Neo4j, Amazon Neptune. Nodes and edges. Good for social networks, recommendations.

When to Choose What

Choose SQL when: Complex relationships, need transactions, data integrity critical, complex queries/reporting
Choose NoSQL when: Massive scale needed, flexible schema required, simple access patterns (key lookups), high write throughput
Many systems use both: SQL for transactions, NoSQL for caching/analytics (polyglot persistence)

Common Interview Questions

Can NoSQL databases do transactions?

Yes, many modern NoSQL databases support transactions. MongoDB has multi-document transactions since 4.0. DynamoDB has TransactWriteItems. But they're often limited to single partition/shard for performance. Cross-partition transactions are expensive.

Is NoSQL always faster than SQL?

No! It depends on the use case. For complex queries with JOINs, SQL with proper indexing is often faster. NoSQL shines for simple key-value lookups at massive scale. The "NoSQL is faster" myth comes from comparing denormalized NoSQL queries to badly designed SQL schemas.

When would you migrate from SQL to NoSQL?

When: (1) Vertical scaling hits limits and you need horizontal scale, (2) Schema changes are too frequent, (3) Your access pattern is simple (key lookups), (4) You need to handle massive write throughput. Don't migrate just because NoSQL is "trendy."

Competitive Edge

The line is blurring. PostgreSQL has JSONB for document storage. MySQL has JSON columns. MongoDB has JOINs ($lookup). CockroachDB is a distributed SQL database. Know that modern systems often combine approaches — use SQL for transactional data, Redis for caching, Elasticsearch for search, all in the same application.

DBMS Concepts

ACID Properties

Key Interview Points

Competitive Edge

Follow-up Questions

Normalization (1NF to BCNF)

Key Interview Points

Common Mistake

Denormalization

When to Denormalize

Competitive Edge

Primary Key vs Foreign Key

Referential Integrity Actions

Indexing

When to Create Indexes

When NOT to Index

Competitive Edge

Clustered vs Non-Clustered Index

✦ Clustered Index

✦ Non-Clustered Index

Competitive Edge

B+ Trees

B+ Tree vs B Tree

Competitive Edge

Joins (All Types)

Key Interview Points

Transactions

Transaction States

Competitive Edge

Locks (Shared/Exclusive)

Lock Granularity

Competitive Edge

Deadlock

Deadlock Handling

Preventing Deadlocks in Your Code

Isolation Levels

Understanding the Anomalies

Competitive Edge

CAP Theorem

Understanding the Trade-offs

Common Misconception

Competitive Edge

Sharding

Sharding Strategies

Sharding Challenges

Competitive Edge

SQL vs NoSQL

NoSQL Types

When to Choose What

Common Interview Questions

Competitive Edge

Ready to test DBMS?