[2022] DBMS Interview Questions – Frequently Asked

DBMS or Data Base Management System is a collection of programs that allows users to create and maintain a database. It provides an interface for performing different operations, which may be the creation of a database, creating,updating and deleting data (CRUD operations). DBMS is a software in which data is stored in a secure manner. Some popular Database Management System Softwares are- MySQL, Oracle, SQL Server, etc.

1. What is a database?

A Database is a rational, consistent and ordered collection of data that can be accessed, managed and updated easily. Databases are structured to provide the facility of creation, insertion, updating of the data efficiently and this
data is stored in form of tables, which includes fields and records.

2. What is the difference between a DBMS and RDBMS?

DBMS

 – Data Base Management System, provides an organized way of managing, retrieving and storing data in a structured way (tables), the CRUD operations can be performed on this data (CReation, Updation,
Deletion)        

RDBMS
 – Relational Data Base Management System also provides the same facilities as DBMS, but along with relational integrity, which means that all the tables are connected with one another.

3. What are the advantages of DBMS?

DBMS provides following advantages:

• Redundancy control
• Prevention from unauthorized access
• Provides multiple user interfaces
• Facilities of data backup and recovery
• Enforces integrity constraints
• Ensure data consistency
• Easy accessibility of data
• Easy data extraction and data processing

4. What are the different languages present in DBMS?

Following languages are present in a DBMS:

Data Definition Language (DDL) – Commands, which are used to define the database.

Data Manipulation Language (DML) – Commands, which are used to manipulate the data, present in the database.

Data Control Language (DCL) – Commands, which deal with the user permissions and controls of the database system.

Transaction Control Language (TCL) – Commands that deal with the database transactions

5. Who proposed the relational model of database?

Edgar F. Codd proposed the relational model in 1970.

6. What do you understand by Data Model?

A Data model is a collection of conceptual tools for describing data, data relationships, data semantics and constraints. These models describe the relationship between the entities and their attributes.

There different data models are:

• Hierarchical data model
• Network model
• Relational model
• Entity-Relationship model

7. What are the different types of DBMS?

The different types of DBMS are:

Relational DBMS: This DBMS uses a structure allowing the users to access data in relation to other piece of data in a database.

Hierarchical DBMS: This type of DBMS has a structure similar to that of a tree, in this DBMS, the nodes represent records and the branches of the tree represent fields.

Network DBMS: This DBMS supports many-to-many relations wherein multiple member records can be linked together with one another

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

8. What do you understand by query optimization?

Query optimization is the process, which devises a plan for evaluation of the query that has the least estimated cost. Whenever there are a lot of algorithms and methods to execute the same task, then query optimization plays an
important role.

9. What is a NULL value?

The NULL value represents a value, which is unavailable, unknown, unassigned or not applicable. It is different from a zero or a blank space.  A zero is a number and blank space is a character.

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

The three levels of abstraction are as follows:

Physical Level: Lowest level of abstraction, it describes that how the data is stored.

Logical Level: This is the next level of abstraction; it determines what data is stored in the database, and what is the relationship between the various data points.

View Level: The View Level is the highest level of abstraction and it describes only a part of the entire database.

11. What is a Relation Schema and a Relation in DBMS?

A Relation Schema or Table Schema is a set of attributes. It is the blueprint with the help of which we can explain that how the data is organized into tables. This is just a blueprint and it contains no data.

A relation is specified as a set of tuples or a set of related attributes with identifying key attributes.

12. What is a degree of Relation?

The degree of a relation is the number of attributes of its relation schema. A degree of relation or Cardinality is the number of occurrence of one entity, which is connected to the number of occurrence of the other entities. There
are three degrees of a relation. One-to-one (1:1), one-to-many (1: M), many-to-many (M: M).

13. What is data abstraction in DBMS?

Data abstraction is the process of hiding irrelevant details from the different users, who access it. Since the database systems are made of complex data structures so, abstraction makes the user interaction with the database an
easier task.

14. What is DML (Data Manipulation Language)?

Data Manipulation Language (DML) enables the users to access or manipulate the data as organized by the data models. Some queries are- SELECT, UPDATE, INSERT, DELETE.

Two types of DML are        Procedural DML and Non-Procedural DML

15. What is an entity-relationship model?

Entity Relationship Model is a diagrammatic approach to database design, where the real-world objects are represented as the entities and the relationships between these entities are drawn. This helps in understanding the database
and the structure in a better way.

16. What is an Entity in DBMS?

An entity is a real-world object having attributes; these attributes are the characteristics of the corresponding object/entity. Usually, in an entity relationship diagram the entity is represented through ovals.

17. What is an entity type?

An Entity type is nothing but a collection of entities that have the same attributes. It refers to one or more related tables in a particular database. It is a characteristic, which uniquely identifies the entity.

18. What is an entity set in DBMS?

An entity set is the collection of all the entities of a particular entity type in a database.

19. What is an Extension of entity type?

An extension of an entity type is a collection of entities of a particular entity type that are grouped into an entity set.

20. What is Weak Entity set?

A weak entity is one that does not has sufficient attributes to form a primary key. A set of such weak entities is known as the Weak Entity Set.

21. What is an attribute?

An attribute is a component of a database and describes the property or the characteristics of an entity. Entities can be uniquely identified using the attributes. One such attribute is a primary key.

22. What is concurrency control?

Concurrency control is the process of managing simultaneous operations in a database so that database integrity is not compromised. For Example: When working with bank accounts, then transactions between two accounts must be
controlled, if from Account A $100 is transferred to Account B. Then Account A must be debited, and Account B must be credited at the same time.

23. What are the DBMS ACID properties?

ACID stands for Atomicity, Consistency, Isolation, and Durability. It is a collection of properties that ensures whether the data transactions are processed reliably in a database system or not. It makes sure that the integrity of
the database is not compromised in any case.

24. Explain the ACID properties in detail.

Atomicity: Whether the transactions are success or failure, is referred to by Atomicity. Each transaction refers to a single logical operation of a data, thus if one part of any transaction fails, the entire transaction fails and
the database state is left unchanged.

Consistency: Consistency ensures that the data must meet all the rules of validation. That is the transaction never leaves the database without completing its state.

Isolation: The main goal of isolation is concurrency control.

Durability: Durability means that if a transaction is committed, it will occur whatever may be the scenario.

25. What is SQL?

Structured Query Language or SQL is the ANSI standard language that updates database and executes the related commands for accessing, manipulating and structuring the database.

26. What is database Normalization?

The process of organizing the data, which does not has any kind of inconsistent dependencies and redundancies, is called normalization. There are different normal forms, which decide the level of normality with which the database is
normalized.

27. What are the advantages of normalizing a database?

Advantages of normalizing database are:

• No duplicate entries
• Saves storage space
• Boasts the performances of the queries
• Removes inconsistencies in the database.

28. What is De-normalization of database?

The process of de-normalization is the reverse of normalization; it is incorporated when the database performance needs to be boosted. It is usually done by adding some redundant data, which also helps dealing with the complex data.

29. What is Relational Algebra?

Relational Algebra is a Procedural Query Language, it contains a set of operations that takes relations as input and produces a new relationship as an output. It is the basic set of operations for the relational model.

30. What are the fundamental relational algebra operations?

 The fundamental operations of relational algebra are as follows:

• select
• project
• set difference
• union
• Rename, etc.

31. What is Functional Dependency?

Functional Dependency or Database Dependency is known as the starting point of normalization. It exists when a relation between two attributes allow the determination of the corresponding attribute’s value uniquely. It is
represented as A->B, which means B is functionally dependent on A.

32. What are the integrity rules in DBMS?

Data integrity is enforced in the database system by imposing a series of rules. There are two integrity rules in DBMS:

Entity Integrity: a primary key cannot have any NULL value

Referential Integrity: a foreign Key either can have a NULL value or should be the primary Key value of other relation

33. What is extension and intension of database?

Extension is the number of tuples that are there in a table. Thus, it changes as the tuples are created, updated or destroyed. It is time dependent.

Intension or Data Schema is the database description specified during the database design. It is expected to remain unchanged thus is a constant value that gives the name, structure of tables and the constraints for the data to be
stored.

34. What is Data Independence?

Data independence specifies that the application is independent of the storage structure and access strategy of data. It ensures the modification of the schema definition at one level without altering the schema definition in the
next level.

35. What are the different types of data independence?

There are two types of Data Independence:

Physical Data Independence
: Physical data is the data stored in the database. It is in the bit-format. Data independence ensures that the modification of data in the physical level should not affect the data at the logical level.

Logical Data Independence

: Logical data in the data about the database that defines the database structure. Data independence ensures that the modification of data in this level should not affect the data at the physical level. It is more difficult to
achieve.

36. What is a Database Join?

Join is useful especially in relational algebra and is used to combine information from two or more relations. It is performed based on a column.

37. What are the different types of Join?

There are two types of join:

Inner joins: Inner join is further bifurcated into three categories. They are:

• Theta join
• Natural join, and
• Equi join

Outer joins: Outer join also has three types. They are:

• Left outer join
• Right outer join, and
• Full outer join

38. What is 1NF?

1NF or First Normal Form is the simplest type of normalization that can be implemented on a database. The primary objectives of 1NF are to:

• Every column must have an atomic value
• There must be no duplicate columns in a table.

It is the first level of normalization.

39. What is 2NF?

2NF or the Second Normal Form specifies the second level of normalization. The conditions of 2NF are:

• The table must be in 1NF.
• 
  Every non-prime attribute is fully functionally dependent on the primary key, i.e., every non-key attribute should be dependent on the primary key in such a way that if any key element is deleted, and then even the non_key
  element must remain in the database.
  

40. What is 3NF?

3NF or Third Normal Form specifies the third level of normalization. The conditions of 3NF are:

• It must be in second normal form.
• There must be no transitive functional dependency.

For E.g.: X->Z        (X is Functionally Dependent on Z)

Where:

X->Y        (X is Functionally Dependent on Y)

Y does not -> X        (Y is not functionally Dependent on X)

Y->Z so, X->Z        

41. What is BCNF?

BCNF stands for Boyce-Codd Normal Form, it is stricter and more advanced version of the third normal form. A table complies with BCNF if it satisfies the following conditions:

• It is in 3NF.
• For every functional dependency X->Y, X should be the super key of the table. Meaning that X cannot be a non-prime attribute if Y is a prime attribute.

42. What is a stored procedure?

Groups of SQL statements, performing a particular operation, which have been created and stored in the database, are known as stored procedures. It increases the reusability, as the procedure is stored into the system and is used
again and again that makes the work easy, reduces processing time and decreases system complexity.

43. What is the difference between a DELETE command and TRUNCATE command?

DELETE command deletes the rows from a table based on the condition that is provided in a WHERE clause. It can be rolled back and maintains a log, making it slow. It uses rowlock while performing DELETE function.

TRUNCATE command removes all rows from a table. It is similar to the DELETE command but with the absence of WHERE clause. This command removes all the rows from the table. It cannot be rolled back. In addition, it does not maintains
a log, which makes it faster than the DELETE command.

44. What are the different types of keys in database?

Primary key: The Primary key of a table can uniquely identify each record in a table. It is mandatory for every table to have one primary key.

Candidate key: It is an attribute or set of attributes that can uniquely identify a tuple. A Primary key for a table 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.

Alternate Key: Alternate Keys are the candidate keys, which is not a Primary key.

Composite Key: A composite key is a combination of two or more columns that identify each tuple uniquely.

45. How can one communicate with an RDBMS?

One must use Structured Query Language (SQL) to communicate with the RDBMS. Using SQL queries, one can give the input to the database then after processing the given query; the database will provide the required output in form of
query response.

46. What are the different types of locks available in a DBMS?

Two locks are available in a DBMS, these are:

• Shared Lock
• Exclusive Lock

47. What is a Shared Lock?

Shared lock is required for reading a data item. In this, many transactions may hold a lock on the same data simultaneously. When more than one transactions are allowed to read the data items then the lock used is known as the
shared lock.

48. What is an Exclusive Lock?

An exclusive lock is applied on the data, whenever a write operation is performed on the data. During writing of the data in a database, only a single transaction is allowed to prevent inconsistency in the database.

49. What are correlated subqueries in DBMS?

A correlated subquery is a subquery reliant on another query. When subqueries are executed for each of the rows of outer queries, then they are termed as correlated subqueries. Each subquery is executed a single time for every row
of the outer query.

50. What is a CLAUSE in SQL?

CLAUSE in SQL is used to limit the result set by mentioning a query condition. Thus, a CLAUSE can be used to filter rows from the entire set of records.