RelaX - relational algebra calculator

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

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 ) ) ) )

st.studentName	su.subjectName	m.mark
Lisa	Computing	C
Mike	History	F

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 fromDatabase 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 fromDatabase 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.20
- added option to automatically replace operators in relational algebra: all plaintext-syntax operators get replaced with the equivalent mathematical symbol or vice versa.
0.19
- added datepicker to quickly insert a date literal
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.jsand Browserify)
0.17
- fixed bug: inline-table-editor not working
- fixed bug: formula for !a was not working
0.16
- disallow relational algebra keywords as column-/relation-names
- fixed precedence for CASE-WHEN-expressions
- added support for the SQL-92 ||-concat operator
- added except as alternative syntax for the relational algebra set-difference operator
- fixed bug where A=R join S A was interpreted asA=(R join S A) instead of A=(R join S) A.
  see help page for more information
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: calculator-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.