Databases Reference
In-Depth Information
1-22-233700-0
Visual Basic
1
$25.00
1
-1111-1111-1
C
++
1
$29.95
0-91-045678-5
Hamlet
2
$20.00
0
-555-55555-9
Macbeth
2
$12.00
0-99-777777-7
King Lear
2
$49.00
0
-123-45678-0
U
lysses
2
$34.00
0-12-345678-9
Jane Eyre
3
$49.00
0
-11-345678-9
Moby-Dick
3
$49.00
0-321-32132-1
Balloon
3
$34.00
0
-55-123456-9
Main Street
3
$22.95
Table 3-6. The PUBLISHERS Table from the LIBRARY Database
PubID
PubName
PubPhone
1
Big House
123-456-7890
2
Alpha Press
999-999-9999
3
Small House
714-000-0000
Notice that we have included the necessary foreign key {PubID} in the BOOKS table in
Table 3-5, to implement the
PublisherOf
relationship, which is one-to-many. Also, we
have included the BOOK/AUTHOR table (Table 3-4) to implement the
WrittenBy
relationship, which is many-to-many.
Even though all relationships are established through foreign keys, we must tell Access
that these foreign keys are being used to implement the relationships. Here are the steps.
3.4.1 Setting Up the Relationships in Access
1.
Just to illustrate a point, make the following small change in the BOOKS table:
Open the table and change the PubID field for Hamlet to 4. Note that there is no
publisher with PubID 4 and so we have created a dangling reference. Then close
the BOOKS window.
2.
Now choose
Relationships
from the
Tools
menu. You should get a window
showing the table schemes in the database, similar to that in Figure 3-4.
Relationships are denoted by lines between these table schemes. As you can see,
there are as yet no relationships. Note that the primary key attributes appear in
boldface.
Figure 3-4. The Relationships view of the BOOKS table
