(Continued from previous part...)

RDBMS FUNDAMENTALS

VI. ENSURING INTEGRITY OF DATA :

The concept of Data Integrity :
v Data Integrity : Data Integrity refers to the correctness and completeness of data in a database.

Integrity Constraints : Integrity constraints allows only correct changes to be made to a database. There are two types of integrity constraints - entity integrity and referential integrity.

Entity Integrity : Entity Integrity ensures that for each row in a table, the value of the primary key is unique and is not null.

Referential Integrity : Referential Integrity ensures that for each row in a table, the value of the foreign key is present in the reference table.

Grouping commands related to a task :
Transaction Processing : A transaction is a sequence of one or more SQL commands that together form a logical task. Transaction Processing ensures that when the RDBMS is making changes related to a single task, either all changes are made as a unit or no changes are made.

Commit : Commit is an SQL command that indicates the successful end of a transaction. After an RDBMS executes this command all the changes are made to the database.

Rollback : Rollback is an SQL command that cancels a transaction before it is complete. The rollback command removes the changes of all previous commands in a transaction from the buffer.

Controlling Concurrent Data Access :
Concurrency Control : All RDBMS must ensure that the transactions of concurrent users do not interfere with each other. If it does not handle the transactions properly, the problems of lost update, uncommitted data, or inconsistent data might occur.

Lost Update Problem : Lost update problem occurs when an update made by a transaction is lost due to an update made by another transaction.

Uncommitted Data Problem : Uncommitted data problem occurs when a transaction accesses data that has been updated by a previous transaction that has not yet ended.

Inconsistent Data Problem : Inconsistent data problem occurs when a transaction accesses data from the database and simultaneously another transaction is changing that data.

Locking : Locking is a facility provided by an RDBMS to ensure that a transaction does not interfere with any other transaction. Locking prevents the problem of lost update, uncommitted data and inconsistent data. An RDBMS provided two types of locks for locking a part of the database - shared locks and exclusive locks.

Shared Locks : If a transaction is only reading data from a database, it gets a shared lock on that part of the database. Other transactions can also get a shared lock on that part of the database to read data. However, they cannot change the data.

Exclusive Locks : If a transaction is updating data in a database, it gets an exclusive lock on that part of the database. No other transaction can read or change this data.