Data validity and accuracy according to database integrity

Question

What does it mean that data is valid and accurate according to database integrity?

Answer 1

At the theoretical level, a domain can be represented by a table of values. (At the theoretical level, because the number of values for, say, the domain of non-negative integers is infinite.) Valid values come from that domain.

In a SQL database, you establish valid values using some combination of appropriate data types and constraints. For example, you might declare "employee_id" to be an integer, and restrict the range of integers with a) a foreign key constraint to a table of 'n' integers or b) a check constraint.

Accurate means that, of all the possible valid values, a user has chosen values that correspond to the entity's state or description in the real world. In this sense, user can be either a human or a program.

For example, let's say a book in a library can have any of these dispositions: "checked out", "recently returned" (not yet in the stacks), "in stacks", "being repaired", and "lost". If I check out a book and the database stores it's status as "checked out", then the database is accurate with respect to the disposition of that copy. (At my library, checkouts and returns are done by computers, not by people.)

So data integrity requires the cooperation of the database (to allow only valid values) and users (to enter the right values from all the possible ones).

Answer 2

Database integrity generally refers to the following conditions being satisfied:

• All constraints on a table are satisfied (including unique constraints, foreign key constraints, primary key constraints, etc....)
• All check constraints are satisfied (for example, in Oracle, I can have a check constraint that requires that a value be nonzero, but not otherwise unique.)
• All unique indexes are satisfied
• All child rows in child tables have parent rows in parent tables (foreign keys)
• All data types are satisfied

Note that there's not really anything here that speaks to if the data is valid and/or accurate according to your business logic. This logic is anything you put into triggers, procedures, validation routines, etc., that you build yourself to try and make sure that everything going into the database is good. (It should be noted that Oracle's check constraints could be considered a form of business logic that is enforced like a unique or primary key constraint).

The database will happily reject data that doesn't fit into the above criteria, assuming they are defined, but will also happily accept invalid or inaccurate data that isn't otherwise in the above criteria. If I have a field that contains people's names, the database can't make the determination if "John Smith" or "Jonh Smiht" is accurate (notice the typo). So while the database will say everything's good, you know from a visual inspection that it isn't. (This example, while trivial to do, is hard to do anything about. After all, how do you know his name isn't really spelled this way? I use it only as an example of any data coming into the system. A field expecting percentages could easily have 5% as 50% -- how can the database tell which is valid? It can't without you writing lots of complicated business logic, and even then -- at some point, how do you know it isn't 50%? Or that it isn't 5%? Or that it was supposed to be 25%, and the typist got really confused!)

Use the database integrity mechanisms to your advantage, but don't assume that because there are no integrity errors that your data is good. It can still, easily, be bad and misleading. Further, don't assume that your business logic ensures good data either. Yes, you can prevent obvious errors (like, say, a field that shouldn't have values out of the range of 0 to 100), but once ranges are satisfied, at some point, you have to trust the user who is giving you the data. And at some point, the user will give it to you incorrectly. Poof, bad data, with perfectly valid database integrity.