A Tutorial on Visual Representations of Relational Queries

Query formulation is increasingly performed by systems that need to guess a user's intent (e.g. via spoken word interfaces). But how can a user know that the computational agent is returning answers to the "right" query? More generally, given that relational queries can become pretty complicated, how can we help users understand existing relational queries, whether human-generated or automatically generated? Now seems the right moment to revisit a topic that predates the birth of the relational model: developing visual metaphors that help users understand relational queries. This lecture-style tutorial surveys the key visual metaphors developed for visual representations of relational expressions. We will survey the history and state-of-the art of relationally-complete diagrammatic representations of relational queries, discuss the key visual metaphors developed in over a century of investigating diagrammatic languages, and organize the landscape by mapping their used visual alphabets to the syntax and semantics of Relational Algebra (RA) and Relational Calculus (RC).Comment: 4 page tutorial paper at VLDB 2023, tutorial web page with slides to be posted in time: https://northeastern-datalab.github.io/visual-query-representation-tutorial/. arXiv admin note: text overlap with arXiv:2208.0161

Towards a Holistic Integration of Spreadsheets with Databases: A Scalable Storage Engine for Presentational Data Management

Spreadsheet software is the tool of choice for interactive ad-hoc data management, with adoption by billions of users. However, spreadsheets are not scalable, unlike database systems. On the other hand, database systems, while highly scalable, do not support interactivity as a first-class primitive. We are developing DataSpread, to holistically integrate spreadsheets as a front-end interface with databases as a back-end datastore, providing scalability to spreadsheets, and interactivity to databases, an integration we term presentational data management (PDM). In this paper, we make a first step towards this vision: developing a storage engine for PDM, studying how to flexibly represent spreadsheet data within a database and how to support and maintain access by position. We first conduct an extensive survey of spreadsheet use to motivate our functional requirements for a storage engine for PDM. We develop a natural set of mechanisms for flexibly representing spreadsheet data and demonstrate that identifying the optimal representation is NP-Hard; however, we develop an efficient approach to identify the optimal representation from an important and intuitive subclass of representations. We extend our mechanisms with positional access mechanisms that don't suffer from cascading update issues, leading to constant time access and modification performance. We evaluate these representations on a workload of typical spreadsheets and spreadsheet operations, providing up to 20% reduction in storage, and up to 50% reduction in formula evaluation time

Principles of Query Visualization

Query Visualization (QV) is the problem of transforming a given query into a graphical representation that helps humans understand its meaning. This task is notably different from designing a Visual Query Language (VQL) that helps a user compose a query. This article discusses the principles of relational query visualization and its potential for simplifying user interactions with relational data.Comment: 20 pages, 12 figures, preprint for IEEE Data Engineering Bulleti

A scalable direct manipulation engine for position-aware presentational data management

With the explosion of data, large datasets become more common for data analysis. How- ever, existing analytic tools are lack of scalability and large-scale data management tools are lack of interactivity. A lot of data analysis tasks are based on the order of data, we are proposing the very first positional storage engine supporting persistence and maintenance of orders for large datasets and allow direct manipulation on orders. We introduce a sparse monotonic order statistic structure for persisting and maintaining order. We also show how to support multiple orders and optimize the storage. After that, we demonstrate a buffered storage manager to ensure the direct manipulation interactivity. Last, we show our final system DataSpread which is interactive and scalable. In the end, we hope that our solution can point out a potential direction to support data analysis for large-scale data

Multimodal Interface for Human–Robot Collaboration

Human–robot collaboration (HRC) is one of the key aspects of Industry 4.0 (I4.0) and requires intuitive modalities for humans to communicate seamlessly with robots, such as speech, touch, or bodily gestures. However, utilizing these modalities is usually not enough to ensure a good user experience and a consideration of the human factors. Therefore, this paper presents a software component, Multi-Modal Offline and Online Programming (M2O2P), which considers such characteristics and establishes a communication channel with a robot with predefined yet configurable hand gestures. The solution was evaluated within a smart factory use case in the Smart Human Oriented Platform for Connected Factories (SHOP4CF) EU project. The evaluation focused on the effects of the gesture personalization on the perceived workload of the users using NASA-TLX and the usability of the component. The results of the study showed that the personalization of the gestures reduced the physical and mental workload and was preferred by the participants, while overall the workload of the tasks did not significantly differ. Furthermore, the high system usability scale (SUS) score of the application, with a mean of 79.25, indicates the overall usability of the component. Additionally, the gesture recognition accuracy of M2O2P was measured as 99.05%, which is similar to the results of state-of-the-art applications.publishedVersionPeer reviewe

Étude comparative de l’utilisabilité de trois interfaces de formulation de requêtes à une base de données relationnelle

RÉSUMÉ : L’utilisation de bases de données relationnelles est une activité complexe pour la grande majorité des utilisateurs et cela ne fait pas exception dans une entreprise de technologie financière comme Croesus Finansoft. De nombreux chercheurs et développeurs se sont intéressés à cette activité ces dernières années pour tenter de réduire la complexité et d’améliorer l’utilisabilité des interfaces utilisateurs des systèmes de requêtes à une base de données relationnelle. L’objectif du projet de recherche est de développer une solution d’interface utilisateur permettant aux utilisateurs d’un logiciel de gestion de portefeuilles de créer plus facilement des requêtes à une base de données qu’avec les interfaces actuelles. La recherche a été effectuée en collaboration avec Croesus Lab. qui oeuvre dans le domaine de la technologie financière et offre des produits logiciels aux grandes institutions financières canadiennes. Elle comporte une étude des problèmes rencontrés par les utilisateurs avec les interfaces d’un logiciel de gestion de portefeuille comprenant une interface de critères de recherche simplifiée basée sur une approche par filtres combinée avec une approche de langage semi-naturel sous forme de phrases structurées rigides et une interface de critères de recherche avancée basée sur une approche par filtres dans un formulaire flexible présenté sous forme hiérarchique. La recherche a aussi mené à la conception de maquettes puis d’un prototype d’interface utilisateur basée sur une approche de formulaire flexible de formulation de requêtes par diagramme. Trois cycles de tests d’utilisabilité visant à tester les maquettes et le prototype et à évaluer les résultats en termes de performance et de satisfaction humaine ont été effectués durant le processus de conception. À chaque cycle de tests, la chercheure a observé soigneusement les comportements des utilisateurs. De plus, elle a mené des entrevues semi-dirigées pour recueillir les commentaires et les impressions des utilisateurs. Ces informations ont permis d’améliorer les maquettes. Dix et 12 sujets ont participé respectivement au premier et au second cycle de tests d’utilisabilité portant sur les maquettes. Par la suite, le prototype fonctionnel d’interface a été développé et un troisième cycle de tests d’utilisabilité a été effectué avec celui-ci auprès de six sujets. Pour finir, nous avons mené une étude expérimentale comparative visant à tester trois hypothèses (H) stipulant que la nouvelle interface du prototype donne de meilleurs résultats que les deux interfaces actuelles en termes de temps d’exécution des tâches (H1), de nombre d’erreurs (H2) et de nombre de demandes d’assistance (H3) de la part des utilisateurs. Douze utilisateurs connaissant déjà les deux interfaces actuelles du logiciel de gestion de portefeuilles ont participé aux tests ; chacun devait effectuer trois tâches de création de requêtes avec chacune des trois interfaces. De façon générale, les commentaires recueillis auprès des utilisateurs au sujet du prototype de la nouvelle interface (formulaire flexible de formulation de requêtes par diagramme) sont positifs : l’interface est considérée comme plus intuitive, elle nécessite moins d’opérations pour formuler les différentes conditions des requêtes et l’utilisateur est guidé pour l’ajout d’opérateurs logiques ET/OU. Les résultats montrent aussi que même si les utilisateurs n’ont aucune expérience avec la nouvelle interface contrairement aux deux autres interfaces, la durée de la formulation de requêtes tend à être plus courte avec la nouvelle interface pour toutes les tâches effectuées : toutefois, une seule (sur trois) offre une différence significative en termes statistiques entre la nouvelle interface et l’interface des critères de recherche avancée. L’hypothèse H1 est donc partiellement confirmée, pour une interface sur deux et pour une tâche sur trois). De plus, la nouvelle interface a permis de réduire le nombre d’erreurs de façon significative par rapport à l’interface des critères de recherche avancée pour l’une des trois tâches. L’hypothèse H2 est partiellement confirmée (pour une interface sur deux, et pour une tâche sur trois). Enfin, il n’y a pas de différence significative entre les deux interfaces actuelles et le prototype de formulaire flexible de formulation de requêtes par diagramme quant au nombre de demandes d’assistance à la tâche. L’hypothèse H3 est donc infirmée. Dans la discussion, nous tentons d’expliquer ces résultats et dans la conclusion, nous proposons quelques pistes de recherches pour la suite.----------ABSTRACT : Exploration of relational databases is a complex activity for most people and database users of financial technology software like Croesus Finansoft are no exception. Lots of research has been done in the late years to reduce the complexity and improve the usability of interfaces used to create relational database requests. The goal of this research is to develop a user interface solution, allowing portfolio management software users to create relational database requests in an easier way than with the actual interfaces. The research has been done in collaboration with Croesus Lab. which works in financial technology field and offers software products to Canadians financial institutions. It includes a study of the problems encountered by the portfolio management software users offering a simplified search criteria interface based on a filter approach combined with a rigid semi-natural language approach and an advanced search criteria interface based on a filter approach presented in a hierarchical flexible form. The research has led to the conception of a mock-up and an interface prototype based on an approach of a flexible form or query formulation by diagram. Three cycles of usability tests were done to test the mock-ups and the prototype and to evaluate the results in terms of performance and human satisfaction has been done during the conception process. In every meeting between the searcher and a subject, the searcher has taken notes about her observations of the subject’s behaviour. At the end of each meeting, an interview was conducted by the searcher to collect feedback from the subjects. The information has been used to improve the mock-ups. Ten and 12 subjects have participated respectively to the first and second usability test cycles on mock-ups. Then, the functional prototype has been developed and a third cycle of usability tests has been done with six subjects. An experimental comparative study has been conducted to validate three hypotheses (H) saying that the new interface prototype gives better results than the two actual interfaces in terms of tasks execution time (H1), the number of errors (H2) and the amount of assistance demands (H3) from users. Twelve users already knowing the two actual interfaces have participated in the tests; each of them had to perform three tasks of requests creation with each of the three interfaces. In general, the collected comments on the new interface prototype (interface based on an approach of a flexible form or query formulation by diagram) are positive : the interface is considered more intuitive, it requires fewer operations to formulate the different request’s conditions and the user is guided during the insertion of logical operators AND/OR. The results show that even if the user has no experience with the new interface, the request formulation duration tends to be shorter with the new interface for all the tasks : however, only one task (on three) offers a significant difference in statistic terms between the new interface and the advanced search criteria. The hypothesis H1 is partially confirmed for one interface out of two and one task out of the three. The new interface as significantly reduce the number of errors compared to the advanced search criteria interface for one task out of the three. The hypothesis H2 is partially confirmed (for one interface out of two and for one task out of the three). There is no significant difference between the two actual interfaces and the new interface in terms of the amount of assistance demands. The hypothesis H3 is informed. In the discussion, we try to explain the results and the conclusion. New search fields are proposed for the future

Towards unifying spreadsheets with databases for ad-hoc interactive data management at scale

We are witnessing the increasing availability of data across a spectrum of domains, necessitating the interactive ad-hoc management and analysis of this data, in order to put it to use. Unfortunately, interactive ad-hoc management of very large datasets presents a host of challenges, ranging from performance to interface usability. This thesis introduces a new research direction of manipulation of large datasets using an interactive interface and makes several steps towards this direction. In particular, we develop DataSpread, a tool that enables users to work with arbitrary large datasets via a direct manipulation interface. DataSpread holistically unifies spreadsheets and relational databases to leverage the benefits of both. However, this holistic integration is not trivial due to the differences in the architecture and ideologies of the two paradigms: spreadsheets and databases. We have built a prototype of DataSpread, which, in addition to motivating the underlying challenges, demonstrates the feasibility and usefulness of this holistic integration. We focus on the following challenges encountered while developing DataSpread. (i) Representation—here, we address the challenges of flexibly representing ad-hoc spreadsheet data within a relational database; (ii) Indexing—here, we develop indexing data structures for supporting and maintaining access by position; (iii) Formula Computation—here, we introduce an asynchronous formula computation framework that addresses the challenge of ensuring consistency and interactivity at the same time; and (iv) Organization—here, we develop a framework to best organize data based on a workload, e.g., queries specified on the spreadsheet interface