From E/R Relationships to Relations

From E/R Relationships to Relations

Relationships in the E/R model are also represented by relations. The relation for a given relationship R has the following attributes:

1. For each entity set involved in relationship R, we take its key attribute or attributes as part of the schema of the relation for R.

2. If the relationship has attributes, then these are also attributes of relation R.

A Note About Data Quality

If one entity set is involved various times in a relationship, in different roles, then its key attributes each appear as many times as there are roles. We must rename the attributes to avoid name duplication. More usually, should the same attribute name appear twice or more among the attributes of R itself and the keys of the entity sets involved in relationship R, then we need to rename to avoid duplication.

Example (a): Suppose the relationship Owns of "From Entity Sets to Relations" figure. This relationship connects entity sets Movies and Studios. Therefore, for the schema of relation Owns we use the key for Movies, which is title and year, and the key of Studios, which is name. That is, the schema for relation Owns is:

Owns(title, year, studioName)

A sample instance of this relation is:
From E/R Relationships to Relations

We have chosen the attribute studioName for clarity; it corresponds to the attribute name of Studios.

Example (b): Similarly, the relationship Stars-In of "From Entity Sets to Relations" figure, can be transformed into a relation with the attributes title and year (the key for  Movies) and attribute starName, which is the key for entity set Stars. Figure 1 shows a sample relation Stars-In.

Because these movie titles are unique, it seems that the year is redundant in figure 1. However, had there been several movies of the same title, like "King Kong", we would see that the year was essential to sort out which stars appear in which version of the movie.

Example (c): Multiway relationships are also easy to convert to relations. Consider the four-way relationship Contracts of "Roles in Relationships" figure, reproduced here as figure 2, involving a star, a movie, and two studios - the first holding the

A relation For relationship Stars-In

The relationship Contracts

star's contract and the second contracting for that star's services in that movie. We represent this relationship by a relation Contracts whose schema consists of the attributes from the keys of the following four entity sets:

1. The key starName for the star.

2. The key consisting of attributes title and year for the movie.

3. The key studioOfStar indicating the name of the first studio; recall we assume the studio name is a key for the entity set Studios.

4. The key producingStudio indicating the name of the studio that will produce the movie using that star.

That is, the schema is:

Contracts(starName, title , year, studioOfStar, producingStudio)

Notice that we have been imaginative in choosing attribute names for our relation schema, avoiding "name" for any attribute,  since it would be unclear whether that referred to a star's name or studio's name, and in the latter case, which studio. Also, were there attributes attached to entity set Contracts, such as salary, these attributes would be added to the schema of relation Contracts.