50 DBMS Interview Questions (With Answers)

DBMS interviews test your understanding of database fundamentals — normalization, transactions, keys, indexing, and concurrency control. This guide covers 50 of the most common DBMS interview questions with clear answers, useful tables, and examples.

Quick reference

Basics

1. What is a DBMS? What are its advantages over a file system?

A Database Management System (DBMS) is software that stores, manages, and retrieves structured data efficiently. It provides an interface between users/applications and the physical data.

Advantages over a file system:

2. What is the difference between DBMS and RDBMS?

3. What is a schema? Difference between logical and physical schema?

A schema is the overall design or structure of a database.

This three-level architecture is called the ANSI/SPARC architecture. It provides data independence — changes at one level don't affect levels above it.

4. What is data independence?

Data independence means the ability to change the schema at one level without changing the schema at the next higher level.

• Physical data independence: change the physical storage structure (e.g., swap disk layout, add index) without changing the logical schema.
• Logical data independence: change the logical schema (e.g., add a column to a table) without changing external schemas (views seen by applications).

Logical data independence is harder to achieve than physical.

5. What are the different types of data models?

ER Model

6. What is an Entity-Relationship (ER) diagram?

An ER diagram is a conceptual data model that shows entities (things), their attributes (properties), and the relationships between them. It is used in the design phase before creating the actual database.

Key symbols:

7. What is a weak entity? Give an example.

A weak entity cannot be uniquely identified by its own attributes alone — it depends on a strong entity (its owner) for identification.

• A weak entity has a partial key (discriminator) that is unique only within the context of its owner.
• The relationship connecting a weak entity to its owner is called an identifying relationship.

Example: Order_Item is a weak entity. An order item is identified by its item_number only within a specific Order. Without the Order_ID, item_number = 3 is meaningless.

Order (strong) ——<< Order_Item (weak)
  Order_ID (PK)        item_number (partial key)
                       quantity

8. Explain the different types of attributes in ER modelling.

9. What are the different types of relationships and their cardinalities?

Cardinality defines how many instances of one entity relate to instances of another.

Participation constraints:

• Total participation (double line): every entity in the set must participate in at least one relationship. (e.g., every Employee must work in a Department)
• Partial participation (single line): some entities may not participate. (e.g., some Employees may not manage a project)

10. What is a ternary relationship?

A ternary relationship involves three entities participating simultaneously.

Example: A Supplier supplies a Part to a Project. This three-way association cannot be correctly decomposed into three binary relationships without losing information.

Supplier ─────── Supplies ─────── Part
                    │
                 Project

Keys

11. What are the different types of keys in a relational database?

12. What is a foreign key? What are referential integrity constraints?

A foreign key is an attribute (or set of attributes) in one table that references the primary key of another table.

Referential integrity means: a foreign key value must either match an existing primary key value in the referenced table, or be NULL (if allowed).

ON DELETE / ON UPDATE actions:

CREATE TABLE Enrollment (
  student_id INT REFERENCES Student(id) ON DELETE CASCADE,
  course_id  INT REFERENCES Course(id)  ON DELETE RESTRICT,
  PRIMARY KEY (student_id, course_id)
);

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

Normalization

14. What is normalization? Why is it needed?

Normalization is the process of organizing a relational database to reduce data redundancy and improve data integrity by decomposing tables into smaller, well-structured tables.

Problems caused by redundancy (update anomalies):

15. Explain 1NF, 2NF, 3NF, and BCNF.

First Normal Form (1NF):

• Each column holds atomic (indivisible) values.
• No repeating groups or arrays.
• All rows are unique.

VIOLATION: Student(ID, Name, Courses)  ← Courses is multivalued
FIX:       Student(ID, Name)  + Enrollment(Student_ID, Course)

Second Normal Form (2NF):

• Must be in 1NF.
• No partial dependency — every non-key attribute depends on the whole composite primary key, not just part of it.

VIOLATION: Enrollment(Student_ID, Course_ID, Student_Name)
           Student_Name depends only on Student_ID (partial dependency)
FIX:       Enrollment(Student_ID, Course_ID, Grade)
           Student(Student_ID, Student_Name)

Third Normal Form (3NF):

• Must be in 2NF.
• No transitive dependency — non-key attributes must not depend on other non-key attributes.

VIOLATION: Employee(Emp_ID, Dept_ID, Dept_Name)
           Dept_Name depends on Dept_ID, which is not the PK
FIX:       Employee(Emp_ID, Dept_ID)
           Department(Dept_ID, Dept_Name)

Boyce-Codd Normal Form (BCNF):

• Stronger version of 3NF.
• For every non-trivial functional dependency X → Y, X must be a super key.
• Resolves anomalies that 3NF misses when a table has multiple overlapping candidate keys.

VIOLATION: Course(Student, Subject, Teacher)
           Teacher → Subject (but Teacher is not a super key)
FIX: Split into two tables:
     Teacher_Subject(Teacher, Subject)
     Student_Teacher(Student, Teacher)

16. What is a functional dependency?

A functional dependency X → Y means: the value of attribute set X uniquely determines the value of attribute set Y.

Types of functional dependencies:

17. What are 4NF and 5NF?

Fourth Normal Form (4NF):

• Must be in BCNF.
• No multivalued dependency: X →→ Y means X determines a set of Y values, independent of other attributes.

VIOLATION: Employee(Emp_ID, Skill, Language)
           Emp_ID →→ Skill and Emp_ID →→ Language independently
FIX:       Employee_Skill(Emp_ID, Skill)
           Employee_Language(Emp_ID, Language)

Fifth Normal Form (5NF) / Project-Join Normal Form (PJNF):

• Must be in 4NF.
• No join dependency that is not implied by candidate keys — the table cannot be losslessly decomposed into smaller tables.
• Rarely needed in practice.

18. What is denormalization? When would you use it?

Denormalization is the intentional introduction of redundancy into a normalized schema to improve read performance at the cost of write complexity and storage.

When to denormalize:

• Read-heavy OLAP/reporting workloads where joins are expensive.
• Aggregated data in dashboards (pre-computed totals).
• Tables that are read millions of times per second but updated rarely.
• Data warehouses (star/snowflake schemas are intentionally denormalized).

Trade-offs:

SQL

19. What are DDL, DML, DCL, and TCL commands?

-- DDL
CREATE TABLE Student (id INT PRIMARY KEY, name VARCHAR(100));

-- DML
INSERT INTO Student VALUES (1, 'Alice');
UPDATE Student SET name = 'Alicia' WHERE id = 1;

-- DCL
GRANT SELECT ON Student TO analyst_role;

-- TCL
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

20. Explain all types of SQL JOINs.

-- Find all students and their enrolled courses (if any)
SELECT s.name, c.title
FROM   Student s
LEFT JOIN Enrollment e ON s.id = e.student_id
LEFT JOIN Course c     ON e.course_id = c.id;

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

22. What is a subquery? What are correlated subqueries?

A subquery (inner query) is a SELECT statement nested inside another SQL statement.

-- Simple subquery: find students who scored above average
SELECT name FROM Student
WHERE marks > (SELECT AVG(marks) FROM Student);

A correlated subquery references a column from the outer query — it executes once per row of the outer query.

-- Correlated: find each student's highest score
SELECT s.name, s.marks
FROM Student s
WHERE s.marks = (
  SELECT MAX(s2.marks)
  FROM Student s2
  WHERE s2.class = s.class  -- references outer query's column
);

23. What is a view? What are the advantages and limitations of views?

A view is a named virtual table defined by a SELECT query. It does not store data itself (unless materialized).

CREATE VIEW ActiveStudents AS
  SELECT id, name, email FROM Student WHERE status = 'active';

Advantages:

• Security: expose only specific columns/rows to users.
• Simplicity: hide complex JOIN logic behind a simple name.
• Logical data independence: application queries the view; underlying table changes don't break the app (if view is updated).
• Consistent data representation: business logic in one place.

Limitations:

• Performance: each query against the view re-executes the underlying SELECT.
• Not all views are updatable (views with GROUP BY, DISTINCT, aggregates, UNION, subqueries are typically read-only).
• Cannot have indexes directly (except materialized views).

24. What is the difference between a view and a materialized view?

Transactions

25. What are ACID properties?

ACID ensures reliable transaction processing in a DBMS.

How ACID is implemented:

26. What are the four transaction isolation levels?

Isolation levels control how concurrent transactions see each other's uncommitted changes.

Concurrency problems:

Isolation levels (ANSI SQL):

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
  -- All reads in this transaction see a consistent snapshot
COMMIT;

27. What is a savepoint?

A savepoint marks a point within a transaction to which you can roll back partially without aborting the entire transaction.

BEGIN;
  INSERT INTO orders VALUES (1, 'Alice', 500);
  SAVEPOINT sp1;

  INSERT INTO orders VALUES (2, 'Bob', 300);
  -- Something went wrong with Bob's order
  ROLLBACK TO SAVEPOINT sp1;  -- Undo only Bob's insert

  INSERT INTO orders VALUES (3, 'Charlie', 200);
COMMIT;  -- Alice and Charlie committed; Bob's insert was rolled back

28. What is a deadlock? How is it detected and prevented?

A deadlock occurs when two or more transactions each hold a lock the other needs, forming a circular wait.

T1 holds Lock(A), waits for Lock(B)
T2 holds Lock(B), waits for Lock(A)
→ Neither can proceed

Detection:

• DBMS maintains a wait-for graph (WFG). A cycle in the WFG = deadlock.
• The DBMS periodically checks for cycles and picks a victim transaction to abort (usually the one with least work done).

Prevention strategies:

Concurrency Control

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

Two-phase locking is a concurrency control protocol that ensures serializability.

Two phases:

1. Growing phase: transaction acquires locks but does not release any.
2. Shrinking phase: transaction releases locks but does not acquire any.

Variants:

30. What are shared (S) and exclusive (X) locks?

Lock compatibility matrix:

Intention locks (used in hierarchical locking for large databases):

• IS (Intention Shared): intent to place S lock on a descendant node.
• IX (Intention Exclusive): intent to place X lock on a descendant node.
• SIX (Shared Intention Exclusive): S lock on current node + intent to X lock descendants.

31. What is MVCC (Multi-Version Concurrency Control)?

MVCC keeps multiple versions of data to allow readers and writers to operate concurrently without blocking each other.

• Readers don't block writers and writers don't block readers.
• Each transaction sees a consistent snapshot of the database as of its start time.
• Old row versions are kept until no active transaction needs them (then garbage-collected).

Used by: PostgreSQL, MySQL InnoDB, Oracle, SQL Server (Snapshot Isolation).

T1 starts at time 10, reads row X → sees version at T=10
T2 starts at time 12, updates row X → creates new version at T=12
T1 still reads the T=10 version (its snapshot) — no lock needed

32. What is timestamp-based concurrency control?

Each transaction gets a unique timestamp when it starts. Data items also have timestamps:

• read_TS(X): largest timestamp of any transaction that read X.
• write_TS(X): largest timestamp of any transaction that wrote X.

Thomas's write rule:

Indexing

33. What is an index? What are the types of indexes?

An index is a data structure that speeds up data retrieval by providing a fast path to rows, at the cost of additional storage and slower writes.

Types:

34. What is a B-tree index? How does it work?

A B-tree (Balanced Tree) is the most common index structure in RDBMS.

Properties:

• All leaf nodes are at the same depth → guaranteed O(log n) search, insert, delete.
• Leaf nodes are linked → efficient range scans.
• Internal nodes store keys and pointers to child nodes.
• Data pages are typically pointed to by leaf nodes (B+ tree variant, used in most RDBMS).

        [30 | 60]
       /    |    \
  [10|20] [40|50] [70|80]

B-tree supports:

• Equality: WHERE id = 42 → O(log n)
• Range: WHERE id BETWEEN 10 AND 50 → O(log n + k)
• Order: ORDER BY id → traverse leaves
• Prefix: WHERE name LIKE 'Ali%' → works
• WHERE name LIKE '%ali%' → does NOT use B-tree

35. What is the difference between a clustered and non-clustered index?

File Organization and Storage

36. What are the types of file organization in DBMS?

37. What is a buffer pool/buffer manager?

The buffer pool (buffer cache) is a region of main memory that the DBMS uses to cache disk pages. Reading from disk is ~1,000× slower than reading from RAM.

Buffer manager responsibilities:

1. Keep frequently used pages in memory.
2. Implement a page replacement policy (LRU, Clock, MRU) when buffer is full.
3. Track which pages are dirty (modified but not yet written to disk).
4. Coordinate with the transaction manager (dirty pages must be flushed at COMMIT for durability).

Page replacement policies:

Advanced Topics

38. What is the CAP theorem?

The CAP theorem (Brewer's theorem) states that a distributed data store can guarantee at most two of the following three properties simultaneously:

Since network partitions are unavoidable in distributed systems, you must choose between CP or AP:

39. What is the difference between OLTP and OLAP?

40. What are stored procedures and triggers?

Stored procedure: a pre-compiled named program stored in the database and executed on demand.

CREATE PROCEDURE TransferFunds(
  IN from_account INT, IN to_account INT, IN amount DECIMAL(10,2)
)
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = from_account;
  UPDATE accounts SET balance = balance + amount WHERE id = to_account;
END;

CALL TransferFunds(1, 2, 500.00);

Trigger: a procedure that executes automatically in response to a DML event on a table.

CREATE TRIGGER audit_salary_change
AFTER UPDATE ON Employee
FOR EACH ROW
BEGIN
  IF OLD.salary <> NEW.salary THEN
    INSERT INTO SalaryAudit(emp_id, old_salary, new_salary, changed_at)
    VALUES (NEW.id, OLD.salary, NEW.salary, NOW());
  END IF;
END;

41. What is relational algebra?

Relational algebra is a procedural query language that forms the theoretical foundation of SQL. It operates on relations (tables) and returns relations.

Core operations:

Example:

Find names of students enrolled in course C101:
π(name) ( σ(course_id='C101') (Student ⋈ Enrollment) )

42. What is a cursor in DBMS?

A cursor is a database object that allows row-by-row processing of a result set — like an iterator over query results.

DECLARE student_cursor CURSOR FOR
  SELECT id, name FROM Student WHERE status = 'active';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN student_cursor;
read_loop: LOOP
  FETCH student_cursor INTO v_id, v_name;
  IF done THEN LEAVE read_loop; END IF;
  -- process each row
END LOOP;
CLOSE student_cursor;

Types:

43. What is hashing in DBMS? Types of hashing?

Hashing applies a hash function to a key to determine which storage bucket a record belongs to, enabling O(1) average-case access.

Types:

Collision handling:

• Chaining (open hashing): overflow records stored in a linked list attached to the bucket.
• Open addressing (closed hashing): probe next available bucket (linear probing, quadratic probing, double hashing).

44. What is query processing and query optimisation?

Query processing steps:

SQL query → Parser → Relational algebra expression
         → Query optimiser → Execution plan (most efficient)
         → Query executor → Result

Query optimiser tasks:

1. Parsing and validation: check syntax, verify table/column names exist.
2. Logical optimisation: rewrite algebraic expression (push selections down, remove redundancy).
3. Physical planning: choose access methods (index vs full scan), join algorithms.
4. Cost estimation: estimate I/O, CPU cost using statistics (row counts, column histograms).

Common join algorithms:

45. What is the difference between a primary index and a secondary index?

Practical and Scenario Questions

46. How would you design a database schema for a university system?

Entities and relationships:

Student(student_id PK, name, dob, email, enrollment_year)
Faculty(faculty_id PK, name, department_id FK, email, hire_date)
Department(dept_id PK, name, building, head_faculty_id FK)
Course(course_id PK, title, credits, dept_id FK)
Section(section_id PK, course_id FK, faculty_id FK, semester, year, room)
Enrollment(student_id FK, section_id FK, grade, enrollment_date)
           PK(student_id, section_id)
Prerequisite(course_id FK, prereq_id FK)
             PK(course_id, prereq_id)

Key design decisions:

• Enrollment is the M:N resolution table between Student and Section.
• Prerequisite is a recursive relationship on Course.
• Department.head_faculty_id is nullable (department may not have a head yet).
• Separate Section from Course to model the same course taught multiple times per semester.

47. What is the difference between a relation and a table?

In SQL, tables are multisets (bags), not true sets — they allow duplicate rows unless constrained.

48. What is the difference between a natural join and an equi-join?

-- Equi-join (preferred)
SELECT e.name, d.dept_name
FROM   Employee e
JOIN   Department d ON e.dept_id = d.dept_id;

-- Natural join (risky — joins on ALL matching column names)
SELECT name, dept_name FROM Employee NATURAL JOIN Department;

49. Explain the concept of a lossless decomposition.

A decomposition of relation R into R1 and R2 is lossless if the original relation can be perfectly reconstructed by joining R1 and R2 — no spurious (extra) tuples are introduced.

Test (Lossless-Join Property):
A decomposition {R1, R2} is lossless if at least one of these functional dependencies holds:

• R1 ∩ R2 → R1 − R2 (intersection is a super key of R1), or
• R1 ∩ R2 → R2 − R1 (intersection is a super key of R2)

Example:

R(A, B, C) with FD: A → B
Decompose into R1(A, B) and R2(A, C)
R1 ∩ R2 = {A}
A → B means A is a super key of R1(A, B) ✅ → Lossless

A lossy decomposition introduces extra rows when joined and loses information — always a design error.

50. What is the difference between SQL and NoSQL databases?

Common mistakes

DBMS vs related topics

FAQ

Q: Is DBMS the same as SQL?
No. DBMS is the system (software like MySQL or PostgreSQL). SQL is the query language used to interact with a relational DBMS. You can have a DBMS without SQL (e.g., MongoDB uses MQL).

Q: What is the most important normal form to know for interviews?
Know 1NF through BCNF thoroughly — especially 2NF (no partial dependency) and 3NF (no transitive dependency). BCNF is often asked in theory questions. 4NF and 5NF are bonus points.

Q: What's the difference between ACID and BASE?
ACID (Atomicity, Consistency, Isolation, Durability) is used by traditional RDBMS for strong consistency. BASE (Basically Available, Soft state, Eventual consistency) is used by many NoSQL systems that trade consistency for availability and scalability.

Q: How is a DBMS different from a file system?
A file system just stores and retrieves files. A DBMS adds: structured query support, data integrity enforcement, concurrent access control, transaction management, recovery mechanisms, and security controls.

Q: What is the difference between a schema and a database?
A database is the entire container (all data + structures). A schema is the logical blueprint describing the structure (tables, columns, constraints, relationships) — like the floor plan of a building vs the building itself. In some RDBMS (PostgreSQL), a schema is also a namespace within a database.

Q: Why study DBMS theory if I just use SQL day-to-day?
DBMS theory explains why SQL works the way it does — why JOINs behave as they do, what query optimisers are doing, why transactions prevent data corruption, and when to use indexes. It helps you debug strange behaviour, design schemas correctly, and reason about performance.