DBMS Interview Questions and Answers
Question - 51 : - What are the ACID properties in DBMS?
Answer - 51 : -
ACID stands for Atomicity, Consistency, Isolation, Durability. It is used to ensure that the data transactions are processed reliably in a database system.
- Atomicity: Atomicity refers to those transactions which are completely successful or failed. Here each transaction refers to a single logical operation of a data. So, even 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 validation rules. In simple words, you can say that your 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 has been committed, it will occur whatever may be the scenario.
Question - 52 : - What is normalization and what are the different types of normalization?
Answer - 52 : -
The process of organizing data to avoid any duplication of data and redundancy is known as Normalization. There are many successive levels of normalization which are known as normal forms. Each consecutive normal form depends on the previous one. The following are the first three normal forms. Apart from these, you have higher normal forms such as BCNF.
- First Normal Form (1NF) – No repeating groups within rows
- Second Normal Form (2NF) – Every non-key (supporting) column value is dependent on the whole primary key.
- Third Normal Form (3NF) – Dependent solely on the primary key and no other non-key (supporting) column value.
Question - 53 : - What do you understand by correlated subqueries in DBMS?
Answer - 53 : -
A correlated subquery is also a sort of subquery reliant on another query. So, 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.
You can also understand correlated subqueries as those queries, which are used for row-by-row processing by the parent statement. Here, the parent statement can be SELECT, UPDATE or DELETE statement.
Question - 54 : - Explain Database partitioning and its importance.
Answer - 54 : -
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 specific partition
- Cheap and slower storage can be used to store data
- Improves query performance
What do you understand by functional dependency and transitive dependency in DBMS?
Answer - 55 : -
Functional Dependency: A functional dependency is a constraint that is used in describing the relationship among different attributes in a relation.
Example: Consider a relation “A1” having attributes X and Y. The functional dependency among these two attributes will be X -> Y, this implies that Y is functionally dependent on X.
Transitive Dependency: A transitive dependency is a constraint that can only occur in a relation of three or more attributes.
Example: Consider a relation “A1” having attributes X, Y and Z. Now, X->Z is said to hold transitive dependency, only if the following functional dependencies holds true:
- X -> Y
- Y doesn’t ->X
- Y -> Z
Question - 56 : - What is a checkpoint in DBMS and when does it occur?
Answer - 56 : -
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.
Question - 57 : - What do you understand by Proactive, Retroactive and Simultaneous Update?
Answer - 57 : -
- Proactive Update: These updates are applied to the database before it becomes effective in the real-world environment.
- Retroactive Update: These retroactive updates are applied to a database after it becomes effective in the real-world environment.
- Simultaneous Update: These updates are applied to the database at the same instance of time as it becomes effective in a real-world environment.
Question - 58 : - What do you understand by intension and extension?
Answer - 58 : -
Intension: Intension or most commonly known as Database schema defines the description of the database. This is specified during the database design and mostly remains unchanged.
Extension: Extension is the number of tuples available in the database at any instance of time. This value keeps changing as and when the tuples are created, updated and destroyed. So, the data present in the database at a specific instance of time is known as the extension of the database or most commonly known as the snapshot of the database.
Question - 59 : - What do you understand by cursor? Mention the different types of cursor
Answer - 59 : -
A cursor is a database object which helps in manipulating data, row by row and represents a result set.
The types of cursor are as follows:
- Implicit cursor: This type of cursor is declared automatically as soon as the execution of SQL takes place. Here, the user is not indicated about the declaration of the cursor.
- Explicit cursor: This type of cursor is defined by the PL/ SQL, as it handles a query in more than a single row.
Question - 60 : - Explain the terms specialization and generalization
Answer - 60 : -
- Specialization: Specialization is a process of defining a set of subclasses of the entity type. Here, each subclass will contain all the attributes and relationships of the parent entity. Apart from this, the subclasses may contain additional attributes and relationships specific to itself.
- Generalization: Generalization is a process of finding relations, common attributes for a particular set of entities; and finally defining a common superclass for them.