Saturday, 3 December 2011

Data Integrity in Database

Data Integrity in Database
It is very important for the data in a database to be accurate, consistent, and reliable. This implies that it is very crucial to enforce data integrity.
Data integrity ensures the consistency and correctness of data stored in a database. It is broadly classified into the following four categories:
Entity integrity
Domain integrity
Referential integrity
User-defined integrity

Entity Integrity
 Entity integrity ensures that each row can be uniquely identified by an attribute called the primary key. The primary key cannot be NULL.
For example, there might be two students with the same name ‘John’. They can be identified using the unique code assigned to them.

Domain Integrity
Domain integrity ensures that only a valid range of values is allowed to be stored in a column. It can be enforced by restricting the type of data, the range of values and the format of the data.
For example, let’s assume a table called BranchOffices that stores the address of various offices of a company. Let’s also assume it has a column city that stores the name of cities where the branch offices are located. Assume that offices are located in  ‘Boston’, ’California’ ,’Dallas’, ’New York’, ’Washington’. By enforcing domain integrity we ensure that only valid values can be entered in the city column of the BranchOffice table.

Referential Integrity
Referential Integrity ensures that the values of the foreign key match with the values of the corresponding primary key.
For example, if a toy has been ordered and an entry is to be made in the OrderDetail table, the the code should exists in the Toys tables. This is to ensure that an order is being placed for the available toys.

User-defined Integrity

User-defined Integrity refers to a set of rules specified by a user, which do not belong to the entity, domain and referential integrity categories.
For example, you do not want a candidate who is less than 18 years to apply for a post.

No comments:

Post a Comment