Home

Foreign key and Referential integrity in RDBMS

Foreign key and Referential integrity  in RDBMS

Foreign Key

     A key attribute in one table can be a non key attribute in another table .

     Thus foreign key is a field that points to the primary key of another table.

     The purpose of foreign key is to ensure referential integrity.

 Referential Integrity 

       It ensure consistency between tables by defining foreign keys.

Components Ensuring ACID Transaction properties in a Database

Components Ensuring ACID  Transaction properties in a Database

  • Query Processor
  • Transaction Manager
  • Recovery Manager
The Query Processor handles DML (Data Manipulation) operations.Transaction Manager handles Isolation property of transaction.Recovery Manager handles Atomicity, and Durability.
 

No components of DBMS ensure consistency property of transaction.It is the responsibility of the programmer to ensure consistency through program code

Semantic Data Modeling in Databse Design

Semantic Data Modeling  in Databse Design

In Semantic Data Modeling,the database designer uses his /her knowledge about the business structure to create a set of relational tables.

ACID Properties of Transcation in Database

ACID Properties of Transcation in Database

Transaction

The term transaction refers to a collection of operation that form a single logical unit of work. A transaction T is a logical unit of database processing that includes one or more database access operation.

Transaction Properties: ACID Properties

  • Atomicity
  • Consistency
  • Isolation
  • Durability

 

Atomicity
A transaction must be atomic. Ie it ensure that either all operations of the transactions are reflected properly in the database or none should

Consistency
If the database is in a consistent state before the execution of the transaction, the database remains consistent after the execution of the transaction.

Example:   Transaction T1 transfers $100 from Account A to Account B. Both Account A and Account B contains $500 each before the transaction.

Transaction T1
                            Read (A)
                            A=A-100
                            Write (A)
                            Read (B)
                            B=B+10

Consistency Constraint

Before Transaction execution Sum = A + B
                                  Sum = 500 + 500
                                  Sum = 1000

After Transaction execution Sum = A + B
                                       Sum = 400 + 600
                                       Sum = 1000
Before the execution of transaction and after the execution of transaction SUM must be equal.


Isolation
When multiple transactions are executing concurrently, then each transaction is unaware of other transactions executing concurrently in the system. Ie the execution of one transaction must not interfere with another.

Durability
The successful execution of a transaction despite the probability of system failure is called durability. Changes applied to a database by a committed transaction must be made permanent even if the system fails


Components Ensuring Transaction properties

  • Query Processor
  • Transaction Manager
  • Recovery Manager
The Query Processor handles DML (Data Manipulation) operations.Transaction Manager handles Isolation property of transaction.Recovery Manager handles Atomicity, and Durability.
 

No components of DBMS ensure consistency property of transaction.It is the responsibility of the programmer to ensure consistency through program code