SQL and Relational Theory, 2nd Edition

WHAT DO THE SHADED ENTRIES MEAN?

Let’s ignore status values for the moment and concentrate on cities. So far, then, I’ve said that shaded entries in the CITY column (as shown in, e.g., Figure C-2) mean we don’t know the applicable supplier city—i.e., the supplier does have a city, but we don’t know what it is. But our not knowing is only one of many possible reasons why we might not be able to use a genuine city name as some entry in that column. For example, it might be that the notion of having a city simply doesn’t apply to some suppliers (perhaps they conduct their business entirely online). If so, we might say, very loosely, that table SC, with those shaded entries in the CITY column (i.e., table SC as shown in Figure C-2), has a predicate looking something like this:

Supplier SNO is located in city CITY OR we don’t know where supplier SNO is located OR supplier SNO isn’t located anywhere.

Note, therefore, that those shaded entries now potentially have two distinct interpretations: Some of them mean we don’t know the applicable city, others mean the property of having a city doesn’t apply. So, again, we apply horizontal decomposition, this time to obtain three tables: SC (suppliers with a known city), SUC (suppliers with an unknown city), and SNC (suppliers with no city). The predicates are:

• SC: Supplier SNO is located in city CITY.

• SUC: We don’t know where supplier SNO is located.

• SNC: Supplier SNO doesn’t have a location.

If we assume for the sake of the example that supplier S2 ...