Database Reference
In-Depth Information
You can use SQL to determine whether this is true. The following query will return any values
of the foreign key that violate the constraint:
/* *** SQL-Query-CH04-02 *** */
SELECT Buyer
FROM
SKU_DATA
WHERE
Buyer NOT IN
(SELECT Buyer
FROM
SKU_DATA, BUYER
WHERE
SKU_DATA.Buyer = BUYER.Buyer);
The subquery finds all values of Buyer for which there is a match between SKU_DATA.Buyer
and BUYER.Buyer. If there is any value of Buyer that is not in this subquery, then that value will
be displayed in the results of the main query. All such values violate the referential integrity
constraint. In the following actual results of the query on the data in our dataset as shown in
Figure 3-21 (where SKU_DATA appears with the table name SKU_DATA_2), we get an
empty
set
—there are
no
values returned in response to the query—which means that there are
no
foreign key values that violate the referential integrity constraint.
After you have assessed the input tables, your next steps depend on whether you are creat-
ing an updatable database or a read-only database. We will consider updatable databases first.
If you are constructing an updatable database, then you need to be concerned about modi-
fication anomalies and inconsistent data. Consequently, you must carefully consider nor-
malization principles. Before we begin, let's first review the advantages and disadvantages of
normalization.
Advantages and Disadvantages of Normalization
Figure 4-2 summarizes the advantages and disadvantages of normalization. On the positive
side, normalization eliminates modification anomalies and reduces data duplication. Reduced
data duplication eliminates the possibility of data integrity problems due to inconsistent data
values. It also saves file space.
By The WAy
Why do we say
reduce
data duplication rather than
eliminate
data duplica-
tion? The answer is that we cannot eliminate all duplicated data because
we must duplicate data in foreign keys. We cannot eliminate Buyer, for example, from
the SKU_DATA table, because we would then not be able to relate BUYER and SKU_
DATA rows. Values of Buyer are thus duplicated in the BUYER and SKU_DATA tables.
This observation leads to a second question: If we only
reduce
data duplication,
how can we claim to
eliminate
inconsistent data values? Data duplication in foreign
keys will not cause inconsistencies because referential integrity constraints prohibit
them. As long as we enforce such constraints, the duplicate foreign key values will
cause no inconsistencies.
On the negative side, normalization requires application programmers to write more
complex SQL. To recover the original data, they must write subqueries and joins to connect
data stored in separate tables. Also, with normalized data, the DBMS must read two or more
tables, and this can mean slower application processing.
Search WWH ::
Custom Search