Databases Reference
In-Depth Information
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
.
Figure 3-2. A one-to-many relationship shown in tables S and T
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.
3.3.2 Implementing a One-to-One Relationship
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.
3.3.3 Implementing a Many-to-Many Relationship—New Entity Classes
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 topic
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.
