Databases Reference
In-Depth Information
Table 5-3. 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
Here is a procedure for executing this request. Don't worry if some of the terms do not
make sense to you now; I will explain them later.
1.
Join
the BOOKS and PUBLISHERS tables, on the PubID attribute.
2.
Select
those rows (of the join) with Price attribute less than $20.00.
3.
Project
onto the columns PubName and PubPhone.
In the relational algebra, this would be translated into the following expression:
proj
PubName,PubPhone
(
sel
Price<20.00
(BOOKS
join
PUBLISHERS))
The result table is shown in Table 5-4.
Table 5-4. Publishe
r
s with books under $20.00
PubName
PubPhone
B
ig House
1
23-456-7890
Alpha Press
999-999-9999
In a relational calculus, the corresponding expression might appear as:
{(x,y) | PUBLISHERS(z,x,y)
and
BOOKS(a,b,z,c)
and
c < $20.00}
where the bar | is read “such that,” and the entire expression is read:
The set of all pairs (x,y) such that (z,x,y) is a row in the PUBLISHERS
table, (a,b,z,c) is a row in the BOOKS table, and c < $20.00.
Note that the variable z appears twice, and it must be the same for each appearance. This
is precisely what provides the link between the BOOKS and PUBLISHERS tables. In
other words, the row PUBLISHERS(z,x,y) in the PUBLISHERS table and the row
BOOKS(a,b,z,c) in the BOOKS table have an attribute value in common (represented by
the common letter z). This attribute, which is the first attribute in PUBLISHERS and the
third attribute in BOOKS, is PubID.
As you can see from the previous example, the relational calculus is generally more
complex (and perhaps less intuitive) than the relational algebra, and I will not discuss it
further in this topic, beyond making the following comments. First, it is important to at
least be aware of the existence of the relational calculus, since there are commercially
available applications, such as IBM's
Query-by-Example
, that use the relational calculus.
Second, most relational calculus-based languages have exactly the same expressive
