Java Reference
In-Depth Information
row of the
AuthorISBN
table 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 is a
one-to-many relationship
between a primary
key and a corresponding foreign key (for example, one author can write many books and
one book can be written by many authors). This means that 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
(because this topic has several
authors) but only once in
Titles
, where
ISBN
is the primary key.
Entity-Relationship (ER) Diagram
There's a one-to-many relationship between a primary key and a corresponding foreign
key (e.g., one author can write many books). A foreign key can appear many times in its
own table, but only once (as the primary key) in another table. Figure 24.9 is an
entity-
relationship
(
ER
)
diagram
for the
books
database. This diagram shows the
database tables
and the
relationships
among them. The first compartment in each box contains the table's
name and the remaining compartments contain the table's columns. The names in italic
are primary keys.
A table's primary key uniquely identifies each row in the table.
Every row
must have a primary-key value, and that value must be unique in the table. This is known
as the
Rule of Entity Integrity
. Again, for the
AuthorISBN
table, the primary key is the
combination of both columns—this is known as a composite primary key.
Authors
AuthorISBN
Titles
1
1
AuthorID
AuthorID
ISBN
FirstName
ISBN
Title
LastName
EditionNumber
Copyright
Fig. 24.9
|
Table relationships in the
books
database.
The lines connecting the tables in Fig. 24.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 relation-
ship
—one book can be written by many authors. Note that the line between the tables
links the primary key
ISBN
in table
Titles
to the corresponding foreign key in table
AuthorISBN
. The relationships in Fig. 24.9 illustrate that the sole purpose of the
Author-
ISBN
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.
