The DBMS sheet covers the relational model, entity-relationship modelling, SQL, normalization, storage and indexing, query optimization, transactions, concurrency control and recovery.
The sheet holds 10 patterns and 50 topics, tracked row by row. From any row you can read the theory for a topic, answer knowledge-check questions on it, have the AI coach teach it back to you, keep notes, bookmark a row and mark it done. Progress is saved against your account, so the sheet is also the record of what you have already covered.
Who it is for
It is for candidates with a database round in the loop, and for backend engineers who write queries daily but have never had to defend an index choice or an isolation level.
How to work through it
Anchor every topic to a query you would actually write. Normalization, index selection and isolation levels are trade-offs, and the interview question is nearly always which trade-off you would take here and why.
Practice runs on the same platform as the sheet: 850+ problems across every track, code execution in 6 languages, and AI mock interviews that follow the pattern you are studying rather than a random question.
What the dbms sheet covers
Interview Soon
5 patterns, 25 topics — the ones that come up first
Database Systems and Architecture — Why Databases Exist, Data Abstraction and the Three-Schema Architecture, Data Models and Database Languages, Database System Structure, Database Users and the DBA
The Relational Model — Relations, Tuples and Attributes, Keys, Integrity Constraints, Relational Algebra, Relational Algebra: Additional Operations
Entity-Relationship Modelling — Entities, Attributes and Relationships, Cardinality and Participation Constraints, Weak Entities and Specialisation, Converting an ER Diagram to Tables, Working Through a Complete Design
SQL — Creating Tables and Basic Queries, Joins, Aggregation and Grouping, Subqueries and Set Operations, Views, Modifications and Access Control
Normalization — Anomalies and Why Normalization Exists, Functional Dependencies and Attribute Closure, First, Second and Third Normal Form, BCNF and Lossless Decomposition, Higher Normal Forms and When to Stop
Deep Dive
5 patterns, 25 topics for full coverage
Storage and File Organization — The Storage Hierarchy and Why Disks Shape Databases, Records, Blocks and File Organization, Indexing Basics: Dense, Sparse and Multilevel, B+ Trees, Hash Indexes and Choosing an Index
Query Processing and Optimization — How a Query Is Processed, Selection and Sorting Algorithms, Join Algorithms, Query Optimization, Reading a Query Plan
Transactions — Transactions and the ACID Properties, Transaction States and Schedules, Serializability, Recoverability and Cascading Rollback, Isolation Levels
Concurrency Control — Lock-Based Protocols, Two-Phase Locking, Deadlock Handling in Databases, Timestamp and Validation-Based Protocols, Multiple Granularity and Multiversion Concurrency
Recovery — Failure Types and the Recovery Problem, Log-Based Recovery, Checkpoints, Recovery After a Crash, Backups and Protecting Against Disk Failure
Reference: 40 questions
Last updated: September 1, 2026 · drawn from 236 dbms knowledge-check questions, free on every plan.
Database Systems and Architecture
What is the difference between a database and a DBMS?
The database is the data; the DBMS is the software that manages access to that data.
The table has four rows today and five rows tomorrow. What changed?
The instance changed; the schema did not.
Is `UPDATE Orders SET amount = 35 WHERE order_id = 101` DDL or DML?
DML, because it changes a stored row.
What happens when a needed disk block is not in memory?
The buffer manager reads it into the buffer pool and may evict another block.
The Relational Model
Can a pure mathematical relation contain the same tuple twice?
No. A relation is a set of tuples, and sets do not contain duplicates.
Can a table have `roll_no` and `email` as candidate keys at the same time?
Yes. The designer chooses one as primary; the other becomes an alternate key.
Why can a primary key not contain `NULL`?
Because a `NULL` key part means the row cannot be reliably identified.
What does \(\pi_{\text{dept}}(\text{Student})\) return for the sample table?
It returns `CS`, `Math`, and `Physics`, with `CS` appearing once.
Entity-Relationship Modelling
Where does `grade` belong in the example?
It belongs to the Enrols relationship because it describes one student-course pairing.
May a customer exist without a loan, while every loan must have a customer?
Customer has partial participation; Loan has total participation.
What is the partial key in the dependent example?
`DependentName`, because it distinguishes dependents only within one employee's dependents.
A student can take many courses, and a course can have many students. Where does `grade` go?
In a new `Enrollment` table, because the grade describes one student-course pair.
SQL
What does this query choose: rows or columns?
```sql
SELECT employee_name, salary
FROM Employee
WHERE department_id = 20;
```
`WHERE` chooses the rows; `SELECT` chooses the columns.
How many pairs does a cross join of tables with 100 rows each produce?
\(100 \times 100 = 10{,}000\).
Which clause finds departments whose employee count exceeds 10?
`HAVING COUNT(*) > 10`, because the count exists only after grouping.
Why can `NOT IN` return no rows when the inner result contains `NULL`?
Comparisons with the unknown `NULL` produce unknown, and `WHERE` keeps only true conditions.
Normalization
What anomaly occurs when the last employee of a department is deleted?
A deletion anomaly occurs because the department fact is stored only in employee rows.
Given `A → B` and `B → C`, what is included in \(A^+\)?
At least `A`, `B`, and `C`, because the dependencies apply in sequence.
Can a table with key `emp_id` violate 2NF because `emp_id -> emp_name`?
No. The key has one column, so there is no partial dependency on a composite key.
In `R(A, B, C)` decomposed into `R1(A, B)` and `R2(A, C)`, when is the split lossless?
When A is a key of `R1` or a key of `R2`.
Storage and File Organization
What is the approximate ratio between a 10-millisecond disk seek and a 100-nanosecond memory access?
About 100,000 to 1.
Why can a variable-length record move without changing its RID in a slotted page?
The RID identifies its slot, and the slot stores the record’s current offset.
A dense index has entries for keys 10, 20, and 40. What is missing if the file contains key 30?
The index is not dense for that file unless 30 is not a search-key value that occurs. Every occurring search-key value must have an entry.
Why is a range query efficient?
The database finds the first qualifying leaf and follows linked leaves in sorted order.
Query Processing and Optimization
What is the difference between a parse tree and a query plan?
A parse tree represents the syntax of the SQL statement; a query plan specifies executable operators and algorithms.
An index has height 3. Approximately how many reads does a selective equality lookup need?
About 4: three index levels and one table block.
What does block nested loop change?
It processes several outer rows together, so it scans the inner input fewer times.
What is the estimated number of matches for 1,000,000 rows and 10,000 evenly distributed distinct values?
\(1{,}000{,}000 / 10{,}000 = 100\) rows.
Transactions
A committed row disappears after a power failure. Which property failed?
Durability, because the commit was acknowledged before the required information was safely stored.
What edge comes from `W1(A), R2(A)`?
`T1 → T2`, because T1’s conflicting operation occurs first.
What edge does `W2(B)` followed by `R1(B)` create?
`T2 → T1`, because `T2` performs the earlier conflicting operation.
If `T2` reads from `T1`, what must happen before `T2` commits?
`T1` must commit first.
Concurrency Control
Can a transaction obtain `X(A)` while another transaction holds `S(A)`?
No. An exclusive lock is incompatible with a shared lock.
Can a transaction acquire a new lock after releasing one under 2PL?
No. Its first release begins the shrinking phase, and no later lock acquisition is allowed.
\(T_1\) is older than \(T_2\). Under wait-die, \(T_1\) requests a lock held by \(T_2\). What happens?
\(T_1\) waits.
Can timestamp ordering produce a deadlock?
No. Transactions do not wait for locks; a violating transaction is rejected instead.
Recovery
Why can an uncommitted transaction leave a disk trace?
Because steal allows a changed page to be written before the transaction commits.
A transaction has a start record and a commit record, but its changed page was not written before the crash. What does recovery do?
It redoes the update by writing its new value.
Why does the checkpoint record list active transactions?
An active transaction may have written changes without committing, so recovery may need to undo those changes.
If `T1` has a commit record and `T2` does not, which transaction is undone?
Only `T2` is undone. `T1` is committed and must remain.
Limited time — 15% off all plans & roadmaps Use code
