# Object-Relational Algebra 3: The Series Join Function

# Object-Relational Algebra 3: The Series Join Function

Join the DZone community and get the full member experience.

Join For FreeMariaDB TX**,** proven in production and driven by the community, is a complete database solution for any and every enterprise — a modern database for modern applications.

The second important addition I would make to relational algebra to give it object-relational capabilities is what I call a "series join" operation. A series join only makes sense in object-relational approaches because the output can be minimal and yet certain functional dependencies on that output can be readily defined.

I use the capital sigma Σ to refer to a series join, acknowledging that the lower case sigma refers to the select operation and so there is a partial conflict.

A series join takes a theta condition much like any other join, but this theta condition operates on the input relation to the series join. The ut set is joined to itself in the first iteration and then to the output set of the previous iteration in subsequent iterations. This is repeated until the output set does not change with subsequent iterations. in a finite data set, the mappings will also be finite. An optional subscript provides a starting set of values in the input relation and an optional superscript provides a maximum iteration depth.

The output is set of tuples of (o1, o2) where o1 is the initial object's key or reference and o2 is the linked to object's key or reference. From here the following functional dependencies arise: path can be used to trace a path (how we get from one record to another) and depth (how many iterations we have to go to reach the destintion record) are two obvious ones.

A series join provides a useful way to express transitive operations. This allows, for example, binary transitive closure to be expressed and tested because one can generate all possible paths from one node on a graph up until the point where they loop back on themselves.

Series join operations in the SQL world are roughly approximated by the CONNECT BY and WITH RECURSIVE constructs, both of which are typically used to construct a finite series of self-joins. However there are key differences too. In my model we are less worried about the tuple membership than what we can clearly derive from a series join.

Please pardon my layout since I am not quite sure how to make mathematical equations display perfectly in blogger.

Suppose we have a relation employee.

We might have reports = employee Σ

_{1}

^{3}with a theta condition of id θ manager and this would provide a list of the direct reports to this employee, their direct reports, and their direct reports. We can also express certain functional dependencies, such as depth(reports), which is between 0 and 3, and path(reports) which will show the chain of command between the report and the employee. If reports = employee Σ

_{1}and employee 1 is the CEO, then we get the entire organizational chart of the business.

Series joins allow us to do graph traversal, hierarchical searches, and more in an OR database, and approximations of this have been implemented in the relational model. They are mathematically clear, clean, avoiding magic operations and solve a great number of problems.

MariaDB AX is an open source database for modern analytics: distributed, columnar and easy to use.

Published at DZone with permission of Chris Travers , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

## {{ parent.title || parent.header.title}}

## {{ parent.tldr }}

## {{ parent.linkDescription }}

{{ parent.urlSource.name }}