REVISED: Sunday, March 3, 2013
You will learn the basic fundamentals of RA.
I. INTRODUCTION
RA is a relational algebra interpreter that translates relational algebra queries into SQL queries, then executes the SQL on a standard relational database system. RA was developed by Prof. Jun Yang at Duke University. RA runs on the SQLite open-source relational database system.
The simplest relational algebra expression is one that returns the contents of a single relation.
Relation and attribute names are case-insensitive; for example, pizzeria is the same as PIZZERIA.
Every operator starts with a backslash (\).
The following is an example of a complex query; it finds all pizzas eaten by at least one person who does not frequent the 'Dominos' pizzeria.
Relation and attribute names are case-insensitive; for example, pizzeria is the same as PIZZERIA.
Every operator starts with a backslash (\).
The following is an example of a complex query; it finds all pizzas eaten by at least one person who does not frequent the 'Dominos' pizzeria.
\project_{pizza} ( ((\project_{name} Person) // all people
\diff (\project_{name} // people who frequent Dominos
\select_{pizzeria='Dominos'} Frequents) \join Eats)) // join with Eats to find pizzas
The syntax is insensitive to whitespace, and queries may span multiple lines.
II. OPERATORS
RA supports the following relational algebra operators:
A. \select_{cond}
\select_{cond} is the relational selection operator. For example, to select people with name Amy or Ben, we write "\select_{name='Amy' or name='Ben'} Person". The syntax for cond follows SQL: string literals can be enclosed in single or double quotes, and boolean operators and, or, and not may be used. Comparison operators <=, <, =, >, >=, and <> work on both string and numeric types.
B. \project_{attr_list}
\project_{attr_list} is the relational projection operator, where attr_list is a comma-separated list of attribute names. For example, to find the pizzas served by Applewood (but without the price information), we would write
C. \cross
\cross is the relational cross-product operator. For example, to compute the cross-product of Person and Frequents, we write "Person \cross Frequents".
D. \join
\join is the relational natural join operator. For example, to join Person(name,age,gender) and Frequents(name,pizzeria) enforcing equality on the shared name attribute, we simply write "Person \join Frequents". Natural join automatically equates all pairs of identically named attributes from its inputs (in this case, name), and outputs only one attribute per matching pair. The schema of the result in our example is (name,age,gender,pizzeria).
E. \join_{cond}
\join_{cond} is the relational theta-join operator. For example, to join the two relations Person(name,age,gender) and Serves(pizzeria,pizza,price) enforcing that the pizza price is lower than the person's age, we write "Person \join_{age>price} Serves". Syntax for cond follows SQL; see above for \select.
F. \union, \diff, And \intersect
\union, \diff, and \intersect are the relational union, difference, and intersection operators, respectively. For example, to compute the union between Person and itself, we write "Person \union Person;", which returns the original Person relation. To compute the difference between Person and itself, we write "Person \diff Person;", which returns the empty relation. To compute the intersection between Person and itself, we write "Person \intersect Person;", which returns the original Person relation.
RA allows these operators to be applied to any two subexpressions that produce an equal number of attributes, even if the corresponding attribute names do not match. This allowance is typical of most SQL implementations but violates the requirements of pure relational algebra. As good practice, and for unambiguous attribute names in the result, we suggest using the \rename operator (next) as needed to enforce matching schemas whenever \union, \diff, or \intersect is used.
G. \rename_{new_attr_name_list}
\rename_{new_attr_name_list} is the relational renaming 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 Person and compute the cross-product of Person with itself, we write "\rename_{name1,age1,gender1} Person \cross \rename_{name2,age2,gender2} Person;".
III. LIMITATIONS
Currently, RA has the following limitations:
A. \rename
\rename only supports renaming of attributes; it does not support renaming of relations.
RA expressions may yield multiple attributes with the same name, but only in the outermost experssion. An error may occur if you try to refer to or even rename such attributes later in the same expression. If a subexpression will yield multiple attributes with the same name, you should use the \rename operator within the subexpression to make the names unique.
C. relName.attrName
The standard relName.attrName notation for referencing an attribute is neither needed nor supported. Using the \rename operator, attribute name names can always be made unambiguous.
D. Attribute Order
As in most SQL implementations, attribute order is relevant in relations and in the result of expressions. This property is significant primarily for set operators, which as mentioned above do not consider attribute names. For example, if we take the \union of relations R(A,B) and S(B,A), the result contains two columns:
(1) The union of the A values in R and the B values in S.
(2) The union of the B values in R and the A values in S.
A good practice is to use the \rename operator to enforce matching schemas on R and S before applying the \union.
E. Error Messages
Error messages in response to ill-formed RA expressions may not be especially meaningful. Recall that RA translates relational algebra expressions into SQL queries. Often an incorrect RA expression simply results in incorrect SQL queries. In these cases, RA just passes back the error messages from the underlying DBMS, without attempting to create RA-specific messages.
--> --> -->
How to Link to My Home Page
It will appear on your website as:"Link to ELCRIC OTTO CIRCLE's Home Page"
