DBMS Interview Questions & Answers: 60 Questions From Normalization to Transactions

DBMS shows up in almost every Indian software interview pipeline, but in different shapes. Campus drives and service companies ask direct theory: "What is BCNF?", "Explain ACID". Product companies hide the same concepts inside SQL questions, design discussions and project deep dives: "Why is this query slow?", "What happens if two users book the last seat at the same time?" This list covers both. Each answer is short enough to speak, with an example where an example is what convinces the interviewer.

How DBMS Is Tested in Interviews

Interviewers rarely want textbook definitions recited. They want you to connect a concept to a consequence: "a missing index causes a full table scan", "read committed allows non-repeatable reads, so a report can be inconsistent". Keep that framing in mind as you read.

DBMS Basics (Q1 to Q8)

Q1. What is a DBMS, and why not just use files?

A database management system is software that stores, retrieves and manages data while providing concurrency control, crash recovery, integrity constraints, security and a query language. With plain files, every application reimplements these: two processes writing the same file corrupt it, a crash mid-write leaves partial data, and finding a record means scanning the file. A DBMS solves data redundancy, inconsistency, concurrent access, atomicity and access control centrally.

Q2. What is the difference between DBMS and RDBMS?

An RDBMS is a DBMS based on the relational model: data in tables (relations) of rows and columns, relationships through keys, constraints such as foreign keys, and SQL as the query language. "DBMS" is the broader term and includes hierarchical, network, document and key-value systems. MySQL, PostgreSQL, Oracle and SQL Server are RDBMSs; MongoDB and Redis are non-relational DBMSs.

Q3. Explain the three-schema architecture and data independence.

The internal level describes physical storage (files, pages, indexes). The conceptual level describes the whole database's logical structure (tables, columns, constraints). The external level is the set of views each user or application sees. Physical data independence means changing storage (adding an index, moving files) does not change the conceptual schema. Logical data independence means changing the conceptual schema (adding a column) does not break external views. Logical independence is harder to achieve in practice.

Q4. What are DDL, DML, DCL and TCL?

Some texts put SELECT in a separate DQL category. Mention it if asked; it is not a point of disagreement worth arguing.

Q5. What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes rows matching a WHERE clause, fires row triggers, logs each row and can be rolled back. TRUNCATE removes all rows by deallocating data pages, is much faster, does not fire row triggers, and usually resets auto-increment counters. DROP removes the table definition, data, indexes and constraints. Rollback behaviour differs by engine: in PostgreSQL both TRUNCATE and DROP are transactional; in MySQL they cause an implicit commit. Saying "it depends on the database" here is a strong answer, not a weak one.

Q6. What is a view? Can you update a view?

A view is a stored query presented as a virtual table. It simplifies complex queries, restricts access to certain columns or rows, and provides logical data independence. Simple views over a single table without aggregation, DISTINCT or GROUP BY are usually updatable; views with joins or aggregates generally are not, or only partially. A materialized view stores the query result physically and must be refreshed; it trades freshness for read speed.

Q7. What are integrity constraints?

Rules the DBMS enforces on data: domain constraints (type, CHECK), entity integrity (primary key is unique and not null), referential integrity (a foreign key must match an existing primary key or be null), plus UNIQUE and NOT NULL. Referential actions such as ON DELETE CASCADE or ON DELETE SET NULL define what happens to child rows when a parent row is deleted.

Q8. What is the difference between a database schema and a database instance?

The schema is the structure: table definitions, columns, types, constraints. It changes rarely. The instance (or state) is the data stored at a particular moment, which changes with every insert, update or delete. Analogy: a class definition versus an object's current field values.

Keys and the ER Model (Q9 to Q16)

Q9. Explain super key, candidate key, primary key, alternate key and composite key.

Take Student(roll_no, email, aadhaar, name, dept), where roll number, email and Aadhaar are each unique.

• Super key: any set of attributes that uniquely identifies a row. {roll_no}, {roll_no, name}, {email, dept} are all super keys.
• Candidate key: a minimal super key, with no attribute removable. Here: {roll_no}, {email}, {aadhaar}.
• Primary key: the candidate key chosen as the main identifier, say roll_no. Not null, unique, one per table.
• Alternate keys: the candidate keys not chosen: email and Aadhaar.
• Composite key: a key with more than one attribute, for example (student_id, course_id) in an enrollment table.

Q10. What is a foreign key?

A column (or set of columns) in one table that references the primary key or a unique key of another table, enforcing referential integrity. In Orders(order_id, customer_id, ...), customer_id references Customers(customer_id), so you cannot create an order for a customer that does not exist. Foreign keys can be null unless declared NOT NULL, and they can reference the same table (an employee's manager_id).

Q11. What is the difference between a primary key and a unique key?

Both enforce uniqueness. A primary key cannot contain nulls and there is one per table; in InnoDB it also becomes the clustered index. A unique key allows nulls (in most databases, multiple nulls, because null is not equal to null) and a table can have many. Use a surrogate primary key (an auto-increment ID or UUID) and unique constraints on natural keys like email.

Q12. Surrogate key vs natural key: which should you use?

A natural key comes from the domain (email, PAN, ISBN). A surrogate key is system-generated with no business meaning (auto-increment, UUID, Snowflake ID). Surrogates are stable (emails change), compact and make joins cheap; natural keys avoid an extra column and make data self-describing. Most production systems use surrogate primary keys plus unique constraints on natural keys. In distributed systems, auto-increment IDs become a bottleneck, which is why ID generators exist; see designing a unique ID generator.

Q13. What is an ER diagram? Name its components.

An Entity-Relationship diagram models data before creating tables. Entities (rectangles) are things like Student and Course. Attributes (ovals) describe them; key attributes are underlined, multivalued attributes use double ovals, derived attributes (like age from date of birth) use dashed ovals. Relationships (diamonds) connect entities, such as Enrolls. Cardinality (1:1, 1:N, M:N) and participation (total or partial) are marked on the connecting lines.

Q14. How do you convert 1:1, 1:N and M:N relationships into tables?

• 1:N (one department, many employees): put the foreign key on the "many" side: Employee.dept_id.
• 1:1 (user and profile): put a unique foreign key on either side, usually the side with total participation, or merge the tables.
• M:N (students and courses): create a junction table Enrollment(student_id, course_id, grade) with a composite primary key and two foreign keys. Relationship attributes like grade live here.

Q15. What is a weak entity?

An entity that cannot be uniquely identified by its own attributes and depends on an owner (identifying) entity. Example: Dependent(name, age) of an Employee; two employees can each have a dependent named "Aarav". Its key is the owner's key plus a partial key: (emp_id, dependent_name). Shown with double rectangles and an identifying relationship in a double diamond.

Q16. What are generalization, specialization and aggregation?

Generalization is bottom-up: combine Car and Bike into Vehicle. Specialization is top-down: split Employee into Engineer and Manager with extra attributes. In tables, you can use one table with a type column, one table per subclass, or a parent table plus child tables sharing the key. Aggregation treats a relationship as an entity so it can participate in another relationship, for example a "Works_On(employee, project)" relationship being monitored by a Manager.

Normalization With a Worked Example (Q17 to Q26)

Q17. What is normalization and why is it needed?

Normalization is organising tables to reduce redundancy and avoid update, insert and delete anomalies, by decomposing tables based on functional dependencies. A non-normalized table stores the same fact many times, so one fact can end up with two different values. Normal forms are progressively stricter rules: 1NF, 2NF, 3NF, BCNF, then 4NF and 5NF for multivalued and join dependencies.

Q18. What are insertion, update and deletion anomalies?

Consider one table Enrollment(student_id, student_name, course_id, course_name, instructor, instructor_phone).

• Insertion anomaly: you cannot add a new course until some student enrolls, because student_id is part of the key.
• Update anomaly: an instructor's phone is stored in every row of every course they teach; updating it in one row but not others leaves contradictory data.
• Deletion anomaly: if the only student in a course drops it, deleting that row also deletes the only record of the course and its instructor.

Q19. What is a functional dependency?

X → Y means that whenever two rows agree on X, they agree on Y: X determines Y. student_id → student_name holds; student_name → student_id does not (names repeat). A dependency is trivial if Y is a subset of X. A partial dependency is a non-key attribute depending on part of a composite key. A transitive dependency is A → B → C where B is not a key.

Q20. Walk through 1NF, 2NF, 3NF and BCNF with an example.

This is the single most important DBMS question for freshers. Use one running example.

Starting table (not even 1NF, because courses holds a list):

1NF: atomic values, no repeating groups. One row per student-course pair:

Key: (student_id, course_id). Functional dependencies: student_id → student_name; course_id → course_name, instructor; instructor → instructor_office.

2NF: 1NF plus no partial dependencies. student_name depends on only student_id, part of the key; course_name and instructor depend on only course_id. Split:

• Student(student_id, student_name)
• Course(course_id, course_name, instructor, instructor_office)
• Enrollment(student_id, course_id)

3NF: 2NF plus no transitive dependencies of non-key attributes on the key. In Course, course_id → instructor → instructor_office. The office depends on the key only through instructor. Split:

• Course(course_id, course_name, instructor)
• Instructor(instructor, instructor_office)

Now the office is stored once per instructor. Update anomaly gone.

BCNF: for every non-trivial FD X → Y, X is a superkey. All tables above satisfy this. To see a 3NF table that is not BCNF, take TA(student, subject, ta) with rules: each TA handles exactly one subject (ta → subject), and each student has one TA per subject ((student, subject) → ta). Candidate keys: (student, subject) and (student, ta). The FD ta → subject has a non-superkey on the left, but subject is a prime attribute (part of a candidate key), so 3NF allows it. BCNF does not. Decompose into TA_Subject(ta, subject) and Student_TA(student, ta). The cost: the dependency (student, subject) → ta can no longer be checked within a single table.

Q21. What is the difference between 3NF and BCNF in one line?

3NF allows X → A if X is a superkey or A is prime; BCNF requires X to be a superkey, full stop. BCNF is stricter, and BCNF decomposition is always lossless but not always dependency-preserving, whereas 3NF can always be achieved with both properties.

Q22. What is a lossless join decomposition?

A decomposition of R into R1 and R2 is lossless if joining R1 and R2 gives back exactly R, with no spurious rows. Test for a binary split: the common attributes must be a key of at least one of the two tables, i.e. R1 ∩ R2 → R1 or R1 ∩ R2 → R2. In our example, Course and Instructor share instructor, which is the key of Instructor, so the split is lossless.

Q23. What is dependency preservation?

A decomposition preserves dependencies if every original FD can be enforced by checking the individual tables, without joining them. The BCNF TA example above loses (student, subject) → ta. This is the classic reason designers sometimes stop at 3NF.

Q24. How do you find candidate keys using attribute closure?

The closure X+ is every attribute determinable from X using the FDs. X is a super key if X+ contains all attributes, and a candidate key if no proper subset also does. Example: R(A, B, C, D) with A → B, B → C, C → D. A+ = {A, B, C, D}, so A is a candidate key. Tip: any attribute that never appears on the right side of an FD must be in every candidate key. Here, that is A.

Q25. What are 4NF and 5NF?

4NF removes non-trivial multivalued dependencies. If a table (employee, skill, language) stores independent skills and languages for each employee, every skill must be paired with every language, causing a cross-product of rows. Split into (employee, skill) and (employee, language). 5NF (project-join normal form) deals with join dependencies, where a table can only be losslessly reconstructed from three or more projections. 5NF is rarely asked beyond its definition.

Q26. What is denormalization, and when would you use it?

Deliberately adding redundancy to speed up reads, for example storing order_count on the customer row or copying the product name into order lines. It avoids expensive joins in read-heavy systems, analytics and caches, at the cost of more complex writes and possible inconsistency. A typical example: an e-commerce order line stores the price at purchase time, which is intentionally not normalized to the current product price, because history must not change.

SQL and Joins (Q27 to Q36)

Examples use two tables: Employee(id, name, dept_id, salary, manager_id) and Department(id, name). For a much larger set of SQL problems, see SQL interview questions.

Q27. Explain the types of joins.

Q28. Find employees whose department does not exist, and departments with no employees.

-- departments with no employees (anti-join)
SELECT d.name
FROM Department d
LEFT JOIN Employee e ON e.dept_id = d.id
WHERE e.id IS NULL;

-- equivalent with NOT EXISTS (safer than NOT IN when NULLs are possible)
SELECT d.name
FROM Department d
WHERE NOT EXISTS (SELECT 1 FROM Employee e WHERE e.dept_id = d.id);

Why not NOT IN? If the subquery returns any NULL, x NOT IN (..., NULL) evaluates to unknown for every row and returns nothing. This NULL trap is a favourite follow-up.

Q29. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping and cannot use aggregates. HAVING filters groups after GROUP BY and can use aggregates. Logical evaluation order: FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. Knowing this order explains why you usually cannot use a SELECT alias inside WHERE.

SELECT dept_id, AVG(salary) AS avg_sal
FROM Employee
WHERE salary > 0
GROUP BY dept_id
HAVING COUNT(*) >= 5;

Q30. Find the second highest salary.

-- handles duplicates and returns NULL if there is no second salary
SELECT MAX(salary) AS second_highest
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

-- Nth highest with a window function (N = 3 here)
SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM Employee
) t
WHERE rnk = 3;

Mention why DENSE_RANK and not ROW_NUMBER: with salaries 100, 100, 90, the second highest distinct salary is 90. ROW_NUMBER would return 100.

Q31. What is the difference between RANK, DENSE_RANK and ROW_NUMBER?

For salaries 100, 100, 90: ROW_NUMBER gives 1, 2, 3 (unique, arbitrary among ties); RANK gives 1, 1, 3 (ties share, then a gap); DENSE_RANK gives 1, 1, 2 (ties share, no gap). Window functions compute per row without collapsing rows, unlike GROUP BY.

Q32. Find employees who earn more than their manager.

SELECT e.name
FROM Employee e
JOIN Employee m ON e.manager_id = m.id
WHERE e.salary > m.salary;

A self join. The interviewer may ask how to include employees with no manager (use LEFT JOIN and decide how to treat NULL).

Q33. What is a correlated subquery?

A subquery that references the outer query's columns, so it is logically evaluated once per outer row. Example: employees earning more than their department's average.

SELECT e.name, e.salary
FROM Employee e
WHERE e.salary > (SELECT AVG(salary) FROM Employee x WHERE x.dept_id = e.dept_id);

Optimisers often rewrite these into joins, but a window function (AVG(salary) OVER (PARTITION BY dept_id)) is usually clearer and faster.

Q34. UNION vs UNION ALL?

UNION removes duplicates, which requires a sort or hash step. UNION ALL keeps all rows and is faster. Use UNION ALL unless you specifically need de-duplication.

Q35. How does NULL behave in SQL?

NULL means unknown. Any comparison with NULL (=, <>, <) yields UNKNOWN, which WHERE treats as false; use IS NULL. Aggregates like SUM and AVG ignore NULLs, while COUNT(*) counts rows and COUNT(col) counts non-null values. GROUP BY puts all NULLs in one group. These details explain many "why is my count wrong" bugs.

Q36. What are stored procedures and triggers? Should you use them?

A stored procedure is precompiled SQL logic stored in the database and invoked by name. A trigger runs automatically on INSERT, UPDATE or DELETE, for example writing an audit row. They reduce round trips and enforce rules close to the data, but business logic hidden in the database is harder to version, test and scale. Many product teams keep logic in the application and reserve triggers for auditing or derived columns. Giving this trade-off, not just the definitions, is what interviewers want.

Indexing (Q37 to Q44)

Q37. What is an index, and what does it cost?

An index is an auxiliary data structure, usually a B+ tree, that maps column values to row locations so lookups avoid scanning the whole table. A lookup on an indexed column drops from O(n) to O(log n) page reads. Costs: extra storage, slower inserts, updates and deletes (each index must be updated), and the optimiser may still skip an index when a query matches a large fraction of rows. The full mechanics are in database indexing and B-trees explained.

Q38. Why do databases use B+ trees instead of binary search trees or hash tables?

Disks and SSDs read in pages (commonly 4 to 16 KB). A B+ tree node fills a page and holds hundreds of keys, so the tree has very high fan-out and is shallow: a few levels cover millions or billions of rows, meaning only a handful of page reads per lookup. A binary tree would be about 30 levels deep for a billion rows. B+ trees keep all data pointers in leaves linked together, so range scans (BETWEEN, ORDER BY) are a sequential walk. Hash indexes give O(1) equality lookups but cannot do ranges or ordering.

Q39. Clustered vs non-clustered index?

A clustered index stores the table rows themselves in index order; there can be only one. In MySQL InnoDB the primary key is the clustered index. A non-clustered (secondary) index stores the indexed column plus a reference to the row: in InnoDB that reference is the primary key, so a secondary lookup is two B+ tree traversals. PostgreSQL stores tables as heaps and all indexes point to physical tuple locations; it has a one-time CLUSTER command but does not maintain clustering automatically.

Q40. What is a composite index, and what is the leftmost prefix rule?

An index on several columns, sorted by the first column, then the second within it, and so on. An index on (city, age) helps WHERE city = 'Pune' and WHERE city = 'Pune' AND age > 25, but not WHERE age > 25 alone, because the rows are not sorted by age globally. Put equality-filtered columns first and range-filtered columns after them.

Q41. What is a covering index?

An index that contains every column the query needs, so the database answers from the index alone without visiting the table (an "index-only scan"). For SELECT age FROM users WHERE city = 'Pune', an index on (city, age) covers the query. PostgreSQL and SQL Server let you add non-key payload columns with INCLUDE.

Q42. When does a database not use an index even if one exists?

• A function or expression on the column: WHERE YEAR(created_at) = 2026 (rewrite as a range or use an expression index).
• Leading wildcard: LIKE '%sharma'.
• Implicit type conversion, such as comparing a string column to a number.
• Low selectivity: the query matches a large share of rows, so a sequential scan is cheaper.
• Not using the leftmost column of a composite index.
• Stale statistics leading the optimiser to a bad estimate.

Show you know how to verify: run EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) and check for full scans.

Q43. Should you index every column?

No. Every index slows writes and uses memory and disk. Index columns used in frequent WHERE, JOIN, ORDER BY and GROUP BY clauses, prefer selective columns, and use composite indexes designed around actual query patterns. A write-heavy table, like an event log, may want only its primary key.

Q44. What is a dense vs sparse index?

A dense index has an entry for every search-key value. A sparse index has entries for only some values, typically one per data block, and works only when the data file is sorted on that key: find the largest entry not greater than the target, then scan the block. Clustered B+ trees behave like sparse indexes at the leaf-to-page level. This is a textbook favourite in campus interviews.

Transactions and Concurrency (Q45 to Q54)

Q45. What is a transaction? Explain ACID.

A transaction is a sequence of operations treated as one logical unit, like transferring ₹500 from account A to account B (debit A, credit B).

• Atomicity: both the debit and the credit happen, or neither. Implemented with undo logs and rollback.
• Consistency: constraints hold before and after (total money unchanged, no negative balance if that is a rule).
• Isolation: concurrent transfers do not see each other's half-done work, to the degree the isolation level promises.
• Durability: once committed, the transfer survives a crash, via the write-ahead log flushed to disk at commit.

Go deeper in ACID transactions and isolation levels explained.

Q46. What are the transaction states?

Active (executing), partially committed (last statement done, not yet durable), committed, failed (error detected), and aborted (rolled back). After abort, the system may restart the transaction or kill it.

Q47. What are dirty reads, non-repeatable reads, phantom reads and lost updates?

• Dirty read: T1 reads data written by T2 that has not committed; T2 rolls back, so T1 used data that never existed.
• Non-repeatable read: T1 reads a row twice and gets different values because T2 updated and committed in between.
• Phantom read: T1 runs the same range query twice and gets a different set of rows because T2 inserted or deleted matching rows.
• Lost update: T1 and T2 both read a counter as 10, both write 11; one increment is lost.

Q48. Explain the SQL isolation levels.

Real engines differ from the standard table. PostgreSQL's default is Read Committed; its Repeatable Read is snapshot isolation, which also prevents phantoms, and its Serializable uses Serializable Snapshot Isolation. MySQL InnoDB defaults to Repeatable Read and uses next-key (gap) locks to prevent most phantoms for locking reads. Oracle defaults to Read Committed. Interviewers are impressed when you know "the standard says X, but Postgres and MySQL actually do Y".

Q49. What is a schedule? What is serializability?

A schedule is an interleaving of operations from concurrent transactions. It is serializable if its effect equals some serial (one-after-another) execution. Conflict serializability is checked with a precedence graph: add an edge Ti → Tj when an operation of Ti conflicts with a later operation of Tj (same item, at least one write). The schedule is conflict serializable if and only if the graph has no cycle; a topological order gives an equivalent serial order. View serializability is a broader notion, but testing it is NP-complete, so systems enforce conflict serializability.

Q50. What is two-phase locking (2PL)?

Each transaction has a growing phase in which it acquires locks and releases none, then a shrinking phase in which it releases locks and acquires none. 2PL guarantees conflict serializability but allows deadlocks and cascading aborts. Strict 2PL holds exclusive locks until commit, preventing cascading aborts; rigorous 2PL holds all locks until commit. Most lock-based databases use strict 2PL.

Q51. Shared vs exclusive locks? What is lock granularity?

A shared (S) lock allows reading; many transactions can hold S locks on the same item. An exclusive (X) lock allows writing and is incompatible with any other lock. Granularity is the size of what is locked: row, page, table. Row locks give more concurrency with more overhead; intention locks (IS, IX) on the table let the database check quickly whether a table lock conflicts with existing row locks.

Q52. What is a deadlock in a database, and how is it handled?

T1 holds a lock on row A and waits for row B; T2 holds B and waits for A. Databases detect deadlocks with a wait-for graph (a cycle means deadlock) and abort one victim, usually the cheaper one to roll back. Prevention schemes such as wait-die and wound-wait use transaction timestamps. In application code, reduce deadlocks by acquiring locks in a consistent order (for example always lock the lower account ID first in a transfer), keeping transactions short, and retrying on deadlock errors.

Q53. What is MVCC?

Multi-version concurrency control keeps several versions of a row so readers see a consistent snapshot without blocking writers, and writers do not block readers. PostgreSQL stores old tuple versions in the table and cleans them with VACUUM; InnoDB reconstructs old versions from undo logs. Writers still conflict with writers on the same row. MVCC is why "readers don't block writers" is true in most modern databases.

Q54. Optimistic vs pessimistic locking: when do you use each?

Pessimistic: lock the row before modifying (SELECT ... FOR UPDATE), good when conflicts are frequent, such as the last few seats in a ticket booking system. Optimistic: read a version number, then update with a condition and retry on failure, good when conflicts are rare.

-- optimistic update: succeeds only if nobody changed the row since we read version 7
UPDATE inventory
SET stock = stock - 1, version = version + 1
WHERE product_id = 42 AND version = 7 AND stock > 0;
-- 0 rows affected means a conflict (or no stock): re-read and retry

Bonus: How does a database recover after a crash?

Through the write-ahead log (WAL): every change is written to the log before the data page is written to disk. On restart, recovery (for example the ARIES algorithm) redoes logged changes of committed transactions not yet reflected on disk and undoes changes of transactions that never committed. Checkpoints bound how much log must be replayed. This is how atomicity and durability are actually implemented.

NoSQL, CAP and Scaling (Q55 to Q60)

Q55. What are the types of NoSQL databases?

Q56. SQL vs NoSQL: how do you choose?

Choose relational when you need multi-row transactions, joins, strong consistency and a schema that is fairly stable: payments, orders, inventory. Choose NoSQL for flexible or nested schemas, very high write throughput, simple access patterns by key, or horizontal scale beyond what a single relational primary handles comfortably: activity feeds, IoT telemetry, chat message storage. Many systems use both. The line has blurred: PostgreSQL has JSONB and MongoDB has multi-document transactions. A deeper comparison is in SQL vs Postgres vs MySQL vs MongoDB.

Q57. What is the CAP theorem?

In a distributed data store, during a network partition, you must choose between consistency (every read sees the latest write, or errors) and availability (every request gets a non-error response). Without a partition you can have both. So "CA" is not a real option for a distributed system. CP systems refuse some requests during partitions; AP systems serve possibly stale data. The PACELC extension adds that, even without partitions, you trade latency against consistency. See the CAP theorem for system design interviews.

Q58. What is BASE?

Basically Available, Soft state, Eventually consistent: the design philosophy of many AP NoSQL systems. Replicas may disagree briefly but converge if writes stop. Contrast with ACID's strict guarantees. In practice many databases let you tune per query, for example Cassandra's consistency levels (ONE, QUORUM, ALL).

Q59. What is the difference between replication and sharding?

Replication copies the same data to several nodes for availability and read scaling (primary-replica or multi-primary); replicas may lag. Sharding (horizontal partitioning) splits different rows across nodes by a shard key, scaling writes and storage. Production systems usually do both: each shard is replicated. Choosing the shard key, handling hotspots and cross-shard queries are the hard parts; see database sharding strategies.

Q60. What is the difference between OLTP and OLAP?

OLTP (online transaction processing) handles many short reads and writes on current data: placing orders, updating balances. It is normalized and row-oriented. OLAP (online analytical processing) runs fewer, heavy queries scanning large history: "revenue by city by month". It favours denormalized star schemas and columnar storage (BigQuery, Redshift, Snowflake, ClickHouse). Data usually moves from OLTP to OLAP through ETL or change data capture pipelines.

How to Prepare DBMS for Interviews

1. Week 1, fundamentals. Keys, ER to tables, functional dependencies, normal forms. Solve 10 normalization problems by hand: list FDs, find candidate keys with closure, decompose, check losslessness.
2. Week 1 to 2, SQL. Install PostgreSQL or MySQL locally, create the Employee/Department tables above, and write 40 to 50 queries: joins, GROUP BY, HAVING, subqueries, window functions, NULL handling.
3. Week 2, indexing. Create a table with a million rows, run EXPLAIN before and after adding indexes, and try composite indexes in both column orders. Ten minutes of this beats an hour of reading.
4. Week 2 to 3, transactions. Open two database sessions, run the anomalies yourself at different isolation levels, and trigger a deadlock deliberately.
5. Connect to your projects. For each project on your resume, be ready to explain the schema, the main indexes, and how you would handle two concurrent updates.

DBMS is usually paired with operating systems and computer networks in fundamentals rounds. Continue with operating system interview questions and computer networks interview questions.

Frequently Asked Questions

Pranjal Jain

Ex-Microsoft SDE · IIT Kanpur · Founder of Prepflix. Helps engineers crack startup and product company interviews across India.