Database Management System (DBMS) is one of the most important subjects for computer science students and IT professionals. Whether you are applying for a software developer, database administrator, or data analyst role, interviewers often test your knowledge of DBMS concepts. Below are some of the most common DBMS interview questions and answers to help you prepare.
1. What is DBMS?
A Database Management System (DBMS) is software that stores, organizes, and manages data. It provides an interface between users and databases, ensuring data security, integrity, and easy retrieval. Examples include MySQL, Oracle, PostgreSQL, and SQL Server.
2. What are the types of DBMS?
- Hierarchical DBMS – Data is stored in a tree-like structure.
- Network DBMS – Data is represented using nodes and relationships.
- Relational DBMS (RDBMS) – Data is stored in tables with rows and columns.
- Object-Oriented DBMS – Stores data in the form of objects.
3. What is a Primary Key?
A primary key is a column (or set of columns) that uniquely identifies each record in a table.
- It must contain unique values.
- It cannot contain NULL values.
4. What is a Foreign Key?
A foreign key is a column that creates a relationship between two tables. It refers to the primary key of another table. This ensures referential integrity.
5. What are the differences between DBMS and RDBMS?
| Feature | DBMS | RDBMS |
|---|---|---|
| Data Storage | Files | Tables |
| Relationships | No relationships | Supports relationships |
| Normalization | Not supported | Supported |
| Examples | dBase, XML | MySQL, Oracle, SQL Server |
6. What is Normalization?
Normalization is the process of organizing data to minimize redundancy and improve efficiency.
- 1NF (First Normal Form): No repeating groups, atomic values.
- 2NF (Second Normal Form): No partial dependency.
- 3NF (Third Normal Form): No transitive dependency.
7. What is Denormalization?
Denormalization is the process of combining normalized tables to improve query performance. It reduces the number of joins but increases redundancy.
8. What is SQL?
SQL (Structured Query Language) is used to interact with databases. It includes commands like:
- DDL (Data Definition Language): CREATE, ALTER, DROP.
- DML (Data Manipulation Language): INSERT, UPDATE, DELETE.
- DQL (Data Query Language): SELECT.
- DCL (Data Control Language): GRANT, REVOKE.
9. What are Joins in DBMS?
A join is used to retrieve data from multiple tables. Types of joins:
- INNER JOIN: Returns matching rows from both tables.
- LEFT JOIN: Returns all rows from the left table and matching rows from the right.
- RIGHT JOIN: Returns all rows from the right table and matching rows from the left.
- FULL JOIN: Returns all rows when there is a match in either table.
10. What is a Transaction?
A transaction is a set of operations performed as a single logical unit of work. Transactions follow ACID properties:
- Atomicity – All or nothing execution.
- Consistency – Database remains in a valid state.
- Isolation – Transactions run independently.
- Durability – Changes are permanent.
11. What is Deadlock in DBMS?
A deadlock occurs when two or more transactions block each other by holding resources the other needs. Deadlocks can be prevented using:
- Timeout
- Resource ordering
- Deadlock detection and recovery
12. What is an Index?
An index is used to speed up data retrieval in a database. It works like the index of a book. However, indexes can slow down insertion and update operations because they need to be updated too.
13. What is a View in DBMS?
A view is a virtual table created using the result of a SQL query. It does not store data physically but provides a way to simplify complex queries and enhance security.
14. What is SQL Injection?
SQL Injection is a security vulnerability where attackers insert malicious SQL queries through input fields.
Prevention:
- Use prepared statements.
- Validate user inputs.
- Limit database permissions.
15. What are Triggers?
A trigger is a stored procedure in DBMS that automatically executes when a specific event (INSERT, UPDATE, DELETE) occurs on a table.
16. Difference Between OLTP and OLAP?
- OLTP (Online Transaction Processing): Focuses on day-to-day transactions, e.g., banking systems.
- OLAP (Online Analytical Processing): Focuses on data analysis, e.g., business reporting.
Conclusion
DBMS interview questions usually test your knowledge of database design, SQL queries, normalization, and transactions. Preparing these fundamental concepts with real-life examples will help you succeed in interviews.