The database consists of three tables: Authors, Titles and AuthorISBN. The Authors table (described in Fig. 22.3) consists of three columns that maintain each author’s unique ID number, first name and last name, respectively. Figure 22.4 contains the sample data from the Authors table.

The Titles table (described in Fig. 22.5) consists of four columns that maintain information about each book in the database, including its ISBN, title, edition number and copyright year. Figure 22.6 contains the data from the Titles table.

The AuthorISBN table (described in Fig. 22.7) consists of two columns that maintain ISBNs for each book and their corresponding authors’ ID numbers. This table associates authors with their books. The AuthorID column is a foreign key—a column in this table that matches the primary-key column in another table (that is, AuthorID in the Authors table). The ISBN column is also a foreign key—it matches the primary-key column (that is, ISBN) in the Titles table. A database might consist of many tables. A goal of a database’s designer is to minimize the amount of duplicated data among the database’s tables. Foreign keys, which are specified when a database table is created in the database, link the data in multiple tables. Together the AuthorID and ISBN columns in this table form a composite primary key. Every row in this table uniquely matches one author to one book’s ISBN. Figure 22.8 contains the data from the AuthorISBN table of the Books database.

Every foreign-key value must appear as another table’s primary-key value so the DBMS can ensure that the foreign key value is valid. For example, the DBMS ensures that the AuthorID value for any particular row of the AuthorISBN table (Fig. 22.8) is valid by checking that there is a row in the Authors table with that AuthorID as the primary key.

Foreign keys also allow related data in multiple tables to be selected from those tables— this is known as joining the data. There’s a one-to-many relationship between a primary key and a corresponding foreign key—e.g., an author can write many books and a book can be written by many authors. So a foreign key can appear many times in its own table but only once (as the primary key) in another table. For example, the ISBN 0132151006 can appear in several rows of AuthorISBN but only once in Titles, where ISBN is the primary key.

Figure 22.9 is an entity-relationship (ER) diagram for the Books database. This diagram shows the tables in the database and the relationships among them.

The first compartment in each box contains the table’s name. The names in italic font are primary keys—AuthorID in the Authors table, AuthorID and ISBN in the AuthorISBN table, and ISBN in the Titles table. Every row must have a value in the primary-key column (or group of columns), and the value of the key must be unique in the table; otherwise, the DBMS will report an error. The names AuthorID and ISBN in the AuthorISBN table are both italic—together these form a composite primary key for the AuthorISBN table.

The lines connecting the tables in Fig. 22.9 represent the relationships among the tables. Consider the line between the Authors and AuthorISBN tables. On the Authors end of the line, there’s a 1, and on the AuthorISBN end, an infinity symbol (∞). This indicates a one-to-many relationship—for each author in the Authors table, there can be an arbitrary number of ISBNs for books written by that author in the AuthorISBN table (that is, an author can write any number of books). Note that the relationship line links the AuthorID column in the Authors table (where AuthorID is the primary key) to the AuthorID column in the AuthorISBN table (where AuthorID is a foreign key)—the line between the tables links the primary key to the matching foreign key.

The line between the Titles and AuthorISBN tables illustrates a one-to-many relationship—one book can be written by many authors. Note that the line between the tables links the primary key ISBN in the Titles table to the corresponding foreign key in the AuthorISBN table. Figure 22.9 illustrates that the sole purpose of the AuthorISBN table is to provide a many-to-many relationship between the Authors and Titles tables—an author can write many books, and a book can have many authors.