RelaX - relational algebra calculator

calculates any relational algebra statement like ( σ a > 42 ( A ) ) ⋈ ( π a, b ( B ) ) on a set of relations.

Databases and Information Systems (DBIS) Institute of Computer Science - University of Innsbruck University of Innsbruck

get started

What is the relational algebra calculator?

If you want to learn SQL you take a database system and try some queries.
But if you want to learn relational algebra what do you use? Pen and paper?

The relational algebra calculator helps you learn relational algebra (RelAlg) by executing it.

Subjects =
suId name
0 Computing
1 Maths
2 English
3 History
Students =
stId name
1 John
2 Mike
3 Lisa
4 Julia
Marks =
suId stId mark
0 1 A
0 2 B
0 3 C
0 4 A
2 2 A
3 2 F
4 1 C

π studentName, subjectName, mark ( ρ studentName←st.name, subjectName←su.name ( ( ( ρ su ( Subjects ) ) ⨝ ( σ mark≥'C' ( ρ m ( Marks ) ) ) ) ⨝ m.stId=st.stId ( ρ st ( Students ) ) ) )
=

Calculator features:

  • supports most common operators
    • projection
    • selection
    • rename relations
    • rename columns
    • group by
    • intersect
    • union
    • set difference
    • cross join
    • theta join
    • natural join
    • natural left outer join
    • natural right outer join
    • natural full outer join
    • theta left outer join
    • theta right outer join
    • theta full outer join
    • left semi join
    • right semi join
    • anti join join
    • order by
  • runs in any modern browser. no plugins needed
  • text based approach. lets you write RelAlg as easy as SQL
  • code editor with syntax highlighting and code completion
  • pre defined sets of relations
  • visualize statement in a operator tree
  • plain text alternatives for special symbols like σ or ⋈
  • variables can be used to simplify expressions
  • new temporal relations can be declared in the statement
  • sql like comments
  • arbitrary boolean expressions in conditions
  • operations keep original order for better traceability
  • translates simple SQL-statements to RelAlg
    • no support for correlated sub-statements

Available data

You can either use one of the following datasets or create a new one.
Name Source Language
Kemper Datenbanksysteme Alfons Kemper, André Eickler: Datenbanksysteme: Eine Einführung 8. Auflage, Seite 84, Abbildung 3.8 de
UIBK - KursDB Tables from and for the lecture Databases: Foundations, Data Models and System Concepts - University of Innsbruck chapter 3 de
UIBK - R, S, T Tables from and for the lecture Databases: Foundations, Data Models and System Concepts - University of Innsbruck chapter 3 de
The Complete Book - Exercise 2.4.1 Sample Data from Database Systems The Complete Book 2nd Edition by Hector Garcia-Molina, Jeff Ullman, and Jennifer Widom: Exercise 2.4.1 Page 52-55 en
The Complete Book - Exercise 2.4.3 Sample Data from Database Systems The Complete Book 2nd Edition by Hector Garcia-Molina, Jeff Ullman, and Jennifer Widom: Exercise 2.4.1 Page 55-57 en

What it is not:

The tool is not meant to be a full database system. The goal of the implementation was to create a tool to support people to learn RelAlg.

  Pen and Paper relational algebra calculator MySQL
SQL support yes :-) basic SQL yes
relational algebra yes yes no
use variables for RelAlg yes yes no
large datasets no no yes
query plan no yes no
intermediate results no yes no
iterative working no yes yes

Changelog

  • 0.19
    • added datepicker to quickly insert a date literal
    • added spanish translation provided by Erick Delfin
  • 0.18
    • added support for the LIKE-operator for SQL and relational algebra
      e.g. pi 'abc' like 'a%'->x R
    • added translation support for the calculator using i18next. This allows easy translation of the main calculator into other languages.
    • calculator is now available in german
    • ported project to ES2015 (it now gets transpiled to ES5 and packed using Babel.js and Browserify)
  • 0.17
    • fixed bug: inline-table-editor not working
    • fixed bug: formula for !a was not working
  • 0.16
  • 0.15
    • added support for hour(), minute() and second()
    • added support for now()
      e.g. pi hour(now())->x, minute(now())->y, second(now())->z ( R )
    • added support for n-ary concat
      e.g. pi concat(b, '_', c)->x R
    • added support for WITH-clauses for SQL
  • 0.14
    • improve error message for theta-joins with conflicting columns
    • improve error message and added example for assignments without query error
    • bugfix: calclulator-tour did not work correctly for Edge on Windows 10
  • 0.13
    • updated to CodeMirror version 5.1 with "experimental mobile support"
    • added support for COALESCE()
      e.g. pi coalesce(a, b, 1)->x R, select coalesce(a, b, 1) as x from R
    • added support for CASE WHEN
      e.g. select case when a > 3 then a+1 else a end as x from R
    • added support for complex union/intersect/except statements for SQL
      e.g. (select * from S union ( select * from T except select * from T )) order by 1
  • 0.12
    • added support for more complex FROM-clauses for SQL
      e.g. select distinct * from R, (S natural join T)
    • simplify and link the syntax diagrams at the help page
  • 0.11
    • added a tour to explain the main features of the tool to new users (using Bootstrap Tour)
    • added reference for SQL in the help section
    • added support for USING clause for joins for SQL
    • added support FETCH FIRST syntax (SQL:2008) for SQL
      e.g. select * from R offset 2 rows fetch first 10 rows only
    • allow DISTINCT on set operators for SQL
    • bugfix: having should be allowed without group by if aggregation is used
  • 0.10
    • added support for arithmetic expressions and functions for SQL
      e.g. select distinct a+2 as x from R where length(b) > 2
    • show warnings instead of errors when not using distinct or using all on set operators in SQL
  • 0.9
    • added support for arithmetic operators and functions in (e.g boolean) expressions for relational algebra
    • projection supports expressions
    • removed magic ROWNUM column; ROWNUM() is now a function
    • tutorials for users and maintainers
    • new braces handling in formula generation (braces are only placed if necessary)
    • bugfix: rename not existent column was silently ignored
    • syntax of dates has changed 1970-01-01 could not be used any more because it is ambiguous (arithmetic operaton or date). The SQL notation (date('1970-01-01')) is now used for the relational algebra mode.
  • 0.8
    • grid editor for inline relations
    • sql-dump import (beta)
    • group editor
    • changed the basic structure of the editors (internally)
    • duplicate rows are removed in every step

Who?

The relational algebra calculator was created by Johannes Kessler BSc at Databases and Information Systems Group at the Institute of Computer Science at the University of Innsbruck under supervision of Michael Tschuggnall PhD and Prof. Dr. Günther Specht

External resources

This tool was not written from scratch but many different external resources/frameworks/projects/libs are used.

This is a list of resources/frameworks/projects/libs used for this tool (in alphabetical order) to give credit where credit is due and guide anyone interested to them without having to look through the code.