Since the entities in an entity class are implemented by giving their attribute values, it makes sense to implement an entity class by the set of attribute names. For instance, the Books entity class can be identified with the set:

{ISBN,Title,Price}

(We will add the PubID attribute name later, when we implement the relationships.)

Since attribute names are usually used as column headings for a table, a set of attribute names is called a table scheme. Thus, entity classes are implemented as table schemes. For convenience, we use notation such as:

Books(ISBN,Title,Price)

which shows not only the name of the entity class, but also the names of the attributes in the table scheme for this class. You can also think of a table scheme as the column headings row (the top row) of any table that is formed using that table scheme. (I will present an example of this shortly.)

We have defined the concepts of a superkey and a key for entity classes. These concepts apply equally well to table schemes, so we may say that the attributes {A,B} form a key for a table scheme, meaning that they form a key for the entity class implemented by that table scheme.

In a relational database, each entity set is modeled by a table. For example, consider the BOOKS table shown in Table 3-1, and note the following:

More formally, a table T is a rectangular array of elements with the following properties:

Let us make some remarks about the concept of a table:

Now that we have defined the concept of a table, we can say that it is common to define a relational database as a finite collection of tables. However, this definition belies the fact that the tables also model the relationships between the entity classes, as we will see.

Implementing a one-to-many relationship, such as the PublisherOf relationship, is fairly easy. To illustrate, since {PubID} is a key for the Publishers entity class, we simply add this attribute to the Books entity class. Thus, the Books entity class becomes:

Books(ISBN,Title,PubID,Price)

The Books table scheme is now:

{ISBN,Title,PubID,Price}

and the BOOKS table now appears as shown in Table 3-2 (sorted by PubID).

The PubID attribute in the Books entity class is referred to as a foreign key, because it is a key for a foreign entity class—that is, for the Publishers entity class.

Note that the value of the foreign key PubID in the BOOKS table provides a reference to the corresponding value in PUBLISHERS. Moreover, since {PubID} is a key for the Publishers entity class, there is at most one row of PUBLISHERS that contains a given value. Thus, for each book entity, we can look up the PubID value in the PUBLISHERS table to get the name of the publisher of that book. In this way, we have implemented the one-to-many PublisherOf relationship.

The idea just described is pictured in more general terms in Figure 3-2. Suppose that there is a one-to-many relationship between the entity classes (or, equivalently, table schemes) S and T. Figure 3-2 shows two tables S and T based on these table schemes. Suppose also that {A₂} is a key for table scheme S (the one side of the relationship). Then we add this attribute to the table scheme T (and hence to table T). In this way, for any row of the table T, we can identify the unique row in table S to which it is related.

Figure 3-2. A one-to-many relationship shown in tables S and T

The attribute set {A₂} in table S is a key for the table scheme S. For this reason, the attribute set {A₂} is also called a foreign key for the table scheme T. More generally, a set of attributes of a table scheme T is aforeign key for T if it is a key for some other table scheme S. Note that a foreign key for T is not a key for T—it is a key for another table scheme. Thus, the attribute set {PubID} is a key for Publishers, but a foreign key for Books.

As with our example, a foreign key provides a reference to the entity class (table scheme) for which it is a key. The table scheme T is called the referencing table scheme, and the table scheme S is called the referenced table scheme . The key that is being referenced in the referenced table scheme is called the referenced key .

Note that adding a foreign key to a table scheme does create some duplicate values in the database, but we must expect to add some additional information to the database in order to describe the relationships.

Of course, the procedure of introducing a foreign key into a table scheme works equally well for one-to-one relationships as for one-to-many relationships. For instance, we only need to rename the ConID attribute to AuID to make ConID into a foreign key that will implement the Authors-Contributors IsA relationship.

The implementation of a many-to-many relationship is a bit more involved. For instance, consider the WrittenBy relationship between Books and Authors.

At first glance, we might think of just adding foreign keys to each table scheme, thinking of the relationship as two distinct one-to-many relationships. However, this approach is not good, since it requires duplicating table rows. For example, if we add the ISBN key to the Authors table scheme and the AuID key to the Books table scheme, then each book that is written by two authors must be represented by two rows in the BOOKS table, so we can have two AuIDs. To be specific, since the book Main Street is written by Smith and Jones, we would need two rows in the BOOKS table:

TITLE: Main Street, ISBN 0-55-123456-9, Price: $22.95 AuID: Smith
TITLE: Main Street, ISBN 0-55-123456-9, Price: $22.95 AuID: Jones

It is clear that this approach will bloat the database with redundant information.

The proper approach to implementing a many-to-many relationship is to add a new table scheme to the database in order to break the relationship into two one-to-many relationships. In our case, we add a Book/Author table scheme, whose attributes consist precisely of the foreign keys ISBN and AuID:

Book/Author(ISBN,AuID)

To get a pictorial view of this procedure, Figure 3-3 shows the corresponding E/R diagram. Note that it is not customary to include this as a portion of the original E/R diagram, since it belongs more to the implementation of the design than to the design itself.

Figure 3-3. A many-to-many relationship in the BOOK/AUTHOR table

There are a few important considerations that we must discuss with regard to using foreign keys to implement relationships. First, of course, is the fact that each value of the foreign key must have a matching value in the referenced key. Otherwise, we would have a so-called dangling reference . For instance, if the PubID key in a BOOKS table did not match a value of the PubID key in the PUBLISHERS table, we would have a book whose publisher did not exist in the database—that is, a dangling reference to a nonexistent publisher.

The requirement that each value in the foreign key be a value in the referenced key is called the referential constraint , and the problem of ensuring that there are no dangling references is referred to as the problem of ensuring referential integrity.

There are several ways in which referential integrity might be compromised. First, we could add a value to the foreign key that is not in the referenced key. This would happen, for instance, if we added a new book entity to the BOOKS table, whose publisher is not listed in the PUBLISHERS table. Such an action will be rejected by a database application that has been instructed to protect referential integrity. More subtle ways to affect referential integrity are to change or delete a value in the referenced key—the one that is being referenced by the foreign key. This would happen, for instance, if we deleted a publisher from the PUBLISHERS table, but that publisher had at least one book listed in the BOOKS table.

Of course, the database program can simply disallow such a change or deletion, but there is sometimes a preferable alternative, as discussed next.

Many database programs allow the option of performing cascading updates , which simply means that, if a value in the referenced key is changed, then all matching entries in the foreign key are automatically changed to match the new value. For instance, if cascading updates are enabled, then changing a publisher’s PubID in a PUBLISHERS table, say from 100 to 101, would automatically cause all values of 100 in the PubID foreign key of the referencing table BOOKS to change to 101. In short, cascading updates keep everything “in sync.”

Similarly, enabling cascading deletions means that if a value in the referenced table is deleted by deleting the corresponding row in the referenced table, then all rows in the referencing table that refer to that deleted key value will also be deleted. For instance, if we delete a publisher from a PUBLISHERS table, all book entries referring to that publisher (through its PubID) will be deleted from the BOOKS table automatically. Thus, cascading deletions also preserve referential integrity, at the cost of performing perhaps massive deletions in other tables. Thus, cascading deletions should be used with circumspection.

As you may know, Microsoft Access allows the user to enable or disable both cascading updates and cascading deletions. We will see just how to do this in Access later.

To view the indexes for a given table in Microsoft Access, open the table in design view, and then choose Indexes from the View menu. For the BOOKS table, you should see a window similar to Figure 3-10 (without the PubTitle entry).

Figure 3-10. Index view of the BOOKS table

To add an index based on more than one attribute, you enter the multiple attributes on successive rows of the Indexes dialog box. We have done this in Figure 3-10, adding an index called PubTitle based on the PubID and the Title attributes. This index indexes the BOOKS entities first by PubID and then by Title (within each PubID).