Table of Contents
Top 30+ Interview Questions and Answers for DBMS asked in TCS NQT 2023 and 2024 Interviews.
1) What is DBMS?
DBMS (Database Management System) is software for storing and retrieving users’ data while also considering appropriate security measures. The system is composed of a number of programs that manipulate the Database. It accepts the request for data from an application and instructs the operating system to provide the specific data.
2) What is RDBMS?
RDBMS is a short form of Relational Database Management system. It is a software system that stores only data that needs to be stored in related tables. In RDBMS, data is managed and stored in rows and columns, known as tuples and attributes.
3) What is Database Partitioning?
Division of a logical database into complete independent units for improving its management, availability, and performance is called Database partitioning.
4) What is Relational Algebra in the DBMS system?
Relational Algebra is a procedural query language that collects relations as input and gives occurrences of relations as output. It uses various operations to perform this action.
5) What are the Important differences between DBMS and RDBMS?
| DBMS | RDBMS |
| DBMS stores data as a file. | Data is stored in the form of tables. |
| It stores data in either a navigational or hierarchical form. | It uses a tabular structure where the headers are the column names, and the rows contain corresponding values |
| DBMS supports single users only. | It supports multiple users. |
| Demands low software and hardware needs. | Demands higher hardware and software needs. |
| DBMS does not support Normalization | RDBMS can be Normalized. |
| Does not support client-server architecture | RDBMS supports client-server architecture. |
6) What is a Deadlock?
A Deadlock is a situation that occurs in OS when any process enters a waiting state as another waiting process is holding the demanded resource. It is a common problem in multi-processing where several processes share a specific type of mutually exclusive resource known as a soft lock.
7) What is Live Lock?
Livelock is a situation in which an exclusive lock request is repeatedly denied as many overlapping shared locks keep interfering with each other. The processes keep changing their status, preventing them from completing the task.
8) Define Unary operations in Relational Algebra?
In relational algebra, PROJECTION and SELECTION are unary operations.
Unary operations are those kinds of operations that use only single operands.
As in SELECTION, relational operators are used, for example – =, <=,>=, etc.
9) What is Concurrency Control?
Concurrency Control is a procedure that helps to manage simultaneous operations without conflicting with each other. It helps you to ensure that Database transactions are executed concurrently and accurately.
10) What are the different types of languages available in the DBMS?
Here are three types of languages used in DBMS:
DDL: Data Definition Language, which defines the database and schema structure using CREATE, ALTER, DROP, and RENAME.
DCL: DCL is a Data Control Language used to control the users’ access inside the Database using some SQL Queries like REVOKW and GRANT.
DML: Data Manipulation Language, which is used to manipulate data in databases, such as inserting, deleting, etc., by using some SQL Queries like SELECT, UPDATE, INSERT, and DELETE.
11) What is Functional Dependency?
Functional Dependencies (FD) in Database Management systems define the relationship between an attribute and another. It allows you to maintain the quality of data in the Database.
12) What is Dynamic SQL?
In a Dynamic SQL database, SQL statements are generated by the application at runtime, where the user is empowered to create queries.
You can build your query at runtime. It is slower than static SQL as the query is compiled at runtime.
13) What is ER model?
ER Model or Entity-Relationship Model is based on the natural world and deals with entities and their relationships.
14) What is an Attribute to ER Model?
An attribute is a particular property that describes an entity. For example, if the student is an entity, then Student_id, Std _firstname, and Std_lastname are attributes that describe the student.
Correlated Subqueries executed for each row of the outer query are known as Correlated Subqueries. They are also called nested queries, i.e., a query written inside some query.
16) What is Primary Key?
Primary keys are columns or sets of columns that uniquely identify every row in the table of an RDMS system. This key can’t be duplicated means the same value should not appear more than once in the table.
17) What is Foreign Key?
A foreign key is a column that creates a relationship between two tables.
They are used to maintain data integrity and facilitate navigation between two instances of the same entity. Moreover, it serves as a cross-reference between two tables since it refers to another table’s primary key.
18) What is Database Relationship?
The database relationship is associations between single or multiple tables that are developed using join statements. Data can be efficiently retrieved from the Database using this method.
There are primarily three types of relationships in DBMS:
One-to-One, One-to-many,
Many-to-many.
19) What is Starvation?
Starvation is a situation where all the low-priority processes get blocked. In any system, requests for high and low-priority resources keep happening dynamically. Therefore, some policy is required to decide who gets support and when.
20) What is a Schedule?
A Schedule is creating a group of multiple parallel transactions and executing them one after one. The order in which the instructions appear should be preserved in every transaction. In case two transactions are executed simultaneously, the result of one transaction may affect the output of the other.
21) What are the main differences between UNION and UNION ALL?
UNION and UNION ALL are used to join the data from two or more tables.
However,
UNION removes duplicate rows and picks. The rows are distinct after combining the data from the tables were
UNION ALL does not remove the duplicate rows; it just picks all the data from the tables.
22) What is Index Hunting?
Index Hunting is a data structure that improves the speed of data retrieval operations on a database. It is the procedure of boosting the collection of indexes. It is done by using methods like query distribution and query optimization.
23) What is a Distributed Database System?
A Distributed Database System is a set of interconnected databases spread physically across various locations. It can be on the same network or multiple networks.
24) What is Data Warehousing?
Data Warehousing is a process for collecting and managing data from varied sources to provide meaningful business insights. This method is typically used to connect and analyze business data from heterogeneous sources.
25) What is Database Architecture?
Database Architecture is a DBMS design representation that helps you Design, develop, implement, and maintain the DBMS system. It divides the DBMS into separate components that can be independently changed, modified, replaced, and altered.
26) What do you mean by transparent DBMS?
The transparent DBMS keeps its physical structure hidden from users.
Physical structure or physical storage structure implies the memory manager of the DBMS.
27) What is Join?
Join operation is one of the most valuable activities in relational algebra. It is widely used to combine information from two or more relations. It is performed based on the same or related column. The JOIN command is used in most complex SQL queries.
28) What is 1NF?
1NF is the First Normal Form. It is the simplest type of Normalization which you can implement in a database.
Every column must have an atomic (single value)
It helps you to Remove duplicate columns from the same table
It should have created separate tables for each group of related data and helped you to identify each row with a unique column
29) What is 2NF in the DBMS? 2NF is the Second Normal Form.
Any table said to have in the 2NF should satisfy the following 2 conditions:
A table is in the INF.
Each non-prime attribute of a table is functionally dependent on the primary key.
30) What is BCNF?
BCNF is a sort from the Boyce-Codd Normal Form. It is an advanced version of the 3rd Normal Form, also called 3.5NF.
A table that complies with BCNF and satisfies the following conditions:
It is in 3NF: To have functional dependency X-›Y, X should be the super key of the entire table.
It means X can’t be a non-prime attribute if Y is a prime attribute.
