Top 10 DBMSInterview Questions & Answers (2025)

A Database Management System (DBMS) is software that manages, stores, and retrieves structured data efficiently. Unlike a file system, a DBMS provides: data integrity enforcement, concurrent access control, query language (SQL), role-based security, crash recovery, and elimination of data redundancy. File systems store raw files with no built-in relationships, constraints, or query capability — you'd have to write custom code for everything a DBMS gives you out of the box.

A DBMS stores data without enforcing relationships between tables (e.g., file-based or hierarchical databases like IMS). An RDBMS (Relational DBMS) organises data into tables with defined relationships enforced via primary keys, foreign keys, and referential integrity constraints. RDBMS follows E.F. Codd's 12 rules for relational databases. Examples: MySQL, PostgreSQL, Oracle, SQL Server are RDBMS; MongoDB (document store) is a NoSQL DBMS.

ACID ensures reliable database transactions: Atomicity — a transaction is all-or-nothing (either all operations succeed or none are committed); Consistency — a transaction brings the database from one valid state to another, never violating constraints; Isolation — concurrent transactions execute as if they were sequential (no dirty reads, phantom reads); Durability — once committed, data survives system crashes (written to disk/WAL). ACID is the foundation of transactional integrity in RDBMS.

Normalization eliminates redundancy and ensures data integrity by organising tables according to normal forms. 1NF: Each column must contain atomic (indivisible) values and each row must be unique — no repeating groups or arrays. 2NF: Must be in 1NF + every non-key attribute must depend on the entire primary key (no partial dependencies — only matters when the key is composite). 3NF: Must be in 2NF + no transitive dependencies (non-key columns must not depend on other non-key columns). BCNF (Boyce-Codd): stricter 3NF — every determinant must be a candidate key.

Denormalization intentionally introduces redundancy into a normalized schema to improve read performance. By storing pre-joined data, you eliminate costly JOIN operations at query time. Use it in read-heavy analytics systems, reporting tables, or data warehouses where writes are infrequent but queries are complex. Example: storing customer_name in an Orders table even though it exists in Customers — saves a JOIN on every order query. The tradeoff: updates become harder (must update in multiple places).

Primary Key (PK): uniquely identifies each row in a table, cannot be NULL, one per table. Foreign Key (FK): a column in one table that references the PK of another table — enforces referential integrity (you can't insert an order for a customer_id that doesn't exist). Unique Key: enforces uniqueness like PK but allows one NULL value and a table can have multiple unique keys. Example: email column in Users table can be a unique key.

A transaction is a logical unit of work consisting of one or more SQL operations that must all succeed or all fail together. Transaction states: Active (executing), Partially Committed (last statement executed, awaiting commit), Committed (all changes made permanent), Failed (error occurred, rollback needed), Aborted (rolled back, database restored to pre-transaction state). Use BEGIN TRANSACTION, COMMIT, ROLLBACK to control transactions in SQL.

INNER JOIN: returns only rows where there's a match in both tables. LEFT JOIN: returns all rows from the left table plus matched rows from the right (NULLs for no match). RIGHT JOIN: all rows from the right + matched from left. FULL OUTER JOIN: all rows from both tables with NULLs where no match. CROSS JOIN: Cartesian product — every combination of rows. SELF JOIN: joins a table to itself (e.g., finding employees with the same manager). NATURAL JOIN: auto-joins on columns with the same name.

An index is a data structure (typically a B-tree or hash) that speeds up data retrieval at the cost of additional storage and slower writes. Types: Primary Index — on the primary key, data file is physically sorted by it. Secondary Index — on non-key attributes, data file is NOT sorted by it (also called non-clustered). Clustered Index — determines the physical order of data on disk; a table can have only one. Non-Clustered Index — a separate structure pointing to row locations; a table can have many. Dense Index — entry for every search key. Sparse Index — entry for only some search keys (in a sorted file).

Clustered index determines the physical sort order of data in the table — the table IS the index. A table can have only one clustered index (usually the PK). Data retrieval is fastest for range queries on the clustered key. Non-clustered index is a separate B-tree structure containing the indexed column(s) plus a pointer (row locator) to the actual data row. A table can have many non-clustered indexes. Non-clustered is slower than clustered but more flexible.