This program provides basic relational algebra operators π, σ, ρ, ∪, ∩, −, ×, and ⋈. All functionality is exposed through your browser's JavaScript console.
This program is not intended to be optimal, fast, or even good.
The algorithms are intentionally naive to assist correctness.
All operations should be pure functions with no side effects.
The Object.freeze JavaScript method is liberally applied to return values.
This attempt at immutability is intended to show that all functions are pure.
Three very basic tables R, S, and U are given as constant sample data.
Additionally, this program generates tables of random actors, appearances, and movies.
π: pi(array of objects, array of columns)
σ: sigma(array of objects, condition function)
ρ: rho(array of objects, object mapping old to new column names)
∪: union(array of objects, array of objects)
∩: intersection(array of objects, array of objects)
−: subtract(array of objects, array of objects)
×: cross(array of objects, array of objects)
⋈:
join(array of objects, array of objects, condition function, [object mapping to to new column names])
⋈: naturalJoin(array of objects, array of objects, joining variable name)
R = [{'a': 1, 'b': 2, 'c': 3}, {'a': 1, 'b': 2, 'c': 4}, {'a': 1, 'b': 2, 'c': 5}, {'a': 61, 'b': 662, 'c': 55}]
S = [{'a': 21, 'b': 22, 'c': 33}, {'a': 1, 'b': 2, 'c': 4}, {'a': 1, 'b': 2, 'c': 5}, {'a': 71, 'b': 72, 'c': 75}]
U = [{'a': 1, 'd': 4, 'e': 10}, {'a': 1, 'd': 10, 'e': 100}, {'a': 2, 'd': 2, 'e': 2}]
pi(R, ['a', 'b'])
sigma(R, tuple => tuple.c < 5)
rho(R, {'a': 'alpha', 'b': 'bravo', 'c': 'charlie'})
union(R, S)
intersection(R, S)
subtract(R, S)
cross(R, rho(S, {'a': "a'", 'b': "b'", 'c': "c'"}))
join(R, U, tuple => tuple["a"] < tuple["a'"], {'a': "a'"})
join(R, rho(U, {'a': "a'"}), tuple => tuple["a"] == tuple["a'"])
naturalJoin(R, U, 'a')
The fourth argument r in the join (⋈) function is optional.
This argument is passed to the renaming function (ρ).
It is only needed if the columns in the two relations have overlapping column names.
It is an error for the two relations to contain overlapping columns after renaming.
To more closely mimic the behavior of a SQL JOIN, the join function does not
automatically drop renamed columns.
MySQL localhost:33060+ ssl algebra SQL > SELECT * FROM R INNER JOIN U ON (R.A = U.A);
+---+---+---+---+----+-----+
| A | B | C | A | D | E |
+---+---+---+---+----+-----+
| 1 | 2 | 3 | 1 | 4 | 10 |
| 1 | 2 | 4 | 1 | 4 | 10 |
| 1 | 2 | 5 | 1 | 4 | 10 |
| 1 | 2 | 3 | 1 | 10 | 100 |
| 1 | 2 | 4 | 1 | 10 | 100 |
| 1 | 2 | 5 | 1 | 10 | 100 |
+---+---+---+---+----+-----+
6 rows in set (0.0007 sec)
The equivalent statement in this program follows. The alpha column is left in place.
> join(R, U, tuple => tuple["a"] == tuple["alpha"], {'a': "alpha"})
[{"a":1,"b":2,"c":3,"alpha":1,"d":4,"e":10},
{"a":1,"b":2,"c":3,"alpha":1,"d":10,"e":100},
{"a":1,"b":2,"c":4,"alpha":1,"d":4,"e":10},
{"a":1,"b":2,"c":4,"alpha":1,"d":10,"e":100},
{"a":1,"b":2,"c":5,"alpha":1,"d":4,"e":10},
{"a":1,"b":2,"c":5,"alpha":1,"d":10,"e":100}]
A convenience function combineColumns will return the overlapping columns from two relations.
This can be used in a projection to get only the original column names. For example:
> pi(join(R, U, tuple => tuple["a"] == tuple["alpha"], {'a': "alpha"}), combineColumns(R, U))
[{"a":1,"b":2,"c":3,"d":4,"e":10},
{"a":1,"b":2,"c":3,"d":10,"e":100},
{"a":1,"b":2,"c":4,"d":4,"e":10},
{"a":1,"b":2,"c":4,"d":10,"e":100},
{"a":1,"b":2,"c":5,"d":4,"e":10},
{"a":1,"b":2,"c":5,"d":10,"e":100}]
There is also a commonColumns convenience function used internally.
This program also provides a naturalJoin function which accepts a single column name as its third argument.
This column must be present in both relations.
The convenience functions showRelation and show can be used to present a relation in an HTML table.
The relation must be consistent.
Both the showRelation and show functions accept three arguments:
R to be shown.show returns R unchanged.
This program also provides an exportSql convenience function.
This function attempts to export an array of JavaScript objects (with consistent schema)
to a SQL-like series of commands that might be used to create a table.