DBMS Interview Questions and Answers

May 6, 2022

Introduction to DBMS

Database management system is a software that controls all the different manipulation of stored or to be stored data in a database. It allows, creation, updation, manipulation, definition of a database.

Top 30 DBMS Interview Questions

1. What is DBMS?

DBMS is a collection of programs that provides users to create and maintain a database. It manages incoming data, Organizes to provide a better and easy way to users. Or in a Simpler way, DBMS provides us an interface or tool for performing different operations such as the creation of a database, inserting data into it, deleting data from it, updating the data, etc. DBMS is a software in which data is stored in a more secure way as compared to the file-based system. Using DBMS, we can overcome many problems such as data redundancy, data inconsistency, easy access, more organized and understandable, and so on.

Answer DBMS is a collection of programs that provides users to create and maintain a database. It manages incoming data, Organizes to provide a better and easy way to users. Or in a Simpler way, DBMS provides us an interface or tool for performing different operations such as the creation of a database, inserting data into it, deleting data from it, updating the data, etc. DBMS is a software in which data is stored in a more secure way as compared to the file-based system. Using DBMS, we can overcome many problems such as data redundancy, data inconsistency, easy access, more organized and understandable, and so on.

2. What are the advantages of DBMS?

Data Independence: It allows to change the structure of the data without affecting the structure of any of the running application programs.
Sharing of Data: Multiple users can use data from the same database simultaneously.
Integrity constraints: These constraints allow the data to be stored in a database in a refined manner.
Redundancy control: Supports a mechanism to control the redundancy of data by integrating all the data into a single database.
Provide backup and recovery facility: Provides a feature of ‘backup and recovery’ to automatically create the data backup and restore the data as and when required.

Answer The advantages of DBMS are as follows:

Data Independence: It allows to change the structure of the data without affecting the structure of any of the running application programs.
Sharing of Data: Multiple users can use data from the same database simultaneously.
Integrity constraints: These constraints allow the data to be stored in a database in a refined manner.
Redundancy control: Supports a mechanism to control the redundancy of data by integrating all the data into a single database.
Provide backup and recovery facility: Provides a feature of ‘backup and recovery’ to automatically create the data backup and restore the data as and when required.

3. What is database partitioning?

It (Database partitioning) is a process where a logical database is divided into different independent parts. The database objects like tables, indexes are subdivided and managed, and accessed at the granular level.

Answer It (Database partitioning) is a process where a logical database is divided into different independent parts. The database objects like tables, indexes are subdivided and managed, and accessed at the granular level.

4. Define Normalization?

Organizing data into a relatable Table is known as Normalization.

Answer Organizing data into a relatable Table is known as Normalization.

5. Enlist the advantages of a normalizing database?

Advantages of the normalizing database are:

No duplicate entries
Saves storage space
Boasts the query performances.

Answer Advantages of the normalizing database are:

No duplicate entries
Saves storage space
Boasts the query performances.

6. What are the different levels of abstraction in the DBMS?

There are 3 levels of data abstraction in the DBMS.

They include:

Physical Level: This is the lowest level of the data abstraction which states how the data is stored in the database.
Logical Level: This is the next level of the data abstraction which states the type of the data and the relationship among the data that is stored in the database.
View Level: This is the highest level in the data abstraction which shows/states only a part of the database.

Answer There are 3 levels of data abstraction in the DBMS.
They include:

Physical Level: This is the lowest level of the data abstraction which states how the data is stored in the database.

Logical Level: This is the next level of the data abstraction which states the type of the data and the relationship among the data that is stored in the database.

View Level: This is the highest level in the data abstraction which shows/states only a part of the database.

7. What is a checkpoint in DBMS and when does it occur?

A checkpoint is a mechanism where all the previous logs are removed from the system and are permanently stored on the storage disk. So, basically, checkpoints are those points from where the transaction log record can be used to recover all the committed data up to the point of crash.

Answer A checkpoint is a mechanism where all the previous logs are removed from the system and are permanently stored on the storage disk. So, basically, checkpoints are those points from where the transaction log record can be used to recover all the committed data up to the point of crash.

8. What is the Relationship?

The Relationship is defined as an association among two or more entities. There are three types of relationships in DBMS-

One-To-One: Here one record of any object can be related to one record of another object.
One-To-Many (many-to-one): Here one record of any object can be related to many records of other objects and vice versa.
Many-to-many: Here more than one record of an object can be related to n number of records of another object.

Answet The Relationship is defined as an association among two or more entities. There are three types of relationships in DBMS-

One-To-One: Here one record of any object can be related to one record of another object.

One-To-Many (many-to-one): Here one record of any object can be related to many records of other objects and vice versa.

Many-to-many: Here more than one record of an object can be related to n number of records of another object.

9. What is RDBMS?

RDBMS is the Relational Database Management System which contains data in the form of the tables and data is accessed on the basis of the common fields among the tables.

Answer RDBMS is the Relational Database Management System which contains data in the form of the tables and data is accessed on the basis of the common fields among the tables.

10. Mention the different types of DBMS?

The different types of DBMS are as follows:

Relational DBMS (RDBMS): This type of DBMS, uses a structure that allows the users to access data in relation to another piece of data in a database. In this type of DBMS, data is stored in the form of tables.

Hierarchical DBMS: As the name suggests, this type of DBMS has a structure similar to that of a tree, wherein the nodes represent records and the branches of the tree represent fields.

Network DBMS: This type of DBMS supports many-to-many relations wherein multiple member records can be linked.

Object-oriented DBMS: Uses small individual software called to object to store pieces of data and the instructions for the actions to be done with the data.

Answer The different types of DBMS are as follows:
Relational DBMS (RDBMS): This type of DBMS, uses a structure that allows the users to access data in relation to another piece of data in a database. In this type of DBMS, data is stored in the form of tables.
Hierarchical DBMS: As the name suggests, this type of DBMS has a structure similar to that of a tree, wherein the nodes represent records and the branches of the tree represent fields.
Network DBMS: This type of DBMS supports many-to-many relations wherein multiple member records can be linked.
Object-oriented DBMS: Uses small individual software called object to store pieces of data and the instructions for the actions to be done with the data.

11. Enlist the disadvantages of the query?

The disadvantages of the query are:

No indexes
Stored procedures are excessively compiled.
Triggers and procedures are without SET NOCOUNT ON.
Complicated joins making up inadequately written queries.
Cursors and temporary tables showcase a bad presentation.

Answer The disadvantages of the query are:

No indexes
Stored procedures are excessively compiled.
Triggers and procedures are without SET NOCOUNT ON.
Complicated joins making up inadequately written queries.
Cursors and temporary tables showcase a bad presentation.

12. What is the purpose of normalization in DBMS?

Normalization is the process of analyzing the relational schemas which are based on their respective functional dependencies and the primary keys to fulfilling certain properties.

The properties include:

To minimize the redundancy of the data.
To minimize the Insert, Delete, and Update Anomalies.

Answer Normalization is the process of analyzing the relational schemas which are based on their respective functional dependencies and the primary keys to fulfilling certain properties.
The properties include:

To minimize the redundancy of the data.
To minimize the Insert, Delete and Update Anomalies.

13. What is an ER diagram in DBMS?

An entity-relationship model or an entity-relationship diagram is a visual representation of data that is represented as entities, attributes, and relationships are set between entities.

Answer An entity-relationship model or an entity-relationship diagram is a visual representation of data that is represented as entities, attributes, and relationships are set between entities.

14. What are the integrity rules in DBMS?

Data integrity is one significant aspect while maintaining the database. So, data integrity is enforced in the database system by imposing a series of rules. Those set of integrity is known as the integrity rules.

There are two integrity rules in DBMS:

Entity Integrity: It specifies that the “Primary key cannot have a NULL value.”

Referential Integrity: It specifies that the “Foreign Key can be either a NULL value or should be the Primary Key value of other relation

Answer Data integrity is one significant aspect while maintaining the database. So, data integrity is enforced in the database system by imposing a series of rules. Those set of integrity is known as the integrity rules.
There are two integrity rules in DBMS:

Entity Integrity: It specifies that the "Primary key cannot have a NULL value."

Referential Integrity: It specifies that "Foreign Key can be either a NULL value or should be the Primary Key value of other relation

15. Define Atomicity and Aggregation?

Atomicity: It’s an all or none concept which enables the user to be assured of incomplete transactions to be taken care of. The actions involving incomplete transactions are left undone in DBMS.
Aggregation: The collected entities and their relationship are aggregated in this model. It is mainly used in expressing relationships within relationships.

Answer Atomicity: It's an all or none concept which enables the user to be assured of incomplete transactions to be taken care of. The actions involving incomplete transactions are left undone in DBMS.
Aggregation: The collected entities and their relationship are aggregated in this model. It is mainly used in expressing relationships within relationships.

16. What is an entity-relationship model?

It is a diagrammatic approach to database design, where you represent real-world objects as entities and mention relationships between them. This approach helps the team of DBA’s to understand the schema easily.

Answer It is a diagrammatic approach to database design, where you represent real-world objects as entities and mention relationships between them. This approach helps the team of DBA's to understand the schema easily.

17. Popular DBMS Software?

Here, is the list of some popular DBMS systems:

MySQL
Microsoft Access
Oracle
PostgreSQL
dBASE
FoxPro
SQLite
IBM DB2
LibreOffice Base
MariaDB
Microsoft SQL Server etc.

Answer Here, is the list of some popular DBMS systems:

MySQL
Microsoft Access
Oracle
PostgreSQL
dBASE
FoxPro
SQLite
IBM DB2
LibreOffice Base
MariaDB
Microsoft SQL Server etc.

18. What is concurrency control?

This is a process managing simultaneous operations in a database so that database integrity is not compromised. The following are the two approaches involved in concurrency control:

Optimistic approach – Involves versioning

Pessimistic approach – Involves locking

Answer This is a process managing simultaneous operations in a database so that database integrity is not compromised. The following are the two approaches involved in concurrency control:

Optimistic approach – Involves versioning

Pessimistic approach – Involves locking

19. What is the purpose of SQL?

SQL stands for Structured Query Language whose main purpose is to interact with the relational databases in the form of inserting and updating/modifying the data in the database.

Answer SQL stands for Structured Query Language whose main purpose is to interact with the relational databases in the form of inserting and updating/modifying the data in the database.

20. Disadvantage of DBMS?

DBMS may offer plenty of advantages but, it has certain flaws-

Cost of Hardware and Software of a DBMS is quite high which increases the budget of your organization.
Most database management systems are often complex systems, so training for users to use the DBMS is required.
In some organizations, all data is integrated into a single database which can be damaged because of electric failure or database is corrupted on the storage media
Use of the same program at a time by many users sometimes leads to the loss of some data.
DBMS can’t perform sophisticated calculations

Answer DBMS may offer plenty of advantages but, it has certain flaws-

Cost of Hardware and Software of a DBMS is quite high which increases the budget of your organization.

Most database management systems are often complex systems, so training for users to use the DBMS is required.

In some organizations, all data is integrated into a single database which can be damaged because of electric failure or database is corrupted on the storage media

Use of the same program at a time by many users sometimes lead to the loss of some data.

DBMS can't perform sophisticated calculations

21. What are the unary operations in Relational Algebra?

Unary operations are operations that use single operands. Unary operations in relational algebra are PROJECTION and SELECTION.

-=,>=,<=,+= are the relational operators used in SELECTION.

Answer Unary operations are the operations that uses single operands. Unary operations in relational algebra are PROJECTION and SELECTION.

-=,>=,<=,+= are the relational operators used in SELECTION.

22. How many types of database languages are?

There are four types of database languages:

Data Definition Language (DDL): These commands are used for updating the data. CREATE, ALTER, DROP, TRUNCATE, RENAME are some examples of DDL commands.
Data Manipulation Language (DML): These commands are used for the manipulation of already updated data. SELECT, UPDATE, INSERT, DELETE are some examples of DML commands.
Data Control Language (DCL) : These commands are used for giving and removing user access to the database. GRANT and REVOKE are the examples of DCL commands.
Transaction Control Language (TCL) : These are the commands used for managing transactions in the database. TCL is used for managing the changes made by DML. COMMIT, ROLLBACK, and SAVEPOINT are the examples of TCL commands.

Answer There are four types of database languages:

Data Definition Language (DDL): These commands are used for updating the data. CREATE, ALTER, DROP, TRUNCATE, RENAME are some examples of DDL commands.
Data Manipulation Language (DML): These commands are used for the manipulation of already updated data. SELECT, UPDATE, INSERT, DELETE are some examples of DML commands.
DATA Control Language (DCL) : These commands are used for giving and removing the user access on the database. GRANT and REVOKE are the examples of DCL commands.
Transaction Control Language (TCL) : These are the commands used for managing transactions in the database. TCL is used for managing the changes made by DML. COMMIT, ROLLBACK, and SAVEPOINT are the examples of TCL commands.

23. Define a Relation Schema?

A relation schema is known as the blueprint with the help of which we can explain how the data is organized into tables.

Answerer A relation schema is known as the blueprint with the help of which we can explain how the data is organized into tables.

24. What is a degree of Relation?

A degree of relation is also known as Cardinality it is defined as the number of occurrence of one entity which is connected to the number of occurrence of other entity.

Answer A degree of relation is also known as Cardinality it is defined as the number of occurrences of one entity which is connected to the number of occurrences of another entity.

25. What are the disadvantages of file processing systems?

There are some disadvantages of file processing system :

Inconsistent
Not secure
Data redundancy
Difficult in accessing data
Data isolation
Data integrity
Concurrent access is not possible
Limited data sharing
Atomicity problem

Answer There are some disadvantages of file processing system :

Inconsistent
Not secure
Data redundancy
Difficult in accessing data
Data isolation
Data integrity
Concurrent access is not possible
Limited data sharing
Atomicity problem

26. What is data abstraction in DBMS?

Data abstraction in DBMS is a process of hiding irrelevant details from users. Because database systems are made of complex data structures so, it makes accessible the user interaction with the database.

Answer Data abstraction in DBMS is a process of hiding irrelevant details from users. Because database systems are made of complex data structures so, it makes accessible the user interaction with the database.

27. What is Relational Algebra?

Relational Algebra is a Procedural Query Language that contains a set of operations that take one or two relations as input and produce a new relationship. Relational algebra is the basic set of operations for the relational model. The decisive point of relational algebra is that it is similar to the algebra which operates on the number.

There are a few fundamental operations of relational algebra:

select
project
set difference
union
rename, etc.

Answer Relational Algebra is a Procedural Query Language that contains a set of operations that take one or two relations as input and produce a new relationship. Relational algebra is the basic set of operations for the relational model. The decisive point of relational algebra is that it is similar to the algebra which operates on the number.
There are a few fundamental operations of relational algebra:

select

project

set difference

union

rename, etc.

28. What is Relational Calculus?

Relational Calculus is a Non-procedural Query Language that uses mathematical predicate calculus instead of algebra. Relational calculus doesn’t work on mathematics fundamentals such as algebra, differential, integration, etc. That’s why it is also known as predicate calculus.

There is two types of relational calculus:

Tuple relational calculus
Domain relational calculus

Answer Relational Calculus is a Non-procedural Query Language that uses mathematical predicate calculus instead of algebra. Relational calculus doesn't work on mathematics fundamentals such as algebra, differential, integration, etc. That's why it is also known as predicate calculus.

There is two types of relational calculus:

Tuple relational calculus

Domain relational calculus

29. Explain ACID properties?

ACID properties are some basic rules, which have to be satisfied by every transaction to preserve the integrity. These properties and rules are:

ATOMICITY: Atomicity is more generally known as ? all or nothing rule.’ This implies all are considered as one unit, and they either run to completion or are not executed at all.

CONSISTENCY: This property refers to the uniformity of the data. Consistency implies that the database is consistent before and after the transaction.

ISOLATION: This property states that the number of transactions can be executed concurrently without leading to the inconsistency of the database state.

DURABILITY: This property ensures that once the transaction is committed it will be stored in the non-volatile memory and a system crash can also not affect it anymore.

Answer ACID properties are some basic rules, which has to be satisfied by every transaction to preserve the integrity.
These properties and rules are:

ATOMICITY: Atomicity is more generally known as ?all or nothing rule.' Which implies all are considered as one unit, and they either run to completion or not executed at all.
CONSISTENCY: This property refers to the uniformity of the data. Consistency implies that the database is consistent before and after the transaction. ISOLATION: This property states that the number of the transaction can be executed concurrently without leading to the inconsistency of the database state. DURABILITY: This property ensures that once the transaction is committed it will be stored in the non-volatile memory and system crash can also not affect it anymore.

30. What is the 3-Tier architecture?

The 3-Tier architecture contains another layer between the client and server. The introduction of 3-tier architecture is for the ease of the users as it provides the GUI, which, makes the system secure and much more accessible. In this architecture, the application on the client-end interacts with an application on the server which further communicates with the database system.

Answer The 3-Tier architecture contains another layer between the client and server. The introduction of 3-tier architecture is for the ease of the users as it provides the GUI, which, makes the system secure and much more accessible. In this architecture, the application on the client-end interacts with an application on the server which further communicates with the database system.

Also Check:

FAQs on DBMS Interview Questions

Describe the types of keys?

There are following types of keys:

Primary key: The Primary key is an attribute in a table that can uniquely identify each record in a table. It is compulsory for every table.

Candidate key: The Candidate key is an attribute or set of an attribute which can uniquely identify a tuple. The Primary key can be selected from these attributes.

Super key: The Super key is a set of attributes which can uniquely identify a tuple. Super key is a superset of the candidate key.

Foreign key: The Foreign key is a primary key from one table, which has a relationship with another table. It acts as a cross-reference between tables.