DBMS Interview Questions and Answers
Question - 41 : - What is 2NF?
Answer - 41 : -
2NF is the Second Normal Form. A table is said to be 2NF if it follows the following conditions:
- The table is in 1NF, i.e., firstly it is necessary that the table should follow the rules of 1NF.
- Every non-prime attribute is fully functionally dependent on the primary key, i.e., every non-key attribute should be dependent on the primary key in such a way that if any key element is deleted, then even the non_key element will still be saved in the database.
Question - 42 : - What is 3NF?
Answer - 42 : -
3NF stands for Third Normal Form. A database is called in 3NF if it satisfies the following conditions:
- It is in second normal form.
- There is no transitive functional dependency.
For example: X->Z
Where:
X->Y
Y does not -> X
Y->Z so, X->Z
Question - 43 : - What is BCNF?
Answer - 43 : -
BCMF stands for Boyce-Codd Normal Form. It is an advanced version of 3NF, so it is also referred to as 3.5NF. BCNF is stricter than 3NF.
A table complies with BCNF if it satisfies the following conditions:
- It is in 3NF.
- For every functional dependency X->Y, X should be the super key of the table. It merely means that X cannot be a non-prime attribute if Y is a prime attribute.
Question - 44 : - Explain ACID properties
Answer - 44 : -
ACID properties are some basic rules, which has to be satisfied by every transaction to preserve the integrity. These properties and rules are:
- ATOMICITY: Atomicity is more generally known as ?all or nothing rule.' Which implies all are considered as one unit, and they either run to completion or not executed at all.
- CONSISTENCY: This property refers to the uniformity of the data. Consistency implies that the database is consistent before and after the transaction.
- ISOLATION: This property states that the number of the transaction can be executed concurrently without leading to the inconsistency of the database state.
- DURABILITY: This property ensures that once the transaction is committed it will be stored in the non-volatile memory and system crash can also not affect it anymore.
Question - 45 : - What is stored procedure?
Answer - 45 : -
A stored procedure is a group of SQL statements that have been created and stored in the database. The stored procedure increases the reusability as here the code or the procedure is stored into the system and used again and again that makes the work easy, takes less time in processing and decreases the complexity of the system. So, if you have a code which you need to use again and again then save that code and call that code whenever it is required.
Question - 46 : - What is the difference between a DELETE command and TRUNCATE command?
Answer - 46 : -
DELETE command: DELETE command is used to delete rows from a table based on the condition that we provide in a WHERE clause.
- DELETE command delete only those rows which are specified with the WHERE clause.
- DELETE command can be rolled back.
- DELETE command maintain a log, that's why it is slow.
- DELETE use row lock while performing DELETE function.
TRUNCATE command: TRUNCATE command is used to remove all rows (complete data) from a table. It is similar to the DELETE command with no WHERE clause.
- The TRUNCATE command removes all the rows from the table.
- The TRUNCATE command cannot be rolled back.
- The TRUNCATE command doesn't maintain a log. That's why it is fast.
- TRUNCATE use table log while performing the TRUNCATE function.
Question - 47 : - How do you communicate with an RDBMS?
Answer - 47 : -
You have to use Structured Query Language (SQL) to communicate with the RDBMS. Using queries of SQL, we can give the input to the database and then after processing of the queries database will provide us the required output.
Question - 48 : - What is the difference between a shared lock and exclusive lock?
Answer - 48 : -
Shared lock: Shared lock is required for reading a data item. In the shared lock, many transactions may hold a lock on the same data item. When more than one transaction is allowed to read the data items then that is known as the shared lock.
Exclusive lock: When any transaction is about to perform the write operation, then the lock on the data item is an exclusive lock. Because, if we allow more than one transaction then that will lead to the inconsistency in the database.
Question - 49 : - Describe the types of keys?
Answer - 49 : -
There are following types of keys:
Primary key: The Primary key is an attribute in a table that can uniquely identify each record in a table. It is compulsory for every table.
Candidate key: The Candidate key is an attribute or set of an attribute which can uniquely identify a tuple. The Primary key can be selected from these attributes.
Super key: The Super key is a set of attributes which can uniquely identify a tuple. Super key is a superset of the candidate key.
Foreign key: The Foreign key is a primary key from one table, which has a relationship with another table. It acts as a cross-reference between tables.
Question - 50 : - What is concurrency control?
Answer - 50 : -
This is a process of managing simultaneous operations in a database so that database integrity is not compromised. The following are the two approaches involved in concurrency control:
- Optimistic approach – Involves versioning
- Pessimistic approach – Involves locking