A database is the backbone of any IT system. As database developers, you must prove your expertise and knowledge in the DBMS (Database Management Systems) area for your next job interview and adequately answer DBMS interview questions if you'd like to achieve a high-paying role.
Interviewers might ask you a range of database interview questions, including DBMS basic interview questions, DBMS viva questions, and DBMS technical questions. To best prepare for the DBMS questions for your interview, you should review examples and practice. You might also consider exploring SQL Tutorials and Top SQL Interview Questions, as SQL is an integrated and vital feature in a DBMS.
Here’s a list of the top 50+ DBMS interview questions and answers that will help you sail smoothly to your next job.
DBMS Interview Questions and Answers
1. Define a DBMS (Database Management System). What is it used for?
Answer: DBMS are software applications that help you build and maintain logically related data. Here are a few advantages of using a DBMS:
- Limit data redundancy
- Restrict unauthorized access
- Enforce integrity constraints.
- More data availability, sharing, protection, and storage
2. What is a database?
Answer: A database is a collection of ordered data that’s stored electronically. Since it's organized and stored, you can easily access, aggregate, and manage it. We use a DBMS to manage and monitor a database.
3. How would you define a DBMS checkpoint?
Answer: A DBMS checkpoint is a point before which all the transactions in the DBMS were committed, and the database was in a consistent state. It is a point where you take a backup, and all the dirty pages (in-memory modified pages) and old transaction logs are cleared from the system to release memory space. The old data is then stored permanently on a storage disk. For example, a manual checkpoint can be added as follows:
CHECKPOINT 10
Where 10 is the duration in seconds.
Read more about DBMS Checkpoints.
4. Explain the different database language types.
Answer: There are four types of database languages:
- DDL (Data Definition Language) is used to define the database structure or skeleton, for example, creating the schema, tables, indexes, constraints, etc. Some DDL statements are created: alter, drop, rename, truncate, comment.
- DCL (Data Control Language) is used to retrieve saved data. Permissions and access control come under DCL and are done using the grant and revoke commands.
- DML (Data Manipulation Language) is used for accessing and manipulating data. Some DML statements are select, insert, delete, update, lock table, explain plan, etc. Basically, DML statements handle user requests.
- TCL (Transaction Control Language) is used to save or roll back changes done by DML. The commands used for TCL are commit and rollback.
5. What are the drawbacks of file processing systems?
Answer: There are several disadvantages of file processing systems:
- Data redundancy and inconsistencies
- Inefficient data access
- Data stored in different formats is challenging to share
- Issues with concurrent access
6. What is DBMS data abstraction?
Answer: Data abstraction is the hiding of irrelevant information from the user to facilitate a smoother interaction. There are three levels of abstraction:
- Physical Level: Lowest level of abstraction that tells how data is physically stored in memory. Data can be accessed through sequential or random access. Files are organized using B+ trees and hashing methods.
- Logical Level: Level that stores information in the form of tables. The relationships between various entities are also stored as simple structures.
- View Level: Top-most extent of abstraction. Users can view only a part of the actual database, in the form of rows and columns. It is possible to have multiple views of the same database. Storage and implementation details are hidden from the users.
7. What is functional dependency?
Answer: A functional dependency expresses the relationship between the non-key attributes and the primary key of a table. There are two types of functional dependency – trivial and non-trivial. The relationship can be described as A -> B, where A is the determinant, and B is the dependent column.
8. What is Denormalization?
Answer: Denormalization is a database optimization technique where redundant data is added to one or more tables to avoid joins. This makes data retrieval faster and more efficient.
9. Explain the difference between extension and intension.
Answer: Intention is the constant value specified during the database design phase, i.e. the description of the database or the schema, and is unlikely to change frequently. Extension is the actual data at a particular time, also referred to as the database snapshot. It is likely to change frequently.
10. What is System R?
Answer: System R is a database management system that provides a high level of data independence and database abstraction from the users. It has data control features like triggered transactions, authorization, integrity assertions, and provisions for data consistency.
11. What are the normal forms of BCNF, 1NF, 2NF, and 3NF?
Answer: All the above are normal forms of data, i.e. organizing data in such a way as to avoid data redundancy and insert, delete & update anomalies.
- 1NF: Also known as the First Normal Form. This rule states that a column of a table can hold only an atomic (single) value.
- 2NF: Short for second normal form. To apply the rule of 2NF, a table should be in 1NF. The rule states that any non-prime attribute (i.e. attribute that is not part of any candidate key) should not be dependent on the proper subset of the table’s candidate key.
- 3NF: Third normal form, the table should already be in 2NF. The rule states that any transitive dependency (functional) of a “non-prime” feature on a super key should be removed.
- BCNF: Advanced and stricter version of 3NF, also more popularly known as 3.5NF. For BCNF, a table should be in 3NF and follow the rule that for any functional dependency A->B, A should be the super key of the table.
12. What are ACID properties?
Answer: ACID properties are for transactions:
- A => Atomicity: The entire transaction happens in one go.
- C => Consistency: Database must be consistent before and after the transaction.
- I => Isolation: Transactions do not interfere with each other; multiple transactions can happen simultaneously.
- D => Durability: A successful transaction is reflected even if a system failure happens.
13. Explain the difference between a DELETE command and TRUNCATE command.
Answer:
|
DELETE |
TRUNCATE |
|
DML type |
DDL type |
|
Supports WHERE Condition |
Doesn’t support WHERE condition |
|
Can acquire a row lock |
Locks the entire table and page |
|
Speed of execution is less |
It is faster than delete |
|
There is a log for each row |
A single log that indicates the deallocation of a page |
|
Example: DELETE FROM employee WHERE emp_id = 2012; |
Example: TRUNCATE TABLE employee_temp |
14. Explain 2-Tier Architecture.
Answer: In DBMS, 2-Tier architecture is a type of database architecture where the User Interface (UI) or view layer (also called the presentation) runs on a client machine (desktop, laptop, tablet, phone, etc.) and the data is stored on a server. To call the database, clients use the ODBC connectivity APIs. This architecture enhances the security of the database, as the client can’t access the database directly.
15. What are the different types of database keys?
Answer: There are several types of DBMS keys:
- Primary Key: Column or set of columns that identify a particular row in a table
- Super Key: Single key or set of multiple keys that identifies rows of a table
- Candidate Key: A set of attributes that identify tuples of a table uniquely. It is essentially a super key without repeated attributes
- Alternate Key: One or more columns of a table that collectively identify each of a table uniquely
- Foreign Key: A common column that defines the relationship between two tables. maintains data integrity
- Compound Key: Consists of two more attributes that uniquely identify a record, even when the column is not unique by itself
- Composite Key: Primary key that has two or more attributes like {customer_id + mobile_number}
- Surrogate Key: Created when there is no naturally available primary key for a table. provides a unique identity to a row in the table
17. What is the purpose of normalization in DBMS?
Answer: Normalization is an important process that removes redundancy from a set of relations. It can reduce irregularities in the insert, delete and update commands by dividing huge tables into smaller tables and using keys to link them.
18. How Are entity, entity type, and entity set different?
Answer:
- Entity: An object with physical existence that is independent and can be differentiated from other objects: for example, an employee, student, course, job, etc.
- Entity type: A collection of similar entities.
- Entity set: A combination of entities of one type at any point in time
19. What is a CLAUSE in terms of SQL?
Answer: CLAUSE is an optional statement in SQL that defines a condition to present data. For example,
select * from employee WHERE emp_id = 2012;
Here, WHERE is the clause that identifies the row to be obtained. Some other clauses in SQL are GROUP BY, HAVING, and ORDER BY.
20. What is the difference between aggregation and atomicity?
Answer: Atomicity means that all actions are carried out at once, or none are carried out. This means that if a transaction is incomplete or fails in between everything will be rolled back to a previous stable state. Contrarily, we use aggregation to express relationships between various entities.
21. Explain the different types of relationships in the DBMS.
Answer: There are three types of relationships in DBMS:
- One-to-One: Each table record is related to only one record in another table. For example: employee_id, and unique_ssn_id
- One-to-many or vice-versa (many-to-one): A record in one table can be related to more than one record in another table. For example: employee_id and phone_number
- Many-to-Many: A record in the first table can be related to more than one record in the second table, and vice-versa. For example: customer_id and product_id.
22. Explain correlated subqueries in DBMS.
Answer: Correlated subqueries are subqueries that are executed once for each row processed by its main statement. For example:
select student_id, student_name, marks from students outer where salary > (select avg(marks) from students where subject = outer.students);
23. What is the difference between two- and three-tier architectures?
Answer: The two-tier architecture follows a client-server architecture, where there is a straight communication link between client and server. Two-tier architecture gives a high performance as there is no middle layer. The client can be any device like a mobile, desktop, or laptop, while all the data sits in a database.
Three-tier architecture consists of the following:
- Presentation layer: A web browser, java applet, WAP phone, etc.
- Business layer: Business logic like data validation, insertions, calculations, etc.
- Data access layer: This is a data source like database, mainframe system, or ERP system. The business layer acts as a middle tier that separates the business logic from the presentation and data layer for better reusability, data integrity, scalability, and performance.
-
What is the Difference between Trigger and Stored Procedures?
Answer: Users can explicitly invoke stored procedures. Stored procedures can take certain input parameters and return output values, like any other program. Triggers are called spontaneously (on their own) when a specific event occurs. Triggers can’t take any input values nor return any output values.
24. What is the difference between Hash Join, Merge Join, and Nested Loops?
Answer:
- Hash-join: In this type of join, the database does full scanning of the main table, builds a RAM hash table, and then searches for matching tables in the other table. Hash-join is faster than the nested loop join but consumes more RAM resources.
- Merge join: This join uses simple concatenation followed by sorting (removing duplicates) and is the most effective technique out of all the joins.
- Nested loop join: In this type of scan, the driving table (main) accesses rows through index range scans, and the result set of the driving table is nested with the probe of the other (secondary) table using the index scan method.
25. Distinguish between proactive, retroactive, and simultaneous Update.
Answer:
- Proactive updates are those that eventuate in the database before they become useful in the real world.
- Retroactive updates come about after they become effective in the real world.
- Simultaneous updates occur at the same time as they become effective in the real world.
26. Explain the differences between clustered and non-clustered indexes.
Answer:
|
Clustered Index |
Non-clustered Index |
|
Defines the order of physical data storage even if they are inserted in random order. |
Does not sort the physical data. Indexes are stored in a different place than the actual data. |
|
Only one clustered index per table as data can be sorted in a single way only. |
More than one non-clustered index in a table is possible. |
|
In many databases, the primary key constraint creates a clustered index on the same column. |
A non-clustered index can be created as: CREATE NON-CLUSTERED INDEX IX_tblStudent_deptt ON student(deptt ASC) |
|
Example: if there is a table student that has a primary key student_id, the data will be stored in ascending order of student_id like 1,2,3,4. The clustered index will be automatically created on student_id. |
An index is stored in a separate table along with its address (a reference to the row), dept row address ECE <address> |
|
No extra space required for these indexes. |
Consumes storage space. |
27. What is the difference between specialization and generalization?
Answer:
|
Generalization |
Specialization |
|
Creating groupings from various entity sets |
Creating subgrouping within an entity set |
|
Starts with several entity sets and creates a high-level entity with some common features |
Starts with a single entity set and then creates a different set using different features |
|
Applied to a group of entities |
Applied to a single entity |
|
Follows bottom-up approach |
Follows top-down approach |
28. Explain the differences between network and hierarchical database models.
Answer: In a hierarchical database model, data is organized into nodes in a tree-like structure. A node is connected to only one parent node above it. Hence, data in this model has a one-to-many relationship. An example of this model is the Document Object Model (DOM) often used in web browsers.
The network database model is a refined version of a hierarchical model. Here, too, data is organized in a tree-like structure. However, one child node can be connected to multiple parent nodes. This gives rise to a many-to-many relationship between data nodes. IDMS (Integrated Database Management System), Integrated Data Store (IDS) are examples of Network Databases.
|
Hierarchical model |
Network model |
|
Parent-child relationship between records |
Pointers or links relationships |
|
Update and delete operations are prone to data inconsistencies |
No data inconsistencies |
|
Doesn’t support many to many relationships |
Supports many to many relationships |
|
Creates a tree structure and data traversal is a bit complex |
Creates graph structure where data traversal is easy, as a node can be accessed both ways, i.e. parent-child or vice-versa |
29. What is deadlock?
Answer: Deadlock happens when a set of processes are blocked. In a deadlock, each process holds up a resource that the other process requires. The other process waits for a resource to be released by the same or another process.
30. What is an RDBMS?
Answer: An relational database management system (RDBMS) is an application that allows you to create, update, and administer a relational database. An RDBMS is organized into tables, records, and columns, and database tables have a well-defined relationship. Tables communicate and share information. which enables data search, data organization, and reporting. An RDBMS is a subset of a DBMS.
31. What is an object-oriented database model?
Answer: In an object-oriented database model, data is represented by objects. For example, a multimedia file or record in a relational database is stored as a data object as opposed to an alphanumeric value.
32. What is SQL?
Answer: SQL (Structured Query Language) is a programming language used to communicate with data stored in databases. SQL language is relatively easy to write, read, and interpret.
33. What are DDL, DML, and DCL statements in SQL?
Answer:
- DDL: Data Definition Language is used to define the database and schema structure by using a set of SQL Queries like CREATE, ALTER, TRUNCATE, DROP and RENAME.
- DCL: Data Control Language is used to control the access of the users to the database by using a set of commands like GRANT and REVOKE in the SQL Query.
- DML: Data Manipulation Language is used for maintaining data by using SQL queries like SELECT, INSERT, DELETE and UPDATE.
Learn more about SQL Command.
34. What is index hunting?
Answer: A database index is a data structure that improves the speed of data retrieval operations on a database. Index hunting is the procedure of boosting the collection of indexes, done by using methods like query optimization and query distribution.
35. What is a distributed database?
Answer: A distributed database is a collection of multiple interconnected databases that are spread physically across various locations. The databases can be on the same network or multiple networks. A DDBMS (Distributed DBMS) integrates data logically so it appears as one single database to the user.
35. What is database partitioning and why is it important?
Answer: Database partitioning is a process where a logical database is divided into distinct independent parts. Database objects like tables, indexes are subdivided, managed, and accessed at the granular level.
Partitioning is a powerful functionality that increases performance with decreased cost. It improves the manageability and availability of data.
36. What is static SQL?
Answer: In a static SQL, the SQL statements are embedded or hard-coded in the application and they do not change at runtime. The process for data access is predetermined, hence more swift and efficient. The SQL statements are compiled at compile time
37. What is dynamic SQL?
Answer: In a dynamic SQL, SQL statements are constructed at runtime, where the application can allow the user to create the queries. Basically, you can build your query at runtime. It is comparatively slower than static SQL as the query is compiled at runtime.
38. Define data warehousing.
Answer: Data Warehousing is a technique that aggregates a large amount of data from one or more sources. Data analysis is performed on the data to make strategic business decisions for organizations.
39. Name one open-source RDBMS.
Answer: MySQL is an open-source relational database management system, which stores data in tables and maintains a relationship between the data. It uses the most powerful query language, SQL, for database access and has a very powerful syntax to create simple and complex queries for data retrieval and structure. Data in MySQL is organized and conforms to a certain format, and is the most popular structured database today. Its ‘free’ source code is available for study, modification, and distribution.
40. What is MongoDB?
Answer: MongoDB is a non-relational unstructured open-source database. This document-oriented database stores your data in collections made of individual documents. In MongoDB, a document is a big JSON object with no particular format or schema. MongoDB represents JSON documents in a binary-encoded format named BSON.
Read about the difference between MongoDB vs MySQL.
41. What is BI (Business Intelligence)?
Answer: Business intelligence (BI) is a technology-driven process for analyzing data and presenting information to help executives, managers, and other corporate end-users make business decisions. There are many tools available like SAP Business Intelligence, MicroStrategy, Sisense, and so on. The tools are user-friendly and help you gather data from varied sources for analysis.
42. What is the role of a DBA in DBMS?
Answer: The Database Administrator (DBA) plays some important roles in DBMS:
- Installing and configuring databases
- Performance monitoring
- Security planning and implementation
- Data backup and recovery
- Troubleshooting
- Data migration
43. What is an ER Diagram in DBMS?
Answer: An entity-relationship model or an entity-relationship diagram is a visual representation of data represented as entities. Attributes and relationships are set between entities.
44. What is an entity in an ER diagram?
Answer: An entity can be an identifiable, real-world object. For example, in a library database, books, publishers, and members can be considered entities. All these entities have some attributes or properties that give them their identity. In an ER model, the entities are related to each other.
45. What is data mining?
Answer: Data mining is a process of sorting through a large amount of data to identify patterns and trends. It uses complex mathematical and statistical algorithms to segment data for the prediction of likely outcomes. There are many tools for data mining like RapidMiner, Orange, Weka, and so on.
46. What is query optimization?
Answer: Query optimization is an important feature when it comes to the performance of a database. A query optimization identifies an execution plan that has the least estimated cost and time for evaluating and executing a query.
47. What is a catalog?
Answer: A catalog is a table that contains information like the structure of each file, the type, and storage format of each data item, and various constraints on the data. The information stored in the catalog is called metadata.
48. How many types of relationships exist in database designing?
Answer: There are three major relationship models in database design:
One-to-one: A row in table (A) is related to only one row in another table (B).
One-to-many: A row in table (A) is linked to many rows in another table (B). But a row in table (B) is linked to only one row in table (A).
Many-to-many: A row in table (A) is linked to many rows in table (B) and vice-versa.
49. What are the primitive operations common in all database management systems?
Answer: Addition, deletion, and modification are the most important primitive operations common to all DBMS.
50. What is cardinality in context to a database?
Answer: In SQL, the term cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table. The lower the cardinality, the more duplicate values in a column.
51. What is SQL SERVER?
Answer: SQL Server is an RDBMS developed by Microsoft. It is a very stable and popular server. The latest version of SQL Server is SQL Server 2017.
52. When should we use indexes?
Answer: We can use indexes to enforce uniqueness, facilitate sorting, and enable fast retrieval by column values. A frequently-used column is a good candidate for an index to be used with suitable conditions in WHERE clauses.
53. What is a hashing technique in a database?
Answer: Hashing is the transformation of a string of characters into a usually shorter fixed-length value or key that represents the original string. Hashing is used to index and retrieve items in a database because it is faster to find the item using the shorter hashed key than to find it using the original value.
54. Describe concurrency control.
Answer: Concurrency control is the process of managing simultaneous operations on a database so that database integrity is not compromised. There are two approaches to concurrency control.
Locking: Controlling access to data items using locks
Versioning: Using Multi-Version Concurrency Control
DBMS Interview Last-Minute Notes
There are tons of DBMS interview questions listed above for you to practice before your interview! As you sift through them and prepare, consider these DBMS last minute notes:
- Get a good night’s rest before your interview.
- Study visually by reading, or auditorily by reading questions and answers out loud - whatever works best for your learning style!
- Start practicing at least a week in advance.
- Get a friend to help role-play the DBMS interview questions.
Conclusion
That concludes our list of interview questions on DBMS. If you attend a DBMS interview anytime soon, let us know if you were asked any of the DBMS interview questions on this list. If we missed any DBMS questions for interviews, we’d love to know, whether they’re DBMS interview questions for freshers, DBMS technical questions, or any other database management system interview questions.
Do you want to expand your DBMS knowledge? Check out this course
Introduction to Database Management Systems (DBMS)
People are also reading:
