Access Database Design and Programming, Second Edition

Implementing the Relationships in a Relational Database

Now let us discuss how we might implement the relationships in an E/R database model. For convenience, we repeat the E/R diagram for the LIBRARY database in Figure 3.1.

Figure 3-1. The LIBRARY entity-relationship diagram

Implementing a One-to-Many Relationship—Foreign Keys

Implementing a one-to-many relationship, such as the PublisherOf relationship, is fairly easy. To illustrate, since {PubID} is a key for the Publishers entity class, we simply add this attribute to the Books entity class. Thus, the Books entity class becomes:

Books(ISBN,Title,PubID,Price)

The Books table scheme is now:

{ISBN,Title,PubID,Price}

and the BOOKS table now appears as shown in Table 3.2 (sorted by PubID).

Table 3-2. The BOOKS Table Sorted by PubID

ISBN	Title	PubID	Price
0-12-333433-3	On Liberty	1	$25.00
0-103-45678-9	Iliad	1	$25.00
0-91-335678-7	Faerie Queene	1	$15.00
0-99-999999-9	Emma	1	$20.00
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	Ulysses	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

The PubID attribute in the Books entity class is referred to as a foreign key, because ...