Page 609 - Discrete Mathematics and Its Applications

588 9 / Relations

TABLE 5 Teaching_assignments. TABLE 6 Class_schedule.
Course_ Course_
Professor Department number Department number Room Time
Cruz Zoology 335 Computer Science 518 N521 2:00 p.m.
Cruz Zoology 412 Mathematics 575 N502 3:00 p.m.
Farber Psychology 501 Mathematics 611 N521 4:00 p.m.
Farber Psychology 617 Physics 544 B505 4:00 p.m.
Grammer Physics 544 Psychology 501 A100 3:00 p.m.
Grammer Physics 551 Psychology 617 A110 11:00 a m.
Rosen Computer Science 518 Zoology 335 A100 9:00 a m.
Rosen Mathematics 575 Zoology 412 A100 8:00 a m.

EXAMPLE 11 What relation results when the join operator J 2 is used to combine the relation displayed in
Tables 5 and 6?

Solution: The join J 2 produces the relation shown in Table 7. ▲

There are other operators besides projections and joins that produce new relations from
existing relations. A description of these operations can be found in books on database theory.

SQL

The database query language SQL (short for Structured Query Language) can be used to carry
out the operations we have described in this section. Example 12 illustrates how SQL commands
are related to operations on n-ary relations.

EXAMPLE 12 We will illustrate how SQL is used to express queries by showing how SQL can be employed
to make a query about airline flights using Table 8. The SQL statement

SELECT Departure_time
FROM Flights
WHERE Destination=’Detroit’

is used to find the projection P 5 (on the Departure_time attribute) of the selection of 5-tuples in
the Flights database that satisfy the condition: Destination = ‘Detroit’. The output would be a
list containing the times of flights that have Detroit as their destination, namely, 08:10, 08:47,

TABLE 7 Teaching_schedule.
Professor Department Course_number Room Time
Cruz Zoology 335 A100 9:00 a m.
Cruz Zoology 412 A100 8:00 a m.
Farber Psychology 501 A100 3:00 p m.
Farber Psychology 617 A110 11:00 a m.
Grammer Physics 544 B505 4:00 p m.
Rosen Computer Science 518 N521 2:00 p m.
Rosen Mathematics 575 N502 3:00 p m.