CPS 116 (Fall 2005):
Introduction to Database Systems

ra is a simple relational algebra interpreter for IBM DB2. It is implemented by translating relational algebra queries into SQL queries and executing them on DB2. To use ra, log into rack40 and set up the DB2 environment. Then, just type ra at the shell prompt. You will see the ra> prompt.

You exit the interface by issuing the \quit; command. The simplest relational query you can write is one that returns the content of a database table---just type tableName;, where tableName is the name of the table. Note that every command/operator should start with a backslash (\), and every query should be terminated by a semicolon (;).

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;. Note that string literals should be enclosed in single quotes, and you may use boolean operators and, or, and not.
• \project_{column_list} is the relational projection operator, where column_list is a comma-separated list of column 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) tables together using drinker name, we can write drinker \join_{name = drinker} frequents;.
• \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 table).
• \rename_{new_column_name_list} is the relational rename operator, where new_column_name_list is a comma-separated list of new names, one for each column of the input table. For example, to rename the columns of table drinker and compute the cross product of drinker and itself, we can write \rename_{name1, address1} drinker \cross \rename_{name2, address2} drinker;.

• ra only supports renaming of columns; it does not support renaming of the input table.
• ra does not support any operation that produces a table with two identically named columns. For example, drinker \cross drinker; is illegal because it would generate a table with two name and address columns. Therefore, the columns need to be renamed before the cross product.
• ra currently does not produce meaningful error messages. Running ra with the -v option will print out the SQL code generated from the relation algebra query, which might be useful in debugging if the user is familiar with SQL.