DBMS Interview Questions

DBMS Interview Questions and Answers

These DBMS interview questions cover database fundamentals, keys, normalization, transactions, indexing, concurrency control, SQL, and database design. Each answer explains the concept directly and includes the distinctions that interviewers commonly expect candidates to make.

Basic DBMS Interview Questions for Freshers

1. What is a DBMS?

A Database Management System (DBMS) is software used to define, store, retrieve, update, and administer data in a database. It provides controlled access to data while handling concerns such as integrity, security, concurrency, and recovery. Examples include MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, and MongoDB.

2. How is a DBMS different from a file system?

File system	DBMS
Applications manage files directly.	The DBMS provides structured data-management services.
Duplicate and inconsistent data can be difficult to control.	Constraints and normalized designs help control redundancy and inconsistency.
Concurrent updates require application-level handling.	Transactions and concurrency-control mechanisms coordinate updates.
Relationships are implemented manually.	Relational databases represent relationships through keys and constraints.
Backup, recovery, and authorization are usually application-specific.	The DBMS normally provides facilities for backup, recovery, and access control.

3. What are the main types of DBMS?

• Hierarchical DBMS: Organizes records in a parent-child tree.
• Network DBMS: Allows records to participate in multiple relationships.
• Relational DBMS: Stores data in related tables and commonly uses SQL.
• Object-oriented DBMS: Stores data as objects with attributes and behavior.
• NoSQL DBMS: Includes document, key-value, wide-column, and graph databases.

4. What is an RDBMS?

An RDBMS is a DBMS based on the relational model. It represents data as relations, commonly implemented as tables containing rows and columns. Keys identify rows and connect tables, while constraints enforce valid relationships and values.

5. What are a table, row, column, and schema?

• Table: A named collection of related data arranged in rows and columns.
• Row or tuple: One record in a table.
• Column or attribute: A named property recorded for every applicable row.
• Schema: The logical definition of database objects, relationships, constraints, and data types.

6. What is data independence in DBMS?

Data independence is the ability to change one level of a database design without requiring changes at a higher level.

• Physical data independence: Storage details, such as indexes or file organization, can change without changing the logical schema.
• Logical data independence: The logical schema can change with minimal effect on user views and application programs.

Logical data independence is generally harder to achieve because applications often depend on table structures and attributes.

DBMS Keys and Integrity Constraint Questions

7. What are the different types of keys in DBMS?

• Super key: Any set of attributes that uniquely identifies a row.
• Candidate key: A minimal super key; removing any attribute makes it non-unique.
• Primary key: The candidate key selected as the main row identifier.
• Alternate key: A candidate key not selected as the primary key.
• Composite key: A key formed from more than one column.
• Foreign key: A column or column set that refers to a candidate key, usually a primary or unique key, in a related table.

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

Both enforce uniqueness, but a table has only one primary key and may have several unique constraints. Primary-key columns cannot contain NULL. Whether a unique constraint permits NULL, and how many NULL values it permits, depends on the database product.

9. What is referential integrity?

Referential integrity ensures that a foreign-key value either matches an existing referenced key or is NULL when the column permits NULL. It prevents orphaned references. A foreign-key definition can specify actions such as restricting, cascading, or setting values to NULL when a referenced row is updated or deleted.

10. What are entity, domain, and referential integrity?

• Entity integrity: Every row must be uniquely identifiable; a primary-key value cannot be NULL.
• Domain integrity: Column values must follow the permitted data type, range, format, and other rules.
• Referential integrity: Relationships between tables must remain valid.

DBMS Normalization Interview Questions

11. What is normalization in DBMS?

Normalization is the process of organizing relational tables to reduce avoidable redundancy and prevent insertion, update, and deletion anomalies. It uses dependencies between attributes to decompose tables while aiming to preserve information and important constraints.

12. What are insertion, update, and deletion anomalies?

• Insertion anomaly: A fact cannot be recorded without supplying unrelated data.
• Update anomaly: The same fact appears in several rows and must be changed everywhere.
• Deletion anomaly: Removing one record unintentionally removes another useful fact.

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

Normal form	Main requirement
First Normal Form (1NF)	Each field holds an atomic value, with no repeating groups in a row.
Second Normal Form (2NF)	The table is in 1NF, and every non-key attribute depends on the whole candidate key rather than part of a composite key.
Third Normal Form (3NF)	The table is in 2NF, and non-key attributes do not depend transitively on a candidate key through another non-key attribute.
Boyce-Codd Normal Form (BCNF)	For every non-trivial functional dependency X → Y, X is a super key.

14. What is a functional dependency?

A functional dependency X → Y means that whenever two rows have the same value for attribute set X, they must also have the same value for attribute set Y. Functional dependencies help identify candidate keys and determine whether a relation satisfies a normal form.

15. What is denormalization, and when is it used?

Denormalization deliberately introduces duplicated or precomputed data to reduce joins or speed up selected read operations. It may be appropriate for reporting, analytics, or read-heavy workloads, but it increases storage and makes consistency harder to maintain. It should follow measurement of an actual performance problem rather than replace sound database design by default.

DBMS Transaction and ACID Interview Questions

16. What is a database transaction?

A transaction is a logical unit of database work containing one or more operations. It should either complete according to the required rules or leave the database in a valid state after failure. Transferring money between accounts is a standard example because the debit and credit belong to one logical operation.

17. What are the ACID properties?

• Atomicity: A transaction’s changes are treated as one unit; incomplete work is rolled back.
• Consistency: A successful transaction moves the database from one valid state to another while respecting defined rules.
• Isolation: Concurrent transactions are controlled so that their interference is limited according to the chosen isolation level.
• Durability: Once a transaction is committed, its changes survive subsequent failures within the guarantees of the system.

18. What do COMMIT, ROLLBACK, and SAVEPOINT do?

• COMMIT: Completes the transaction and makes its changes durable.
• ROLLBACK: Reverses uncommitted changes in the transaction.
• SAVEPOINT: Creates a named point to which part of a transaction can be rolled back, where supported.

START TRANSACTION;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 101;

SAVEPOINT after_debit;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 202;

COMMIT;

In production code, the application should verify that both accounts exist and that all business rules are satisfied before committing.

19. What are dirty reads, non-repeatable reads, and phantom reads?

• Dirty read: A transaction reads data written by another transaction that has not committed.
• Non-repeatable read: Reading the same row twice produces different committed values because another transaction updated or deleted it.
• Phantom read: Repeating a range query returns a different set of rows because another transaction inserted or removed matching rows.

20. What are SQL transaction isolation levels?

The SQL standard defines Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Higher isolation generally prevents more concurrency anomalies but can reduce concurrency or increase retry requirements. Exact behavior depends on the database engine and its locking or multiversion concurrency-control implementation.

DBMS Concurrency, Locking, and Recovery Questions

21. What is concurrency control in DBMS?

Concurrency control coordinates transactions that access the same data at the same time. Its purpose is to preserve correctness while allowing useful parallel work. Common approaches include locking, timestamp ordering, optimistic concurrency control, and multiversion concurrency control (MVCC).

22. What are shared and exclusive locks?

A shared lock is commonly used for reading and can usually coexist with other shared locks. An exclusive lock is used when data is changed and conflicts with other locks on the same resource. The exact compatibility rules and lock granularity depend on the DBMS.

23. What is a deadlock in DBMS?

A deadlock occurs when transactions wait on one another in a cycle, so none can proceed. For example, transaction A holds one resource and waits for a resource held by transaction B, while B waits for A’s resource. A DBMS may detect the cycle and abort one transaction. Applications can reduce deadlocks by accessing resources in a consistent order, keeping transactions short, and retrying transactions selected as deadlock victims.

24. What is a transaction schedule, and what is serializability?

A schedule is the order in which operations from one or more transactions execute. A serial schedule completes one transaction before starting another. A concurrent schedule is serializable when its effect is equivalent to some serial execution, even though its operations are interleaved.

25. How does a DBMS recover from a failure?

Recovery mechanisms commonly combine transaction logs, checkpoints, backups, and restore procedures. Write-ahead logging records required log information before corresponding data changes are written to persistent storage. After a failure, the DBMS can use its log to redo committed work and undo incomplete work, according to the engine’s recovery design.

DBMS Indexing and Query Processing Questions

26. What is an index in DBMS?

An index is an auxiliary data structure that helps the DBMS locate rows without scanning every row in a table. Indexes can improve filtering, joining, ordering, and uniqueness checks. They consume storage and add work to INSERT, UPDATE, and DELETE operations, so indexes should be selected according to real query patterns.

27. What is the difference between clustered and nonclustered indexes?

A clustered index determines or closely corresponds to the physical organization of table data, depending on the DBMS. A nonclustered or secondary index stores index entries separately and points to table rows or to the table’s clustering key. Terminology and implementation differ among database products, so candidates should state which DBMS they are describing.

28. Why are B-trees commonly used for database indexes?

Balanced tree structures keep search paths short and ordered. They support equality lookups, range scans, ordered traversal, insertion, and deletion efficiently. Database systems often use B-tree variants designed to work well with page-based storage.

29. What is a composite index, and why does column order matter?

A composite index contains multiple columns. Its column order affects which query predicates and sort operations can use it efficiently. For an index on (department_id, hire_date), queries beginning with a condition on department_id can often use the index effectively. Whether a query using only hire_date benefits depends on the optimizer and DBMS.

30. What is a query execution plan?

A query execution plan describes how the DBMS intends to execute a query. It can show table scans, index access, join algorithms, sorting, aggregation, estimated row counts, and costs. Interviewers may ask candidates to use an EXPLAIN facility to identify missing indexes, poor join choices, or inaccurate estimates.

EXPLAIN
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10
ORDER BY hire_date DESC;

SQL and Relational Database Interview Questions

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

Statement	Purpose
DELETE	Removes selected rows and can normally use a WHERE clause.
TRUNCATE	Removes all rows using a table-level operation; exact logging, identity-reset, trigger, and transaction behavior is product-specific.
DROP	Removes the database object itself, including its definition.

32. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping and aggregation. HAVING filters groups after GROUP BY. A query can use both.

SELECT department_id, COUNT(*) AS employee_count
FROM employees
WHERE employment_status = 'ACTIVE'
GROUP BY department_id
HAVING COUNT(*) >= 5;

33. What are the main SQL join types?

• INNER JOIN: Returns matching rows from both inputs.
• LEFT OUTER JOIN: Returns every row from the left input and matching rows from the right input.
• RIGHT OUTER JOIN: Returns every row from the right input and matching rows from the left input.
• FULL OUTER JOIN: Returns matched rows plus unmatched rows from both inputs, where supported.
• CROSS JOIN: Returns the Cartesian product of the inputs.
• SELF JOIN: Joins a table to itself by using aliases.

34. What is the difference between UNION and UNION ALL?

UNION combines compatible query results and removes duplicate rows. UNION ALL preserves duplicates and usually avoids the duplicate-elimination work. Both operands must return compatible column counts and data types.

35. What is a view in DBMS?

A view is a named query that presents data as a virtual table. Views can simplify repeated queries and limit direct exposure of columns or rows. A regular view does not normally store its result independently. A materialized view stores a result that must be refreshed according to the product and configuration.

36. What is the difference between a stored procedure, function, and trigger?

• Stored procedure: A named routine invoked explicitly to perform a set of operations.
• Function: A routine that returns a value or table and may be usable within SQL expressions, depending on the DBMS.
• Trigger: Code executed automatically in response to a defined database event, such as an insert, update, or delete.

Capabilities, restrictions, and syntax for these objects vary significantly between database products.

Database Design Questions for Experienced Candidates

37. How would you design a database for a new application?

Begin with business requirements, data ownership, access patterns, retention rules, expected volume, and consistency needs. Identify entities and relationships, choose keys, define constraints, and normalize the relational model. Then evaluate indexes, transaction boundaries, security, backup and recovery, growth, and operational monitoring. Validate the design with representative queries and failure cases before optimizing it.

38. What is an ER model?

An Entity-Relationship model describes entities, their attributes, and the relationships between them. Relationship cardinalities include one-to-one, one-to-many, and many-to-many. In a relational design, a many-to-many relationship is normally represented by an associative table containing foreign keys to the participating tables.

39. What is database partitioning?

Partitioning divides a large logical table or index into smaller physical units while retaining a unified logical interface. Common strategies include range, list, and hash partitioning. It can improve maintenance and allow partition pruning for suitable queries, but it does not automatically make every query faster.

40. What is the difference between partitioning and sharding?

Partitioning is a general division of data into subsets and may occur within one database system. Sharding usually distributes subsets of data across separate database instances or nodes. Sharding can increase horizontal capacity, but it complicates routing, cross-shard queries, transactions, rebalancing, and recovery.

41. What is replication in DBMS?

Replication maintains copies of data on multiple database servers or nodes. It may support availability, disaster recovery, geographic distribution, or read scaling. Designs can use synchronous or asynchronous replication, each with different latency, consistency, and data-loss trade-offs.

42. How would you troubleshoot a slow database query?

• Capture the exact query, parameters, execution time, and frequency.
• Review the actual execution plan where the DBMS provides one.
• Compare estimated and actual row counts.
• Check indexes, join predicates, filters, sorts, and returned column volume.
• Look for blocking, lock waits, disk pressure, memory pressure, or stale statistics.
• Test a focused change with representative data and workload.
• Measure the result and check whether write cost or another query regressed.

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

Pessimistic concurrency control prevents conflicts by acquiring locks before performing sensitive work. Optimistic concurrency control allows work to proceed and checks for conflicting changes before committing, often through a version or timestamp column. Optimistic control is useful when conflicts are uncommon; pessimistic control may be appropriate when conflicts are frequent or costly.

DBMS Interview Preparation Checklist

• Explain super, candidate, primary, composite, and foreign keys with one consistent table example.
• Normalize a sample relation to 3NF and identify the anomalies removed at each step.
• Describe ACID properties through a transaction instead of giving only memorized definitions.
• Compare isolation anomalies and state that implementation details vary by database engine.
• Read an execution plan and explain when an index may help or add unnecessary write cost.
• Write joins, aggregation, subqueries, and transaction-control statements without relying on product-specific syntax unless requested.
• Prepare one database-design example covering constraints, indexes, transactions, security, and recovery.
• For experienced roles, describe a real performance or concurrency problem using measured evidence and the trade-offs of the chosen fix.

DBMS Interview Questions FAQ

Which DBMS topics should freshers prepare first?

Freshers should begin with tables, schemas, relational concepts, keys, integrity constraints, normalization, SQL joins, transactions, ACID properties, and indexes. They should be able to explain each topic with a small example rather than reciting definitions alone.

What DBMS questions are commonly asked of experienced candidates?

Experienced candidates are commonly asked about schema design, execution plans, indexing trade-offs, transaction isolation, deadlocks, replication, partitioning, sharding, migrations, and recovery. Answers should state workload assumptions and explain why one option was chosen over another.

Are DBMS and SQL interview questions the same?

No. DBMS questions cover broader concepts such as data models, normalization, transactions, storage, concurrency, recovery, and database architecture. SQL questions test the language used to define, query, and modify data in relational systems. Interviews often include both areas.

How should I answer a database-design interview question?

Clarify requirements and scale first. Identify entities, relationships, keys, constraints, and transaction boundaries. Then discuss normalization, indexes, access patterns, security, retention, backup, and expected growth. State assumptions and explain trade-offs instead of presenting one design as universally correct.

Should DBMS interview answers be database-specific?

Start with the general DBMS concept, then identify product-specific behavior when it matters. Isolation semantics, index organization, NULL handling, transaction behavior, procedural objects, and SQL syntax can differ among MySQL, PostgreSQL, Oracle Database, SQL Server, and other systems.