CPS 116 (Fall 2008):
Introduction to Database Systems

Course Information   Lecture Notes   Assignments   Tentative Syllabus   Programming Notes   Blackboard (Grades)
Getting Started with cps116 Server   Getting Started with Gradiance   DB2 SQL Notes   Using ra   JDBC Notes   Tomcat Notes   XML Notes

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."

Last updated Mon Aug 25 14:18:21 EDT 2008