DBMS Concepts

DBMS Tutorial Components of DBMS. Applications of DBMS The difference between file system and DBMS. Types of DBMS DBMS Architecture DBMS Schema Three Schema Architecture. DBMS Languages.

DBMS ER Model

ER model: Entity Relationship Diagram (ERD) Components of ER Model. DBMS Generalization, Specialization and Aggregation.

DBMS Relational Model

Codd’s rule of DBMS Relational DBMS concepts Relational Integrity Constraints DBMS keys Convert ER model into Relational model Difference between DBMS and RDBMS Relational Algebra DBMS Joins

DBMS Normalization

Functional Dependency Inference Rules Multivalued Dependency Normalization in DBMS: 1NF, 2NF, 3NF, BCNF and 4NF

DBMS Transaction

What is Transaction? States of transaction ACID Properties in DBMS Concurrent execution and its problems DBMS schedule DBMS Serializability Conflict Serializability View Serializability Deadlock in DBMS Concurrency control Protocols

Difference

Difference between DFD and ERD

Misc

Advantages of DBMS Disadvantages of DBMS Data Models in DBMS Relational Algebra in DBMS Cardinality in DBMS Entity in DBMS Attributes in DBMS Data Independence in DBMS Primary Key in DBMS Foreign Key in DBMS Candidate Key in DBMS Super Key in DBMS Aggregation in DBMS Hashing in DBMS Generalization in DBMS Specialization in DBMS View in DBMS File Organization in DBMS What Is A Cloud Database What Is A Database Levels Of Locking In DBMS What is RDBMS Fragmentation in Distributed DBMS What is Advanced Database Management System Data Abstraction in DBMS Checkpoint In DBMS B Tree in DBMS BCNF in DBMS Advantages of Threaded Binary Tree in DBMS Advantages of Database Management System in DBMS Enforcing Integrity Constraints in DBMS B-Tree Insertion in DBMS B+ Tree in DBMS Advantages of B-Tree in DBMS Types of Data Abstraction in DBMS Levels of Abstraction in DBMS 3- Tier Architecture in DBMS Anomalies in Database Management System Atomicity in Database Management System Characteristics of DBMS DBMS Examples Difference between Relational and Non-Relational Databases Domain Constraints in DBMS Entity and Entity set in DBMS ER Diagram for Banking System in DBMS ER Diagram for Company Database in DBMS ER Diagram for School Management System in DBMS ER Diagram for Student Management System in DBMS ER Diagram for University Database in DBMS ER Diagram of Company Database in DBMS Er Diagram Symbols and Notations in DBMS How to draw ER-Diagram in DBMS Integrity Constraints in DBMS Red-Black Tree Deletion in DBMS Red-Black Tree Properties in DBMS Red-Black Tree Visualization in DBMS Redundancy in Database Management System Secondary Key in DBMS Structure of DBMS 2-Tier Architecture in DBMS Advantages and Disadvantages of Binary Search Tree Closure of Functional Dependency in DBMS Consistency in Database Management System Durability in Database Management System ER Diagram for Bank Management System in DBMS ER Diagram for College Management System in DBMS ER Diagram for Hotel Management System in DBMS ER Diagram for Online Shopping ER Diagram for Railway Reservation System ER Diagram for Student Management System in DBMS Isolation in DBMS Lossless Join and Dependency Preserving Decomposition in DBMS Non-Key Attributes in DBMS Data Security Requirements in DBMS DBMS functions and Components What is Homogeneous Database? DBMS Functions and Components Advantages and Disadvantages of Distributed Database

What is Transaction in DBMS

A transaction is a collection of logically related operations which reads and possibly updates the various data items in the database. Usually, a transaction is initiated by a user program written in high-level DML language (SQL), or programming language, with an embedded database, accesses in JDBC or ODBC.

In other words, we can say that any logical calculation which is done in a consistent mode in the database is called a transaction. Every database before and after the transaction must be in a consistent state.

A transaction has the following properties to ensure the integrity of data:

  1. Atomicity
  2. Consistency
  3. Isolation
  4. Durability

Simple Transaction Example

Suppose a bank employee transfers money from X’s account to Y’s account. As the amount of 100Rs gets transferred from the X account to Y’s account, the following series of operations are performed in the background of the screen.

Simply we can say, the transaction involves many following operations such as:

  1. Open the account of X
  2. Read the old balance
  3. Deduct the certain amount 100Rs from X account
  4. saving new balance to X account
  5. Open the account of Y
  6. Read the old balance
  7. Add the certain amount 100Rs to Y’s account
  8. Save new balance to Y account

X’s Account

Open_Account(X)
Old_Balance = X.balance
New_Balance = Old_Balance - 100
X.balance = New_Balance
Close_Account(X) 

Y’s Account

Open_Account(Y)
Old_Balance = Y.balance
New_Balance = Old_Balance + 100
Y.balance = New_Balance
Close_Account(Y) 

Operations of transaction

Following are the two main operations in a transaction:

  1. Read operation
  2. Write operation

Read Operation: This operation transfers the data item from the database and then stores it in a buffer in main memory.

Write Operation: This operation writes the updated data value back to the database from the buffer.

For example: Let Ti be a transaction that transfer Rs50 from X account to Y account. This transaction can be defined as:

T i: read(X); …….(1)
X := X?50; …….(2)
write(X); …….(3)
read(Y); …….(4)
Y := Y + 50; …….(5)
write(Y).…….(6)

Assume that the value of both X and Y before starting of the transaction Ti is 100.

  • The first operation read the value of X(100) from the database and stores it in a buffer. The second operation will decrease X value by 50. So buffer will contain 50. The third operation will write the value of the buffer to the database. So the final value of X will be 50.
  • The fourth operation read the value of Y(100) from the database and stores it in a buffer.The fifth operation will add X value by 50. So buffer will contain 150. The sixth operation will write the value of buffer to the database. So the final value of Y will be 150.

But, it may be possible that due to hardware or software failure that transaction may fail before completing all the operations in the set.
For example: If in the above transaction, the transaction fails after executing operation 5, then Y's value will remain 100 in the database, which is not acceptable by the bank.

To overcome this problem, we have two important operations Commit and Rollback.
Commit Operation: This operation is used to save the work done permanently in the database.
Rollback Operation: This operation is used to undo the work done.