Core CS · 26 questions
Database Management Systems interview questions
Keys, normalisation, transactions, isolation, indexes and SQL: the DBMS questions campus interviews ask most, answered the way you would say them.
Basics
What is a DBMS, and why not just store data in files?
A DBMS is software that stores data and controls every access to it. Plain files can hold data too, but then every program has to solve the hard parts itself.
- Many users at once: concurrent reads and writes without corrupting data.
- Crashes: transactions and logs bring data back to a consistent state.
- Rules: constraints (keys, NOT NULL, foreign keys) stop bad data getting in.
- Queries: SQL asks for what you want; the DBMS works out how to get it fast.
- Security: who can read or change what.
What are the three levels of abstraction, and what is data independence?
Physical: how data is stored (files, pages, indexes). Logical (conceptual): what tables, columns and relationships exist. View (external): what each user or app is allowed to see.
Data independence means changing one level doesn't break the one above. Physical independence: add an index or move files and queries still work. Logical independence: add a column and existing views and apps still work. Logical independence is the harder one to get.
SQL or NoSQL: how do you choose?
SQL (relational): a fixed schema, joins, and strong transactions. Good when data is related and correctness matters: payments, orders, bookings.
NoSQL: document, key-value, wide-column or graph stores with a flexible schema, built to spread across many machines. Good for huge scale, simple access patterns, or data whose shape changes often: sessions, feeds, logs, catalogues.
The honest answer is about access patterns: what will you read and write, how much, and how consistent must it be?
Keys and constraints
Explain super key, candidate key, primary key and foreign key.
Take Student(roll_no, email, name, dept_id).
- Super key: any set of columns that identifies a row:
{roll_no},{roll_no, name},{email}. - Candidate key: a minimal super key, nothing extra:
{roll_no}and{email}. - Primary key: the candidate key you choose:
roll_no. The others are alternate keys. - Foreign key: a column that refers to another table's key:
dept_id→Department(id). - Composite key: a key of more than one column, like
(student_id, course_id)in an enrolment table.
Primary key vs unique key?
Both stop duplicates. A table has one primary key, and it can never be NULL. It can have many unique keys, and they allow NULL: MySQL and PostgreSQL allow several NULLs in a unique column; SQL Server allows one.
In many databases (MySQL's InnoDB, SQL Server by default) the primary key is also the clustered index, so it decides how rows are stored.
What is referential integrity?
A foreign key must point at a row that exists (or be NULL). The database enforces it: you can't add an order for a customer who doesn't exist.
When the parent row is deleted, the foreign key says what happens: ON DELETE CASCADE deletes the children too, SET NULL clears the link, and RESTRICT / NO ACTION refuses the delete.
ER model
How do 1:1, 1:N and M:N relationships become tables?
- 1:N (one department, many students): put the foreign key on the "many" side:
Student.dept_id. - 1:1 (a user and their profile): a foreign key on either side, made
UNIQUE; or merge the two tables. - M:N (students and courses): a third table,
Enrolment(student_id, course_id), with both foreign keys as its composite primary key. Attributes of the relationship, like grade, live there.
What is a weak entity?
An entity that can't be identified on its own. It needs its owner. A dependant of an employee is identified by the employee's id plus the dependant's name (the partial key), and it disappears if the employee is deleted.
Normalisation
What is normalisation, and what problem does it solve?
Splitting tables so that each fact is stored once. Repeated facts cause anomalies:
- Update: a department's name is stored on every student row; rename it and you must change them all, or the data contradicts itself.
- Insert: you can't add a new department until a student joins it.
- Delete: delete the last student and the department's details vanish too.
Explain 1NF, 2NF, 3NF and BCNF.
- 1NF: every cell holds one value; no lists in a column and no repeating groups.
- 2NF: 1NF, and no column depends on part of a composite key. In
Enrolment(student_id, course_id, course_name), course_name depends on course_id alone, so it moves to a Course table. - 3NF: 2NF, and no non-key column depends on another non-key column (no transitive dependency). In
Student(id, dept_id, dept_name), dept_name depends on dept_id, so it moves out. - BCNF: for every dependency X → Y, X is a super key. Stricter than 3NF: it also catches a key column that depends on a non-key column.
A short way to say 3NF: every non-key column depends on the key, the whole key, and nothing but the key.
What is a functional dependency?
X → Y means that whenever two rows have the same X, they have the same Y. roll_no → name: one roll number, one name. Normal forms are defined by which dependencies a table allows.
What is denormalisation, and when is it right?
Deliberately storing a fact more than once, usually to avoid an expensive join on a hot read path: keeping author_name on every post, or a precomputed order_total.
It trades write work and the risk of inconsistency for faster reads. It's common in reporting, read-heavy services and NoSQL designs.
Transactions and concurrency
What are the ACID properties?
Think of moving ₹500 from account A to account B.
- Atomicity: both the debit and the credit happen, or neither does.
- Consistency: rules hold before and after (money is neither created nor lost).
- Isolation: other transactions don't see the half-done transfer.
- Durability: once committed, it survives a crash (it's in the log on disk).
What can go wrong when transactions run at the same time?
- Dirty read: reading another transaction's change before it commits; it may roll back.
- Non-repeatable read: reading the same row twice and getting different values, because someone updated it in between.
- Phantom read: running the same query twice and getting new rows, because someone inserted them.
- Lost update: two transactions read a value, both change it, and one overwrites the other.
What are the isolation levels?
- Read Uncommitted: allows dirty reads.
- Read Committed: no dirty reads; non-repeatable reads can happen. The default in PostgreSQL, Oracle and SQL Server.
- Repeatable Read: rows you've read don't change; the SQL standard still allows phantoms. MySQL InnoDB's default.
- Serializable: as if transactions ran one at a time. The safest, and the slowest.
What is two-phase locking?
A transaction takes locks in a growing phase and releases them in a shrinking phase; once it releases any lock, it can't take a new one. This guarantees conflict-serializable schedules.
Strict 2PL holds write locks until commit, so no one reads uncommitted data (no cascading rollbacks). 2PL can still deadlock.
How does a database deal with deadlocks?
T1 locks row A and wants B; T2 locks B and wants A: both wait forever. Databases detect it with a wait-for graph (a cycle means deadlock), or a timeout, then pick a victim, roll it back and let the other go on.
In application code, avoid it by touching rows in the same order everywhere and keeping transactions short.
What is conflict serializability?
A schedule is conflict serializable if you can swap non-conflicting operations to turn it into some serial (one-after-another) schedule. Two operations conflict when they're from different transactions, touch the same item, and at least one is a write.
The test: draw a precedence graph (an edge T1 → T2 for each conflict where T1 goes first). No cycle means conflict serializable.
Indexes and SQL
What is an index, and what does it cost?
A separate structure (usually a B+ tree) that maps column values to rows, so a lookup reads a few pages instead of scanning the whole table: O(log n) instead of O(n).
The costs: every insert, update and delete must update the index too, and it takes space. Index the columns you filter, join and sort on, not every column.
Clustered vs non-clustered index?
A clustered index stores the rows themselves in key order, so a table has only one. A non-clustered index is a separate structure whose entries point to the rows (or to the clustered key), and a table can have many.
Range queries on the clustered key are fastest, because matching rows sit next to each other.
Why do databases use B+ trees?
Each node is one disk page holding hundreds of keys, so the tree is very shallow: three or four levels hold millions of rows, and a lookup reads three or four pages. All data sits in the leaves, and the leaves are linked in order, so range queries (BETWEEN, ORDER BY) just walk along them.
DELETE vs TRUNCATE vs DROP?
- DELETE: removes rows (all, or those matching a WHERE), one by one; fires triggers; can be rolled back.
- TRUNCATE: removes all rows at once by deallocating pages; much faster; no WHERE; usually resets identity counters.
- DROP: removes the table itself, its structure and data.
WHERE vs HAVING?
WHERE filters rows before grouping; HAVING filters groups after GROUP BY, so it can use aggregates.
SELECT dept_id, COUNT(*) FROM Student WHERE year = 4 GROUP BY dept_id HAVING COUNT(*) > 50; counts final-year students and keeps departments with more than 50.
Explain the types of joins.
INNER keeps only matching pairs. LEFT keeps every row of the left table, with NULLs where nothing matches; RIGHT is the mirror image. FULL OUTER keeps everything from both sides. CROSS pairs every row with every row. A self join joins a table to itself, like employees and their managers.
Write a query for the second-highest salary.
SELECT MAX(salary) FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee);
For the Nth highest, with ties counted once: SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM Employee) t WHERE r = 2;
What is a view? And a materialised view?
A view is a saved query that you can select from like a table. It stores no data, and it's used to simplify queries and to show users only some columns.
A materialised view stores its result, so reading it is fast, but it has to be refreshed when the base tables change.
A few cards a day, back just before you'd forget them.