Keys of Relations

Keys of Relations

We say a set of one or more attributes {A1, A2,. . . ,An} is a key for a relation R if:

1. Those attributes functionally determine all other attributes of the relation. That is, because relations are sets, it is impossible for two different tuples of R to agree on all of A1, A2, ... ,An.

Functional Dependencies Tell Us About the Schema

2. No proper subset of {A1, A2,,...., An} functionally determines all other attributes of R;  i.e., a key must be minimal.

When a key consists of a single attribute A, we often say that A (rather than {A}) is a key.

Example : Attributes { title, year, starName} form a key for the relation Movies of figure 2 of "Functional Dependencies". First, we must show that they functionally determine all the other attributes. That is, suppose two tuples agree on these three attributes: title, year, and starName. Because they agree on title and year, they must agree on the other attributes - length, filmType, and studioName - as we discussed in "Functional Dependencies" example. Therefore two different tuples cannot agree on all of title, year, and starName; they would in fact be the same tuple.

Now, we must argue that no proper subset of {title, year, starName} functionally decides all other attributes. To see why, begin by observing that title and year do not decide starName, because many movies have more than one star. Thus, {title, year} is not a key.

{year, starName} is not a key because we could have a star in two movies In the same year; therefore

year starName → title

is not a FD. Also, we claim that {title, starName} is not a key, because two movies with the same title, made in different years, rarely have a star in common.

Often a relation has more than one key. If so, it is common to designate one of the keys as the primary key. In commercial database systems, the choice of primary key can influence some implementation issues such as how the relation is stored on disk. A useful convention we shall follow is:
●  Underline the attributes of the primary key when displaying its relation schema.

Minimality of Keys