Accessing Databases Using JDBC - Java Programming: Comprehensive Concepts and Techniques - page 696

(columns) in the two tables. The cardinality of a relationship is described as
one-to-one, one-to-many, or many-to-many, depending on the number of
records in a table that may relate to a given record in another table.
Relationship Cardinality
The cardinality of a relationship between records in a table is
one-to-one only if one record in a given table relates to only one
record in a second table; one-to-many if one record relates to
multiple records in a second table; and many-to-many if multiple
records are related to multiple records in a second table. Many-to-
many relationships typically are modeled by a third table.
In order to manipulate the data efficiently and establish relationships
between tables, at least one index must be established per table. An index is an
external data structure built from a field or group of fields and is used to identify
and sort records in a table. An index for a table is used as you would use an
index in a book; you use a keyword (index) to look up the location of where that
data exists. A primary key is an index with a unique, non-null value for each
record. Only one primary key can exist for a given table. A primary key often is
used to prevent duplicate records and to access a given record more efficiently
than simple sequential retrieval. A primary key is not required unless it is speci-
fied as a foreign key in another table. A foreign key is one or more fields in a
table that reference the primary key field(s) of another table. It is acceptable for a
foreign key to have the same name as the referenced field, because they exist
within different tables. Because the fields reference the same information, using
the same field name makes this obvious and is a common practice, although it is
not required. In the case of the Users table, the userID field is the primary key
because it contains a unique value for each user record. The UserStocks table
also has a field, userID, which is a foreign key to the Users table.
Naming Foreign Keys
A foreign key field does not have to be named the same as the
primary key it references. It is commonplace to do so, however,
not simply because the two fields contain the same data. Using
the same name makes the relationship between the tables more
obvious.
Each user record may be associated with zero, one, or many stock holdings.
It is redundant to maintain a separate stock name for each stock holding. Each
stock symbol is associated with only one stock name, regardless of the number
of users holding shares of that stock. The Stocks table is created to hold each
stock symbol and its associated stock name. Because the stock symbol uniquely
identifies a stock, the symbol then is used as the primary key. The relationship of
users to stocks is modeled by the UserStocks table, which represents the stock
holdings for each user. The purpose of the table is to relate the Users table to the