DBMS Interview Questions & Deep Dives: Normalization to ACID
The practical database engineering principles tech interviewers actually look for in freshers.
StudyVex Editorial Team
Engineering & Placement Mentors
1. Moving Beyond Rote Memorization
In campus placements, students frequently lose points in DBMS rounds because they regurgitate textbook lines like "Atomicity means all or nothing".
When an interviewer hears that, their immediate follow-up is:
"Suppose you are designing a digital wallet transaction where user A sends ₹500 to user B. The database deducts ₹500 from user A, but crashes before adding it to user B. How does the database guarantee Atomicity at the storage engine level?"
To impress interviewers, you need to understand Write-Ahead Logging (WAL), Undo/Redo Logs, and Two-Phase Locking (2PL).
2. ACID Properties with Real-World Failure Scenarios
Atomicity
- Concept: A transaction is an indivisible unit of execution.
- Under the Hood: Relational databases (like PostgreSQL and MySQL InnoDB) use Write-Ahead Logging (WAL). Changes are written sequentially to an append-only log on disk before modifying the actual data pages. If a crash occurs mid-transaction, during recovery the engine reads the WAL and rolls back all uncommitted changes using Undo logs.
Consistency
- Concept: The database moves from one valid state to another, obeying all integrity constraints, foreign keys, and unique checks.
Isolation
- Concept: Concurrent transactions execute without interfering with one another.
- The 4 ANSI SQL Isolation Levels:
- Read Uncommitted: Suffers from Dirty Reads.
- Read Committed: Solves Dirty Reads, suffers from Non-Repeatable Reads. (Default in PostgreSQL).
- Repeatable Read: Solves Non-Repeatable Reads, can suffer from Phantom Reads in older architectures. (Default in MySQL InnoDB).
- Serializable: Full sequential equivalence via Strict 2PL or SSI (Serializable Snapshot Isolation).
Durability
- Concept: Once committed, transaction data survives power outages and server failures via disk syncs (
fsync).
3. Normalization: 1NF, 2NF, 3NF & BCNF with Concrete Schemas
Let us trace a real College Placement Registration schema:
| 1 | -- Raw Unnormalized Table |
| 2 | CREATE TABLE StudentPlacement ( |
| 3 | student_id INT, |
| 4 | student_name VARCHAR(100), |
| 5 | department VARCHAR(50), |
| 6 | hod_name VARCHAR(100), |
| 7 | interview_companies VARCHAR(255), -- Multiple companies in one cell! |
| 8 | PRIMARY KEY (student_id) |
| 9 | ); |
1NF: Atomic Attributes
Eliminate multi-valued columns. Every attribute must hold a single indivisible scalar value.
2NF: No Partial Dependencies
Every non-prime attribute must depend on the entire candidate key, not a partial subset. This only applies when you have composite candidate keys.
3NF: No Transitive Dependencies
No non-prime attribute should depend on another non-prime attribute ($X \to Y \to Z$).
In our table above, student_id -> department, and department -> hod_name. Therefore, hod_name has a transitive dependency on student_id. We extract Department(department_id, name, hod_name) into its own table.