1 Introduction to Databases 2 2 Basic Relational Data Model 11

10.3Example Elements

1. 
   Any value matches a blank query item or a variable
   
2. 
   A value matches a comparison item if it satisfies the specified comparison
   

F
igure 10-12

In extracting facts from several relations that share attribute domains, example elements are the key to finding related target tuples from the different relations. Consider the query:

“Get the names and phone numbers of customers that have purchased both product number 1 and product number 2”.

F
igure 10-13 Example elements over several relations

The Transaction relation has part of the information we are after. Specifically, we look for records of purchase of each item by the same customer, ie. a tuple where the product number is 1, another where the product number is 2, but both with the same customer number. The entries in the Transaction template in Figure 10-13 capture this requirement.

However, this tells us only the customer number (the instantiation of X). Information about the customer’s name and phone number must be obtained from the Customer relation. We need to ensure, though, that these values are obtained from a customer tuple that represents the same customer found in the Transaction relation. In QBE, this is simply achieved by specifying the same example element X in the customer number column of the Customer relation (as shown in the Customer template of Figure 10-13).

The query in Figure 10-13 may be evaluated, assuming the following extensions of Transaction and Customer, as follows.

Transaction					Customer
C#	P#	Date	Qnt		C#	Cname	Ccity	Cphone
1	1	21.01	20		1	Codd	London	2263035
1	2	23.01	30		2	Martin	Paris	5555910
2	1	26.01	25		3	Deen	London	2234391
3	2	29.01	20

1. 
   The subquery in the first row of the Transaction template is matched by the first and third tuples of the Transaction relation, ie. X = {1,2}
   
2. 
   The subquery in the second row of the Transaction template is matched by the second and fourth tuples of the Transaction relation, ie. X = {1,3}
   
3. 
   The result of evaluating the Transaction template is therefore {1,2}  {1,3} = {1}.
   
4. 
   The subquery in the Customer template matches all the tuples in the Customer relation, ie. the entire relation is the result.
   
5. 
   The final result is the intersection, over C#, of the results in (3) and (4), ie. {}
   

In other words, the query can be paraphrased as:

“Get the names and phone numbers of those customers who bought the product CPU”.

The preceding two examples should be enough for the reader to realise that (unadorned) example elements spread across tables are in fact existentially quantified. For example, there may be more than one Transaction tuple that can match the customer number found in Customer, but we don’t care which! The examples also show that, more generally, a QBE query can spread over a number of rows of a single relation and across other relations. A few further examples will serve to highlight QBE’s power and features.

In Figure 10-15, we see a complex-looking QBE query. A closer examination will reveal, however, that within each relation template the rows do not share elements, although the elements are shared across relations. In fact, there are two disjoint sets of rows - one taken from the first row of each relation and the other from the second row of each relation.

The first set is actually equivalent to the QBE query in Figure 10-14.

The second differs only in the specified product (replace ‘CPU’ by ‘VDU’ in the above paraphrased query). By analogy with earlier constructions involving unrelated multiple rows, this type of construction therefore denotes a disjunctive query. In other words, combining the two sets of rows yield the query:

“Get the names and phone numbers of those customers who bought the product CPU or the product VDU”

E
arlier, we’ve seen examples of elements used in multiple rows of the same relation. However, given now an understanding of multi-table queries, such constructions can equivalently be seen as a multi-table query involving the same table! This is shown in Figure 10-16 below.

Example elements may also be negated. Negated elements are written with the prefix ‘!’, eg. !X (read “not X”). The negated form can only be used if there is at least one occurrence of the unnegated element elsewhere in the query. It is then interpreted as matching any corresponding domain value that the unnegated form did not match.

Consider, for example, the illustration in Figure 10-17. There are two parts to the illustration, labelled (a) and (b), each with a query table and an extension of the corresponding relation. For purposes of this example, the two query tables constitute a multi-table query, ie. the example element X is the same one in both. Note, however, that X is negated in (b).

Given the extension of Transaction as shown, the domain values matching the example element X in (a) is {1,2}. Turning now to the subquery in (b), the specification of ‘!X’ in it means that the only tuples that can match it are tuples such that the C# value is not in {1,2}. Given the extension of Customer as shown, this means that only the third tuple matches the example, ie. the answer returned for elements A and B are ‘Deen’ and ‘2234391’ respectively.

F
igure 10-17