DBMS Interview Questions

A database management system (DBMS) is a collection of tools that can be used to build and manage a database. DBMS is a suite of applications that makes building and managing databases easier.

Some things to know about DBMS:

• Data stored in a database management system is more secure than file-based systems.
• DBMS helps to fix problems like duplicate data, data that isn't consistent, data that is hard to get to, data that isn't organized or clear, and so on. The list includes popular DBMSs like MySQL, Oracle, SQL Server, Amazon SimpleDB (Cloud), Etc.
• DBMS is software or instructions for working with databases, including creating, modifying, and deleting records.

In this article, I'll show the most frequently asked DBMS questions to ace your interview. Based on the following sections, we have categorized the interview questions:

• Fresher graduates
• Experienced professionals
• FAQ's

Top 10 Frequently Asked DBMS Interview Questions

1. Exactly What is a DBMS?
2. What is the definition of normalization in DBMS?
3. What is RDBMS?
4. What is the definition of durability in DBMS?
5. What are the different types of DBMS?
6. What are the advantages of DBMS?
7. What are the different DBMS languages available?
8. What is the E-R model?
9. What are the different types of keys in a DBMS?
10. What does CLAUSE mean in SQL?

Top DBMS Interview Questions For Freshers

1) What is a database?

A database is a logically structured collection of data that can be easily updated, accessed, and managed. Anything created using the create command is a database object, and databases typically contain sets of tables or objects that include fields and records. The fundamental units of data storage, which provide details about a specific table aspect, are an attribute or a column. DBMS uses user-provided queries to extract data from databases.

2) Exactly What is a DBMS?

A database management system is a group of applications or programs that lets users build and keep databases. DBMS allows it to add, delete, update, and do other things to maintain a database. It also provides a tool or interfaces for doing these things. It is software that lets you store data in a way that is safer and more efficient than a file-based system. 

Enroll your name on Teradata DBA Training Course to get prior knowledge on DBA.

3) How does the DBMS life cycle work?

The database life cycle encompasses the fundamental processes of developing a global schema for a logical database, allocating data across a network, and developing regional schemas for DBMSs.

The DBMS life cycle works on 5 major steps that include:

STEP 1: Requirement Analysis

STEP 2: Logical Design

STEP 3: Physical Design

STEP 4: Implementation

STEP 5: Monitoring, modification and maintenance.

4) What is the definition of normalization in DBMS?

DBMS normalization is the process of making sure that a database has as few duplicates as possible. It also helps eliminate problems with inserting, deleting, and updating.

5) What is the definition of denormalization in DBMS?

DBMS denormalization is the process of putting data into a database that is already there. 

It is done after normalization so that expensive joins are not needed. It is a common way to improve the performance of a database through database optimization.

6) What is RDBMS?   

A database management system called an (RDBMS) relational database management system that stores data in linked tables and enables users to access that data by querying shared fields between those tables.

Related Article: RDBMS Interview Questions

7) What are the parts of an entity-relationship model?

This database design method uses diagrams to discuss the connections between entities representing real-world objects. This method makes it easier for the DBA team to grasp the schema.

8) What is the difference between the DELETE and TRUNCATE commands in a DBMS?

DELETE command: With the WHERE clause in place, rows can be removed from the table by using this command. If you use a WHERE clause, only those rows will be removed from the database.

SYNTAX: DELETE FROM table_name WHERE condition; 

TRUNCATE command: If you want to clear out an entire table in your database, you'll need to use this command. It's the same as a DELETE command without a WHERE clause. This function deletes all rows from a database table.

SYNTAX: TRUNCATE TABLE table_name;

9) What is the primary key of a database management system?

The primary key is the column in a table that contains information that distinguishes each row from all others. In order for Optim to update,  insert, delete or restore data from a database table must have the primary key.

10) Describe the various types of DBMS architecture.

Three types of DBMS architecture are:

1. Single-tier architecture - Information can be accessed instantly on the client machine.
2. Two-tier architecture - The client machine has the DBMS software, and the server machine has the database.
3. Three-tier architecture - There is a layer between the client and server machines, so they can't talk to each other directly—a server DBMS program on the server machine talks to a client DBMS program on the client machine.

11) Can a foreign key have a NULL value?

There could be a lot of foreign keys on a table. If even one part of the foreign key doesn't work, the whole thing doesn't work. 

The value of a foreign key is NULL  if any of its parts are null.

12) What constitutes a domain in relational databases?

Domains are the definitions of data types that resolve to another domain or a simple data type. In the language of relational databases, a domain is the range of acceptable values for an attribute of an entity.

13) What is the definition of durability in DBMS?

If the system crashes before all the changes are written to the disc, the effect of the transaction should still be present once the DBMS notifies the user that it has been completed successfully. 

Durability is the term for this quality. When a transaction is committed to the database, the durability feature ensures that the data is stored in non-volatile memory and unaffected by system failure.

[Related Article: EY Interview Questions]

14) What are the different types of DBMS?

Different DBMS types are explained as follows:

• Hierarchical DBMS:  This type forms a tree, with nodes standing in for records and branches for their associated fields.
• Relational DBMS (RDBMS): This kind of DBMS has a structure that lets users access data in a database based on how it relates to other data in the same database. In this kind of DBMS, information is stored in tables.
• Object-oriented DBMS: Uses small pieces of software called "objects" to store data and the instructions for what to do with it.
• Network DBMS: This kind of DBMS supports many-to-many relationships, linking multiple member records.

15) Mention the drawbacks of conventional file-based systems that DBMSs have over them.

The only way to find content in a traditional file-based system is to scan the entire page because there is no indexing. It takes a long time and moves slowly. The other issue is that files often contain redundant and duplicate data, affecting all of them if you alter one. The disorganized nature of traditional file-based systems makes it more difficult to access data.

Lack of concurrency control is another issue, as a single operation can lock the entire page. On the other hand, multiple operations can run concurrently on the same file in DBMS.

Traditional file-based systems also have issues with security, atomicity, integrity checks, and other issues for which DBMSs have workable solutions.

DBMS Interview Questions For Experienced Professionals

16) What are the advantages of DBMS?

Some of the advantages of DBMS are

• Provides backup and recovery
• Enforces integrity constraints
• Redundancy control
• Provides multiple user interfaces
• Restriction for unauthorized access
• Easy accessibility
• Ensures data consistency
• Easy data extraction and data processing due to the use of queries

17) What are the different DBMS languages available?

Here are some of the languages that can use in DBMS:

1) DML(Data Manipulation Language): It has the commands needed to change the information in the database. 

Example: UPDATE, SELECT, DELETE, INSERT, etc.

2) DDL(Data Definition Language):  It has all the commands to set up the database. 

Examples: ALTER, CREATE, TRUNCATE, DROP, RENAME, etc.

3) TCL(Transaction Control Language):  It has commands to handle the database's transactions. 

Example: ROLLBACK, COMMIT, and SAVEPOINT.

4) DCL(Data Control Language): It has the commands to work with the database system's user controls and permissions. 

Example: REVOKE and GRANT.

18) What do the ACID properties of the DBMS mean?

ACID is an acronym for "atomicity," "consistency," "isolation," and "durability" in relational database management systems. These features ensure a safe and sound information exchange process between numerous parties.

19) What's the difference between primary key constraints and unique constraints?

Primary keys cannot have NULL values, but unique constraints can. A table can have multiple distinct constraints but only one primary key.

Check Out: TCS Interview Questions

20) What is the E-R model?

When referring to the Entity-Relationship model, the abbreviation "E-R model" is commonly used. The inspiration for this model comes straight from the world around us. It defines the entities and relationships between them that are required for the system to function. The entity, an attribute of the entity, a relationship set, and an attribute of the relationship set are the primary objects that an E-R diagram in this context can represent.

21) Describe the various DBMS data abstraction levels.

Data abstraction refers to the process by which unnecessary information is concealed from users. There are three distinct tiers of data abstraction:

• Physical Level: The least abstract level tells how the data is stored.
• Logical Level: After the Physical level, this is the next step away from the real world. This layer decides what data goes into the database and how the data points are connected.
• View Level: The View Level is the most abstract and only discusses a small part of the database.

22) Exactly what is a transaction?

Database transactions consist of a group of related operations that must execute in lockstep with one another.

23) Describe indexes in detail.

An index in a database table is a data structure that facilitates fast data retrieval at the expense of increased database write activity and storage requirements. Discs can only store data in sequential order.

[Related Article: Tech Mahindra Interview Questions]

24) What are the different types of keys in a DBMS?

In a DBMS, the following types of keys are available.

1. Primary key - An attribute used to identify each record in a table in a unique manner. A primary key is required for all tables.
2. Candidate key - It is a collection of attributes used to identify a table uniquely.
3. Super key - A set of attributes used to identify a tuple uniquely.
4. Foreign key - A field or collection of fields used to link one table's primary key to another table's primary key.

25) What are the integrity rules in DBMS?

There are two integrity rules in DBMS:

• Entity Integrity: "Primary key cannot have a NULL value" is written there.
• Referential Integrity: "Foreign Key can be either a Primary Key or NULL value of another relation."

26) Exactly what is QBE?

Query-by-example (QBE) is a graphical method for accessing data in a database using query templates known as skeleton tables. It demonstrates how adding sample values to a query template can be utilized.

Related Article: Virtusa Interview Questions

27) Why is DBMS normalization necessary?

Database normalization is the process of arranging a database's attributes so that there is less or no duplicate data (having the same data but in different places).

The following goals are for making things normal.

• It fixes errors in relational tables and database errors.
• Normalization helps reduce complexity and duplication by looking at the new data types used in the table.
• It is helpful to break up the big database table into smaller tables and use relationships to link them together.

28) What would occur if the HDFS cluster's NameNode failed?

The HDFS cluster has a single NameNode that manages all of the metadata for the DataNodes. HDFS clusters are vulnerable to having a single point of failure because there is only one NameNode in each cluster. Therefore, the failure of NameNode could cause the systems to become inoperable.

29) In DBMS, what is a checkpoint?

The Checkpoint method permanently saves all system logs to the storage drive after they are deleted from the system.

Related Article: CheckPoint Interview Questions

30) What exactly do you mean when you talk about correlated subqueries in relation to DBMS?

There is also the concept of a correlated subquery, which is a subquery that depends on the results of another query. Therefore, correlated subqueries are executed for each row of outer queries. Each child query is run just once for each outer query result row.

Correlated subqueries are queries that are used by the parent statement to perform row-by-row processing. A SELECT, UPDATE, or DELETE statement can serve as the parent statement in this case.

Frequently Asked Questions

31) If an attribute has no value, would you give it a blank space, a value of 0, or a value of NULL?

The NULL value is used for any attribute whose status is currently unknown. whereas BLANK stands for an empty String value, and ZERO indicates an integer value of zero.

32) How do you know which database model to use when making a database?

This is entirely dependent on the objective of the database, as each model possesses its own unique collection of benefits and advantages. 

For instance, if you are looking for atomic data, the relational model is the one to use because it provides the best results. When working with text or semi-structured data, the document model is the option that will be most effective for you.

33) What does CLAUSE mean in SQL?

A clause is a type of query element in SQL that allows you to filter or personalize how your data is accessed.

Related Article:SQL Server Tutorial

34) Why do embedded SQL queries need cursors?

A cursor object is used by applications to temporarily store query results before processing them individually, row by row. The results of SQL queries are also in the form of data collection.

35) What distinguishes a database state from its schema?

A database state is a description of the information that is currently stored in a database, whereas a database schema is a description of the overall layout of the database. Both of these descriptions can be found in a database.

36) How are clustered indexes different from non-clustered indexes?

• Clustered indexes are the kinds of indexes that control how data is physically stored on a disc. So, only one clustered index can be made for each database table.
• Non-clustered indexes list the data's logical order instead of its physical order.

Conclusion

Around 2.5 quintillion bytes of data are produced daily in the market today. Therefore, we must analyze this data and produce the necessary results using database management systems (DBMS). Data engineering includes all aspects of designing, building, and transforming data pipelines to make them easier for a data science engineer or big data engineer to use. So, we've listed the most frequently asked DBMS interview questions to help you face your interview and solidify your understanding of the subject.