On the Utility of Adding An Abstract Domain and Attribute Paths to SQL

Albeit its popularity today, RDBMS and the relational model still have many limitations. For example, one needs to pay premature attention to naming issues in the schema designing phase; and the syntax for conjunctive queries is verbose and redundant, especially for multi-table joins and composite primary/foreign keys. In this thesis, we introduce and explain the method to handle and resolve these issues that is proposed by Borgida, Toman, and Weddell: the conceptual schema that supports abstract relations and attributes, and an extended query language SQLpath built on top of standard SQL that supports the usage of attribute paths and abstract attributes in queries. We demonstrate a systematic approach to map a database schema expressed in the relational model to the abstract relational model and illustrate how to write SQLpath queries with attribute paths to solve query problems involving complex table joins. This thesis can serve as both an introduction and tutorial to abstract database modelling and the SQLpath query language. Additionally, we performed an empirical experiment to evaluate the performance of SQLpath when solving real database query problems by employing students with prior experience with SQL to read and write SQLpath queries and recorded their accuracy and time consumption against usage of regular SQL. The result of this experiment is presented in this thesis, including a statistical analysis of the results. In short, we uncover evidence that SQLpath is more efficient to use for both reading and writing conjunctive and alike queries, especially for non-trivial cases where multiple constraints were required. However, while SQLpath can hide explicit table joins when writing queries spanning multiple intermediate tables, whether this benefit can make users produce more accurate results still remains unclear as we were not able to draw any conclusion from collected data due to lack of statistical significance

Compiling Equality in an Abstract Relational Model via Preference Tables and Translation Tables

Borgida et al. have introduced a refinement to the relational model (RM) [1] which they call the abstract relational model (ARM) that extends the former in the following three ways: 1. the addition of a new abstract domain eid of entity identifiers to Structured Query Language (SQL) built-in concrete domains; 2. a capacity to resolve reference issues via PRIMARY KEY clauses is replaced by a new domain specific language for referring expression types; and 3. terms in SQL of the form “v.A” can now have the form “v.A1. · · · .Ak” to more compactly encode navigation over foreign keys, thus yielding the language SQLP. They have also proposed an algorithm for mapping ARM schemata to corresponding RM schemata via referring expression types and to subsequently map SQLP queries over the former to corresponding SQL queries over the latter, again via referring expression types. This mapping system relies on introducing so-called preference tables to enable coercion between alternative primary keys. Such tables, however, fail to account for circumstances in which explicit translation tables can exist to map between such alternatives in order to satisfy programmer intentions. In this thesis, we remedy this by extending their algorithm to enable the generation of such translation tables and their use in compiling SQLP