Multiple Relations in a Variable

One approach would be to try to reduce the number of relation variables (tables) by allowing one variable to hold multiple types of relations. In our example we have two base relation variables:

And we have a useful derived relation value of

Which would be calculated as the union of S_City [-City] (projecting away City [ See later the next chapter for a description of this projection notation.] ) and S_NoCity. The interdependence among these three relations is very clear, so it would be nice to be able to express and manage them all together. This will alleviate some of the complexity in using the basic normalized approach.

Multi-Relation Variables

What if we allowed S_All to have multiple relations within it, where each tuple knew what relation it belonged to? Something like this:

We seem to have simplified the number of variables and derived values significantly, from three to one. Notice that we have the same number of relations:

S_City.2: There exists a supplier with identifier S#, who has the name SName, who is located only in city City, and …

S_NoCity.2: There exists a supplier with identifier S#, who has the name SName, and … and for which we do not know the city it is located in.

S_All.1: There exists a supplier with identifier S#, who has the name SName, and …

This is good. We don’t want to reduce the useful meanings in our database; we just want to reduce how many entities we have to manage.

He examples has a visual duplication of rows. We repeated all the tuples from S_City and S_NoCity to specify they are in S_All as well. This "duplication" would happen automatically: S_All is still a derived value (i.e. the union) of the S_City and S_NoCity tuples within S_All.

Interacting with Multi-Relation Variables

The first question that might come to mind is "How do we interact with a multi-relation variable"? How do we insert relations into it and how do we query a multi-relation variable? For example, how do we ask:

Q4.1: What suppliers can be proven to be among those that we don’t know their location?

It would appear we would have to add a way to specify which tuples we want to consider from the table. Something like "SELECT … FROM S_All.{S_NoCity}". This leads us to as much complexity as if we had separate tables. This approach may organize the relations and variables but it will not reduce the complexity of the database scheme.