Friday, Oct 20th

Last update12:59:40 PM GMT

Database Integrity

Write e-mail

What is Integrity?

argaiv1874

In simple terms by integrity we mean that all data stored in a database should be error-free, accurate and consistent.

There are 4 areas in data integrity

Entity Integrity

Domain Integrity

Referential Integrity

User Defined Integrity

These parameters might not be of great relevance for companies looking for candidates, but

1) companies which take written tests based on databases do ask about them, and

2) an understanding of this concept helps you appreciate data security in databases which comes later on in our course.

So, let me briefly take you through all these parameters. 

Entity Integrity

No duplicate rows.

This means that in any given table, every row should be unique. A properly normalized database should not have duplicate rows as when one of such rows gets updated and a duplicate one doesn't, it leads to inconsistencies in the database.

Entity integrity is generally enforced through the use of a primary key, which is always unique for a row. 

Exceptions: You have a table of name and address information and 2 users enter information for same individual, but wrongly spell the names. In this case there would be a duplicate entry (row) in the table. In such cases, a soft search of the tables beforehand is a safe step before entering information. One such tool is a "soft" search algorithm, called Soundex search*.

Domain Integrity 

The values in any given column should fall within an accepted range.

It is important to define not only data-types for the columns, but also valid range of values which the column can accept. For frequently changing values, lookup tables can be used by defining a referential integrity constraint (which we discuss next) between the column and the lookup table.

Database engines also provide the ability to check your range against values entered in columns in a different table as well.

Referential Integrity 

Foreign key values point to valid rows in the referenced table.

A foreign key is a value in one table that references to, or points to, a related row in another table. For e.g. in the article on normalization, SCStudentID in the StudentSubjects table is a foreign key pointing to StudentID in the Students table.  It is possible to have NULL foreign key values but they should never be invalid. If a foreign key is entered, it must reference a valid row in the related table. 

Database engines also allow for cascading updation and deletions whereby any changes to primary keys are automatically reflected at all instances of foreign keys in other tables; i.e. if a primary key name or value changes, you don't have to update every foreign key that points to it, the engine does that for you by itself.

User-defined Integrity 

The data complies with applicable business rules, i.e. integrity constraints which are custom made according to organization's needs.

Database engines provide the ability to organizations to enforce tailor made integrity constraints in their databases according to their requirements.

A textbook example of a business rule which must be enforced is a funds transfer in a banking application. If a customer wishes to transfer funds from a savings account to a checking account, you must deduct the withdrawl from the savings account and add the deposit to the checking account. If you successfully record the withdrawl without recording the deposit, the customer will have "lost" the amount (and not be pleased). If you record the deposit without the withdrawl, the customer will get a "free" deposit (and the bank will be unhappy). To enforce this type of rule, you'll need to use a Transaction in your application code.

-------------------------------------------------

*Soundex is an algorithm that will produce a code which describes a phonetic equivalent of a word. Soundex codes are widely used with name searches because they will detect matches even if names are misspelled. Microsoft SQL Server comes with a built-in function to determine the Soundex code for a word. You can also build your own if the database engine you are using does not provide one. The algorith is publicly available almost everywhere. A search of the Web for the keyword "Soundex" should produce the algorith within the first few matches.

References

Share this post



Add comment

Please refrain from using slang or abusive language in the comments.
To avoid waiting for your comment to be published after being reviewed, please log in as a registered user.


Security code
Refresh

Web Hosting