Combining Relations

Combining Relations

Often, the relations that we get from converting entity sets and relationships to relations are not the best possible choice of relations for the given data. One ordinary situation occurs when there is an entity set E with a many-one relationship R from E to F. The relations from E and R will each have the key for E in their relation schema. In addition, the relation for E will have in its schema the attributes of E that are not in the key, and the relation for R will have the key attributes of F and any attributes of R itself. Because R is many-one, all these attributes have values that are decided uniquely by the key for E, and we can combine them into one relation with a schema consisting of:

1. All attributes of E.

2. The key attributes of F.

3. Any attributes belonging to relationship R.

For an entity e of E that is not related to any entity of F, the attributes of types (2) and (3) will have null values in the tuple for e. Null values were introduced informally in "Single-Value Constraints", in order to represent a situation where a value is missing or unknown. Nulls are not a formal part of the relational model, but a null value, denoted NULL, is available in SQL, and we shall use it where needed in our discussions of representing E/R designs as relational database schemas.

Example (a) : In our running movie example, Owns is a many-one relationship from Movies to Studios, which we converted to a relation in example (a) of "From E/R Relationships to Relations". The relation obtained from entity set Movies was discussed in "From Entity Sets to Relations" example. We can combine these relations by taking all their attributes and forming one relation schema. If we do, the relation looks like that in the following figure.

Combining relation Movies with relation Owns

Whether or not we choose to combine relations in this manner is a matter of judgment. Though, there are some advantages to having all the attributes that are dependent on the key of entity set E together in one relation, even if there are a number of many-one relationships from E to other entity sets. For instance, it is sometimes more efficient to answer queries involving attributes of one relation than to answer queries involving attributes of several relations. In fact, some design systems based on the E/R model combine these relations automatically for the user.

On the other hand, one might wonder if it made sense to combine the relation for E with the relation of a relationship R that involved E but was not many-one from E to some other entity set. Doing so is risky, because it often leads to redundancy, an issue we shall discuss in "Design of Relational Database Schemas".

Example (b) : To get an idea of what can go wrong, suppose we combined the relation of above figure with the relation that we get for the many-many relationship Stars-in; recall this relation was suggested by Figure 1 of "From E/R Relationships to Relations". Then the combined relation would look like the following figure.
The relation Movies with star information

Because a movie can have many stars, we are forced to repeat all the information about a movie, once for each star. For example, we see in above figure that the length of Star Wars is repeated three times - once for each star - as is the fact that the movie is owned by Fox. This redundancy is unwanted, and the purpose of the relational-database design theory of "Design of Relational Database Schemas" is to split relations such as that of above figure, and thus remove the redundancy.