Database Redesign - Database Processing: Fundamentals, Design, and Implementation - page 368

EmployeeNumber LastName Email Department DeptPhone

100 Johnson [e-mail] Accounting 834-1100

200 Abernathy [e-mail] Finance 834-2100

300 Smathers [e-mail] Finance 834-21000

400 Caruthers [e-mail] Accounting 834-1100

500 Jackson [e-mail] Production 834-4100

600 Caldera [e-mail] Legal 834-3100

834-3100

700 Bandalone [e-mail] Legal

Figure 8-1
Table Showing Constraint
Assumption Violation

What Is a Correlated Subquery?
A correlated subquery looks very much like the noncorrelated subqueries we discussed in
Chapter 2, but, in actuality, correlated subqueries are very different. To understand the differ-
ence, consider the following noncorrelated subquery, which is like those in Chapter 2:

/* *** SQL-Query-CH08-01 *** */
SELECT A.FirstName, A.LastName

FROM ARTIST AS A

WHERE A.ArtistID IN

(SELECT W.ArtistID

FROM WORK AS W

WHERE W.Title = 'Blue Interior');

The DBMS can process such subqueries from the bottom up; that is, it can first find all
of the values of ArtistID in WORK that have the title 'Blue Interior' and then process the up-
per query using that set of values. There is no need to move back and forth between the two
SELECT statements. The result of this query is the artist Mark Tobey, as we would expect
based on the data in the Figure 7-16:

Searching for Multiple Rows with a Given Title
Now, to introduce correlated subqueries, suppose that someone at View Ridge Gallery proposes
that the Title column of WORK be an alternate key. If you look at the data in Figure 7-16(d),
you can see that although there is only one copy of 'Blue Interior', there are two or more copies
of other titles, such as 'Surf and Bird'. Therefore, Title cannot be an alternate key, and we can
determine this by simply looking at the dataset.
However, if the WORK table had 10,000 or more rows, this would be difficult to determine.
In that case, we need a query that examines the WORK table and displays the Title and Copy of
any works that share the same title.
If we were asked to write a program to perform such a query, our logic would be as follows:
Take the value of Title from the first row in WORK and examine all of the other rows in the
table. If we find a row that has the same title as the one in the first row, we know there are du-
plicates, so we print the Title and Copy of the first work. We continue searching for duplicate
title values until we come to the end of the WORK table.