Step 4
Using
“Primary Keys” to Re-Connect (or “join) the Lists
From the previous step, remember that
the author’s record number
uniquely identifies the author of each book title.
By storing the author’s “Primary
Key” with each title, each row in the table of book titles
will now contain the record number (row
number) of its author.
Below, the number that has been added to
the table of titles,
and can now be used to identify who wrote each book.
These two tables can now be “joined”
in a “relationship”
by using the matching numbers in the “AuthorNo” column in each table.
The “AuthorNo” columns from each table form
what is called “common keys,”
that “tell” the database management system (DBMS)
how to connect the two tables.
The DBMS
uses “common keys” to logically “join” the “Titles” list with the “Authors”
list
by matching the “AuthorNo”
value in the “Titles” list
with the “AuthorNo”
value in the “Authors” list.
If these values match, then
that book was written by that author.
For example, we can now logically
‘connect’ book # 3 with author # 1,
by matching the “common keys” in each table,
meaning that “2001 : A Space Odyssey” was
written by Arthur C. Clarke.
|
TitleNo |
Titles |
AuthorNo |
|
AuthorNo |
Authors |
|
|
1 |
Lost
Worlds of 2001 |
1 |
|
1 |
Arthur C. Clarke |
|
|
2 |
Childhood's
End |
1 |
|
2 |
Ayn Rand |
|
|
3 |
2001 : A Space Odyssey |
1 |
|
3 |
Dan Brown |
|
|
4 |
2010 :
Odyssey Two |
1 |
|
4 |
David Alexander |
|
|
5 |
2061 :
Odyssey Three |
1 |
|
5 |
Douglas Adams |
|
|
6 |
3001 :
Final Odyssey |
1 |
|
6 |
Georges Simenon |
|
|
7 |
The Moon
Is a Harsh Mistress |
1 |
|
7 |
Isaac Asimov |
|
|
8 |
A Fall of
Moondust |
1 |
|
8 |
Douglas Adams |
|
|
9 |
Breaking
Strain |
1 |
|
9 |
Joe Haldeman |
|
|
10 |
Maelstrom |
1 |
|
10 |
Douglas Adams |
|
|
11 |
Hide and
Seek |
1 |
|
11 |
Michael Crichton |
|
|
12 |
Tales
from Planet Earth |
1 |
|
12 |
Nella Parson |
|
|
13 |
Rendezvous
with Rama |
1 |
|
13 |
Richard Prather |
|
|
14 |
Rama Revisited |
1 |
|
14 |
Robert Heinlein |
|
|
15 |
Atlas
Shrugged |
2 |
|
15 |
Scott Turow |
|
|
16 |
Fountainhead |
2 |
|
16 |
Tony Fiore |
|
|
17 |
Deception
Point |
3 |
|
|
|
|
|
18 |
Digital
Fortress |
3 |
|
|
|
|
|
. . . . . . |
|
|
||||
|
|
|
|||||
|
51 |
For Us,
the Living |
14 |
|
|
|
|
|
52 |
Glory
Road |
14 |
|
|
|
|
|
53 |
Pleading
Guilty |
15 |
|
|
|
|
|
54 |
Burden of
Proof |
15 |
|
|
|
|
|
55 |
The
Corvair Decade |
16 |
|
|
|
|
Note that while these titles are shown in sequence of the
“AuthorNo”
ID,
this is not
necessarily true.
Title records can be in any order and
they will
remain ‘connected’ to the author in the “Authors” table,
regardless of
where they are in the “Titles” table