A key is a set of attributes that identifies an object. The primary key of an entity is one key that uniquely identifies the entity that owns the attributes. An alternate or candidate key is a unique
identifier other than the primary key. An entity can have any number of keys, though having more than a single alternate is quite unusual. A foreign key is a key that identifies an object in another entity; it acts as a data pointer to the other kind of entity, relating the two entities. A composite keyis a key where the set has more than one attribute in it; I don't think there is a special name for the key with a one-element set, which is far more common.
Note
As with everything to do with computer science, somebody somewhere has probably given this a name. More likely, there are multiple names for the concept. I just haven't run into them. As with cardinality and multiplicity, the name isn't as important as the concept. In Chen diagrams, you show the primary key either by an asterisk on the attribute name or names or by underlining them, as in Figure 6-12. There is no way of showing alternate keys. The IDEF1X notation puts the primary key attributes in the upper division of the entity and marks alternate keys with an "AK<n>" in parentheses after the attribute name, with <n> being an integer identifying the key if it has several attributes. The crow's-foot notation, which does not show attributes, has therefore no way of showing primary keys. CASE tools extend the notation with notations similar to Chen or IDEF1X. Those tools depend on this information to generate the correct relational tables, including key constraints.
The foreign key does not exist formally in the ER diagram, as it is really the relational way of representing the many side of a one-to-many relationship. When they generate a relational schema from the ER diagram, most ER case tools take such relationships and include attributes from the primary key of the one side in the table on the many side. A foreign key is thus a set of attributes that represents a relationship to another group of entities. IDEF1X represents this explicitly using
"(FK<n>)" to mark the foreign key attributes, again with <n> identifying the key over multiple attributes.
Figure 6-12: Primary and Alternate Keys in ER Notations
Many-to-many relationships create foreign keys in a completely separate object, one that represents the relationship itself. The "plays a role in" relationship of Figure 6-10, for example, is a many-to-many relationship between a person and a criminal organization. That is, one person may play a role in several criminal organizations, and one criminal organization has at least two and possibly more persons playing a role in it. In this case, you can see that placing the primary key attributes in either of the two entities isn't going to work. You have to put their primary keys in the relationship, which then explicitly represents the connections between people and organizations. The relationship's primary key is then the combination of the two foreign keys. Using the IDEF1X notation, the two keys appear together in the upper part of the entity box, both marked with the (FK) adornment to indicate they are foreign keys.
Note These kinds of associative entities are also examples of weak or identifying relationships; see the following section on "Strong and Weak Relationships" for details.
You can have relationships that relate more than two entities to one another. A binary relationship is a two-way relationship with two roles. A ternary relationship is a three-way relationship with three roles. You can keep going, but there are very few relationships with four or more roles. As an example of a ternary relationship, consider the "plays a role in" relationship again. Figure 6-13 shows in Chen notation an expanded relationship that includes the RoleType entity. One use for this might be to create a description of the role that several people play in different organizations. Another use might
be to permit modeling of the role structure of organizations with a graph structure (Underboss reports to Boss, Lieutenant reports to Underboss, and so on).
Figure 6-13: A Ternary Relationship in Chen Notation
The relationship contains three keys, one for Person, one for Criminal Organization, and one for Role. It represents a specific role played by a specific person in a specific criminal organization. This particular ternary relationship expresses a many-to-many-to-many multiplicity. That is, each of the relationship roles is a to-many role. Often, there are functional dependencies that limit ternary roles to a to-one role. For example, you might want to say that a person can play a role in only one criminal organization, not several. Then the criminal organization role (the link from the relationship to criminal organization) would be a to-one link, and the relationship would be many-to-many-to-one. This constraint is a functional dependency from the person and role to the criminal organization. Chapter 11 discusses functional dependencies and their impact on relational database design and normal forms in more detail. In that context, putting the relations into fifth normal form requires breaking the relationship into three tables, showing the relationships between person and criminal organization, person and role, and role and criminal organization. Combining these tables using a relational join lets you express any fact about the relationship without introducing spurious facts or losing any
information. The ER model shows you the relationship directly and lets you express the real semantics of the situation in a very direct way.
The foreign key, as a surrogate for relationship, is at the center of the storm in the data modeling world. More than anything else, the foreign key and the relationship provide the differentiator between most design notations and methods as well as the DBMS physical models that implement the
relationships. The foreign key is essentially a relational concept. Chen, in creating the relationship, tried to abstract the notion away from the relational data model to accommodate the needs of other models. Later ER models such as IDEF1X downplay this aspect of the relationship by forcing you to create what is essentially a relational model. Instead of many-to-many relationships or ternary relationships, for example, you create an associative entity [Logic Works 1996] with one-to-many relationships to the entities that make it up. Figure 6-14 shows the expanded "plays a role in" ternary relationship in the equivalent IDEF1X notation.
Figure 6-14: A Ternary Relationship in IDEF1X Notation
The "plays a role in" relationship is now an entity with the appropriate relationship links to the three components. The primary key of the associative entity consists of the three primary keys of the three related entities. These keys are now foreign keys that are part of the primary key, making the
associative entity a weak or dependent entity. This kind of entity, and its reliance on foreign keys, has a special representation in ER diagrams.