Transaction Concepts, ACID, Normalization

Question 1

What are ACID transactions in a database?

Answer 1

• A Transaction is a logical unit of work in which all steps must be performed or none.
• ACID describes the properties of a Transaction. In the context of transaction processing, the acronym ACID refers to atomicity, consistency, isolation, and durability.
• After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure.

Question 2

What is Normalization?

Answer 2

A process of organizing the fields and tables of a relational database to minimize redundancy and dependency.

1. Supports ACID Properties
2. Reduces NULL values
3. Enables efficient indexing
4. Eliminates redundant data

Question 3

What are the first three Normal Forms?

Answer 3

1NF - Removes duplicated attributes. Attribute data should be atomic and attribute should be the same kind.
2NF - Should be in 1NF and each non-key is fully dependent on the primary key.
3NF - Should be in 2NF and all the non-key attributes which are not dependent on the primary key should be removed. All the attributes which are dependent on the other non-key attributes should also be removed.

1-3NF is called Normalization and is done in OLTP.

Question 4

What is Denormalization and when would it be used?

Answer 4

Denormalization is the reverse process of Normalization. It can increase the query performance by reducing the number of joins. Denormalization can increase redundancy and is used for OLAP applications.

Question 5

What is Atomicity?

Answer 5

Atomicity means the transaction finishes completely, or it will not occur at all.

Question 6

What is Consistency?

Answer 6

Consistency means the transaction will repeat in a predictable way each time it is performed.

Question 7

What is Isolation?

Answer 7

The data in the transactions are independent of each other. The success of one transaction doesn’t depend on the success of another.

Question 8

What is Durability?

Answer 8

Durability guarantees the database will keep track of the pending changes so that the server will be able to recover if an error occurs.

Question 9

What does @@TRANCOUNT do?

Answer 9

Returns the number of active transactions for the current user.