Top 40 DBMS Interview Questions and Answers 2026

Last Updated: March 2026 | Level: Beginner to Advanced | Topics: Basics, ER Model, Normalization, Transactions, Indexing, NoSQL

Whether you're preparing for a software engineering role, a data engineering position, or a backend developer interview, DBMS (Database Management System) is one of the most consistently tested subjects. This guide covers the top 40 DBMS interview questions for 2026, organized by topic, with clear explanations, SQL examples, and comparison tables.

---

Table of Contents

1. Basics & ER Model (Q1–Q10)
2. Normalization (Q11–Q18)
3. Transactions & ACID (Q19–Q26)
4. Indexing (Q27–Q33)
5. NoSQL (Q34–Q40)

---

Basics & ER Model

Q1. What is a DBMS and how is it different from a file system?

A Database Management System (DBMS) is software that enables users to define, create, maintain, and control access to a database. Unlike a traditional file system, a DBMS provides:

Feature	File System	DBMS
Data Redundancy	High (duplicate files)	Controlled via normalization
Data Integrity	Managed manually	Enforced via constraints
Concurrent Access	Limited, error-prone	Handled via transactions
Data Security	OS-level only	Role-based access control
Query Language	None	SQL / query interfaces
Backup & Recovery	Manual	Built-in mechanisms

Example: In a file system, student records and enrollment records might duplicate student names. A DBMS uses foreign keys to link tables without repetition.

---

Q2. What are the different types of DBMS?

1. Hierarchical DBMS – Data organized in a tree structure (parent-child). Example: IBM IMS.
2. Network DBMS – Data organized as a graph (records linked via pointers). Example: IDMS.
3. Relational DBMS (RDBMS) – Data stored in tables with relationships. Example: MySQL, PostgreSQL, Oracle.
4. Object-Oriented DBMS – Data stored as objects. Example: db4o.
5. NoSQL DBMS – Flexible schema, designed for scale. Example: MongoDB, Cassandra, Redis.

Most modern interviews focus on RDBMS and NoSQL.

---

Q3. What is a schema in DBMS?

A schema is the logical structure or blueprint of a database. It defines how data is organized: tables, columns, data types, relationships, and constraints — without containing the actual data.

• Physical Schema: How data is stored on disk.
• Logical Schema: How data appears to users (tables, views).
• View Schema (External Schema): What individual users can see.

-- Example: Creating a schema
CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(100) UNIQUE,
    dob        DATE
);

---

Q4. What is an Entity-Relationship (ER) Model?

The ER Model is a high-level conceptual data model used to design databases visually before implementation. Key components:

• Entity: A real-world object (e.g., Student, Course).
• Attribute: Property of an entity (e.g., student_id, name).
• Relationship: Association between entities (e.g., Student enrolls in Course).

Types of Attributes:

• Simple (atomic): Cannot be divided further — e.g., Age.
• Composite: Can be divided — e.g., Name → First Name + Last Name.
• Derived: Calculated from other attributes — e.g., Age from DOB.
• Multivalued: Multiple values — e.g., Phone Numbers.

---

Q5. What are the different types of relationships in ER diagrams?

Cardinality	Meaning	Example
One-to-One (1:1)	One entity maps to exactly one other	Person ↔ Passport
One-to-Many (1:N)	One entity maps to many others	Teacher → Students
Many-to-One (N:1)	Many map to one	Students → Department
Many-to-Many (M:N)	Many map to many	Students ↔ Courses

Many-to-Many relationships are resolved using a junction/bridge table in RDBMS.

-- Junction table for Students ↔ Courses
CREATE TABLE Enrollments (
    student_id INT REFERENCES Students(student_id),
    course_id  INT REFERENCES Courses(course_id),
    enrolled_on DATE,
    PRIMARY KEY (student_id, course_id)
);

---

Q6. What is a primary key? How is it different from a unique key?

Feature	Primary Key	Unique Key
Uniqueness	Must be unique	Must be unique
NULL values	Not allowed	Allowed (one NULL per column)
Count per table	Only one	Multiple allowed
Purpose	Uniquely identifies rows	Prevents duplicates

CREATE TABLE Employees (
    emp_id   INT PRIMARY KEY,        -- Primary Key
    email    VARCHAR(100) UNIQUE,    -- Unique Key
    username VARCHAR(50) UNIQUE      -- Another Unique Key
);

---

Q7. What is a foreign key and why is it important?

A foreign key is a column (or set of columns) in one table that references the primary key of another table. It enforces referential integrity — ensuring that you can't insert a value in the child table that doesn't exist in the parent table.

CREATE TABLE Orders (
    order_id   INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

ON DELETE CASCADE means if a customer is deleted, all their orders are deleted automatically.

---

Q8. What is the difference between DDL, DML, DCL, and TCL?

Category	Full Form	Commands	Purpose
DDL	Data Definition Language	CREATE, ALTER, DROP, TRUNCATE	Define/modify schema
DML	Data Manipulation Language	SELECT, INSERT, UPDATE, DELETE	Manipulate data
DCL	Data Control Language	GRANT, REVOKE	Control access
TCL	Transaction Control Language	COMMIT, ROLLBACK, SAVEPOINT	Manage transactions

-- DDL
CREATE TABLE Products (id INT, name VARCHAR(50));

-- DML
INSERT INTO Products VALUES (1, 'Laptop');
SELECT * FROM Products;

-- TCL
BEGIN;
UPDATE Products SET name = 'Desktop' WHERE id = 1;
ROLLBACK;  -- Undoes the update

---

Q9. What is a view in DBMS?

A view is a virtual table based on the result of a SQL query. It doesn't store data physically — it's a saved query that behaves like a table.

Benefits:

• Simplifies complex queries
• Provides security (hide sensitive columns)
• Ensures data abstraction

CREATE VIEW ActiveCustomers AS
SELECT customer_id, name, email
FROM Customers
WHERE status = 'active';

-- Query the view
SELECT * FROM ActiveCustomers;

---

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

Command	Type	WHERE clause	Rollback	Effect
DELETE	DML	Yes	Yes	Removes specific rows
TRUNCATE	DDL	No	No (usually)	Removes all rows, resets identity
DROP	DDL	No	No	Removes entire table/structure

DELETE FROM Orders WHERE order_date < '2020-01-01';  -- Specific rows
TRUNCATE TABLE TempLogs;                              -- All rows, fast
DROP TABLE OldArchive;                               -- Destroys table completely

---

Normalization

Q11. What is normalization and why is it important?

Normalization is the process of organizing a relational database to reduce data redundancy and improve data integrity. It involves decomposing tables into smaller, well-structured tables using a series of normal forms (NF).

Goals:

• Eliminate redundant data (same data stored multiple times)
• Ensure data dependencies make sense (only storing related data in a table)
• Prevent update, insertion, and deletion anomalies

---

Q12. What are the different normal forms?

Normal Form	Requirement
1NF (First)	Atomic values, no repeating groups, primary key defined
2NF (Second)	1NF + No partial dependency on composite primary key
3NF (Third)	2NF + No transitive dependency
BCNF (Boyce-Codd)	Stricter 3NF — every determinant must be a candidate key
4NF	BCNF + No multi-valued dependencies
5NF	4NF + No join dependencies

Most interview questions focus on 1NF through BCNF.

---

Q13. Explain First Normal Form (1NF) with an example.

A table is in 1NF if:

1. All columns contain atomic (indivisible) values.
2. Each column contains values of a single type.
3. Each row is unique (has a primary key).

Violation (Not in 1NF):

StudentID	Name	Courses
1	Aditya	Math, Physics, CS

Fixed (1NF):

StudentID	Name	Course
1	Aditya	Math
1	Aditya	Physics
1	Aditya	CS

---

Q14. Explain Second Normal Form (2NF) with an example.

A table is in 2NF if it's in 1NF and every non-key attribute is fully functionally dependent on the entire primary key (no partial dependencies).

Violation (Not in 2NF) — composite PK: (StudentID, CourseID):

StudentID	CourseID	CourseName	Grade
1	C101	Math	A

Here, CourseName depends only on CourseID, not the full composite key — partial dependency.

Fixed (2NF):

Courses (CourseID, CourseName)
Enrollments (StudentID, CourseID, Grade)

---

Q15. Explain Third Normal Form (3NF) with an example.

A table is in 3NF if it's in 2NF and no non-key attribute depends on another non-key attribute (no transitive dependency).

Violation (Not in 3NF):

EmpID	EmpName	DeptID	DeptName
1	Rahul	D1	Engineering

DeptName depends on DeptID, not on EmpID — transitive dependency.

Fixed (3NF):

Departments (DeptID, DeptName)
Employees (EmpID, EmpName, DeptID)

---

Q16. What is BCNF (Boyce-Codd Normal Form)?

BCNF is a stricter version of 3NF. A table is in BCNF if, for every functional dependency X → Y, X must be a super key.

Scenario: A table (Student, Subject, Teacher) where:

• Each teacher teaches one subject.
• Each student can have multiple teachers per subject.

This can violate BCNF if a non-candidate-key determines another attribute. The fix is to decompose into two tables: (Teacher, Subject) and (Student, Teacher).

---

Q17. What are the anomalies that normalization prevents?

Anomaly	Description	Example
Insertion Anomaly	Cannot insert data without other unrelated data	Can't add a course without a student enrolled
Deletion Anomaly	Deleting a row removes unintended data	Deleting last student removes course info
Update Anomaly	Updating data requires changes in multiple rows	Changing dept name in 100 employee rows

---

Q18. What is denormalization and when should you use it?

Denormalization is the deliberate introduction of redundancy into a normalized database to improve read performance. It's a trade-off: you sacrifice some data integrity for speed.

Use cases:

• Read-heavy applications (dashboards, analytics)
• Data warehouses (star/snowflake schemas)
• When JOINs across many tables become performance bottlenecks

-- Denormalized: store category_name directly in Products
-- instead of always joining with Categories table
CREATE TABLE Products (
    product_id    INT PRIMARY KEY,
    product_name  VARCHAR(100),
    category_id   INT,
    category_name VARCHAR(50)  -- Redundant but faster reads
);

---

Transactions & ACID

Q19. What is a transaction in DBMS?

A transaction is a logical unit of work that consists of one or more database operations (SQL statements). It either completes fully or doesn't execute at all — ensuring data consistency.

BEGIN TRANSACTION;
    UPDATE Accounts SET balance = balance - 500 WHERE account_id = 1;
    UPDATE Accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;
-- If any step fails, ROLLBACK ensures no partial update

---

Q20. What are the ACID properties?

ACID stands for the four properties that guarantee database transactions are processed reliably:

Property	Meaning	Example
Atomicity	All operations succeed or none do	Bank transfer: debit + credit together
Consistency	DB moves from one valid state to another	Account balance can't go negative
Isolation	Concurrent transactions don't interfere	Two users booking last seat simultaneously
Durability	Committed transactions persist even after crashes	Transaction survives power failure

---

Q21. What are the different transaction isolation levels?

Isolation levels control how much a transaction is isolated from others:

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	✅ Possible	✅ Possible	✅ Possible
Read Committed	❌ Prevented	✅ Possible	✅ Possible
Repeatable Read	❌ Prevented	❌ Prevented	✅ Possible
Serializable	❌ Prevented	❌ Prevented	❌ Prevented

-- Set isolation level in PostgreSQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

---

Q22. What is a dirty read, non-repeatable read, and phantom read?

• Dirty Read: Transaction T1 reads data written by uncommitted transaction T2. If T2 rolls back, T1 has invalid data.

• Non-Repeatable Read: T1 reads a row, T2 updates and commits it, T1 reads again — gets different data.

• Phantom Read: T1 executes a query twice; between the two, T2 inserts new rows matching T1's query condition, so T1 sees "phantom" rows.

---

Q23. What is a deadlock in DBMS and how can it be resolved?

A deadlock occurs when two or more transactions are waiting for each other to release locks, creating a circular dependency — neither can proceed.

Example:

• T1 locks Row A, waits for Row B.
• T2 locks Row B, waits for Row A.

Resolution Strategies:

1. Deadlock Detection: System detects cycles and kills/rolls back one transaction.
2. Deadlock Prevention: Impose lock ordering or use timeouts.
3. Wait-Die / Wound-Wait: Timestamp-based protocols to decide which transaction waits or is rolled back.

---

Q24. What is the difference between shared lock and exclusive lock?

Lock Type	Alias	Read Allowed	Write Allowed	Multiple Holders
Shared Lock	Read Lock	✅ Yes	❌ No	✅ Yes
Exclusive Lock	Write Lock	❌ No (for others)	✅ Yes	❌ No

A transaction must acquire an exclusive lock to modify data, preventing others from reading or writing simultaneously.

---

Q25. What is Two-Phase Locking (2PL)?

Two-Phase Locking is a concurrency control protocol that ensures serializability:

1. Growing Phase: Transaction acquires all necessary locks (no release).
2. Shrinking Phase: Transaction releases locks (no new acquisitions).

This prevents transactions from interleaving in ways that cause inconsistency, at the cost of reduced parallelism and potential deadlocks.

---

Q26. What is the difference between optimistic and pessimistic concurrency control?

Approach	Strategy	Best For
Pessimistic	Lock data before using it	High-contention, write-heavy systems
Optimistic	Operate without locks; validate before commit	Low-contention, read-heavy systems

In optimistic control, if validation fails (conflict detected), the transaction is rolled back and retried.

---

Indexing

Q27. What is an index in DBMS and why is it used?

An index is a data structure that improves the speed of data retrieval operations on a table. It works similarly to a book's index — instead of scanning every page (full table scan), you jump directly to the relevant location.

-- Create an index on the email column
CREATE INDEX idx_email ON Users(email);

-- Query now uses index instead of full table scan
SELECT * FROM Users WHERE email = '[email protected]';

Trade-off: Indexes speed up reads but slow down writes (INSERT/UPDATE/DELETE must also update the index).

---

Q28. What are the types of indexes?

Index Type	Description	Use Case
Primary Index	Based on primary key; auto-created	Default row lookup
Secondary Index	On non-primary key columns	Frequent searches on non-PK columns
Clustered Index	Reorders physical data rows	Range queries; only one per table
Non-Clustered Index	Separate structure pointing to rows	Multiple per table
Composite Index	On multiple columns	Queries filtering on multiple columns
Unique Index	Enforces uniqueness	Email, username columns
Full-Text Index	Optimized for text search	Search engines, blog platforms

---

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

Feature	Clustered Index	Non-Clustered Index
Physical data order	Sorted by index key	Data order unchanged
Count per table	Only 1	Multiple allowed
Lookup speed	Faster (data is the index)	Slight overhead (pointer lookup)
Storage	No extra storage	Extra storage for index structure
Example	Primary key in InnoDB	Secondary indexes

---

Q30. What is a B-Tree index? How does it work?

A B-Tree (Balanced Tree) is the most common index structure in relational databases (MySQL, PostgreSQL). It maintains sorted data and allows searches, sequential access, insertions, and deletions in O(log n) time.

Structure:

• Root node at the top
• Internal nodes as guides
• Leaf nodes contain actual data pointers
• All leaf nodes at the same depth (balanced)

B-Trees are excellent for equality and range queries (=, <, >, BETWEEN).

---

Q31. What is a Hash Index? When should you use it?

A Hash Index uses a hash function to map keys to storage locations, enabling O(1) average lookup time.

When to use:

• Exact match queries only (=)
• NOT suitable for range queries (>, <, BETWEEN)

-- PostgreSQL supports hash indexes
CREATE INDEX idx_hash_username ON Users USING HASH (username);

Feature	B-Tree	Hash
Range queries	✅ Yes	❌ No
Exact match	✅ Yes	✅ Yes (faster)
Sorted order	✅ Yes	❌ No

---

Q32. What is query optimization and how do indexes help?

Query optimization is the process the DBMS uses to find the most efficient way to execute a SQL query. The query optimizer generates multiple execution plans and selects the lowest-cost one.

Indexes help by:

• Enabling Index Scan instead of Full Table Scan
• Supporting Index-Only Scans (data retrieved from index alone)
• Enabling efficient JOIN operations via indexed foreign keys

-- Use EXPLAIN to see query execution plan
EXPLAIN SELECT * FROM Orders WHERE customer_id = 42;
-- Look for "Index Scan" vs "Seq Scan" in output

---

Q33. When should you NOT use an index?

Indexes are not always beneficial:

• Small tables — full scan is faster than index overhead.
• Columns with low cardinality — e.g., a gender column with only 2 values.
• Frequently updated columns — each update must modify the index.
• Columns rarely used in WHERE/JOIN clauses — index never gets used.
• Bulk INSERT operations — temporarily drop indexes for bulk loads, then rebuild.

---

NoSQL

Q34. What is NoSQL and how does it differ from SQL (RDBMS)?

NoSQL (Not Only SQL) refers to databases designed for large-scale data storage that don't rely on the traditional relational model.

Feature	SQL (RDBMS)	NoSQL
Schema	Fixed, predefined	Flexible / dynamic
Scalability	Vertical (scale up)	Horizontal (scale out)
Data Model	Tables with rows	Documents, KV pairs, graphs, columns
Transactions	Strong ACID	Often BASE (eventual consistency)
Query Language	SQL	Varies (MongoDB Query Language, CQL, etc.)
Best For	Structured, relational data	Unstructured, high-volume, distributed
Examples	MySQL, PostgreSQL, Oracle	MongoDB, Cassandra, Redis, Neo4j

---

Q35. What are the types of NoSQL databases?

Type	Description	Examples	Use Cases
Document	Stores JSON/BSON documents	MongoDB, CouchDB	CMS, catalogs, user profiles
Key-Value	Simple key → value pairs	Redis, DynamoDB	Caching, sessions, leaderboards
Column-Family	Column-oriented wide rows	Cassandra, HBase	Time-series, analytics, IoT
Graph	Nodes and edges	Neo4j, Amazon Neptune	Social networks, recommendations

---

Q36. What is the CAP theorem?

The CAP Theorem (Brewer's Theorem) states that a distributed database can only guarantee 2 out of 3 properties simultaneously:

Property	Meaning
Consistency (C)	Every read receives the most recent write
Availability (A)	Every request receives a response (not necessarily the latest data)
Partition Tolerance (P)	System works even if network partitions occur

• CP Systems: Consistent + Partition tolerant (e.g., HBase, ZooKeeper)
• AP Systems: Available + Partition tolerant (e.g., Cassandra, CouchDB)
• CA Systems: Only in non-distributed setups (traditional RDBMS)

In practice, partition tolerance is mandatory in distributed systems, so the real trade-off is C vs A.

---

Q37. What is MongoDB and what are its key features?

MongoDB is a leading document-oriented NoSQL database that stores data as BSON (Binary JSON) documents.

Key Features:

• Flexible schema (no fixed structure required)
• Rich query language with aggregation pipelines
• Horizontal scaling via sharding
• Built-in replication with replica sets
• Supports ACID transactions (since v4.0)

// MongoDB document example
{
  "_id": ObjectId("507f1f77bcf86cd799439011"),
  "name": "Aditya Sharma",
  "skills": ["Python", "SQL", "MongoDB"],
  "address": {
    "city": "Mumbai",
    "zip": "400001"
  }
}

---

Q38. What is the difference between MongoDB and Cassandra?

Feature	MongoDB	Cassandra
Data Model	Document (JSON/BSON)	Wide-column (column families)
Query Language	MongoDB Query Language (MQL)	CQL (Cassandra Query Language)
Consistency	Tunable (default strong)	Tunable (default eventual)
Write Performance	Good	Excellent (optimized for writes)
Read Performance	Excellent	Good (with proper partition key)
Best For	General purpose, flexible queries	High-write, time-series, IoT
Joins	Limited ($lookup)	Not supported

---

Q39. What is Redis and what are its common use cases?

Redis (Remote Dictionary Server) is an in-memory key-value store known for its sub-millisecond latency.

Common Use Cases:

• Caching: Store frequently accessed DB query results.
• Session Management: Store user session data.
• Rate Limiting: Track API call counts per user.
• Pub/Sub Messaging: Real-time event broadcasting.
• Leaderboards: Sorted sets for gaming rankings.

import redis
r = redis.Redis(host='localhost', port=6379)

# Caching a DB query result
r.set('user:1:profile', json.dumps(user_data), ex=3600)  # TTL: 1 hour
data = r.get('user:1:profile')

---

Q40. What is eventual consistency and how does it work in NoSQL?

Eventual Consistency is a consistency model used by many NoSQL databases (AP systems). It guarantees that, given enough time and no new updates, all replicas of data will converge to the same value.

How it works:

• Write goes to one node first.
• Data propagates to other replicas asynchronously.
• During propagation delay, different nodes may return different values.
• Eventually, all nodes agree.

Trade-off vs Strong Consistency:

• Eventually consistent → Higher availability, better performance.
• Strongly consistent → Lower availability, slower but always accurate.

Example: After posting a tweet, some followers might see it slightly later than others — eventual consistency in action.

---

Quick Revision Cheat Sheet

Topic	Key Concept
Primary Key	Unique + Not Null identifier
Foreign Key	Referential integrity enforcer
1NF	Atomic values, no repeating groups
2NF	No partial dependencies
3NF	No transitive dependencies
BCNF	Every determinant is a candidate key
ACID	Atomicity, Consistency, Isolation, Durability
Clustered Index	One per table, reorders data
B-Tree	Default index; supports range queries
CAP Theorem	Choose 2 of: Consistency, Availability, Partition Tolerance
Eventual Consistency	All replicas converge over time

---

Frequently Asked DBMS Interview Questions (Quick List)

1. Difference between DBMS and RDBMS?
2. What is a surrogate key?
3. Explain the difference between UNION and UNION ALL.
4. What is a stored procedure vs a function?
5. What are triggers in DBMS?
6. Explain the difference between WHERE and HAVING.
7. What is a self-join?
8. What is the difference between INNER JOIN and OUTER JOIN?
9. What is a cursor in DBMS?
10. What are aggregate functions? Give examples.

---

Preparing for placements? Also check out our Top 40 Networking Interview Questions 2026 and Top 50 OS Interview Questions 2026.

© 2026 PlacementPapers.in — All rights reserved.