Explore Top 50 DBMS Interview Questions

The database management field offers lucrative job opportunities such as software developer, database administrator, information security analyst, and market research analyst. If one of these opportunities interests you, then it is time to get your preparation going. This blog will list DBMS interview questions for freshers, intermediate-level, and experienced candidates to help you ace your interview. 

DBMS Interview Questions for Freshers

Begin your preparation with DBMS basic interview questions. The following are the top DBMS interview questions for beginners: 

1. What is a database?

A database is a collection of data or information stored in electronic form in a computer system. It allows the user to easily access, manage, and update the information stored in it. 

2. What is a database management system?

The database management system is a software system that allows its users to create and manage databases. One can store, retrieve, and run queries on data in a DBMS. 

3. What is SQL? What is its purpose?

SQL (Structured Query Language) is a programming language used for the creation and manipulation of a database. The purpose of SQL is to interact with the database by inserting, updating, and deleting data in it. 

4. Name some of the popular database management systems.

Some popular database management systems include MySQL, Oracle, PostgreSQL, SQL Server, IBM DB2, etc.

5. What are the advantages of DBMS?

Some of the advantages of DBMS are as follows:

• Integrity Constraints: DBMS provides constraints that allow the data to be stored in a refined manner. 
• Multi-user Interface: Multiple users can access the same database simultaneously. 
• Data consistency: It allows the user to change the data structure without altering the structure of any running application program. 
• Data Recovery: It provides automatic data back-ups that make it easy to recover data when required. 
• Redundancy Control: It controls redundancy by integrating all the data into one single database. 

6. What are the different types of DBMS?

Different types of DBMS are as follows:

• Relational database management system or RDBMS
• Network DBMS
• Hierarchical DBMS
• Object-oriented DBMS

7. What is RDBMS?

A relational database management system or RDBMS is a software system that stores information or data in related tables. Data is stored here in rows and columns known as tuples and attributes. 

Also, explore the difference between DBMS and RDBMS.

8. What is query optimization?

Query optimization means determining possible plans for a query for efficient execution. It is advantageous when working with complex and large-scale databases as well. It reduces time and space complexity. 

9. What is a relationship in DBMS?

A relationship in DBMS means a scenario where two entities are related to each other. This relationship is established when the foreign key of one table references the primary key of another table. 

10. What is the entity-relationship model?

Entity-relationship model is a model that describes the conceptual design of the database. Real-world objects are represented as entities, and relationships are mentioned between them. It is useful in understanding the schema. 

11. What is aggregation?

Aggregation refers to the feature of the entity-relationship model where a relationship set is allowed to participate in another relationship set. 

12. What are the ACID properties in DBMS?

ACID properties refer to atomicity, consistency, isolation, and durability. They ensure that data transactions are processed reliably in a database system. 

13. What is atomicity?

The atomicity feature of DBMS states that a database modification will either follow all the rules or nothing at all. Therefore, if one part of a transaction fails, the entire transaction will fail. 

14. What is concurrency control?

Concurrency control is the process of managing simultaneous operations in a database to ensure that database integrity is maintained. It involves two approaches-optimistic and pessimistic. 

15. What is normalization?

Normalization is the process of organizing data to avoid any redundancy or duplication of data. It has many successive levels known as normal forms, where each level depends on the previous one. 

16. What is a candidate key?

A candidate key is a set of attributes that identify a table uniquely. There can be more than one candidate key in a table. But out of these, only one is chosen as the primary key. 

17. What are a super key, an alternate key, and a composite key? 

A super key is a key that uniquely identifies a tuple or a row in a database. A primary key, a unique key, or a candidate key is a super key. 

An alternate key is the candidate key that was not chosen as the primary key.

A composite key is a combination of two or more columns used to uniquely identify each tuple. 

18. What is a primary key?

A primary key is a set of attributes that uniquely identify a tuple or a row in a table. There is only one primary key in a table. It is selected from the candidate keys. 

19. What is relational algebra in DBMS?

Relational algebra is a procedural query language. It collects relations as inputs and returns occurrences of relations as outputs. 

20. What are unary operations in relational algebra?

Unary operations are those operations that use only single operands.

In relational algebra, there are two unary operations- PROJECTION and SELECTION.

Intermediate Level DBMS Interview Questions 

Here are intermediate-level database management system interview questions for candidates with one to three years of experience:

21. Name different languages in DBMS.

The different languages in DBMS are as follows:

• Data Definition Language: It consists of commands necessary to define the database. 
• Data Control Language: It consists of commands that deal with user permissions and controls of a database system.
• Transaction Control Language: It consists of commands that deal with the transaction of the database. 
• Data Manipulation Language: It consists of commands that are used to manipulate the data in the database.

22. What is database partitioning, and why is it important?

Data partitioning means dividing a logical database into independent units for better management and performance. It is essential because it enables the user to access large parts of a specific partition and store data in slower storage. 

23. What is data abstraction in DBMS?

Data abstraction is the process of hiding irrelevant data from the user to make the interaction with the complex database structure more accessible. The Data abstraction happens on three levels-physical, logical, and view. 

24. What is relational calculus?

Relational calculus is a non-procedural query language that uses mathematical predicate calculus instead of algebra. It does not work on mathematical fundamentals such as differential, algebra, and integration. There are two types of relational calculus-tuple and domain. 

25. What is denormalization?

Denormalization is a process wherein redundant data is added to the database to make the data less complex and boost the performance of the database. The process involves the introduction of normalization to remove the redundant data and then adding the redundant data as per requirement to avoid costly joins. 

26. What is a join in DBMS?

Join operation in DBMS is used to combine information from two or more relations. It is performed based on the same or related column. 

27. What is the inner join and the right join?

The inner join allows the user to return rows from both tables when specific conditions are specified.

The right join returns all the columns from the table on the right and matching records from the table on the left. If there is no match from the left table, the result is NULL. 

28. What is an entity and an entity type?

An entity is a real-world object that has a set of attributes in a database. In the real world, these attributes are considered the characteristics of the object. 

The entity type is a collection of entities having the same attribute. Usually, an entity type refers to one or more related tables in a particular database. 

29. What is an entity set and a weak entity set?

An entity set is a collection of all the entities of a specific entity type in a database. It is a set of all entities that share the same properties or characteristics. 

A weak entity set is a set of entities that do not have sufficient attributes to be a primary key. A member of the weak entity set is called a subordinate entity. 

30. What is functional dependency and transitive dependency?

Functional dependency is a constraint that is used to describe the relationship between different attributes in a relation. 

Transitive dependency is a constraint that occurs only in a relation of three or more attributes. 

31. What are the integrity rules in DBMS?

Data integrity in DBMS is imposed with the help of the following rules:

• Entity Integrity: According to this rule, the primary key cannot have a NULL value. 
• Referential Integrity: According to this rule, the foreign key can either be a NULL value or a primary key value of another relation. 

32. What is a stored procedure?

A stored procedure is a group of SQL statements already created and stored in the database. They help increase the reusability and decrease the complexity of the system. 

33. What is two-tier architecture in DBMS?

The two-tier architecture is similar to client-server architecture. Here, the applications on the client end can directly communicate with the database on the server side. 

34. What is the three-tier architecture in DBMS?

The three-tier architecture contains an extra layer between the client and the server. The client communicates with the application or GUI. This GUI further communicates with the database system. The introduction of GUI in the architecture is to enhance the security and accessibility of the system. 

35. What is the difference between the unique key and the primary key?

There are two differences between a unique key and a primary key of a table.

• Unique keys can have a NULL value, but the primary key cannot.
• A table can have more than one unique key, but it can have only one primary key. 

DBMS Interview Questions for Experienced

For the experienced candidates, DBMS technical interview questions are asked the most. Here are some DBMS interview questions and answers for candidates with more than three years of experience: 

36. What are the different types of normalization?

Different types of normalization are as follows:

• First Normal Form (1NF): It ensures no groups are repeated within a row. 
• Second Normal Form (2NF): Here, every non-key column value is dependent on the whole primary key. 
• Third Normal Form (3NF): A relation is in this normal form if it is in 2NF but has no transitive partial dependency.

37. What is shared lock and exclusive lock? 

A shared lock is used to only read the data item. It is a shared lock when many transactions hold a lock on the same data item and are allowed to read it. 

An exclusive lock allows a transaction to modify the data but prevents other transactions from reading or modifying it. Hence, only one transaction can own it at a time. 

38. What is indexing?

Indexing is a data structure method that facilitates quick retrieval of records from the database file. It is a small table with two columns. One stores the primary or candidate key of the table, and the other contains a set of pointers holding the address of the disk block where a particular key value is stored. 

39. What is a clustered index and a non-clustered index?

The cluster index sorts the rows in a table according to their fundamental values. There is only one clustered index for each table in the database management system. The index helps the DBMS in defining the order in which the data is stored in a table. 

Non-clustered indexes store the data at one location and the indices at another. It has pointers to the location of the data. A single table can have many non-clustered indexes because the index is stored in different directions. 

40. What is index hunting?

Index hunting is the process of boosting a collection of indexes. It improves the query performance and increases its speed. 

41. What is hashing?

Hashing in DBMS is a technique to search the location of the required data on the disc directly without using an index structure. It is used to index and retrieve items in a database since the shorter hashed key searches faster than the original value. 

42. What is a checkpoint in DBMS?

Checkpoint in DBMS is a process where all the previous logs are removed from the system and permanently stored on the storage disk. It is useful when the system crashes since transaction logs records from the checkpoints are used to recover all the committed data till the point of crash.  

43. What are the triggers in DBMS?

The triggers in the DBMS are a special kind of stored procedure that are not called directly by the user but are created and programmed to fire when a specific event occurs. No transactions are allowed within a trigger, and they do not return a value. They also do not accept parameters as inputs. 

44. What is a nested loop, hash join, and merge join?

• Nested Loop: A nested loop means a loop within a loop. There is an outer loop and an inner loop. 
• Hash Join: A hash join is used for joining large tables. 
• Merge Join: A merge join is used when the projections of the joined tables are sorted on the join columns. 

45. What is an intension and extension in DBMS?

Intension: Intension, also known as schema, defines the description of the database. It is specified during database designing and remains unchanged. 

Extension: It is the number of tuples available in the database at any instance of time. The value of the extension keeps changing according to the creation, updation, and deletion of the tuple. The data present in the database at a specific instance is known as an extension or snapshot of the database. 

46. Explain the network database model.

In a network database model, each parent node can have multiple child nodes and vice versa. It is a flexible way of representing objects and their relationship since it allows one-to-one, one-to-many, and many-to-many relationships. 

47. Explain the hierarchical database model.

The hierarchical database model is a top-down structure where each parent node can have multiple child nodes but a child node can only have one parent node. It supports one-to-one and one-to-many relationships. 

48. What is QBE?

QBE (Query-By-Example) is a graphical approach to accessing information in a database by using query templates called skeleton tables. The user has to enter example values directly into the query table to represent the desired result. QBE has two distinct features. It has two-dimensional syntax, and the queries look like tables. 

49. What is subquery and correlated subquery?

A subquery is simply a query found within a query. It is an inner query inside an outer query. 

A correlated subquery is a nested subquery, which means it is a query within a query. It is used for row-by-row processing. Each subquery is executed once for every outer query. It is evaluated once for each row processed by the parent statement (SELECT, UPDATE, or DELETE statement). 

50. Explain the Boyce-Codd Normal form. 

Boyce-Codd Normal Form (BCNF) is a more advanced version of the third normal form. It ensures that each determinant in the table is a candidate key. The following are the rules for a relational table to be in BCNF:

• The table must be in the third normal form. 
• X is the superkey of the table for every non-trivial functional dependency X->Y. Therefore, X cannot be a non-prime attribute if Y is a prime attribute.

Conclusion

Sailing smoothly through the interview is not an impossible task to achieve if you have prepared well. It does not matter if you are a fresher beginning in the field or an experienced candidate ready to grab new opportunities, the DBMS interview questions above will help with your preparation.