What you'll learn
Quick Answer
DBMS interviews concentrate on normalisation up to third normal form, ACID properties, the different kinds of keys, indexing and how it works, transactions and concurrency problems, and joins. Answer with a concrete example rather than a definition — describing the update anomaly that second normal form prevents shows understanding, while reciting the definition does not.
Fundamentals
What is a DBMS, and how does an RDBMS differ?
A DBMS is software for storing and retrieving data. An RDBMS stores it in tables with defined relationships, enforces constraints, and supports SQL. Every RDBMS is a DBMS; not every DBMS is relational.
What is a schema? The structure — tables, columns, types, constraints and relationships. It is the blueprint, not the data.
What are the types of keys? Be precise here, because it is asked constantly:
- Super key — any set of columns that uniquely identifies a row.
- Candidate key — a minimal super key, with no removable column.
- Primary key — the candidate key chosen to identify rows. Unique and never NULL.
- Alternate key — the candidate keys not chosen.
- Foreign key — a column referencing another table's primary key.
- Composite key — a primary key made of more than one column.
Primary key vs unique key? Both enforce uniqueness. A table has one primary key which cannot be NULL; it can have several unique keys, which typically allow one NULL.
What is referential integrity? The guarantee that a foreign key always points at a row that exists. It is why you cannot insert an order for a non-existent customer, and why deleting a parent either cascades or is blocked.
Normalisation — Answer With Examples
The definitions are easy to recite and easy to forget. Anchor each one to the problem it prevents.
First normal form (1NF) — atomic values, no repeating groups.
BAD: student_id | name | subjects
1 | Riya | "Maths, Physics" ← not atomic
GOOD: a separate row (or table) per subjectSecond normal form (2NF) — 1NF, plus no non-key column depends on only part of a composite key.
Key = (student_id, course_id)
BAD: student_name depends only on student_id, not the whole key
Problem it prevents: the student's name is duplicated in every enrolment row,
so changing it means updating many rows — and missing one leaves the data inconsistent.Third normal form (3NF) — 2NF, plus no transitive dependency: non-key columns must depend on the key and nothing else.
BAD: employee_id | dept_id | dept_name
dept_name depends on dept_id, which depends on the key
Problem it prevents: renaming a department means updating every employee row,
and deleting the last employee loses the department entirely.BCNF is a stricter 3NF where every determinant is a candidate key.
What is denormalisation, and why would you do it? Deliberately reintroducing duplication to avoid expensive joins in read-heavy systems. The cost is redundancy and the risk of inconsistency. Being able to say when you would break the rule is what distinguishes a strong answer.
Transactions, ACID and Concurrency
What is a transaction? A sequence of operations treated as a single unit — all succeed or none do.
Explain ACID, ideally with the bank transfer example:
- Atomicity — debit and credit both happen, or neither. A crash between them must not leave money destroyed.
- Consistency — the database moves from one valid state to another, respecting all constraints.
- Isolation — concurrent transactions do not see each other's partial work.
- Durability — once committed, the change survives a crash or power loss.
What concurrency problems can occur? Name them precisely:
- Dirty read — reading data another transaction wrote but has not committed, which may be rolled back.
- Non-repeatable read — reading the same row twice and getting different values, because another transaction updated it in between.
- Phantom read — running the same query twice and getting different rows, because another transaction inserted or deleted some.
- Lost update — two transactions read then write, and one overwrites the other's change.
Isolation levels trade correctness against concurrency, from Read Uncommitted (allows all of the above) through Read Committed and Repeatable Read to Serializable (prevents all, at the highest cost).
What is a deadlock? Two transactions each holding a lock the other needs. Databases detect it and abort one. Preventing it usually means acquiring locks in a consistent order everywhere.
Indexing and Storage
What is an index? A sorted structure, usually a B-tree, letting the database locate rows without scanning the table — turning a full scan into a logarithmic lookup.
Always state the cost, because the follow-up is coming: every insert, update and delete must maintain every index, and indexes consume disk. So index the columns you filter, join and sort on — not every column.
Clustered vs non-clustered index? A clustered index determines the physical order of the rows, so there can be only one per table, and in many systems the primary key is it. A non-clustered index is a separate structure holding pointers to rows, and you can have many.
When is an index not used? A function applied to the column, a LIKE pattern beginning with a wildcard, low selectivity, or a type mismatch. Mentioning EXPLAIN as the way to check is a strong signal.
What is a view? A stored query that behaves like a table. It simplifies complex joins and can restrict which columns a user sees. A materialised view stores the results physically and must be refreshed, trading freshness for speed.
What is a stored procedure, and a trigger? A stored procedure is precompiled SQL called by name, reducing round trips. A trigger runs automatically in response to an insert, update or delete — useful for audit logs, but easy to overuse because the logic is invisible from the application.
Design Questions and SQL vs NoSQL
What is an ER diagram? A model of entities, their attributes and the relationships between them, used to design a schema before creating tables. Be ready to describe cardinality — one-to-one, one-to-many, many-to-many.
How do you implement a many-to-many relationship? With a junction table holding foreign keys to both sides. Students and courses need an enrolments table; the composite key is usually (student_id, course_id).
SQL vs NoSQL — when would you choose each?
Choose relational when the data has clear relationships, you need transactions and joins, and the schema is reasonably stable. Choose document or key-value stores when the schema varies between records, you need horizontal scale, or the access pattern is simple lookups by key.
Avoid the answer that NoSQL is "newer and faster". The honest version is that they optimise for different things, and most applications are well served by a relational database.
What is sharding, and what is replication? Sharding splits data across servers so each holds a subset — it scales writes but complicates joins. Replication copies the same data to several servers — it scales reads and provides failover, but introduces replication lag, so a read immediately after a write may return stale data.