Databases Reference
In-Depth Information
i
j
k
l
c
f
i
j
k
l
s
j
The importance of the natural join comes from the fact that, when there is a one-to-many
relationship from S to T, we can arrange it—by renaming, if necessary—so that the only
common attributes are the key of S and the foreign key in T. In this case, the natural join
S
nat-join
T is simply the table obtained by matching rows that are related through the
one-to-many relationship.
For example, consider the following BOOKS and PUBLISHERS tables in Tables Table
5-9 and Table 5-10, respectively.
Table 5-9. The BOOKS table
ISBN
Title
Price
PubID
0
-103-45678-9
The Firm
$
24.95
1
0
-11-345678-9
Moby-Dick
$
49.00
2
0
-12-333433-3
War and Peace
$
25.00
1
0
-12-345678-9
Jane Eyre
$
34.00
1
0
-26-888888-8
Persuasion
$
13.00
3
0
-555-55555-9
Emma
$
12.00
3
0
-91-045678-5
The Chamber
$
20.00
3
0
-91-335678-7
Partners
$
15.00
1
0
-99-777777-7
Triple Play
$
44.00
3
0
-99-999999-9
Mansfield Park
$
18.00
1
Table 5-10. The PUBLISHER
S
table
PubID
PubName
PubPhone
1
B
ig House
212-000-1212
2
L
ittle House
213-111-1212
3
Medium House
614-222-1212
Then PUBLISHERS
nat-join
BOOKS is the table formed by taking each PUBLISHERS
row and adjoining each BOOKS row with a matching PubID, as shown in Table 5-11.
Table 5-
1
1. The PUBLISH
E
RS nat-join BOO
K
S table
PubID
PubName
PubPhone
ISBN
Title
Price
1
Big House
2
12-000-1212
0-103-45678-9
The Firm
$24.95
1
Big House
2
12-000-1212
0-12-333433-3
War and Peace
$25.00
1
Big House
2
12-000-1212
0-12-345678-9
Jane Eyre
$34.00
1
Big House
2
12-000-1212
0-91-335678-7
Partners
$15.00
1
Big House
2
12-000-1212
0-99-999999-9
Mansfield Park
$18.00
2
Little House
2
13-111-1212
0-11-345678-9
Moby-Dick
$49.00
3
Medium House
6
14-222-1212
0-26-888888-8
Persuasion
$13.00
3
Medium House
6
14-222-1212
0-555-55555-9
Emma
$12.00
3
Medium House
6
14-222-1212
0-91-045678-5
The Chamber
$20.00
3
Medium House
6
14-222-1212
0-99-777777-7
Triple Play
$44.00
