Keys in the E/R Model

Keys in the E/R Model

A key for an entity set E is a set K of one or more attributes such that, given any two separate entities e1 and e2 in E, e1 and e2 cannot have the same values for each of the attributes in the key K. If K is made of more than one attribute, then it is possible for e1 and e2 to agree in some of these attributes, but never in all attributes. Three useful points to remember are:

● A key can consist of more than one attribute; an illustration appears in following example (a).

● There can also be more than one possible key for an entity set, as we shall see in example (b). However, it is usual to pick one key as the "primary key" and to act as if that were the only key.

● When an entity set is involved in an isa-hierarchy, we need that the root entity set have all the attributes required for a key, and that the key for each entity is found from its component in the root entity set, regardless of how many entity sets in the hierarchy have elements for the entity in question.

Example (a) : Let us think about the entity set Movies from "Entity Sets" example . One might first suppose that the attribute title by itself is a key. However, there are a number of titles that have been used for two or even more movies, for instance, King Kong. In this way, it would be unwise to declare that title by itself is a key. If we did so, then we would not be able to contain information about both King Kong movies in our database.

A better choice would be to take the set of two attributes title and year as a key. We still run the risk that there are two movies made in the same year with the same title (and thus both could not be stored in our database), but that is improbable.

For the other two entity sets, Stars and Studios, introduced in "Entity Sets" example, we must again think carefully about what can serve as a key. For studios, it is reasonable to suppose that there would not be two movie studios with the same name, so we shall take name to be a key for entity set Studios. However, it is less clear that stars are distinctively recognized by their name. Certainly name does not distinguish among people in general. However, since stars have traditionally chosen "stage names" at will, we might hope to find that name serves as a key for Stars too. If not, we might  choose the pair of attributes name and address as a key, which would be satisfactory unless there were two stars with the same name living at the same address.

Example (b) : Our experience in above example (a) might lead us to believe that it is difficult to find keys or to be sure that a set of attributes makes a key. In reality the matter is generally much simpler. In the real-world situations usually modeled by databases, people frequently go out of their way to create keys for entity sets. For instance, companies normally assign employee ID's to all employees, and these ID's are carefully chosen to be unique numbers. One purpose of these ID's is to make sure that in the company database each employee can be differentiated from all others, even if there are several employees with the same name. Thus, the employee-ID attribute can serve as a key for employees in the database.

In US corporations, it is usual for every employee to also have a Social Security number. If the database has an attribute that is the Social Security number, then this attribute can also serve as a key for employees. Note that there is nothing wrong with there being a number of choices of key for an entity set, as there would be for employees having both employee ID's and Social Security numbers.

The concept of creating an attribute whose function is to serve as a key is quite extensive. In addition to employee ID's, we find student ID's to differentiate students in a university. We find drivers license numbers and automobile registration numbers to distinguish drivers and automobiles, respectively, in the Department of Motor Vehicles. The reader can definitely find more examples of attributes created for the primary purpose of serving as keys.

Constraints Are Part of the Schema

We could look at the database as it exists at a certain time and decide incorrectly that an attribute makes a key because no two entities have the same values for this attribute. For instance, as we create our movie database we might not enter two movies with the same title for some time. Therefore, it might look as if title were a key for entity set Movies. However, if we decided on the basis of this preliminary evidence that title is a key, and we designed a storage structure for our database that assumed title is a key, then we might find ourselves unable to enter a second King Kong movie into the database.

Therefore, key constraints, and constraints in general, are part of the database schema. They are declared by the database designer along with the structural design (e.g., entities and relationships). Once a constraint is declared, insertions or modifications to the database that violate the constraint are not allowed.

Thus, although a specific instance of the database may satisfy certain constraints, the only "true" constraints are those identified by the designer as holding for all instances of the database that correctly model the real-world. These are the constraints that may be assumed by users and by the structures used to store the database.