Robert W. Weeks bob.weeks@cox.net, http://members.cox.net/bob.weeks
Most Paradox users are familiar with the use of an example element in a query. Examples serve as placeholders, holding a value so that we can use it again somewhere else in the query. In some ways, example elements are analogous to variables in programming languages.
Sets extend the power of Paradox's example elements. Using sets, you can create example elements that stand for a list of values. You can then use this list somewhere else in the query. If an example is like a variable, sets are similar to arrays in a programming language.
As an example, consider the enrollment records for a college or university. There are three main tables. First, the Student table lists the students' names along with their Student number (abbreviated Sn).
STUDENT Sn Last Name First Name
1 | 1000 | Smith | William |
2 | 1001 | Wilson | Steve |
3 | 1002 | Butler | Susan |
4 | 1003 | Jones | Sarah |
5 | 1004 | Smith | John |
6 | 1005 | Jackson | William |
7 | 1006 | Brown | Arthur |
8 | 1007 | Connors | Joseph |
9 | 1008 | Johnson | Chester |
10 | 1009 | Smith | Albert |
The Section table lists the class sections that the university has offered. Each section as a Section#, along with the offering department, class level, term (spring, fall, summer) and year.
SECTION Section Department Level Term Year
1 | 1 | English | 101 | F | 86 |
2 | 2 | History | 100 | F | 86 |
3 | 3 | Physical Ed | 110 | F | 86 |
4 | 4 | Psychology | 101 | F | 86 |
5 | 5 | Sociology | 101 | F | 86 |
6 | 6 | English | 101 | S | 86 |
7 | 7 | History | 100 | S | 86 |
8 | 8 | Physical Ed | 101 | S | 86 |
9 | 9 | Psychology | 101 | S | 86 |
10 | 10 | Sociology | 101 | S | 86 |
11 | 11 | English | 101 | U | 86 |
12 | 12 | History | 100 | U | 86 |
13 | 13 | Physical Ed | 101 | U | 86 |
14 | 14 | Physical Ed | 110 | U | 86 |
15 | 15 | Sociology | 110 | | 87 |
16 | 16 | English | 110 | F | 87 |
17 | 17 | History | 110 | F | 87 |
18 | 18 | Math | 101 | F | 87 |
19 | 19 | Music | 101 | F | 87 |
20 | 20 | Physics | 101 | F | 87 |
21 | 21 | Psychology | 110 | F | 87 |
22 | 22 | Sociology | 110 | F | 87 |
The Enroll table records when a student (identified by the Sn column) enrolls in a class section (identified by the Section# column). This table also records the grade the student received.
ENROLL Sn Section# Grade
1 | 1000 | 1 | B |
2 | 1000 | 3 | I |
3 | 1000 | 5 | B |
4 | 1000 | 8 | D |
5 | 1000 | 12 | D |
6 | 1000 | 16 | B |
7 | 1000 | 18 | A |
8 | 1000 | 26 | A |
9 | 1000 | 28 | B |
10 | 1000 | 33 | C |
11 | 1000 | 40 | C |
12 | 1000 | 43 | C |
13 | 1000 | 44 | C |
14 | 1000 | 48 | D |
15 | 1000 | 50 | A |
16 | 1000 | 51 | C |
17 | 1000 | 58 | I |
18 | 1001 | 1 | C |
19 | 1001 | 4 | B |
20 | 1001 | 14 | B |
21 | 1001 | 15 | A |
22 | 1001 | 17 | A |
Let's start by asking a simple question: Which students have not taken Physical Ed classes? Without using sets, here's one method of providing solution to this problem. First, query the Section table to produce a list of all section numbers for Physical Ed classes as follows:
SECTION Section Department
|check | Physical Ed |
| | |
| | |
ANSWER Section
1 | 3 |
2 | 8 |
3 | 13 |
4 | 14 |
Now, take the list of values in the Answer table and compare them against the section number values in the Enroll table. We need to perform a separate comparison for each student. If a student has taken one of these classes, the student doesn't qualify as one who has taken no Physical Ed classes. But, if after looking at all rows for a student, we find that none of the values in the Answer table list are found, the student qualifies as one who has taken no Physical Ed classes. We will not present a Paradox solution to this process, as it is difficult to produce without using sets.
Consider this solution, which uses two query forms and the set concept. (An underscore before a word, as in _x, means the example element x. Examples normally appear in reverse video or a different color on the Paradox screen.)
SECTION Section Department
set | _x | Physical Ed |
| | |
| | |
ENROLL Sn Section#
|check | no _x |
| | |
| | |
ANSWER
1 | 1002 |
Student 1002, then, has taken no Physical Ed classes. The query form for the Section table defines the set. Compare this query form with the query form shown in the first solution:
SECTION Section# Department
|check | Physical Ed |
| | |
| | |
There are two differences between these forms: The set version uses the word set to denote that this row of the query form defines a set. The second difference is the use of an example element (_x in this illustration) instead of the checkmark. This is quite startling when you think about it. In the first example, the checkmark produces an Answer table containing a list of values. In the set query, the example element _x represents the same list! This list is often called the defined set.
We can now use this defined set in other tables. Generally, we compare a group (formed with a checkmark) to the example element (representing the defined set) using one of the following set operators:
| Operator | Meaning | Example |
| only | The values in the group contain only members of the defined set. | A(1,2,3) B(1,2,3,4,5) Is A only B? Yes,
as all elements of A are present in B. A(1,2,6) B(1,2,3,4,5) Is A only B? No, because A contains an element (6) that is not present in B. |
| no | None of the values in the group are in the defined set. | A(1,2,3) B(4,5,6,7,8) Is A no B? Yes,
because none of the values in A are present in B. A(1,2,3) B(3,4,5,6,7,8) Is A no B? No, because there is an element of A (3) that is present in B. |
| every | The values in the group include all members of the defined set. The group might have additional members that the defined set doesn't have. | A(1,2,3) B(1,2,3) Is A every B? Yes,
because each element of A is also in B. A(1,2,3,4) B(1,2,3) Is A every B? Yes, because each element of B is in A. A has elements that B doesn't have, but A has every element that B has. A(1,2,3) B(1,2,3,4) Is A every B? No, because A does not contain all elements of B. |
| exactly | The values in the group exactly match the values in the set, one-for-one. | A(1,2,3) B(1,2,3) Is A exactly B? Yes,
because the elements of A and B match exactly. A(1,2) B(1,2,3) Is A exactly B? No, because A and B don't match exactly. A(1,2,3) B(1,2) Is A exactly B? No, because A and B don't match exactly. |
In our example, we used the operator no, because we wanted those students who had taken no Physical Ed classes.
The video tape rental club has members, identified by a member number as in the Member table:
MEMBER Member# Name
1 | 1 | Baker, John |
2 | 2 | Wilson, Jill |
3 | 3 | Smith, Jack |
4 | 4 | Johnson, Bob |
5 | 5 | Fisher, Bart |
6 | 6 | Wilson, Steve |
7 | 7 | McDonald, Joe |
8 | 8 | Nelson, Susan |
The club also has a catalog of tapes available for rental, as shown in the Catalog table:
CATALOG ID# Title Rating ChargeCode
1 | 1 | Crocodile Dundee | R | A |
2 | 2 | Gone With The Wind | G | C |
3 | 3 | Teenage Mutant Ninja Turtles | G | A |
4 | 4 | Dancing With Wolves | PG13 | A |
5 | 5 | Honey, I Shrunk The Kids | G | A |
6 | 6 | Casablanca | | C |
7 | 7 | It's A Wonderful Life | | C |
8 | 8 | City Slickers | PG13 | B |
9 | 9 | Wiseguys | R | B |
10 | 10 | Rocky IV | PG13 | A |
11 | 11 | Rocky III | PG13 | A |
12 | 12 | Rocky II | PG13 | B |
13 | 13 | Rocky | PG13 | C |
14 | 14 | V.I. Warshawski | PG13 | B |
The Tape table contains information about each tape the club owns. The club may have several copies of any given title. In this case, each copy of a title is identified by a copy number.
TAPE ID# Copy# Suppid# PurchaseDate PurchasePrice
1 | 1 | 1 | 1 | 1/01/90 | 55.95 |
2 | 1 | 2 | 1 | 1/01/90 | 55.95 |
3 | 1 | 3 | 1 | 1/01/90 | 55.95 |
4 | 1 | 4 | 2 | 6/01/90 | 50.95 |
5 | 2 | 1 | 3 | 1/23/90 | 69.95 |
6 | 3 | 1 | 3 | 6/05/90 | 29.95 |
7 | 3 | 2 | 3 | 6/05/90 | 29.95 |
8 | 3 | 3 | 3 | 6/05/90 | 29.95 |
9 | 4 | 1 | 1 | 4/01/91 | 59.95 |
10 | 4 | 2 | 1 | 4/01/91 | 59.95 |
11 | 5 | 1 | 2 | 4/05/90 | 59.95 |
12 | 6 | 1 | 2 | 1/05/90 | 59.95 |
13 | 6 | 2 | 2 | 1/05/90 | 59.95 |
14 | 7 | 1 | 3 | 1/05/90 | 59.95 |
15 | 8 | 1 | 1 | 6/01/91 | 59.95 |
16 | 8 | 2 | 1 | 6/01/91 | 59.95 |
17 | 9 | 1 | 3 | 4/15/91 | 69.95 |
18 | 9 | 2 | 3 | 4/15/91 | 69.95 |
19 | 10 | 1 | 3 | 1/15/91 | 69.95 |
20 | 10 | 2 | 3 | 1/15/91 | 69.95 |
21 | 10 | 3 | 2 | 1/17/91 | 79.95 |
22 | 10 | 4 | 2 | 1/17/91 | 79.95 |
The Rental table contains information about actual rentals of tapes. For each rental, we record the member number, ID number, and copy number, along with the date checked out and the date returned.
RENTAL Member# ID# Copy# DateOut DateIn
1 | 1 | 1 | 1 | 7/01/91 | 7/03/91 |
2 | 1 | 3 | 1 | 7/01/91 | 7/08/91 |
3 | 1 | 9 | 1 | 8/01/91 | 8/02/91 |
4 | 2 | 1 | 2 | 7/01/91 | 7/03/91 |
5 | 2 | 10 | 1 | 8/11/91 | 8/13/91 |
6 | 2 | 10 | 1 | 8/15/91 | 8/17/91 |
7 | 2 | 11 | 1 | 8/20/91 | 8/26/91 |
8 | 2 | 12 | 1 | 8/30/91 | 8/31/91 |
9 | 2 | 13 | 1 | 9/11/91 | |
10 | 3 | 2 | 1 | 8/01/91 | 8/03/91 |
11 | 3 | 3 | 1 | 8/01/91 | 8/03/91 |
12 | 3 | 5 | 1 | 8/25/91 | 9/01/91 |
13 | 5 | 1 | 4 | 7/02/91 | 7/05/91 |
14 | 5 | 9 | 1 | 8/02/91 | 8/08/91 |
15 | 6 | 1 | 1 | 12/01/90 | 12/04/90 |
16 | 6 | 2 | 1 | 12/01/90 | 12/04/90 |
17 | 6 | 3 | 1 | 12/01/90 | 12/04/90 |
18 | 6 | 4 | 1 | 12/01/90 | 12/04/90 |
19 | 6 | 5 | 1 | 12/01/90 | 12/04/90 |
20 | 6 | 6 | 1 | 12/02/90 | 12/04/90 |
21 | 6 | 7 | 1 | 1/03/91 | 1/05/91 |
22 | 6 | 8 | 1 | 2/08/91 | 2/09/91 |
Let's start by asking a few questions. What members have yet to rent a tape? (These are members who have received a club membership, but haven't rented any tapes.)
RENTAL Member# ID#
set | _x | |
| | |
| | |
MEMBER Member# Name
| no _x |check |
| | |
| | |
ANSWER Name
1 | Johnson, Bob |
2 | Nelson, Susan |
The query form for the Rental table defines a set of members who have rented tapes. (All members appear in the Member table, but since the Rental table contains records of rentals, that table contains Member# values for only those members who have rented a tape.) In the Member table, we use no as the comparison operator to the set _x. No, as in the previous example, selects those rows or groups that have none of the values in the defined set.
Another example: Who has rented only R-rated movies?
CATALOG ID# Title Rating
set | _x | | R |
| | | |
| | | |
RENTAL Member# ID# Copy#
|check | only _x | |
| | | |
| | | |
ANSWER Member#»
1 | 5 |
The first query form defines a set of movies with an R rating. In the Rental table, we first group the data by Member# by checking that column. Then, for each group, look for groups that contain only members of the set of R-rated movies. If a member has rented a move other than one in the set of R-rated movies, the member isn't one that we're looking for.
Another example: Find the members who have rented all the G-rated movies that we have.
CATALOG ID# Title Rating
set | _x | | G |
| | | |
| | | |
RENTAL Member# ID# Copy#
|check | every _x | |
| | | |
| | | |
ANSWER Member#
1 | 3 |
2 | 6 |
3 | 7 |
The query form for the Catalog table establishes a set of tapes with a G rating. Then, in the Rental table, we ask for those members whose rentals include every member of the set. Every means that the group contains all members of the set (the list of tapes that the member has rented includes all G-rated movies). The member may have rented other than G-rated movies, as the every operator does not care about excess members in the group.
Compare the following example: Here, we wish to find those members who have rented only G-rated movies:
CATALOG ID# Title Rating
set | _x | | G |
| | | |
| | | |
RENTAL Member# ID# Copy#
|check | only _x | |
| | | |
| | | |
ANSWER Member#
1 | 3 |
Again, the first query form establishes the set of G-rated movies. In the Rental table, look for those members where the group contains only members of the set. If a member has rented any movies other than G-rated movies, the member won't qualify. However, the member need not have rented every G-rated movie to qualify. A member who has rented just a single movie, as long as it was a G-rated movie, qualifies.
Are there members who have rented all the G-rated movies, but only G-rated movies?
CATALOG ID# Title Rating
set | _x | | G |
| | | |
| | | |
RENTAL Member# ID# Copy#
|check | exactly _x | |
| | | |
| | | |
ANSWER Member#
1 | 3 |
Here, the comparison operator is exactly, which means that the group and the defined set must match exactly. If member 3 had rented any movie other than a G-rated movie, or had failed to rent all G-rated movies, there would not have been a match.