The blog covers the top 31 advanced-level DBMS Interview Questions. Let’s continue learning about the top DBMS Interview Questions and their answers asked by leading tech companies.
DBMS Interview Questions
Q1. What is a transaction in DBMS?
Answer: Transaction is a single logical unit of work performed by a set of operations.
Q2. What are the various operations in a transaction?
Answer: In DBMS, there are mainly 2 types of operations in a transaction such as:
● Read Operation – Read(A) instruction reads the value of ‘A’ from the database and stores it in the buffer in the main memory.
● Write Operation – Write(A) writes ‘A”s updated value from the buffer to the database.
Q3. What are the various transaction states?
The various transaction states of DBMS are:
- Active State
- Partially Committed
Q4. What are the advantages and disadvantages of views in DBMS?
Answer: Advantages of Views:
- Views help in generating output without wasting resources as there is no physical location where the data in the view is stored.
- Data access is restricted as it does not allow commands like insertion, updation, and deletion.
Disadvantages of Views:
- The view becomes irrelevant if we drop a table related to that view.
- Much memory space is occupied when the view is created for large tables.
Q5. What are the various ACID Properties in DBMS?
- Atomicity –
o This property ensures that either the transaction occurs entirely or it does not occur at all.
o In other words, it ensures that no transaction occurs partially.
● Consistency –
o This property ensures that integrity constraints are maintained.
o In other words, it ensures that the database remains consistent before and after the transaction.
● Isolation –
o This property provides that multiple transactions can co-occur without causing any inconsistency.
o The resultant state of the system after executing all the transactions is the same as the state that would be achieved if completed the transactions serially, one after the other.
● Durability –
o This property ensures that all the changes made by a transaction after its successful execution are written successfully to the disk.
o It also ensures that these changes exist permanently and are never lost even if there occurs a failure of any kind.
Q6. What is a schedule?
Answer: The order in which the operations of multiple transactions appear for execution is called a schedule in DBMS.
Q7. How many types of schedules are there in the transaction? Name them.
Answer: There are two types of schedules in DBMS transactions:
- Serial Schedules
- Non Serial Schedules
Q8. What is the difference between DBMS’s Serial and Non Serial Schedules?
|Serial Schedules||Non Serial Schedules|
|Transactions execute serially one after the other.||Multiple transactions execute concurrently.|
|When one transaction executes, then no other transaction is allowed to perform.||Operations of all the transactions are mixed with each other.|
|They are always- Consistent, Recoverable, Cascadeless and Strict||They are not always- Consistent, Recoverable, Cascadeless and Strict.|
Q9. What is the formula used for finding the total number of schedules?
Answer: Total Number of possible schedules (serial + non-serial) in DBMS can be calculated by-
Where n is the Number of transactions T1, T2, T3 …., Tn with N1, N2, N3 …., Nn as the number of operations, respectively.
Q10. What is the total number of serial schedules in a transaction?
Answer: Total Number of serial schedules
= number of different ways of arranging n transactions
Q11. What is the total number of non-serial schedules in a transaction?
Answer: Total Number of non-serial schedules
= Total Number of schedules – Total Number of serial schedules
Q12. What is the problem faced by Non-Serial Schedules in DBMS? How do we identify it?
Answer: Non-Serial schedules may lead to inconsistency of the database. This can be identified via Serializability. It helps us determine which non-serial schedules are correct and hence maintain the consistency of the database.
Q13. What are the different types of Non-Serial Schedules?
Answer: There are two types of non Serial Schedules in DBMS, namely :
- Serializable Schedule
- Non Serializable Schedules
|Serializable Schedule||Non-Serializable Schedule|
|It is a non-serial schedule of ‘n’ transactions equivalent to some serial schedule of ‘n’ transactions.||A non-serial schedule that is not serializable is called a non-serializable schedule. It is not guaranteed to produce the same effect as a serial schedule on any consistent database.|
|It is always Consistent, Recoverable, Cascadeless and Strict.||It may or may not be consistent and recoverable.|
Q14. Explain different types of serializability.
Types of Serializability in Database Management System are –
● Conflict Serializability – A non-serial schedule can be converted into a serial schedule by swapping its non-conflicting operations.
● View Serializability – A view serializable schedule which is equivalent to some serial schedule.
Q15 What are various types of Non-Serializable Schedules?
Answer: There are two types of non Serializable Schedules in DBMS, namely :
- Irrecoverable Schedules
- Recoverable Schedules
|Irrecoverable Schedule||Recoverable Schedule|
|In it, a transaction performs a dirty read operation from an uncommitted transaction, and it commits before the transaction from which it has read the value.||A transaction performs a dirty read operation from an uncommitted transaction, and its commit operation is delayed until the uncommitted transaction either commits or rollbacks.|
This is a recoverable schedule since T1 commits before T2, which makes the value read by T2 correct.
The table above shows a schedule with two transactions, T1 reads and writes A and that value is read and written by T2. T2 commits. But later on, T1 fails. So we have to roll back T1. Since T2 has read the value written by T1, it should also be rolled back. But we have already committed that. So this schedule is irrecoverable.
Q16. What are various types of recoverable schedules in DBMS?
Types of Recoverable Schedules –
● Cascading Schedule – If in a schedule, failure of one transaction causes several other dependent transactions to rollback or abort, then such a schedule is called as a Cascading Schedule or Cascading Rollback or Cascading Abort.
● Cascades Schedule – If in a schedule, a transaction is not allowed to read a data item until the last transaction that has written it is committed or aborted, then such a schedule is called a Cascadeless Schedule.
● Strict Schedule – If in a schedule, a transaction is neither allowed to read nor write a data item until the last transaction that has been written it is committed or aborted, then such a schedule is called a Strict Schedule.
Q17. What is known as “Relational Algebra”?
Answer: Relational Algebra in DBMS is a procedural query language that takes a relation as an input and generates a relation as an output.
Q18. Explain some Basic Operators in Relational Algebra.
|σ(Selection)||Select rows based on given condition|
|∏(Projection)||Project some columns|
|X (Cross Product)||Cross product of relations, returns m*n rows where m and n are the number of rows in R1 and R2 respectively.|
|U (Union)||Return those tuples which are either in R1 or in R2. Max no. of rows returned = m+n and Min no. of rows returned = max(m,n)|
|−(Minus)||R1-R2 returns those tuples which are in R1 but not in R2. Max no. of rows returned = m and Min no. of rows returned = m-n|
|ρ(Rename)||Renaming a relation to another relation.|
Q19. Explain various types of Joins in Relational Algebra
Answer: The different types of Joins are:
|⋈c(Conditional Join)||Makes selection from two or more tables based on some condition (Cross product followed by selection)|
|⋈(Equi Join)||It is a particular case of conditional join where only equality (=) conditions are applied between attributes.|
|⋈(Natural Join)||In natural join, equality conditions on common attributes hold, and duplicate attributes are removed by default. Note: Natural Join is equivalent to cross-product if two relations have no attribute in common, and natural join of a relation R with itself will return R only.|
|⟕(Left Outer Join)||When applying join on two relations R and S, some tuples of R or S do not appear in the result set which does not satisfy the join conditions. But Left Outer Joins gives all tuples of R in the result set. The tuples of R which do not satisfy the join condition will have values as NULL for attributes of S.|
|⟖(Right Outer Join)||When applying join on two relations R and S, some tuples of R or S do not appear in the result set, which does not satisfy the join conditions. But Right Outer Joins gives all tuples of S in the result set. The tuples of S that do not satisfy the join condition will have values as NULL for R attributes.|
|⟗(Full Outer Join)||When applying join on two relations R and S, some tuples of R or S do not appear in the result set, which does not satisfy the join conditions. But Full Outer Joins gives all tuples of S and all tuples of R in the result set. The tuples of S which do not satisfy the join condition will have values as NULL for attributes of R and vice versa.|
Q20. What is SQL?
Answer: SQL is a standard query language for storing, manipulating, and retrieving/accessing data in databases.
Q21. What is the difference between SQL and MYSQL?
SQL is a standard language for retrieving and manipulating structured databases.
MySQL is a relational database management system used to manage SQL databases like SQL Server, Oracle, or IBM DB2.
Q22. What is a Primary Key?
Answer: The Primary Key Constraint in DBMS uniquely identifies each row in a table. It must contain UNIQUE values and should not have any NULL values.
Q23 Write a SQL Command to add the primary key ‘t_id’ to the table ‘teachers.’
ALTER TABLE teachers
ADD PRIMARY KEY(t_id);
Q24. Write a SQL Command to add the primary key constraint ‘pk_a’ for table ‘table_a’ and fields’ col_b, col_c.’
ALTER TABLE table_a
ADD CONSTRAINT pk_a PRIMARY KEY(col_b, col_c);
Q25. What is a query in DBMS?
Answer: It is a request for data or information from a database table or combination of tables.
Q26. What is a table in a relational database? What is composed of?
Answer: A table is an organized collection of data stored in the form of rows and columns.
It is made up of Columns and Rows.
The columns in the table are called fields, and the rows are known as records.
Q27. Foreign key ensures what type of integrity constraint?
Answer: Referential Integrity
Q28. What are the various constraints in SQL?
Answer: In SQL, Constraints are used to specify the rules for entering data in the database table. It can be applied to single or multiple fields in an SQL table.
The constraints are:
- NOT NULL – It restricts NULL value from being inserted into a column.
- CHECK – It verifies that all values in a field satisfy a condition.
- DEFAULT – It automatically assigns a default value if no value has been specified for the field.
- UNIQUE – It ensures unique values to be inserted into the field.
- INDEX – It indexes a field, providing faster retrieval of records.
- PRIMARY KEY – It uniquely identifies each record in a table.
- FOREIGN KEY – It ensures referential integrity for a record in another table.
Q29. Write a SQL Statement to create a UNIQUE Index “my_index” on “my_table” for fields “column_1” and “column_2”.
Answer: Create Unique Index my_index ON my_table (column_1, column2);
Q30. Write a SQL Statement to fetch “names” that are present in either table “accounts” or in table “registry.”
Answer: SELECT names FROM accounts UNION SELECT names FROM registry;
Q31. Write a query to select all from the table “LIMITED” with the alias “Ltd.”
Answer: SELECT *FROM LIMITED AS LTD;
Check out more blogs on Other Top DBMS Interview Questions to read more about this topic in detail. And if you liked this blog, share it with your friends!
In this blog, we enlisted the top 31 advanced DBMS Interview questions that are highly probable to be asked in interviews. In this series of questions, we have covered more basic level questions in DBMS Interview Questions Part 1 and intermediate level in DBMS Interview Questions Part 2. Do check them out in case you missed them. If you like this blog, do share it with your friends!
By: Raksha Jain