DBMS Interview Guide: Keys, Normalisation, Transactions, Indexing and Concurrency

Why DBMS is a must-prepare subject

Almost every application stores data, so database questions appear in nearly every placement and backend interview. This guide covers the theory; practise the queries in the SQL guide. DBMS is one of the three big core CS subjects with operating systems and networks.

Answer pattern that works: define it, give a tiny example from a familiar system (a student database, a bank, an online shop), then state the benefit or trade-off.

1. Basics

A database is an organised collection of data; a DBMS is the software that creates, stores and controls access to it (MySQL, PostgreSQL, Oracle, SQL Server, MongoDB). A relational database stores data in tables (relations), made of rows (tuples) and columns (attributes). The schema is the structure; an instance is the data at a moment in time.

Advantages over file systems: less redundancy and inconsistency, data sharing, integrity constraints, security, concurrent access and backup and recovery.

Three-level architecture: the physical level (how data is stored), the logical level (tables and relationships) and the view level (what each user sees). Data independence means a change at one level does not force changes at the next.

Languages: DDL (define structure), DML (manipulate data), DCL (permissions: GRANT, REVOKE) and TCL (COMMIT, ROLLBACK).

2. Keys and integrity

• Super key: any set of attributes that uniquely identifies a row
• Candidate key: a minimal super key (no attribute can be removed)
• Primary key: the chosen candidate key; unique and never NULL
• Alternate key: the candidate keys not chosen
• Unique key: unique values, but NULL is allowed (details vary by database)
• Foreign key: refers to a primary key in another table and enforces referential integrity
• Composite key: made of two or more attributes; surrogate key: an artificial id such as an auto-number

Example: Student(roll_no, email, name): both roll_no and email are candidate keys. Choosing roll_no as primary leaves email as an alternate key.

Integrity constraints: domain (types and checks), entity (primary key not null and unique), referential (foreign keys must match an existing key or be NULL). Foreign key actions on delete: CASCADE, SET NULL, RESTRICT.

3. ER model and design

Entities, attributes (simple, composite, multivalued, derived) and relationships with cardinality (1:1, 1:N, M:N) and participation (total or partial). A weak entity has no key of its own and depends on an owner.

Converting to tables: an entity becomes a table; a 1:N relationship adds a foreign key on the N side; an M:N relationship becomes a separate junction table with both foreign keys (students and courses become Enrollment(student_id, course_id)).

4. Functional dependencies and normalisation

A functional dependency A -> B means that if two rows have the same A, they have the same B (roll_no determines name). The closure of a set of attributes is everything it determines; a candidate key is a minimal set whose closure contains all attributes. Practise computing closures and finding candidate keys, since numericals ask for this.

Why normalise: to remove redundancy and the anomalies it causes: insertion (cannot add a fact without another), update (must change many rows) and deletion (losing one fact loses another).

• 1NF: every cell holds an atomic value; no repeating groups
• 2NF: 1NF and no partial dependency (no non-key attribute depends on part of a composite key)
• 3NF: 2NF and no transitive dependency (non-key attributes depend only on the key). Standard phrase: "every non-key attribute depends on the key, the whole key and nothing but the key."
• BCNF: for every dependency X -> Y, X is a super key; stricter than 3NF

Worked example. Orders(order_id, product_id, customer_id, customer_city, quantity) with key (order_id, product_id):

• customer_id and customer_city depend only on order_id (partial dependency), so it is not in 2NF. Split into Orders(order_id, customer_id, customer_city) and OrderItems(order_id, product_id, quantity).
• In Orders, customer_city depends on customer_id, which depends on order_id (transitive), so it is not in 3NF. Split into Orders(order_id, customer_id) and Customers(customer_id, customer_city).

Know lossless-join and dependency-preserving decompositions at a definition level. Denormalisation deliberately adds redundancy to speed up reads (reports, analytics), at the cost of harder updates.

5. Transactions and ACID

A transaction is a logical unit of work that must happen completely or not at all, such as transferring money.

• Atomicity: all or nothing (the recovery system undoes partial work)
• Consistency: the database moves from one valid state to another, respecting constraints
• Isolation: concurrent transactions behave as if they ran one after another
• Durability: committed changes survive crashes (write-ahead logs)

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;            -- ROLLBACK; undoes both if something fails

Transaction states: active, partially committed, committed, failed and aborted. Know savepoints at a basic level.

6. Concurrency problems and isolation levels

Problems when transactions interleave:

• Dirty read: reading another transaction's uncommitted change
• Non-repeatable read: reading the same row twice and getting different values
• Phantom read: the same query returns different sets of rows because another transaction inserted or deleted rows
• Lost update: two transactions overwrite each other's changes

Isolation level	Dirty read	Non-repeatable read	Phantom
Read uncommitted	possible	possible	possible
Read committed	prevented	possible	possible
Repeatable read	prevented	prevented	possible (in the SQL standard)
Serializable	prevented	prevented	prevented

Real databases differ in details (PostgreSQL defaults to read committed; MySQL InnoDB defaults to repeatable read), so say "in the SQL standard" when giving the table.

Serializability: a schedule is conflict serializable if it can be turned into a serial schedule by swapping non-conflicting operations; test it with a precedence graph (no cycle means serializable).

Concurrency control:

• Locking: shared (read) and exclusive (write) locks; two-phase locking (2PL) has a growing phase and a shrinking phase and guarantees serializability, but can deadlock
• Timestamp ordering and MVCC (multi-version concurrency control), where readers see a snapshot and do not block writers
• Optimistic concurrency: check for conflicts at commit time
• Database deadlocks are detected and one transaction is rolled back (OS deadlock section)

7. Recovery

The write-ahead log (WAL) records changes before they reach the data files, so after a crash the database can redo committed work and undo uncommitted work. Know checkpoints (reduce recovery time), and the difference between immediate and deferred update. Backups, replication and point-in-time recovery matter in real operations.

8. Indexing and storage

An index is a separate structure that lets the database find rows without scanning the whole table, like a book's index.

• B-tree / B+ tree: balanced trees, the default; support equality, range queries and ordering. B+ trees keep all keys in linked leaf nodes.
• Hash index: very fast equality lookups, no range queries
• Clustered index: defines the physical order of the rows (one per table; often the primary key). Non-clustered: separate structure pointing to rows.
• Dense vs sparse index, and composite indexes following the left-most prefix rule
• Trade-offs: faster reads, slower writes, extra storage; too many indexes hurt inserts and updates
• Check with EXPLAIN whether a query uses an index (SQL performance tips)

Partitioning splits a big table into pieces; sharding splits data across servers; replication copies data to other servers for availability and read scaling.

9. SQL concepts to revise alongside

INNER, LEFT, RIGHT, FULL joins; GROUP BY and HAVING; subqueries and EXISTS; window functions; views (saved queries); stored procedures, functions and triggers; DELETE vs TRUNCATE vs DROP; UNION vs UNION ALL. Relational algebra operators (select, project, join, union, difference, division) appear in university-style tests.

10. SQL vs NoSQL and the CAP theorem

	Relational (SQL)	NoSQL
Structure	Fixed schema, tables	Document, key-value, column-family, graph
Strength	Joins, transactions, strong consistency	Horizontal scale, flexible schema, speed for specific access patterns
Examples	PostgreSQL, MySQL	MongoDB, Redis, Cassandra, Neo4j

CAP theorem: during a network partition, a distributed system must choose between consistency and availability. Many NoSQL systems offer eventual consistency. The right choice depends on the use case: banking needs strong consistency; a social feed can tolerate eventual consistency (system design basics).

11. Frequently asked questions

1. DBMS vs RDBMS; database vs data warehouse
2. Primary key vs unique key vs foreign key; candidate key vs super key
3. What is normalisation? Explain 1NF, 2NF, 3NF and BCNF with an example
4. What is denormalisation, and when is it useful?
5. Explain ACID properties with an example
6. What are the isolation levels and the problems each prevents?
7. What is a deadlock in a database? How is it handled?
8. What is an index? Clustered vs non-clustered? When can an index hurt?
9. What is a B+ tree, and why do databases use it?
10. What is a view? What is a trigger? What is a stored procedure?
11. DELETE vs TRUNCATE vs DROP
12. What is MVCC? What is 2PL?
13. SQL vs NoSQL; when would you choose each?
14. What is the CAP theorem?
15. What is sharding vs replication?

12. How to study

Design the schema for a small app (a job portal, a library, an online shop), take it to 3NF, write the queries and add indexes where needed. Write down for each topic: definition, example, trade-off. Then solve key-finding and normalisation numericals, since they are common in written tests. This project also makes a strong portfolio entry.