The Relational Model and Normalization - Database Processing: Fundamentals, Design, and Implementation - page 131

Notice that ORDER_ITEM.SKU is both a foreign key and also part of the primary key of
ORDER_ITEM. This condition sometimes occurs, but it is not required. In the example above,
EMPLOYEE.DepartmentName is a foreign key, but it is not part of the EMPLOYEE primary
key. You will see some uses for foreign keys later in this chapter and the next, and you will
study them at length in Chapter 6.
In most cases, we need to ensure that the values of a foreign key match a valid value of a
primary key. For the SKU_DATA and ORDER_ITEM tables, we need to ensure that all of the
values of ORDER_ITEM.SKU match a value of SKU_DATA.SKU. To accomplish this, we create
a referential integrity constraint , which is a statement that limits the values of the foreign
key. In this case, we create the constraint:

SKU in ORDER_ITEM must exist in SKU in SKU_DATA

This constraint stipulates that every value of SKU in ORDER_ITEM must match a value of SKU
in SKU_DATA.

By The WAy While we have defined a referential integrity constraint to require a cor-
responding primary key value in the linked table, the technical definition of
the referential integrity constraint allows for one other option-that the foreign key cell in
the table is empty and does not have a value. 2 If a cell in a table does not have a value,
it is said to have a null value (we will discuss null values in Chapter 4).
It is difficult to imagine a foreign key having null values in a real database when the
referential integrity constraint is actually in use, and we will stick with our basic defini-
tion of the referential integrity constraint in this topic. At the same time, be aware that
the complete, formal definition of the referential integrity constraint does allow for null
values in foreign key columns.

We have defined three constraints so far in our discussion:

By The WAy

● The domain integrity constraint ,

● The entity integrity constraint , and

● The referential integrity constraint

The purpose of these three constraint, taken as a whole, is to create database
integrity, which means that the data in our database will be useful, meaningful data. 3

Normal Forms

All relations are not equal. Some are easy to process, and others are problematic. Relations are
categorized into normal forms based on the kinds of problems that they have. Knowledge of
these normal forms will help you create appropriate database designs. To understand normal
forms, we need first to define modification anomalies.

Modification Anomalies
Consider the EQUIPMENT_REPAIR relation in Figure 3-10, which stores data about manufac-
turing equipment and equipment repairs. Suppose we delete the data for repair number 2100.
When we delete this row (the second one in Figure 3-10), we remove not only data about the
repair, but also data about the machine itself. We will no longer know, for example, that the

2 For example, see the Wikipedia article on Referential Integrity at http://en.wikipedia.org/wiki/
Referential_integrity .
3 For more information and discussion, see the Wikipedia article on Database Integrity at http://en.wikipedia
. org/wiki/Database_integrity and the articles linked to that article.