Using ra
ra is a simple relational algebra interpreter built
on top of IBM DB2. It is implemented by translating relational
algebra queries into SQL queries and executing them on DB2. To use
ra, log into cps116. Then, just type ra
at the shell prompt. You will see the ra> prompt.
You exit the interface by issuing the \quit; command.
Use the \list; command to see what relations are available
for query in your database. The simplest relational query you
can write is one that returns the content of a relation: Just type
relName;, where relName is the name
of the relation. Note that every command/operator should start
with a backslash (\), and every query/command should be
terminated by a semicolon (;).
Relation and attribute names are case-insensitive. For example,
drink is just as good as DRINK. Output from
ra usually shows relation and attribute names in
upper case, however. Attributes can be of a variety of types.
Details are not important; just beware that INTEGER,
SMALLINT, FLOAT, REAL, DOUBLE,
DECIMAL, NUMERIC are for numbers, and CHAR
and VARCHAR are for strings.
Here is an example of a complex query, which returns beers liked
by those drinkers who do not frequent James Joyce Pub:
\project_{beer} (
((\project_{name} // all drinkers
Drinker)
\diff
(\rename_{name} // rename so we can diff
\project_{drinker} // drinkers who frequent JJP
\select_{bar = 'James Joyce Pub'}
Frequents))
\join_{drinker = name} /* join with Likes to find beers */
Likes
);
The syntax is insensitive to white space, and and it is fine to
enter a query on multiple lines; ra will number the
lines (beyond the first one) you enter for the current query.
C/C++/Java-style comments (// and /*...*/) are
supported. Also, ra supports command-line input history
and editing using arrow keys: Up/Down recall previous/next lines,
and Left/Right move within the current line.
ra supports the following relational algebra operators:
- \select_{cond} is the relational selection
operator. For example, to select Drinker tuples with
name Amy or Ben, we can write \select_{name = 'Amy' or name =
'Ben'} Drinker;. Syntax for cond follows SQL. Note that
string literals should be enclosed in single quotes,
and you may use boolean operators and, or, and
not. Comparison operators <=, <,
=, >, >=, and <>
work on both string and numeric types. For string match you can
use the SQL LIKE operator; e.g., \select_{name like
'A%'} drinker; finds all drinkers whose name start with A,
as % is a wildcard character that matches any number of
characters.
- \project_{attr_list} is the relational projection
operator, where attr_list is a comma-separated
list of attribute names. For example, to find out what beers are
served by Talk of the Town (but without the price information),
we can write \project_{bar, beer} (\select_{bar = 'Talk of the
Town'} Serves);.
- \join_{cond} is the relational join operator.
For example, to join Drinker(name, address) and
Frequents(drinker, bar, times_a_week) relations together
using drinker name, we can write Drinker \join_{name = drinker}
Frequents;. Syntax for cond again follows SQL; see
notes on \select for more details.
- \cross is the relational cross product operator.
For example, to compute the cross product of Drinker
and Frequents, we can write Drinker \cross
Frequents;.
- \union and \diff are the relational union
and difference operators. For a trivial example, to compute
the union (or difference) between Drinker and itself,
we can write Drinker \union Drinker; (or Drinker
\diff Drinker;), which would return Drinker itself
(or an empty relation, respectively).
- \rename_{new_attr_name_list} is the relational
rename operator, where new_attr_name_list is a
comma-separated list of new names, one for each attribute of the
input relation. For example, to rename the attributes of relation
Drinker and compute the cross product of Drinker
and itself, we can write \rename_{name1, address1} Drinker
\cross \rename_{name2, address2} Drinker;.
Currently, ra has the following limitations:
- \rename only supports renaming of attributes; it does
not support renaming of the input relation.
- The natural join operator is not supported.
- As a general rule, ra never allows a relational algebra
expression (or subexpression) to produce a result relation with
identically named attributes, because these attributes cannot be
distinguished. In particular, \cross and \join
do not allow input relations with identically named attributes.
For example, Drinker \cross Drinker; is illegal because
it would generate a relation with two name and two
address attributes. Therefore, the attributes need to be
renamed before the cross product. Also, \project cannot
output the same attribute more than once. The error message is
somewhat obscure: "The statement does not include a required
column list."
- Because of the above limitation, the
relName.attrName notation for referencing
an attribute does not work (and is not needed).
- As a general rule, ra would signal an error whenever
it has trouble determining how to name the attributes of a result
relation. In particular, \union and \diff only
work on input relations with attribute lists that are identical
in both type and name. For example, even though Bar
and Beer both have two attributes with compatible string
types, they cannot be unioned because their attribute names are
different; however, \rename_{A, B} Bar \union \rename_{A,
B} Beer; works (never mind what this query means). Also,
\project_{price+1} Serves does not work, not because we
do not support arithmetic operators, but because ra does
not know what to name the resulting attribute. Again, you will
see an obscure error message: "The statement does not include
a required column list."
|