A Simple Approach to SQL Joins in a Relational Algebraic Notation (original) (raw)
Related papers
Note on the structure of joins
Information Systems, 1992
When SQL is used to formulate queries for a relational database, many conditions in the WHERE clause appear to be very predictable. These are the so-called join conditions which indicate how the tables of a database are related. It seems that a system should be able to generate these conditions to a great extent automatically from the knowledge of the database structure. To this end the notion of the structure of a join is introduced and mathematically described as a graph morphism. It turns out to be a generalization of the notion of a natural join. It is claimed that this approach is theoretically elegant and provides in practice a good basis for the development of query generators.
Mapping Relational Algebra Operators into SQL Queries: A Database Case Study
2015
Relational algebra operators and mapping to resulting structured query language (SQL) queries are among the most important concepts and skills for students taking a course in database design and implementation, especially those majoring in IS/IT. The most typical relational algebra operators mapped to foundational SQL include unions and intersections, as well as other relational operators applied to these operators, including differences and various joins. Unfortunately, few textbooks or external resources provide ample opportunity for students to apply the full set of most common relational algebra operators mapped to resulting SQL over a single unified case. Most database textbooks exemplify each separate relational algebra construct to a single, sparse (although visually-stimulating) example, wherein the full set of operators is not near fully exemplified. This paper presents an overview of a case example that exemplifies and maps a more complete set of relational algebra operato...
Query processing techniques in the summary-table-by-example database query language
ACM Transactions on Database Systems, 1989
Summary-Table-by-Example (STBE) is a graphical language suitable for statistical database applications. STBE queries have a hierarchical subquery structure and manipulate summary tables and relations with set-valued attributes. The hierarchical arrangement of STBE queries naturally implies a tuple-by-tuple subquery evaluation strategy (similar to the nested loops join implementation technique) which may not be the best query processing strategy. In this paper we discuss the query processing techniques used in STBE. We first convert an STBE query into an “extended” relational algebra (ERA) expression. Two transformations are introduced to remove the hierarchical arrangement of subqueries so that query optimization is possible. To solve the “empty partition” problem of aggregate function evaluation, directional join (one-sided outer-join) is utilized. We give the algebraic properties of the ERA operators to obtain an “improved” ERA expression. Finally we briefly discuss the generation...
A System to Support Teaching and Learning Relational Database Query Languages and Query Processing
The importance of relational algebra in a database course is widely recognized to facilitate teaching and learning of SQL. From our experience we have also found it very useful for the students to understand the basics of query processing in terms of execution plans. However currently there are no specific tools to make the process of learning relational algebra and execution plans an interesting and stimulating activity. The features of the JRS (Java Relational System) graphical editors of query plans are presented. The graphical editors are used to define and execute queries on a database represented by two kinds of trees: A logical plan of relational algebra, and a physical plan that describes an algorithm to execute a query using the physical operators of the relational DBMS developed in Java as a teaching tool.
Relational queries in a domain based DBMS
ACM SIGMOD Record, 1983
This paper addresses the problem of relational queries processing in the domain based database machine DBMAC. A brief description of the storage organisation of the domain based DBMS is first given. Then the operating principles of the domain based data-model, called D-model, is described through some examples. The central part of the paper deals with the translation of relational queries into operations on objects of the D-model. Objects of the D-model and a set of operations on these objects are first defined : this set S of operations is shown to be complete in that any relational query can be translated into D-model operations belonging to S .Finally we give a method for processing relation queries using D-model operations.The basic advantages to be expected from a domain based physical organization of data are :1. fast equi-join execution,2. a compact representation of intermediate results.The latter should lead to efficient processing of complex queries, provided a powerful pa...
Improving the Query Mode and its Interface for Relational Database Applications
2000
The paper presents an enhanced generic query mode and its interface for any database application. It is intelligent in the sense that it answers most of the possible queries or questions that may arise in the user's mind without the need or support of the application developer. This is not the case in all previous traditional query modes; all of
An application of flexible query interface to relational databases
The use of databases has for long been for the computer elites in the society as well as multinationals that can pay for their services, and these has inhibited the scope and wide use of database applications. The aim of this paper is to develop yet another flexible query interface for relational databases that is user friendly and has the capability to adequately help users work with databases without a thorough knowledge of database programming. It also provides guidelines for users interested in learning the technicalities involved in database query writing. The proposed system uses an object oriented methodology and was implemented using Java programming language.From the result, the system shows a high level of flexibility in database query processing.
Journal of Physics: Conference Series
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 co...