Interview Questions on Database Transactions
9. What is a database transaction?
Database transaction takes database from one consistent state to another. At the end of the transaction the system must be in the prior state if the transaction fails or the status of the system should reflect the successful completion if the transaction goes through.
1. Atomicity A transaction consists of many steps. When all the steps in a transaction get completed, it will get reflected in DB or if any step fails, all the transactions are rolled back. 2. Consistency The database will move from one consistent state to another, if the transaction succeeds and remain in the original state, if the transaction fails. 3. Isolation Every transaction should operate as if it is the only transaction in the system. 4. Durability Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis.
11. What is a Database Lock ?
Database lock tells a transaction, if the data item in questions is currently being used by other transactions.
12. What are the type of locks ?
1. Shared Lock When a shared lock is applied on data item, other transactions can only read the item, but can't write into it. 2. Exclusive Lock When an exclusive lock is applied on data item, other transactions can't read or write into the data item.
Database Normalization Interview Questions
13. What are the different type of normalization?
In database design, we start with one single table, with all possible columns. A lot of redundant data would be present since it’s a single table. The process of removing the redundant data, by splitting up the table in a well defined fashion is called normalization. 1. First Normal Form (1NF) A relation is said to be in first normal form if and only if all underlying domains contain atomic values only. After 1NF, we can still have redundant data. 2. Second Normal Form (2NF) A relation is said to be in 2NF if and only if it is in 1NF and every non key attribute is fully dependent on the primary key. After 2NF, we can still have redundant data. 3. Third Normal Form (3NF) A relation is said to be in 3NF, if and only if it is in 2NF and every non key attribute is non-transitively dependent on the primary key.
Database Keys and Constraints SQL Interview Questions
14. What is a primary key?
A primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. If a row is deleted from the table, its primary key may not be assigned to any new rows in the future. To define a field as primary key, following conditions had to be met :
1. No two rows can have the same primary key value. 2. Every row must have a primary key value. 3. The primary key field cannot be null. 4. Value in a primary key column can never be modified or updated, if any foreign key refers to that primary key.