Database Design Using Normalization - Database Processing: Fundamentals, Design, and Implementation - page 171

shown in Figure 2-5, not the three-column version discussed in this chapter on page 161, even
though the query would actually run correctly on either version of the table), we can use the
SQL query:

/* *** SQL-Query-CH04-05 *** */
SELECT SKU_Description, COUNT(*) as SKU_Description_Count
FROM SKU_DATA
GROUP BY SKU_Description;

The result of this query for the SKU_DATA values we have been using is:

In this case, there are no inconsistent values, but if there were, they would stand out.
If the list resulting from the select is too long, groups can be selected that have just one or
two elements using HAVING. Neither check is foolproof. Sometimes, you just have to read
the data.
When working with such data, it is important to develop an error reporting and tracking
system to ensure that inconsistencies that users do find are recorded and fixed. Users grow
exceedingly impatient with data errors that persist after they have been reported.

Missing Values
Missing values are a third problem that occurs when creating databases from existing data.
A missing value, or null value , is a value that has never been provided. It is not the same as
a blank value because a blank value is a value that is known to be blank. A null value is not
known to be anything.
The problem with null values is ambiguity. A null value can indicate one of three condi-
tions: The value is inappropriate; the value is appropriate but unknown; or the value is appro-
priate and known, but no one has entered it into the database. Unfortunately, we cannot tell
from a null value which of these conditions is true.
Consider, for example, a null value for the column DateOfLastChildbirth in a PATIENT
table. If a row represents a male patient, then the null occurs because the value is inappropri-
ate; a male cannot give birth. Alternatively, if the patient is a female, but the patient has never
been asked for the data, then the value is appropriate, but unknown. Finally, the null value
could also mean that a date value is appropriate and known, but no one has recorded it into
the database.
You can use the SQL term IS NULL to check for null values. For example, to find the num-
ber of null values of Quantity in the ORDER_ITEM table, you can code:

/* *** SQL-Query-CH04-06 *** */
SELECT COUNT (*) as QuantityNullCount

FROM ORDER_ITEM

WHERE Quantity IS NULL;