1 Introduction to Databases 2 2 Basic Relational Data Model 11
Download 1.01 Mb.
|
- Navigate this page:
- 10.5Library Functions
10.4The Prefix ALLThe prefix ALL can be applied to example elements. The occurrence of such an element in an arbitrary query row of an arbitrary relation denotes a set of values such that each, together with a particular instantiation of other items in the row, matches a tuple of the relation. As an example, consider the following relation and query: F In this case, there is only one other item in the query row: another element X. The set of values denoted by ‘All.Y’ therefore needs to be determined for each value that X takes. Thus,
If the query items had been prefixed with ‘P.’, the result displayed would be:
I  n the simplest case, a query row contains only one element prefixed with ALL. In this case, the element simply denotes the set of values in the corresponding domain. This is illustrated in Figure 10 -19 below. Figure 10-19 Simple use of ALL The use of ALL is more interesting when it involves multitable queries. For example, combining the query in Figure 10 -18 and Figure 10 -19 into a single query, we effectively restrict X to just the value 1. This is because ALL.Y occurs in both tables and must denote the same set, and the only set satisfying this is {1,2}. It should be clear now that ALL is used in QBE in the same way that a universal quantifier is used in relational calculus with domain variables. To highlight this, consider the query: “Get the names of customers who bought all types of the company’s product” T Figure 10-20 The query “Get the names of customers who bought all types of the company’s product” One final word about ALL: it does not remove duplicate values, in contrast to an unprefixed element which will return only unique matching values. This is illustrated in Figure 10 -21 below. We shall see in the next section how this property is used (if fact, is necessary) in order to answer certain classes of practical queries. 
Figure 10-21 ALL does not remove duplicates! 10.5Library FunctionsAs with SQL, QBE also provides arithmetic operations and a number of built-in functions which are necessary to manipulate the values in ways not otherwise within the scope of relational calculus, eg. to count the number of occurrences of returned values or to sum them up. As you may expect by now, these operations are provided in the form of prefixes. For example, suppose we wish to know how many transactions were related to the purchase of a particular product, say product number 1. We can extract, for example, all customer numbers in transactions involving product number 1:
But what we are really interested in is counting the number of such values. QBE allows us to do this with the prefix CNT (equivalent to the function COUNT in SQL), which counts the number of values matching the element it prefixes. Thus the same query above, different only in the addition of the CNT prefix, achieves the desired result:
Note that the use of ALL is necessary. If the example element was simply “P.CNT.X”, the result would be 2! This is because without the ALL prefix, the values matching the element X are returned with duplicate values removed (as illustrated earlier in Figure 10 -21). Another frequently used function is SUM, which sums up the values matching the example element it prefixes. Suppose, we wish to know the total number of product number 1 that has been sold. Instead of counting the number of customers that purchased it, we sum instead the quantities recorded in the relevant transactions. Thus:
QBE also allows us to group tuples in a relation based on a specified example element. That is, tuples with the same value of the example element are collected into one group (there will be as many groups as there are distinct values matching the example element). Grouping is specified using the G prefix (this is similar to the ‘Group By’ clause in SQL). Thus:
Aritmetic functions may be applied to groups. Thus, if we wanted to know the total number of items purchased by each customer, we can modify the above query as follows:
Groups may additionally be selected based on conditions that are specified in an additional column (this corresponds to the ‘Having clause’ of SQL). This additional conditions column may be created by means of a special menu item in the QBE interface. Thus, if we are only interested in finding customers who have purchased more than 45 items, our query would be as follows:
In summary, grouping and arithmetic functions can be used in combination to obtain useful derived values from the database. Directory: scripts scripts -> Swiver by Piers Beckley Stef Morgan scripts -> 1 Introduction 2 2 Software Engineering and Software Paradigms 5 scripts -> Theatricals 229 West 28th Street • 11th Floor • New York, ny 10001 scripts -> Host Programming scripts -> Googly written by Mehul Oza May 11, 2009 int. Day dressing room of cricketers scripts -> 4th Revision 2009 Rico Alago scripts -> The Umbrella Academy: Trade 1 Apocalypse Suite Based on the Comic Book Series by Gabriel Ba and Gerard Way scripts -> Script box for new patient green light case exceptancetm Download 1.01 Mb. Share with your friends:
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
