DBMS Interview Questions And Answers

DBMS stands for Database Management System. However, to understand what DBMS is, you must know what a database is.

A database is an organized collection of data stored in a schema, views, reports, etc., that you can access electronically. In simpler terms, you may also say that it is a collection of interrelated data that you can use to retrieve, insert and delete the data efficiently.

Small Databases are usually stored on a file system, while larger Databases are hosted on computer clusters or cloud storage. The design of Databases spans formal techniques, including data modelling, efficient data representation, secure storage, fault tolerance, concurrent access and more.

DBMS is software that interacts with the end users, application and the Database itself to administer the data. DBMS software provides an interface encompassing core facilities to capture and analyze the data. Moreover, in the case of multiple users, it also maintains data consistency. 

Check it out

DBMS allow users to do the following tasks:

  • Data Definition
  • Data Updation
  • Data Retrieval
  • User Administration  

For example, some popular DBMSs are MySQL, Oracle, MS Fox Pro, Red Brick, Ingres, Adabas, and Uni Data. Before getting into the section of DBMS interview questions and answers for beginners, find out the most important characteristics of DBMS.

Characteristics of DBMS

  • Uses a digital repository established on a server to store and manage the data.
  • Provide a clear/ logical view of the process of manipulating the data.
  • DBMS contains automatic backup and recovery procedures.
  • It contains ACID properties (Atomicity- Consistency- Isolation- Durability) which maintain data in a healthy state in case of failure.
  • Reduce the complex relationship between data and makes it easy to work on.
  • Support manipulation and processing of data.
  • Provide security of data.
  • Allows you to view the Database from different viewpoints according to the user’s requirements.

Advantages of DBMS

Before you start with the technical DBMS interview questions and answers, you must know what the benefits of DBMS are. Here are some of the main reasons which made a shift from file system to DBMS:

  • Controls Database Redundancy: DBMS controls data redundancy by storing all the data in one single database file.
  • Data sharing: In DBMS, only authorized users can share the data among multiple users.
  • Easily Maintenance: DBMS makes data easy to maintain because of its centralized nature.
  • Reduce time: It is easy to access and retrieve. Hence, it reduces development time and maintenance needs.
  • Backup: A backup and recovery subsystem always restores the data when required.
  • Multiple User interface: DBMS offers different types of interfaces to its users, like graphical user interfaces (GUI) and application program interfaces (API).

 To brush up your knowledge, here are some DBMS interview questions and answers that you must prepare. 

1 . What is DBMS?

Database Management System or DBMS is a collection of programs that enables users to store and retrieve data from a database securely. With the help of a DBMS, you can perform a number of operations such as creating a new database, inserting and deleting data, updating the data, etc. It is a much safer way of storing data as compared to the traditional file-based system. Some common examples are MySQL, Oracle and SQL Server.

2 . What are the advantages of DBMS over traditional file systems?

Some of the most important advantage of DBMS over traditional file-based systems are:

  • Data redundancy can be controlled
  • No unauthorised access of data
  • Multiple user interfaces
  • Easy accessibility and processing of data
  • Provides backup and data recovery

3 . What do you mean by normalization in DBMS?

Normalisation is the process of minimising redundancies in a database. It also helps to overcome insert, delete and update anomalies.

4 . What do you mean by denormalization in DBMS?

Denormalization is the process of adding redundant data to a database. You can do it is after normalisation to avoid costly joins. It is a common database optimization technique which helps to boost the performance of a database.

5 . Explain the different types of DBMS architecture.

There are three types of DBMS architecture:

  • Single tier architecture – The data is readily available on the client machine.
  • Two tier architecture – The DBMS software is present on the client machine and the database is present on the server machine.
  • Three tier architecture – A layer is present between the client and server machine and there is no direct communication between them. A client DBMS application on the client machine interacts with a server DBMS application on the server machine.

6 . What is a checkpoint in DBMS?

A checkpoint or savepoint works to declare a state where you want to remove all the previous logs from the system. In the event of a system crash, you can use checkpoints for recovery. Instead of performing the transactions from the very beginning, you can also use it to get the last recorded snapshot of the database.

7 . What is a database language?

A database language refers to the queries for updating and modifying the data in a database. There are four main types of database languages:

  1. Data Definition Language (DDL) – These commands works to update the data.
  2. Data Manipulation Language (DML) – These commands works to manipulate the updated data.
  3. Data Control Language (DCL) – These commands works for providing and revoking user access to a database.
  4. Transaction Control Language (TCL) – These commands works to manage all the transactions in a database.

8 . What do you mean by Data Model?

A Data model is a collection of tools that works to describe data, semantics and constraints. They also help describe the relationship between the data entities and their attributes. Some of the most common data models are hierarchical data model, network model, entity relationship model and relational model. You can also check out other data modelling interview questions to learn more about data models.

10 . What is the difference between an entity and an attribute?

An entity refers to a real-world object in a database. For example, in an employee database, the different entities can be employee, designation, department, and so on. 

An attribute is a characteristic that describes an entity. For example, the entity “employee” can have name, ID and age as its attributes.

11 . Explain the ACID properties of a DBMS.

ACID properties are the basic rules of a DBMS which you should follow in order to preserve data integrity. They are:

Atomicity – It is also popular as the “all or nothing” rule, which implies that you should consider everything as one unit that is either run to completion or not executed at all.

Consistency – This property implies that the data in the database remains consistent before and after every transaction.

Isolation – This property states that different numbers of transactions that you can execute concurrently.

Durability – This property ensures that every transaction that you store is in the non-volatile memory after you complete the transaction.

We will next look at the Technical DBMS interview questions and answers.

12 . What do you mean by functional dependency?

Functional dependency or database dependency determines the dependence of one attribute to another. It is denoted by X -> Y, which means that Y is functionally dependent on X.

13 . What is the main difference between DELETE and TRUNCATE command?

DELETE command works to remove the rows in a table based on the condition set by the WHERE clause. The rows deleted can be rolled back.

TRUNCATE command works to remove all the rows in a table without any conditions. The rows cannot be rolled back.

14 . Explain the different normal forms for normalisation.

The most common normal forms are:

  • First Normal Form(1NF) – A table is in 1NF if it holds only atomic values. 
  • Second Normal Form(2NF) – A table is in 2NF if it is in 1NF and non-prime attributes are not dependent on any candidate key.
  • Third Normal Form(3NF) – A table is in 3NF if it is in 2NF and non-prime attributes are not dependent on any super key.
  • Boyce & Codd Normal Form(BCNF) – A table is in BCNF if it is in 3NF and for every functional dependency A->B, B is the super key of the table.

15 . What are the differences between Hash join and Merge join?

Hash join – A hash join works to join large tables.

Merge join – A merge join works to join two input streams of the joined tables into a single output stream.

16 . Define a relationship in DBMS and its various types.

A relationship is an association or link between two or more data entities. There are three main types of relationships in DBMS:

  • One to one – A single record in one table is related to a single record in another table and vice versa.
  • One to many / Many to one – A single record of one table is related to many records of other tables and vice versa.
  • Many to many – Multiple records of one table are related to multiple records of another table.
  • It describes only how the user interacts with the database.

17 . Describe the different types of keys in a DBMS?

There are following types of keys in a DBMS: 

Primary key – It is an attribute that uniquely identify each record in a table. A primary key is compulsory for every table. 

Candidate key – It is a set of attributes to uniquely identify a table. 

Super key – It is a set of attributes to uniquely identify a tuple. 

Foreign key – It is a field, or a collection of fields used to link one table to a primary key from another table.

18 . What are the drawbacks of the file system which you can overcome by the database management system?

Data redundancy & isolation, difficulty in accessing data, data isolation, and integrity problems are drawbacks of the file system.

19 . Which level of abstraction describes what data is stored in the database?

Logical level describes what data is stored in the database.

20 . What is view level or external level data hiding?

It is the highest level of abstraction that describes only part of the entire database; this part implies the interaction of users with the system.

21 . Why is the entity set called a weak entity set?

The entity set is called a weak entity set because it does not have the primary key.

22 . What are the extended features of the ER diagram?

Specialisation, generalisation, aggregation are the extended features of ER diagrams.

23 . What is the minimum cardinality of zero specifies?

Partial participation is the minimum cardinality of zero specifies.

24 . When the fan trap may exist in the ER model.

When two or more one to more relationships fan out from the same entity set then the fan trap may exist in the ER model.

25 . What are RAT axioms in DBMS?

Reflexivity, argumentation, transitivity these are the Armstrong rule which is sufficient enough to find closure sets.

26 . What is meant by index cardinality?

Index cardinality is the total number of distinct key values for each index.

27 . What is the prime attribute of the relational schema?

An attribute is called prime attribute when it is the part of any candidate key in the relational schema.

28 . What is the full form of PGNF?

The full form of PGNF has projected joint normal form; it is the 5 the normal form and it cannot have a lossless decomposition into any number of smaller tables.

29 . What is RDBMS?

Relational database management system (RDBMS) is the database management system that maintains data records and indices in tables.

30 . What are the different phases of the transaction?

There are three different phases of the transaction. Analysis phase, Redo phase, Undo phase.

40 . What are the Transaction states?

 A transaction moves from one state to the other as it entries the system to be executed. There are many states like active, partially committed, failed, Aborted, committed.

41 . What do you mean by transparent DBMS?

The transparent DBMS is a type of DBMS which keeps its physical structure hidden from users. Physical structure or physical storage structure implies to the memory manager of the DBMS, and it describes how the data is stored on disk.

42 . What are the unary operations in Relational Algebra?

PROJECTION and SELECTION are the unary operations in relational algebra. Unary operations are those operations which use single operands. Unary operations are SELECTION, PROJECTION, and RENAME.

As in SELECTION relational operators are used for example – =,<=,>=, etc.

43 . Define a Relation Schema and a Relation.

A Relation Schema is specified as a set of attributes. It is also known as table schema. It defines what the name of the table is. Relation schema is known as the blueprint with the help of which we can explain how the data is organised into tables. This blueprint contains no data.

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

44 . What is a degree of Relation?

The degree of relation is a number of attributes of its relation schema. 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. There are three degrees of relation; they are one-to-one(1:1), one-to-many(1:M), and many-to-one(M:M).

45 . What are the disadvantages of file processing systems?

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

46 . 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, it makes accessible the user interaction with the database.

For example: We know that most of the users prefer those systems which have a simple GUI that means no complex processing. So, to keep the user tuned and to make the access to the data easy, it is necessary to do data abstraction. In addition to it, data abstraction divides the system in different layers to make the work specified and well defined.

47 . What is DDL (Data Definition Language)?

Data Definition Language (DDL) is a standard for commands which defines the different structures in a database. Most commonly DDL statements are CREATE, ALTER, and DROP. These commands are used for updating data into the database.

48 . What is DML (Data Manipulation Language)?

DData Manipulation Language (DML) is a language that enables the user to access or manipulate data as organised by the appropriate data model. For example- SELECT, UPDATE, INSERT, DELETE.

There is two type of DML:

Procedural DML or Low level DML: It requires a user to specify what data is needed and how to get that data.

Non-Procedural DML or High level DML:It requires a user to specify what data is needed without specifying how to get that data.

49 . Explain the functionality of DML Compiler.

The DML Compiler translates DML statements in a query language that the query evaluation engine can understand. A DML Compiler is required because the DML is the family of syntax elements which is very similar to the other programming language which requires compilation. So, it is essential to compile the code in the language which the query evaluation engine can understand and then work on those queries with proper output.

50 . What is Relational Algebra?

Relational Algebra is a Procedural Query Language which 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 few fundamental operations of relational algebra:

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

51 . What is Relational Calculus?

Relational Calculus is a Non-procedural Query Language which 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 type of relational calculus:

  • Tuple relational calculus
  • Domain relational calculus

52 . What do you understand about query optimization?

The term query optimization specifies an efficient execution plan for evaluating a query that has the least estimated cost. The concept of query optimization came into the frame when there were a number of methods, and algorithms existed for the same task. Then the question arose which one is more efficient and the process of determining the efficient way is known as query optimization.

There are many benefits of query optimization:

  • It reduces the time and space complexity.
  • More queries can be performed as due to optimization every query comparatively takes less time.
  • User satisfaction as it will provide output fast

53 . What is the concept of sub-query in terms of SQL?

Sub-query is basically the query which is included inside some other query and can also be called as an inner query which is found inside the outer query.

54 . What is the use of DROP commands and what are the differences between DROP, TRUNCATE and DELETE commands?

DROP command is a DDL command which is used to drop/delete the existing table, database, index or view from the database.

The major difference between DROP, TRUNCATE and DELETE commands are:

DROP and TRUNCATE commands are the DDL commands which are used to delete tables from the database and once the table gets deleted, all the privileges and indexes that are related to the table also get deleted. These 2 operations cannot be rolled back and so should be used only when necessary.

DELETE command, on the other hand, is a DML Command which is also used to delete rows from the table, and this can be rolled back.

55 . What is the main difference between UNION and UNION ALL?

UNION and UNION ALL are used to join the data from 2 or more tables but UNION removes duplicate rows and picks the rows which are distinct after combining the data from the tables whereas UNION ALL does not remove the duplicate rows, it just picks all the data from the tables.

56 . What is a Correlated Subquery in DBMS?

A Subquery is also known as a nested query i.e. a query written inside some query. When a Subquery is executed for each of the rows of the outer query then it is termed as a Correlated Subquery.

57 . 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.

58 . What integrity rules exist in the DBMS?

There are 2 major integrity rules that exist in the DBMS.

They are:

  • Entity Integrity: This states a very important rule that the value of a Primary key can never have a NULL value.
  • Referential Integrity: This rule is related to the Foreign key which states that either the value of a Foreign key is a NULL value or it should be the primary key of any other relation.

59 . What is the E-R model in the DBMS?

E-R model is known as an Entity-Relationship model in the DBMS which is based on the concept of the Entities and the relationship that exists among these entities.

60 . What is a CLAUSE in terms of SQL?

This is used with the SQL queries to fetch specific data as per the requirements on the basis of the conditions that are put in the SQL. This is very helpful in picking the selective records from the complete set of the records.

For Example, there is a query which has WHERE condition or the query with the HAVING clause.