Technical Interview Masterclass: Top 25 DBMS Interview Questions & Concepts

Data fuels every modern business function, from tracking financial transactions to managing e-commerce inventories. To store, organize, and retrieve this data efficiently, systems rely on a Database Management System (DBMS).

This guide provides comprehensive, textbook-grade study notes covering the essential DBMS concepts frequently tested in technical interviews, structured to help both freshers and experienced engineers build a robust mental model of database internals.

1. Introduction to DBMS & Core Storage Paradigms

A Database Management System (DBMS) is a collection of programs that enables users to create, manage, update, and retrieve structured data securely and efficiently. It acts as an interface between the database and its end-users or application programs, ensuring that data remains consistently organized and easily accessible.

Real-Life Analogy

Imagine managing a massive, rapidly growing library. If you store book records on loose sheets of paper scattered across different drawers, you will struggle with duplicates, lost records, and slow search times. A DBMS is like hiring an automated, highly secure librarian who keeps every record cataloged, prevents duplicate entries, and retrieves any book's location instantly.

File System vs. Database Management System (DBMS)

Historically, data was stored in flat files within a file system. While simple, this approach has severe limitations when scaled.

2. Structural Foundations: Keys, Constraints, and Degrees

Relational databases rely on structural rules to maintain data integrity and establish relationships between tables.

The Primary Key

A Primary Key is a column (or set of columns) in a table that uniquely identifies each row or record.

• Uniqueness: No two rows can have the same primary key value.
• Non-Nullability: A primary key column cannot contain NULL values.

Unique Constraint vs. Null Constraint

Both constraints enforce data integrity, but they govern different properties of a column:

• Unique Constraint: Ensures that all values in a column are distinct across all rows. Unlike a primary key, a unique constraint allows a single NULL value (in most SQL dialects) to accommodate missing or unknown information while still enforcing uniqueness on non-null entries (e.g., a Citizen_ID column).
• Null / Not Null Constraint: Controls whether a column can accept missing or unknown data. Enforcing a NOT NULL constraint guarantees that every row must contain a valid, non-null value in that specific column.

Degree of a Relation

The degree of a relation refers to the total number of attributes (columns) present in a table. It represents the "width" of the table schema.

Degree = Number of Columns

• Unary Relation: A table containing exactly one column.
• Binary Relation: A table containing exactly two columns.
• Ternary Relation: A table containing exactly three columns.
• $N$-ary Relation: A table containing $N$ columns.

3. Database Design: Logical vs. Physical & Levels of Abstraction

Database design is divided into distinct phases to separate conceptual planning from physical implementation.

Logical vs. Physical Database Design

The Three Levels of DBMS Abstraction

To reduce system complexity and support data independence, a DBMS is structured into three levels of abstraction:

1. Physical Level (Lowest Level): Describes how the data is actually stored on physical storage devices (e.g., block sizes, memory allocation, and file structures). This level is managed primarily by Database Administrators (DBAs).
2. Logical Level (Middle Level): Describes what data is stored in the database and what relationships exist among those data points. It defines the database schema (tables, columns, data types, and constraints).
3. View Level (Highest Level): Describes only a part of the entire database that is relevant to a specific user group. It simplifies interaction by hiding unnecessary schema details (e.g., an HR employee sees employee names and departments, while a payroll manager sees names and salaries).

4. Database Languages & Query Execution: DDL, DML, DQL, and Optimization

To interact with a database, users write commands using specialized database sub-languages.

Database Languages

• Data Definition Language (DDL): Used to define, modify, or destroy the physical and logical structure of database objects (tables, indexes, views).
  • Commands: CREATE, ALTER, DROP, TRUNCATE, RENAME.
• Data Manipulation Language (DML): Used to manage and manipulate the data stored within existing database structures.
  • Commands: INSERT, UPDATE, DELETE.
• Data Query Language (DQL): Used to retrieve specific data from the database.
  • Commands: SELECT.

Query Optimization

Query Optimization is the process of finding the most efficient execution plan (the fastest and least resource-heavy route) to retrieve requested data.

When a query is submitted, the DBMS evaluates multiple execution paths, analyzing factors such as CPU load, memory usage, and disk I/O. It then selects the optimal path.

Why Query Optimization Matters

• Faster Response Times: Delivers results rapidly, improving user experience.
• Higher Throughput: Frees up system resources quickly, allowing the database to handle more concurrent queries.
• Reduced Hardware Wear: Minimizes disk reads and CPU cycles, extending the lifespan of physical storage drives and reducing power consumption.

5. Data Filtering & Aggregation: WHERE vs. HAVING

Filtering data is a core operation in SQL. However, developers often confuse the WHERE and HAVING clauses.

Key Differences

Practical Code Comparison

Scenario A: Filtering Individual Rows (Using WHERE)

Retrieve all orders where the individual item price is greater than $100.

SELECT Order_ID, Product_Name, Price
FROM Orders
WHERE Price > 100;

Scenario B: Filtering Aggregated Groups (Using HAVING)

Retrieve product IDs where the total sales across all orders exceed $500.

SELECT Product_ID, SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Product_ID
HAVING SUM(Sales) > 500;

6. Indexing Mechanics: Clustered vs. Non-Clustered Indexes

An index is a disk-based data structure associated with a table that speeds up data retrieval. It acts as a pointer system to avoid scanning the entire table (full table scan) for every query.

Real-Life Analogy

Think of a textbook. A Clustered Index is like the physical arrangement of the book itself—the pages are bound sequentially from page 1 to page 500. A Non-Clustered Index is like the index section at the back of the book. The index lists topics alphabetically (e.g., "Normalization... Page 204"), pointing you to the exact physical page where the data resides.

Clustered vs. Non-Clustered Index

7. Transaction Management & Concurrency Control

A transaction is a sequence of logically connected database operations treated as a single, indivisible unit of work.

Real-Life Analogy

Consider a bank transfer where Account A transfers $50 to Account B. This transaction requires two distinct operations:

1. Deduct $50 from Account A.
2. Add $50 to Account B.

If the system crashes after Step 1 but before Step 2, $50 vanishes. To prevent this, the DBMS treats both steps as a single transaction: either both steps succeed (Commit), or both are undone (Rollback).

Concurrency Control Problems

When multiple transactions execute simultaneously, they can interfere with one another, leading to data inconsistency.

1. Read-Write (Dirty Read) Problem

Occurs when Transaction 1 updates a row but has not yet committed. Transaction 2 reads this updated value. If Transaction 1 subsequently fails and rolls back, the data read by Transaction 2 becomes invalid ("dirty").

2. Lost Update Problem

Occurs when two transactions read the same initial data and attempt to update it concurrently. The transaction that commits last overwrites the updates made by the transaction that committed first.

Transaction 1 (T1)             Transaction 2 (T2)
       |                               |
  Reads Balance ($100)                 |
       |                               | Reads Balance ($100)
  Adds $10 (Local: $110)               |
       |                               | Adds $20 (Local: $120)
  Writes $110 to DB                    |
       |                               | Writes $120 to DB
       v                               v
  [Final DB Balance: $120. T1's update of $10 is completely lost!]

Concurrency Control Mechanisms

• Shared Locks (Read Locks): Allow multiple transactions to read a resource simultaneously, but prevent any transaction from writing to it.
• Exclusive Locks (Write Locks): Prevent other transactions from reading or writing to the locked resource.
• Two-Phase Locking (2PL): A protocol that ensures serializability by requiring that transactions acquire locks during a "growing phase" and release them only during a "shrinking phase."
• Timestamp Ordering: Assigns a unique, monotonically increasing timestamp to each transaction when it enters the system, executing operations in strict timestamp order to prevent conflicts.

8. Entity-Relationship (ER) Modeling & Structural Components

The Entity-Relationship (ER) Model is a high-level conceptual data model used to visually represent a database's logical structure.

Core Components

1. Entity: A real-world object or concept with an independent existence (e.g., Employee, Book).
2. Attribute: A property or characteristic of an entity (e.g., Employee_ID, Email).
   • Key Attribute: Uniquely identifies an entity (e.g., Student_ID).
   • Composite Attribute: Can be divided into smaller sub-parts (e.g., Name split into First_Name and Last_Name).
   • Multivalued Attribute: Can have more than one value for a single entity (e.g., Phone_Numbers).
   • Derived Attribute: Calculated from other attributes (e.g., Age derived from Date_of_Birth).
3. Relationship: An association or connection between two or more entities (e.g., an Employee works in a Department).

Entity vs. Entity Type vs. Entity Set

• Entity: A single, specific real-world object (e.g., a specific row: [ID: 1, Name: 'John Doe', Age: 20]).
• Entity Type: A collection of entities sharing common attributes; conceptually equivalent to a table schema or definition (e.g., the Student table structure containing ID, Name, and Age columns).
• Entity Set: The actual collection of all entities of a particular entity type at any given moment; equivalent to all the rows currently stored in a table.

Generalization vs. Specialization

These are design approaches used to establish hierarchical relationships between entities.

• Generalization (Bottom-Up): The process of combining multiple low-level entities with shared attributes into a single, high-level generalized entity. For example, combining Student and Faculty into a generalized Person entity.
• Specialization (Top-Down): The process of splitting a high-level entity into multiple sub-entities based on unique characteristics. For example, dividing an Employee entity into specialized sub-entities like Developer and Tester.

9. Mathematical Foundations: Relational Algebra

Relational algebra is a procedural query language that takes one or more relations as input and produces a new relation as output. It provides the mathematical foundation for SQL.

Key Operators

1. Selection ($\sigma$)

Filters rows (tuples) from a relation that satisfy a specified condition.

Example: Select all tuples from table R where column C is greater than 3:

2. Projection ($\pi$)

Selects specific columns (attributes) from a relation, discarding the rest. By default, projection removes duplicate rows from the output.

Example: Project only the Name and Age columns from the Student table:

3. Union ($\cup$)

Combines all tuples from two relations. The relations must be union-compatible (they must have the same number of attributes with matching data types in the same order).

4. Set Difference ($-$)

Finds tuples that exist in the first relation but do not exist in the second relation. The relations must be union-compatible.

5. Cartesian Product ($\times$)

Combines every row of the first relation with every row of the second relation. If relation $R$ has $m$ rows and relation $S$ has $n$ rows, the Cartesian product will yield $m \times n$ rows.

6. Rename ($\rho$)

Renames a relation or its attributes.

10. Database Normalization: 1NF to 5NF & BCNF

Normalization is the systematic process of organizing database tables to minimize data redundancy and eliminate update anomalies (insertion, update, and deletion anomalies). It involves dividing large, bloated tables into smaller, related tables.

1NF (Atomic Values) 
   v
2NF (1NF + No Partial Dependencies)
   v
3NF (2NF + No Transitive Dependencies)
   v
BCNF (Stricter 3NF - Every Determinant is a Candidate Key)
   v
4NF (BCNF + No Multivalued Dependencies)
   v
5NF (4NF + No Join Dependencies)

1. First Normal Form (1NF)

• Rule: Every column must contain atomic (indivisible) values. Multi-valued attributes or comma-separated lists within a single cell are strictly prohibited.

Naive (Non-1NF) Table

Normalized (1NF) Table

2. Second Normal Form (2NF)

• Rule: Must be in 1NF, and no partial dependencies can exist. Every non-key attribute must be fully functionally dependent on the entire primary key, not just a part of it (relevant for composite primary keys).

3. Third Normal Form (3NF)

• Rule: Must be in 2NF, and no transitive dependencies can exist. Non-key attributes must not depend on other non-key attributes. They must depend only on the primary key.
• Transitive Dependency: If $A \rightarrow B$ and $B \rightarrow C$, then $A \rightarrow C$ is a transitive dependency that must be eliminated.

4. Boyce-Codd Normal Form (BCNF)

• Rule: A stricter, stronger version of 3NF. For every functional dependency $X \rightarrow Y$, the determinant $X$ must be a candidate key.

5. Fourth Normal Form (4NF)

• Rule: Must be in BCNF and contain no multi-valued dependencies. A multi-valued dependency occurs when the presence of one or more rows in a table implies the presence of one or more other rows.

6. Fifth Normal Form (5NF)

• Rule: Must be in 4NF and contain no join dependencies. A table is in 5NF if it cannot be decomposed into smaller tables without losing data or introducing spurious rows when those tables are joined back together (lossless join).

11. Advanced Database Objects & Operations: Triggers, Constraints, and Pattern Matching

Triggers

A trigger is a stored program automatically executed (fired) by the DBMS in response to a specific event (such as an INSERT, UPDATE, or DELETE statement) occurring on a designated table.

Real-Life Analogy

When you add an item to your online shopping cart, a notification instantly pops up saying "Item added to cart." You did not manually run a program to show that alert; the action of adding an item to the cart table triggered the notification system automatically.

SQL Syntax for Creating a Trigger

CREATE TRIGGER trigger_name
AFTER INSERT ON shopping_cart
FOR EACH ROW
BEGIN
    -- Trigger body: actions to perform automatically
    INSERT INTO audit_log(action, timestamp) 
    VALUES ('Item Added', NOW());
END;

Integrity Constraints

Integrity constraints are rules enforced by the DBMS to prevent accidental damage to the database and ensure data accuracy.

• Domain Constraint: Restricts the values an attribute can take (e.g., Age must be an integer greater than 0).
• Not Null Constraint: Prevents a column from storing NULL values.
• Entity Integrity Constraint: Mandates that the primary key cannot be NULL and must be unique.
• Referential Integrity Constraint: Ensures that a foreign key value in a child table must match a primary key value in the parent table, or be NULL.

Pattern Matching in SQL

Pattern matching allows you to search for complex text patterns within columns using the LIKE operator and wildcards:

• % (Percent Sign): Matches zero or more characters.
• _ (Underscore): Matches exactly one character.

Practical Examples

-- Find all employees whose names end with the letter 'y'
SELECT Name FROM Employees WHERE Name LIKE '%y';

-- Find all employees whose names have 'a' as the second character
SELECT Name FROM Employees WHERE Name LIKE '_a%';

12. System Architecture & Practical SQL: 2-Tier vs. 3-Tier & Duplicate Detection

Two-Tier vs. Three-Tier Architecture

Practical SQL: Finding Fully Repeated Rows

In technical interviews, you may be asked to write a query to identify duplicate records in a table.

Scenario

Given a table named Registrations with columns Name and Section, write a query to find rows where both columns are fully repeated.

SQL Query Solution

To find the duplicate rows, group the data by both columns and filter for groups with a count greater than 1:

SELECT Name, Section, COUNT(*) AS Occurrence_Count
FROM Registrations
GROUP BY Name, Section
HAVING COUNT(*) > 1;

Query Explanation

1. GROUP BY Name, Section: Groups identical combinations of Name and Section together.
2. COUNT(*): Calculates the total number of occurrences for each unique combination.
3. HAVING COUNT(*) > 1: Filters out any groups that appear only once, leaving only the duplicated rows.

Summary and Key Takeaways

• DBMS vs. File System: A DBMS minimizes data redundancy, enforces consistency, and provides built-in backup, recovery, and security mechanisms that flat file systems lack.
• Primary Key: A column that uniquely identifies each record in a table. It cannot contain NULL values.
• Indexing: Clustered indexes physically sort the data rows in a table, while non-clustered indexes maintain a separate pointer structure. Indexing speeds up data retrieval at the cost of write performance.
• WHERE vs. HAVING: Use WHERE to filter individual rows before grouping; use HAVING to filter aggregated groups after grouping.
• Transactions: Treated as a single, indivisible unit of work. They must execute completely or not at all to maintain database consistency.
• Concurrency Control: Prevents anomalies like Dirty Reads and Lost Updates when multiple transactions run simultaneously. It uses locking protocols (like 2PL) and timestamping.
• Normalization: The process of organizing tables to eliminate redundancy and update anomalies. It progresses from 1NF (atomic values) up to 5NF.
• Three-Tier Architecture: Centralizes business logic on an application server between the client and the database, improving security, scalability, and maintainability over two-tier setups.