TransJoin: An Algorithm to Implement Division Operator of Relational Algebra in Structured Query Language

Abstract

Division operator is one of operators in Relational Algebra which is not implemented directly in SQL (Structured Query Language) standard. Therefore it is not be able to retrieve division query results with an SQL statement in the current relational database management systems (RDBMS). Database programmers have to create a complicated query to perform the task. It is the only relational algebra operators which is not implemented in ANSI SQL standard. This research aimed to study and design an algorithm named TransJoin (transformation and join) to implement it in SQL. TransJoin works to relation P(x, y) divided by Q(y) resulted R(x) through grouping and transforming each y attribute becomes P’(x, y’) and Q’(y’) and each x value is a single tuple with y’ is a composite value of y. Then, result R(x) is resulted of joining P’(x, y’) and Q’(y’) relations by matching y’ attribute. TransJoin was implemented in an open source RDBMS SQLLite. TransJoin tested and delivered valid results by comparing of traditional SQL queries and our proposed SQL queries with various data. The research showed that our algorithm is much more efficient by consuming 0.078 milliseconds compared contrasted the traditional query in 1,974.08 milliseconds for 9,991 tuples.