Data integrity in DBMS

Data integrity refers to the overall accuracy, completeness, and reliability of data.

Data integrity is the validity of the data being entered in the database.

Student_ID Student_Name Fee_Paid Class
1 Asad 1000 BSCS
2 Akram 1000 BSCS
3 Arshad xyz BSCS

Consider this example of data integrity. While the fee of all students is an integer, one student has a fee in alphanumeric characters. Since the accounts table only accepts integers (INT), the value xyz will not be accepted by our database. This example shows domain-level data integrity.

data integrity examples
data integrity examples

Types of Data Integrity

Physical Integrity

Physical integrity is the process of ensuring  the wholeness , correctness, and accuracy of data when data is stored and retrieved.

How to ensure Physical Integrity?

We can ensure Physical Integrity by accurate data storage and retrieval and by preventing the followings;

  1. Preventing  power outages
  2. Preventing storage erosion
  3. Preventing hackers targeting database functions
  4. Preventing natural disasters etc.
  5. Preventing Human Errors
  6. Preventing Bugs and Viruses
  7. Preventing Errors during Transfer: occurs when transferring data from one table to another table.
  8. Prevention from Compromised/faulty Hardware
  9. Compromised/faulty hardware can result in malfunctions and failures. Some possible results of Compromised/faulty Hardware are mentioned below;
    • Data can be rendered incompletely
    • Data can be rendered incorrectly
    • Data access removed or limited,
    • Data can become hard for users to work with data etc.

Validate Input

When end user enters the data, then data must be validate and verified to ensure the accuracy in data entry.

Remove Duplicate Data

Back Up Data:

Prevent the data from being permanently loss.

Access Controls:

Each database user just have permission to access the necessary desired parts of database. Try to give the different rights to each user to ensure the data integrity.


Logical integrity in DBMS

Logical integrity refers to the accuracy and consistency of the data itself. Logical integrity ensures that the data makes sense in its context. Types of logical integrity include:

Types of logical integrity

  1. Entity integrity
  2. Domain integrity

Entity integrity:

Entity integrity refers to two basic rules mentioned below;

  • Table must have a primary key. The primary key is a unique value that recognizes each row in the table
  • Primary key value can’t be null.

What is Domain Level Integrity

Domain Integrity rules govern the values. In a database system, the domain integrity is defined by:

  1. The datatype and the length
  2. The NULL value acceptance
  3. The allowable values, through techniques like constraints or rules
  4. The default value

Examples of  Domain Level Integrity

Some examples of  Domain Level Integrity are mentioned below;

  • Data Type– For example integer, and characters etc.
  • Date Format– For example dd/mm/yy or mm/dd/yyyy or yy/mm/dd.
  • Null support– Indicates whether the attribute can have null values.
  • Constraints– Are special restrictions on allowable values. For example, the Passing marks  for a student must always be greater than 50%.
  • Length– Represents length of characters in a value.
  • Range– The range specifies the lower and upper boundaries of the values the attribute may legally have.

What is a domain in database?

A domain is a set of values for an attribute that can be stored in attribute of a database table.

Example Of Domain In Database

For example,

A domain of week days can accept Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and sunday as possible values.

A domain of integers can accept whole numbers that are negative, positive and zero.

Referential Integrity constraint

A referential integrity constraint are famous as foreign key constraint. The value of  foreign key values are derived from the Primary key of another table.

Master or Referenced Table Child or Referencing Table
Nominated for primary key for relationship Nominated for foreign key for relationship
Table from which the values are derived is called  Referenced Table Table in which values are inserted according to master table are called Referencing Table
Referenced Table is also called Master table.

 

Referencing Table is also called Child table.
table containing the Primary key/candidate key is called the referenced table table containing the foreign key is called the child table
Syntax for Referenced table CREATE TABLE Students (RollNo int PRIMARY KEY, Name varchar(40) , Course varchar(25) );

 

Syntax for Referencing table CREATE TABLE Subjects (RollNo int references Students, SubjectCode int, SubName varchar(25) );

 

Types of referential integrity constraints

There are two types of referential integrity constraints:

Insert Constraint: We can’t inert value in CHILD Table if the value is not stored in MASTER Table

Delete Constraint: We can’t delete value from MASTER Table if the value is existing in CHILD Table

Suppose you wanted to in

Insert constraint

Referential Integrity Constraints Examples
Referential Integrity Constraints Examples

Suppose we wanted to insert RollNo = 09 in SUBJECTS Table(child table) by the following SQL command, Insert into SUBJECTS values(9);

In this case error occurs as “Foreign key Constraint is Violated“.

This will not be entertained by SQL due to Insertion Constraint.

Note: As we can’t insert value in a child table[SUBJECTS Table] if the value is not exist in the master table [Students table], since RollNo = 9 is not present in the master table [Students table], so it is not permitted to enter RollNo = 9 in child table[SUBJECTS Table].

Delete constraint

Suppose we want to delete RollNo = 3 from STUDENTS Table ) by the following SQL command, Delete from STUDENT where RollNo = 3;

In this case error occurs as “Foreign key Constraint is Violated“.

This will not be entertained by SQL due to Deletion Constraint.

Note:  We can’t delete the value from the master table [Students table] if the value exist in the child table [Subjects], since RollNo = 3 is present in the child table[Subjects], hence it will not be allowed to delete RollNo = 3 from the master table [Students table].

Solution to this problem

lets if somehow we managed to delete RollNo = 9, then RollNo = 9 will be available in child table which will ultimately violate insertion constraint. )

 

 User-defined integrity: Users can define their own processes for system integrity. User-defined integrity can help strengthen other data integrity measures.

Foreign Key Constraints of Cascade strict and SET Null on delete and Update