View on GitHub

Relational

Educational tool for relational algebra

Downloads | Screenshots | Query language | Optimizations | Shortcuts |

Query language

This page explains the query language supported by relational.

Operators

It has 2 class of operators:

Binary operators

Operators without parameters work on two relations. The syntax for those operators is: relation operator relation.

Symbol Name Example
* product A * B
- difference A - B
union A ∪ B
intersection A ∩ B
÷ division A ÷ B
join A ⋈ B
left outer join A ⧑ B
right outer join A ⧒ B
full outer join A ⧓ B

Unary operators

Operators with parameters work on a single relation, and the result will depend on the passed parameters. The syntax for those is: operator parameters (relation)

Symbol Name Example Note
σ selection σ id==index or rank>3 (A) Expression must be written in python
π projection π name,age (A)
ρ rename ρ old_name➡new_name,age➡old (A)

The language is formally defined here.

Complex queries

In any place where a relation is expected, a query can be used instead. For example, since a*b is a relation itself, you can evaluate π f1,f2 (a*b) ∪ R

You can use parenthesis to change priority: a ⋈ (q ∪ d)

Examples

These are some valid queries.


σage > 25 and rank == weight(A)
σ (name.upper().startswith('J') and age>21 )(people)
Q ⋈ π a,b(A) ⋈ B
ρ id➡i,name➡n(A) - π a,b(π a,b(A)) ∩ σage > 25 or rank = weight(A)
π a,b(π a,b(A))
ρ id➡i,name➡n(π a,b(A))
A ⋈ B

Relational uses automatic casting, so it will try to use an appropriate type for the values.

Dates

When a field contains something like 2007-12-24, this will be considered as a date, and will have a particoular behaviour in selection operations.

Fields

Field Description
intdate String representation of the date
day
month
weekday Day of the week (numeric)
year

Examples

Load the file dates.csv and set d as relation's name.
We select every friday:
σ date.weekday==4(d)

We select every date before 2000:
σ date.year<2000 (d)

We rename the field and do the product, we add 2 days to d and see if it is greater than date
σ d+2>date(ρdate➡d(d)*d)