Disambiguating Attributes

Disambiguating Attributes

Sometimes we ask a query involving many relations, and among these relations are two or more attributes with the same name. If so, we need a way to indicate which of these attributes is meant by a use of their shared name. SQL solves this problem by allowing us to place a relation name and a dot in front of an attribute. Thus R.A refers to the attribute A of relation R.

Example 1: The two relations

MovieStar(name, address, gender, birthdate)
MovieExec(name, address, cert#, netWorth)

each have attributes name and address. Suppose we wish to find pairs consisting of a star and an executive with the same address. The following query does the job.

SELECT MovieStar.name, MovieExec.name
FROM MovieStar, MovieExec
WHERE MovieStar.address = MovieExec.address;

In this query, we look for a pair of tuples, one from MovieStar and the other from MovieExec, such that their address components agree. The WHERE clause enforces the requirement that the address attributes from each of the two tuples agree. Then, for each matching pair of tuples, we extract the two name attributes, first from the MovieStar tuple and then from the other. The result would be a set of pairs such as

Disambiguating Attributes

The relation name, followed by a dot, is allowable even in situations where there is no uncertainty. For example, we are free to write the query of "Queries Involving More Than One Relation" Example 1, as

SELECT MovieExec.name
FROM Movie, MovieExec
WHERE Movie.title = 'Star Wars'
              AND Movie.producerC# = MovieExec.cert#;

On the other hand, we may use relation names and dots in front of any subset of the attributes in this query.

Tags