SQL Clearly Explained- P2

SQL Clearly Explained- P2

SQL Clearly Explained- P2: You don’t need to be a database designer to use SQL successfully.
However, you do need to know a bit about how relational
databases are structured and how to manipulate those
structures.

Nội dung Text: SQL Clearly Explained- P2

Join 45
understand how the result table came to be might assume that
it is correct and make business decision based on the bad data.
The joins you have seen so far have used a single-column pri- Equi-Joins over
mary key and a single-column foreign key. There is no reason,
however, that the values used in a join can’t be concatenated. Concatenated Keys
As an example, let’s look again at the accounting firm example
from Chapter 1. The design of the portion of the database that
we used was
accountant (acct_first_name, acct_last_name,
date_hired, office_ext)
customer (customer_numb, first_name,
last_name, street, city, state_province,
zip_postcode, contact_phone)
project (tax_year, customer_numb,
acct_first_name, acct_last_name)
form (tax_year, customer_numb, form_id,
is_complete)
Suppose we want to see all the forms and the year that the
forms were completed for the customer named Peter Jones by
the accountant named Edgar Smith. The sequence of relation-
al operations would go something like this:
1. Restrict from the customer table to find the single row
for Peter Jones. Because some customers have dupli-
cated names, the restrict predicate would probably con-
tain the name and the phone number.
2. Join the table created in Step 1 to the project table over
the customer number.
3. Restrict from the table created in Step 2 to find the
projects for Peter Jones that were handled by the ac-
countant Edgar Smith.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Join 47
4. Now we need to get the data about which forms appear
on the projects identified in Step 3. We therefore need
to join the table created in Step 3 to the form table.
The foreign key in the form table is the concatenation
of the tax year and customer number, which just hap-
pens to match the primary key of the project table. The
join is therefore over the concatenation of the tax year
and customer number rather than over the individual
values. When making its determination whether to in-
clude a row in the result table, the DBMS puts the tax
year and customer number together for each row and
treats the combined value as if it were one.
5. Project the tax year and form ID to present the specific
data requested in the query.
To see why treating a concatenated foreign key as a single unit
when comparing to a concatenated foreign key is required,
take a look at Figure 2-8. The two tables at the top of the illus-
tration are the original project and form tables created for this
example. We are interested in customer number 18 (our friend
Peter Jones), who has had projects handled by Edgar Smith in
2006 and 2007.
Result table (a) is what happens if you join the tables (without
restricting for customer 18) only over the tax year. This invalid
join expands the 10 row form table to 20 rows. The data imply
that the same customer had the same form prepared by more
than one accountant in the same year.
Result table (b) is the result of joining the two tables just over
the customer number. This time the invalid result table implies
that in some cases the same form was completed in two years.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

50 Chapter 2: Relational Algebra
hiking biking
tour_numb | start_time | end_time tour_numb | start_time | end_time
-----------+------------+---------- -----------+------------+----------
6 | 01:00:00 | 16:00:00 1 | 09:00:00 | 12:00:00
8 | 09:00:00 | 11:30:00 2 | 09:00:00 | 11:30:00
9 | 10:00:00 | 14:00:00 3 | 09:00:00 | 12:30:00
10 | 09:00:00 | 12:00:00 4 | 12:00:00 | 15:00:00
7 | 12:00:00 | 15:30:00 5 | 13:00:00 | 17:00:00
Figure 2-9: Source tables for the Θ-join examples
To determine which pairs of outings you could do on the same
day, you need to find pairs of outings that satisfy either of the
following conditions:
hiking.end_time < biking.start_time
biking.end_time < hiking.start_time
A Θ-join over either of those conditions will do the trick, pro-
ducing the result tables in Figure 2-10. The top result table
contains pairs of outings where hiking is done first; the middle
result table contains pairs of outings where biking is done first.
If you want all the possibilities in the same table, a union op-
eration will combine them, as in the bottom result table. An-
other way to generate the combined table is to use a complex
join condition in the Θ-join:
hiking.end_time < biking.start_time OR
biking.end_time < hiking.start_time
Note: As with the more restrictive equi-join, the “start” table for
a Θ-join does not matter. The result will be the same either way.
An outer join (as opposed to the inner joins we have been con-
Outer Joins sidering so far) is a join that includes rows in a result table even
though there may not be a match between rows in the two
tables being joined. Wherever the DBMS can’t match rows, it
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Join 51
hiking JOIN biking OVER hiking.end_time < biking.start_time GIVING hiking_first
tour_numb | start_time | end_time | tour_numb | start_time | end_time
-----------+------------+----------+-----------+------------+----------
4 | 12:00:00 | 15:00:00 | 8 | 09:00:00 | 11:30:00
5 | 13:00:00 | 17:00:00 | 8 | 09:00:00 | 11:30:00
5 | 13:00:00 | 17:00:00 | 10 | 09:00:00 | 12:00:00
hiking JOIN biking OVER biking.end_time < hiking.start_time gIVING biking_first
tour_numb | start_time | end_time | tour_numb | start_time | end_time
-----------+------------+----------+-----------+------------+----------
2 | 09:00:00 | 11:30:00 | 7 | 12:00:00 | 15:30:00
Figure 2-10: The results of Θ-joins of the tables in Figure 2-9
places nulls in the columns for which no data exist. The result
i ing OIN b i g OVER iking nd time < iki g st
may therefore not be a legal relation, because it may not have
a primary key. However, because the query’s result table is t
t _ mb | st rt m | d im r b |
a
virtual table that ---- -- stored -- the --+--- -- --+ no
--- --- --+ is never ---+- in - database, having --
primary key does not 00:00 a data integrity problem.
4 | 1 present 1 00:00 |
0 |
| 7 0
Why might someone want to
7 | 12: :00
perform an
outer join? An em-
| 15 30 0 09
ployee of the rare book store, for example, might want to see
the names of all customers along with the books ordered in the
last week. An inner join of customer to sale would eliminate
those customers who had not purchased anything during the
previous week. However, an outer join will include all custom-
ers, placing nulls in the sale data columns for the customers
who have not ordered. An outer join therefore not only shows
you matching data but also tells you where matching data do
not exist.
There are really three types of outer join, which vary depend-
ing the table or tables from which you want to include rows
that have no matches.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

52 Chapter 2: Relational Algebra
The Left Outer Join The left outer join includes all rows from the first table in the
join expression
Table1 LEFT OUTER JOIN table2 GIVING
result_table
For example, if we use the data from the tables in Figure 2-5
and perform the left outer join as
customer LEFT OUTER JOIN sale GIVING
left_outer_join_result
then the result will appear as in Figure 2-11: There is a row for
every row in customer. For the rows that don’t have orders, the
columns that come from sale have been filled with nulls.
The Right Outer Join The right outer join is the precise opposite of the left outer
join. It includes all rows from the table on the right of the
outer join operator. If you perform
customer RIGHT OUTER JOIN sale GIVING
right_outer_join_result
using the data from Figure 2-5, the result will be the same as
an inner join of the two tables. This occurs because there are
no rows in sale that don’t appear in customer. However, if you
reverse the order of the tables, as in
sale RIGHT OUTER JOIN customer GIVING
right_outer_join_result
you end up with the same data as Figure 2-11.
Choosing a Right versus As you have just read, outer joins are directional: the result
Left Outer Join depends on the order of the tables in the command. (This is
in direct contrast to an inner join, which produces the same
result regardless of the order of the tables.) Assuming that you
are performing an outer join on two tables that have a primary
key–foreign key relationship, then the result of left and right
outer joins on those tables is predictable (see Table 2-1). Refer-
ential integrity ensures that no rows from a table containing a
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

54 Chapter 2: Relational Algebra
Table 2-1 The effect of left and right outer joins on tables with a primary key–foreign key relationship
Outer Join Format Outer Join Result
primary_key_table LEFT OUTER JOIN foreign_key_table All rows from primary key
table retained
foreign_key_table LEFT OUTER JOIN primary_key_table Same as inner join
primary_key_table RIGHT OUTER JOIN foreign_key_table Same as inner join
foreign_key_table RIGHT OUTER JOIN primary_key_table All rows from primary key
table retained
foreign key will ever be omitted from a join with the table that
contains the referenced primary key. Therefore, a left outer join
where the foreign key table is on the left of the operator and a
right outer join where the foreign key table is on the right of
the operator are no different from an inner join.
The Full Outer Join When choosing between a left and a right outer join, you
therefore need to pay attention to which table will appear on
which side of the operator. If the outer join is to produce a
result different from that of an inner join, then the table con-
taining the primary key must appear on the side that matches
the name of the operator.
A full outer join includes all rows from both tables, filling in
rows with nulls where necessary. If the two tables have a pri-
mary key–foreign key relationship, then the result will be the
same as that of either a left outer join when the primary key
table is on the left of the operator or a right outer join when
the primary key table is on the right side of the operator. In the
case of the full outer join, it does not matter on which side of
the operator the primary key table appears; all rows from the
primary key table will be retained.
Valid versus Invalid To this point, all of the joins you have seen have involved
tables with a primary key–foreign key relationship. These are
Joins
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Join 55
the most typical types of join and always produce valid re-
sult tables. In contrast, most joins between tables that do not
have a primary key–foreign key relationship are not valid. This
means that the result tables contain information that is not
represented in the database, conveying misinformation to the
user. Invalid joins are therefore far more dangerous than mean-
ingless projections.
As an example, let’s temporarily add a table to the rare book
store database. The purpose of the table is to indicate the
source from which the store acquired a volume. Over time, the
same book (different volumes) may come from more than one
source. The table has the following structure:
book_sources (isbn, source_name)
Someone looking at this table and the book table might con-
clude that because the two tables have a matching column
(isbn) it makes sense to join the tables to find out the source
of every volume that the store has ever had in inventory. Un-
fortunately, this is not the information that the result table will
contain.
To keep the result table to a reasonable length, we’ll work with
an abbreviated book_sources table that doesn’t contain sources
for all volumes (Figure 2-12). Let’s assume that we go ahead
and join the tables over the ISBN. The result table (without
columns that aren’t of interest to the join itself ) can be found
in Figure 2-13.
If the store has ever obtained volumes with the same ISBN
from different sources, there will be multiple rows for that
ISBN in the book_sources table. Although this doesn’t give us a
great deal of meaningful information, in and of itself the table
is valid. However, when we look at the result of the join with
the volume table, the data in the result table contradict what
is in book_sources. For example, the first two rows in the re-
sult table have the same inventory ID number, yet come from
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

56 Chapter 2: Relational Algebra
isbn | source_name
-------------------+---------------------
978-1-11111-111-1 | Tom Anderson
978-1-11111-111-1 | Church rummage sale
978-1-11111-118-1 | South Street Market
978-1-11111-118-1 | Church rummage sale
978-1-11111-118-1 | Betty Jones
978-1-11111-120-1 | Tom Anderson
978-1-11111-120-1 | Betty Jones
978-1-11111-126-1 | Church rummage sale
978-1-11111-126-1 | Betty Jones
978-1-11111-125-1 | Tom Anderson
978-1-11111-125-1 | South Street Market
978-1-11111-125-1 | Hendersons
978-1-11111-125-1 | Neverland Books
978-1-11111-130-1 | Tom Anderson
978-1-11111-130-1 | Hendersons
Figure 2-12: The book_sources table
different sources. How can the same volume come from two
places? That is physically impossible. This invalid join there-
fore implies facts that simply cannot be true.
The reason this join is invalid is that the two columns over
which the join is performed are not in a primary key–foreign
key relationship. In fact, in both tables the isbn column is a
foreign key that references the primary key of the book table.
Are joins between tables that do not have a primary key–for-
eign key relationship ever valid? On occasion, they are, in par-
ticular if you are joining two tables with the same primary key.
You will see an example of this type of join when we discuss
joining a table to itself when a predicate requires that multiple
rows exist before any are placed in a result table.
For another example, assume that you want to create a table to
store data about your employees:
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

58 Chapter 2: Relational Algebra
result table will contain rows only for the manager because
employees without rows in the managers table will be left out
of the join. There will be no spurious rows such as those we got
when we joined the volume and book_sources tables. This join
therefore is valid.
Note: Although the id_numb column in the managers table
technically is not a foreign key referencing employees, most data-
bases using such a design would nonetheless include a constraint
that forced the presence of a matching row in employees for every
manager.
The bottom line is that you need to be very careful when per-
forming joins between tables that do not have a primary key–
foreign key relationship. Although such joins are not always
invalid, in most cases they will be.
Difference Among the most powerful database queries are those phrased
in the negative, such as “show me all the customers who have
not purchased from us in the past year.” This type of query is
particularly tricky because it asking for data that are not in the
database. The rare book store has data about customers who
have purchased, but not those who have not. The only way to
perform such a query is to request the DBMS to use the dif-
ference operation.
Difference retrieves all rows that are in one table but not in
another. For example, if you have a table that contains all your
products and another that contains products that have been
purchased the expression—
all_products MINUS products_that_have_been_
purchased GIVING not_purchased
—is the products that have not been purchased. When you re-
move the products that have been purchased from all products,
what are left are the products that have not been purchased.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Intersect 59
The difference operation looks at entire rows when it makes
the decision whether to include a row in the result table. This
means that the two source tables must be union compatible.
Assume that the all_products table has two columns—prod_
numb and product_name—and the products_that_have_been_
purchased table also has two columns—prod_numb and order_
numb. Because they don’t have the same columns, the tables
aren’t union-compatible.
As you can see from Figure 2-14, this means that a DBMS
must first perform two projections to generate the union-com-
patible tables before it can perform the difference. In this case,
the operation needs to retain the product number. Once the
projections into union-compatible tables exist, the DBMS can
perform the difference.
As mentioned earlier in this chapter, to be considered rela-
tionally complete a DBMS must support restrict, project, join,
Intersect
union, and difference. Virtually every query can be satisfied
using a sequence of those five operations. However, one other
operation is usually included in the relational algebra specifica-
tion: intersect.
In one sense, the intersect operation is the opposite of union.
Union produces a result containing all rows that appear in ei-
ther relation, while intersect produces a result containing all
rows that appear in both relations. Intersection can therefore
only be performed on two union-compatible relations.
Assume, for example, that the rare book store receives data
listing volumes in a private collection that are being offered for
sale. We can find out which volumes are already in the store’s
inventory using an intersect operation:
books_in_inventory INTERSECT books_for_sale
GIVING already_have
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Divide 61
As you can see in Figure 2-15, the first step in the process is
to use the project operation to create union-compatible opera-
tions. Then an intersect will provide the required result. (Col-
umns that are not a part of the operation have been omitted so
that the tables will fit on the book page.)
Note: A join over the concatenation of all the columns in the two
tables produces the same result as an intersect.
An eighth relational algebra operation—divide—is often in-
cluded with the operations you have seen in this chapter. It
Divide
can be used for queries that need to have multiple rows in the
same source table for a row to be included in the result table.
Assume, for example, that the rare book store wants a list of
sales on which two specific volumes have appeared.
There are many forms of the divide operation, all of which ex-
cept the simplest are extremely complex. To set up the simplest
form you need two relations, one with two columns (a binary
relation) and one with a single column (a unary relation). The
binary relation has a column that contains the values that will
be placed in the result of the query (in our example, a sale ID)
and a column for the values to be queried (in our example, the
ISBN of the volume). This relation is created by taking a pro-
jection from the source table (in this case, the volume table).
The unary relation has the column being queried (the ISBN).
It is loaded with a row for each value that must be matched in
the binary table. A sale ID will be placed in the result table for
all sales that contain ISBNs that match all of the values in the
unary table. If there are two ISBNs in the unary table, then
there must be a row for each of them with the same sale ID in
the binary table to include the sale ID in the result. If we were
to load the unary table with three ISBNs, then three matching
rows would be required.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Divide 63
You can get the same result as a divide using multiple restricts
and joins. In our example, you would restrict the volume table
twice, once for the first ISBN and once for the second. Then
you would join the tables over the sale ID. Only those sales
that had rows in both of the tables being joined would end up
in the result table.
Because divide can be performed fairly easily with restrict and
join, DBMSs generally do not implement it directly.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.