RelaX - relational algebra calculator
calculates any relational algebra statement like
( σ _{a > 42} ( A ) ) ⋈ ( π _{a, b} ( B ) )
on a set of relations.
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 ) ) ) )
=
execute
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 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.