Top 50 DBMS Interview Questions

• Introduction
• Here is the list of DBMS interview question

Introduction

In this era we face billions of TeraBytes of data every day which leads the market to work properly. Data can decide “how the Market will run ? “ “ how will a company do? “How will their products do in the market ?”. This article on ” DBMS interview questions ” can help you become database administators.

 So it’s really important for companies to manage the info and use it towards the development of the company. This leads us towards the DBMS (Database Management system). The DBMS manages the data; the database engine allows data to be accessed, locked, and modified; and therefore the database schema defines the database’s logical structure. These three fundamental elements help us to provide concurrency, security, data integrity, and uniform data administration procedures. The database administration tasks the DBMS supports that include change management, performance monitoring, and tuning, security, and backup and recovery. Most management systems also are liable for automated rollbacks and restarts as logging and auditing of activity in databases and therefore the applications that access them.

The DBMS provides a centralized view of knowledge that will be accessed by multiple users, from multiple locations, in a controlled manner. A DBMS can limit what data the end-user sees and the way that end-user can view the info, providing many views of one database schema. End users and software programs both are independent of having to know where the info is physically located or on what sort of storage media it resides because the DBMS handles all requests.

The DBMS offers both logical and physical data independence. This suggests it can protect users and applications from wanting to know where data is stored or worrying about changes to the body of knowledge. As long as programs use the appliance programming interface (API) for the DBMS database, developers won’t need to modify programs simply because changes are made to the database. DBMS gives us the opportunity to become a Database Administrator. In order to become a Database administrator we have to go through the Interview process. This process is meant to test your problem solving skills and as well as your knowledge,  as well as your team work, soft skills and every important perspective for Work in a Group of people and keep the workflow at the peak.

Now let’s just jump in to the Frequently asked question for a database Administrator interview or a Database Management interview Questions. 

Check out database management courses and upskill.

Here is the list of DBMS interview question

1) What do you mean by DBMS?

Database Management System (DBMS) is software for storing and retrieving users’ data while considering appropriate security measures. It consists of a set of programs that manipulate the database. The DBMS accepts the request for data from an application and instructs the OS to supply the precise data. In large systems, a DBMS helps users and other third-party software to store and retrieve data.

DBMS allows users to make their own databases as per their requirements. The term “DBMS” includes the utilization of the database and other application programs. It gives an interface between the info and therefore the software application.

2) What Do You Mean By Database?

A database is an organized collection of knowledge in order that it is often easily accessed and managed.

You can organize data into tables, rows, columns, and index it to form it easier to seek out relevant information.

Database handlers create a database in such a way that just one set of software programs provides access to data to all or any of the users.

The main purpose of the database is to work an outsized amount of data by storing, retrieving, and managing data.

There are many dynamic websites on the planet Wide Web nowadays which are handled through databases. For instance, a model that checks the supply of rooms during a hotel. It’s an example of a dynamic website that uses a database.

There are a lot of databases available such as MySQL, Sybase, Oracle, MongoDB, Informix, PostgreSQL, SQL Server, etc.

Modern databases are managed by the management system (DBMS).

SQL or Structured command language is employed to work on the info stored during a database. SQL totally depends on relational algebra and as well as tuple relational calculus.

3) What is the meaning of a Database system?

The collection of database and DBMS software together is understood as a database system. Through the database system, we will perform many activities such as-

The data are often stored within the database with ease, and there are not any problems with data redundancy and data inconsistency.

The data are going to be extracted from the database using DBMS software whenever required. So, the mixture of database and DBMS software enables one to store, retrieve and access data with considerate accuracy and security.

4) What is the meaning of checkpoint in DBMS?

The Checkpoint may be a sort of mechanism where all the previous logs are faraway from the system and permanently stored within the storage disk.

Two ways can help the DBMS in recovering and maintaining the ACID properties, and that they are- maintaining the log of every transaction and maintaining shadow pages. So, when it involves a log-based recovery system, checkpoints inherit existence. Checkpoints are those points to which the database engine can recover after a crash as a specified minimal point from where the transaction log records are often wont to recover all the committed data up to the purpose of the crash.

5) When does the Checkpoint in DBMS Happen?

A checkpoint is sort of a snapshot of the DBMS state. Using checkpoints, the DBMS can reduce the quantity of labor to be done during a restart in the event of subsequent crashes. Checkpoints are used for the recovery of the database after the system gets crashed. Checkpoints are utilized in the log-based recovery system. When thanks to a system crash we’d like to restart the system then at that time we use checkpoints. So that, we do not need to perform the transactions from the very starting.

6) What is a Transparent DBMS?

The transparent DBMS may be a sort of DBMS that keeps its body hidden from users. body or physical storage structure implies the memory manager of the DBMS, and it describes how the info is stored on a disk. 

7) What are unary operations in Relational Algebra?

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

As in SELECTION relational operators are used for instance – =,=, etc.

8) What Do You Mean By RDBMS?

   RDBMS stands for electronic database Management Systems. it’s wont to maintain the info records and indices in tables. RDBMS is the sort of DBMS which uses the structure to spot and access data concerning the opposite piece of knowledge within the database. RDBMS is the system that permits you to perform different operations such as- update, insert, delete, manipulate and administer an electronic database with minimal difficulties. Most of the time RDBMS uses SQL language because it’s easily understandable and is employed for some time .

9) What are the types of Database languages available ?

There are four sorts of database languages:

Data Definition Language (DDL) uses some of the basic commands such as CREATE, ALTER, DROP, TRUNCATE, RENAME, etc.  for updating the info that’s why they’re referred to as Data Definition Language.

Data Manipulation Language (DML) uses such commands as SELECT, UPDATE, INSERT, DELETE, etc are the commands that  are used for the manipulation of already updated data so they’re a part of Data Manipulation Language.

DATA Control Language (DCL) such commands as  GRANT and REVOKE are the commands that are used for adding and removing the user access to the database. So, they’re a part of the Data Control Language.

Transaction Control Language (TCL)  such commands as COMMIT, ROLLBACK, and SAVEPOINT. These are the commands used for managing transactions within the database. TCL is employed for managing the changes made by DML.

Database language implies the queries that are used for the updating, modifying and manipulating the info in the system.

10) What is a Data Model according to you ?

The Data model is specified as a set of conceptual tools for describing data, data relationships, data semantics and constraints. These models are to describe the connection between the entities and their attributes.

There is the amount of knowledge models:

1. Hierarchical data model
2. Network model
3. Relational model
4. Entity-Relationship model then on.

11) Explain a Relation Schema and a Relation.

A Relation Schema is specified as a group of attributes. It’s also referred to as table schema. It explains what the name of the table is. A relation schema is understood because the blueprint with the assistance of which we will explain how the info is organized into tables. This blueprint contains no data.

A relation is specified as a group of tuples. A relation is that the set of related attributes identifies the key attributes.

See this example:

Let r be the relation that includes set tuples (t1, t2, t3, …, tn). Each tuple contains an ordered list of n-values t=(v1,v2, …., vn).

12) What do you mean by a degree of Relation?

The degree of relation defines the number of attributes of its relation schema. A degree of relation is additionally referred to as Cardinality; it’s defined because the number of occurrences of 1 entity which is connected to the amount of occurrence of another entity. There are three degrees of relation; they’re one-to-one(1:1), one-to-many(1:M), many-to-one(M:M).

13) What is the meaning of Relationship?

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

One-To-One: Here one record of any object is often associated with one record of another object.

One-To-Many (many-to-one): Here one record of any object is often associated with many records of other objects and the other way around.

Many-to-many: Here quite one record of an object is often associated with n number of records of another object.

14) What are the disadvantages we face for file processing systems?

File processing system has several disadvantages :

• Inconsistent: File processing systems are inconsistent, which delays the work. 
• Not secure: This system is not that secure or reliable.
• Data redundancy : It just displays the same sort of data in several places.
• Difficult in accessing data : Accessing the data is really difficult in this system.
• Data isolation :- Due to this, retrieving the appropriate data becomes difficult for new applications. 
• Data integrity : This system is unable to deliver the data accuracy and consistency .
• Concurrent access is not possible : With this system data can not be accessed by more than one user.
• Limited data sharing : This system has a limited amount of data sharing which makes the work difficult.
• Atomicity problem : This system is unable to make atomic transactions.

15) What do you mean by abstraction in Database management system?

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

For example, we all know that the majority of the users prefer those systems which have an easy GUI meaning no complex processing. So, to stay the user tuned and for creating access to the info easily, it’s necessary to try to abstraction. Additionally, data abstraction divides the system into different layers to form the work specified and well defined.

16) What are the layers of data abstraction?

Following are three levels of knowledge abstraction:

Physical level: it’s rock bottom level of abstraction. It describes how data is stored.

Logical level: it’s subsequent higher level of abstraction. It describes what data is stored within the database and what the connection among those data is.

View level: it’s one of the best levels of data abstraction. It explains only a part of the whole database.

For example- The user interacts with the system using the GUI and fills within the required details, but the user doesn’t have any idea how the info is getting used . So, the abstraction level is entirely high within the VIEW LEVEL.

Then, the subsequent level is for PROGRAMMERS as during this level the fields and records are visible and therefore the programmers have the knowledge of this layer. So, the extent of abstraction here may be a little low within the VIEW LEVEL.

And at last, the physical level during which storage blocks are described.

17) What is query Optimization according to you ?

Query optimization is that phase which identifies an idea for an evaluation query that has the smallest amount estimated cost. This phase comes into the image when there are tons of algorithms and methods to execute an equivalent task.

The advantages of query optimization are as follows:

• It gives the output faster
• A larger number of queries are often executed in less time
• It Reduces time and as well as space complexity

18) What is the meaning or entity-relationship model?

It is a diagrammatic way of database designing, In which we can represent real-world objects as entities and mention or explain the relationship between the entities. This approach helps the team of DBAs’ to know the schema easily.

19) Explain the different types of keys in Database.

In general there are 7 types of keys that we consider in a database. Let’s talk about them in detail. 

Candidate Key – This is often a group of attributes which may uniquely identify a table. Each table can have quite a candidate key. Aside from this, out of all the candidate keys, one key is often chosen because of the Primary key. For example, since CustomerID and PanNumber can uniquely identify every tuple, they might be considered as a Candidate Key. 

Super Key – This is often a group of attributes which may uniquely identify a tuple. So, a candidate key, primary key, and a singular key a superkey, but vice-versa isn’t true.

Primary Key – This is often a group of attributes which are wont to uniquely identify every tuple. For example, since CustomerID and PanNumber are candidate keys, anybody of them is often chosen as a Primary Key. Here CustomerID is chosen as the primary key.

Unique Key – The unique key’s almost like the first key, but allows NULL values within the column. Here the PanNumber is often considered as a singular key.

Alternate Key – Alternate Keys are the candidate keys, which aren’t chosen as a Primary key. For example, the alternate key’s PanNumber For  Government .  

Foreign Key – An attribute which will only take the values present because the values of another attribute, is that the foreign key to the attribute to which it refers. For example, the CustomerID from the purchasers Table is mentioned as the CustomerID from the Customer_Payment Table.

Composite Key – A composite key’s a mixture of two or more columns that identify each tuple uniquely. For example  the CustomerID and Date_of_Payment are often grouped together to uniquely identify every tuple within the table.

20) What do you mean by correlated subqueries  in DBMS?

A correlated subquery is additionally a kind of subquery reliant on another query. So, when subqueries are executed for every of the rows of outer queries, then they’re termed as correlated subqueries. Each subquery is executed one time for each row of the outer query.

You can also think of correlated sub-queries as those queries, which are used for row-by-row processing by the parent statement. Here, the parent statements are often SELECT, UPDATE or DELETE statements.

21) What is Database partitioning and how is it important ?

Data partitioning is the process of dividing a logical database into independent units for the betterment of availability, performance, and manageability.

The importance of database partitioning is as follows:

• Enables you to access large parts of a selected partition
• Cheap and slower storage are often used to store data
• Improves query performance

22) According to you what is Functional dependency and Transitive dependency in DBMS?

Functional Dependency: A functional dependency can be defined as a constraint that’s utilized in describing the connection among different attributes during a relation.

For example we can consider a relation “A1” having attributes X and Y. The functional dependency among these two attributes is going to be X -> Y, this suggests that Y is functionally hooked into X.

Transitive Dependency: A transitive dependency can be defined as a constraint that will only occur concerning three or more attributes. 

For example we can consider a relation “A1” having attributes X, Y, and Z. Now, X->Z is claimed to carry transitive dependency, as long as the subsequent functional dependencies hold:

X -> Y

Y doesn’t ->X

Y -> Z

23) What do you mean by proactive, Retroactive and Simultaneous update?

Proactive Update: These updates are applied to the database before it becomes effective within the real-world environment.

Retroactive Update: These retroactive updates are applied to a database after it becomes effective within the real-world environment.

Simultaneous Update: These updates are applied to the database at an equivalent instance of your time because it becomes effective during a real-world environment.

24) What do you mean DDL(Data Definition Language)?

Data Definition Language (DDL) is defined asDData Manipulation Language (DML) is a language that enables the user to access or manipulate data as organized by the appropriate data model. For example- SELECT, UPDATE, INSERT, DELETE.

25) What do you mean DML(Data Manipulation Language)?

There are two sorts of DML:

Procedural DML or Low-level DML: Which requires a user to specify what data is needed and the way to urge that data.

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

 A typical for commands which define the varied structures during a database. The  most commonly used DDL statements are CREATE, ALTER, and DROP. These commands are used for updating data within the database.

26) How does DML Compiler function?

The DML Compiler translates DML statements during a command language that the query evaluation engine can understand. A DML Compiler is required because the DML is the family of syntax elements which is extremely almost like the opposite programming language which needs compilation. So, it’s essential to compile the code within the language which the query evaluation engine can understand, then work on those queries with proper output.

27) What is Cursor according to you? And what are the types of cursor?

The DML Compiler translates the cursor as a database object which helps in manipulating data, row by row, and represents a result set.

The types of the cursor are as follows:

Implicit cursor: this sort of cursor is said automatically as soon as the execution of SQL takes place. Here, the user isn’t indicated about the declaration of the cursor.

Explicit cursor: this sort of cursor is defined by the PL/ SQL, because it handles a question in addition to one row.es DML statements during a command language that the query evaluation engine can understand. A DML Compiler is required because the DML is the family of syntax elements which is extremely almost like the opposite programming language which needs compilation. So, it’s essential to compile the code within the language which the query evaluation engine can understand then work on those queries with proper output.

28) What do you mean by Relational Algebra?

Relational Algebra may be a Procedural command language that contains a group of operations that take one or two relations as input and produce a replacement relationship. Relational algebra is the basic set of operations for the relational model. The decisive point of relational algebra is that it’s almost like the algebra which operates on the amount .

There are few fundamental operations of relational algebra:

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

29) What is relational Calculus?

Relational Calculus may be a Non-procedural command language that uses mathematical functional calculus rather than algebra. Relational calculus doesn’t work on mathematics fundamentals like algebra, differential, integration, etc. That’s why it’s also referred to as functional calculus .

There is two sorts of relational calculus:

Tuple relational calculus

Domain relational calculus

30) What is Specialization and Generalization?

Specialization: Specialization can be termed as a process of defining a group of sub-classes of the entity type. Here, every subclass will contain all the attributes and relationships of the parent entity. aside from this, the sub-classes may contain additional attributes and relationships specific to themselves.

Generalization: Generalization can be termed as a process of finding relations, common attributes for a specific set of entities; and eventually defining a standard super-class for them.

31) What do you know about Query Optimization?

The term query optimization specifies an efficient execution plan for evaluating a question that has the smallest amount estimated cost. The concept of query optimization came into the frame when there have been a variety of methods, and algorithms existed for an equivalent task then the question arose that which one is more efficient and therefore the process of determining the efficient way is understood as query optimization.

There are many benefits of query optimization:

It reduces the time and space complexity.

More queries are often performed as thanks to optimization every query comparatively takes less time.

User satisfaction because it will provide output fast.

32) What does the Fill Factor concept mean regarding indexes?

Fill Factor is employed to say the share of space left on every leaf-level page, which is full of data. Usually, the default value is 100.

33) What do you mean by index hunting and how does it help in improving query performance?

The process of boosting a set of indexes is understood as Index hunting. This is often done as indexes improve the query performance and therefore the speed at which they’re processed.

It helps in improving query performance within the following way:

The best queries are obtained using the query optimizer.

Index, query distribution, and their performance are used as metrics to see the effect

Databases are tuned into a little collection of problem queries.

34) What does one mean by the sturdiness in DBMS?

Once the DBMS informs the user that a transaction has completed successfully, its effect should persist albeit the system crashes before all its changes are reflected on the disk. This property is named durability. Durability ensures that when the transaction is committed into the database, it’ll be stored within the non-volatile memory and then system failure cannot affect that data anymore.

35) what’s normalization?

Normalization may be a process of analyzing the given relation schemas consistent with their functional dependencies. It wants to minimize redundancy and also wants to minimize insertion, deletion, and update distractions. Normalization is taken into account an important process because it is employed to avoid data redundancy, insertion anomaly, update anomaly, deletion anomaly.

Their most ordinarily used normal forms are:

First Normal Form(1NF)

Second Normal Form(2NF)

Third Normal Form(3NF)

Boyce & Codd Normal Form(BCNF)

36) What’s Denormalization?

Denormalization is the process of boosting up database performance and adding redundant data which helps to obviate complex data. Denormalization may be a part of the database optimization technique. This process is employed to avoid the utilization of complex and dear joins. Denormalization doesn’t ask the thought of not normalizing rather than that denormalization takes place after normalization. During this process, firstly the redundancy of the info is going to be removed using the normalization process then through the denormalization process we’ll add redundant data as per the need in order that we will easily avoid the costly joins.

37) What’s Functional Dependency?

Functional Dependency is the start line of normalization. It exists when a relation between two attributes allows you to work out the corresponding attribute’s value uniquely. The functional dependency is additionally referred to as database dependency and defines because the relationship which occurs when one attribute during a relation uniquely determines another attribute. it’s written as A->B which suggests B is functionally hooked into A.

38) What’s the E-R model?

E-R model may be a short name for the Entity-Relationship model. This model is predicated on the important world. It contains necessary objects (known as entities) and therefore the relationship among these objects. Here the first objects are the entity, attribute of that entity, relationship set, and attribute of that relationship set are often mapped within the sort of E-R diagram.

In the E-R diagram, entities are represented by rectangles, relationships are represented by diamonds, attributes are the characteristics of entities and represented by ellipses, and data flow is represented through a line .

39) what’s an entity?

The Entity may be a set of attributes during a database. An entity is often a real-world object which physically exists during this world. All the entities have their attributes which within the world are considered because of the characteristics of the thing.

For example: within the employee database of a corporation, the worker, department, and designation are often considered entities. These entities have some characteristics which can be the attributes of the corresponding entity.

40) what’s an Entity type?

An entity type is specified as a set of entities, having an equivalent attribute. The entity type typically corresponds to at least one or several related tables within the database. A characteristic or trait which defines or uniquely identifies the entity is named entity type.

Just for example’s sake we can consider, a student has student_id, department, and course as its characteristics.

41) What’s an Extension of entity type?

An extension of an entity type is specified as a set of entities of a specific entity type that are grouped into an entity set.

42) what do you mean by 1NF?

1NF is the First Normal Form. it’s the only sort of normalization that you simply can implement during a database. the first objectives of 1NF are to:

Every column must have atomic (single value)

To Remove duplicate columns from an equivalent table

Create separate tables for every group of related data and identify each row with a singular column

43) what do you mean by 2NF?

2NF is the Second Normal Form. A table is claimed to be 2NF if it follows the subsequent conditions:

The table is in 1NF, i.e., firstly it’s necessary that the table should follow the principles of 1NF.

Every non-prime attribute is fully functionally hooked into the first key, i.eEvery non-key attribute should be hooked into the first key in such a way that if any key element is deleted, then even the non_key element will still be saved within the database.

44) what do you mean by 3NF?

3NF stands for Third Normal Form. A database is named in 3NF if it satisfies the subsequent conditions:

It is in its second normal form.

There is no transitive functional dependency.

For example: X->Z

Where:

X->Y

Y doesn’t -> X

Y->Z so, X->Z

45) What’s BCNF?

BCMF stands for Boyce-Codd Normal Form. it’s a complicated version of 3NF, so it’s also mentioned as 3.5NF. BCNF is stricter than 3NF.

A table complies with BCNF if it satisfies the subsequent conditions:

It is in 3NF.

For every functional dependency X->Y, X should be the super key of the table. It merely means X can’t be a non-prime attribute if Y may be a prime attribute.

46) Difference between PRIMARY and UNIQUE KEY:

47) Difference between EXISTS and IN:

48) What are the components of DBMS?

DBMS has several components and each of the components perform significant tasks in the DBMS environment.

• Software: The DBMS software component controls and manages the overall database. 
• Hardware: DBMS hardware component consists of a set of physical electronic devices such as computers, I/O devices, to provide the interface between computers and real-world systems.
• Data: DBMS is all about collecting, storing, processing and accessing the data. So, this is one of the important components in  DBMS.
• Procedures: The procedure component is used in designing, running the database and to guide the users that operate and manage it.
• Database Access Language: This component is used to access the data, enter the new data, update the data, and retrieve the data. 
• Query Processor: This component is used to transform the user queries into a series of low-level queries and translates them into an efficient series of operations.
• Run Time Database Manager: This component handles the database at run-time and converts operations into queries. 
• Data Manager: This component is also called cache manager which is responsible for providing recovery to the system that allows it to recover the data after a failure.
• Database Engine: This component is a core service in DBMS that manages the 
• storing, processing, and securing data. 
• Data Dictionary:  This component is reserved in DBMS where it stores information about itself.
• Report Writer:  This component is responsible for generating reports. 

49) Query to find the Running total by Business Units for a specific company 

Select year,
 Business_Unit,
Amount_utilized,
Sum(Amount_utilized) 
Over (Partition By 
Business_Unit Order by 
Year) As RunningTotal
From 
Table_companies;

50) Query to find monthly change in a column from table A 

Select month,
          Column_A,
         Column_A - LAG(Column_A,1) Over (Order By month) As Month_on_Month_change
From table_X ;

This brings us to the end of the blog on DMBS Interview Questions. We hope that these DBMS Interview Questions help you. If you wish to learn more such concepts, you can join Great Learning Academy’s Free Online Courses and learn the most in-demand skills today.