research-article

Expressive Query Construction through Direct Manipulation of Nested Relational Results

Authors:

David R. KargerAuthors Info & Claims

SIGMOD '16: Proceedings of the 2016 International Conference on Management of Data

Pages 1377 - 1392

https://doi.org/10.1145/2882903.2915210

Published: 14 June 2016 Publication History

Abstract

Despite extensive research on visual query systems, the standard way to interact with relational databases remains to be through SQL queries and tailored form interfaces. We consider three requirements to be essential to a successful alternative: (1) query specification through direct manipulation of results, (2) the ability to view and modify any part of the current query without departing from the direct manipulation interface, and (3) SQL-like expressiveness. This paper presents the first visual query system to meet all three requirements in a single design. By directly manipulating nested relational results, and using spreadsheet idioms such as formulas and filters, the user can express a relationally complete set of query operators plus calculation, aggregation, outer joins, sorting, and nesting, while always remaining able to track and modify the state of the complete query. Our prototype gives the user an experience of responsive, incremental query building while pushing all actual query processing to the database layer. We evaluate our system with formative and controlled user studies on 28 spreadsheet users; the controlled study shows our system significantly outperforming Microsoft Access on the System Usability Scale.

References

[1]

A. Abouzied, J. Hellerstein, and A. Silberschatz. DataPlay: Interactive tweaking and example-driven correction of graphical database queries. In Proceedings of the 25th annual ACM symposium on User interface software and technology (UIST '12), pages 207--218, New York, NY, USA, 2012. ACM.

Digital Library

[2]

S. Achler. GBXT: A gesture-based data exploration tool for your favorite database system. In Model and Data Engineering, pages 224--237. Springer International Publishing, Cham, Switzerland, 2014.

[3]

M. Angelaccio, T. Catarci, and G. Santucci. Query by Diagram: A fully visual query system. Journal of Visual Languages & Computing, 1(3):255--273, 1990.

Digital Library

[4]

E. Bakke and E. Benson. The schema-independent database UI: A proposed holy grail and some suggestions. In Proceedings of the 5th Biennial Conference on Innovative Data Systems Research (CIDR '11), 2011.

[5]

E. Bakke, D. R. Karger, and R. C. Miller. Automatic layout of structured hierarchical reports. IEEE Transactions on Visualization and Computer Graphics, 19(12):2586--2595, December 2013.

Digital Library

[6]

E. Baralis and J. Widom. An algebraic approach to static analysis of active database rules. ACM Transactions on Database Systems (TODS), 25(3):269--332, September 2000.

Digital Library

[7]

T. Berners-Lee, Y. Chen, L. Chilton, D. Connolly, R. Dhanaraj, J. Hollenbach, A. Lerer, and D. Sheets. Tabulator: Exploring and analyzing linked data on the semantic web. In Proceedings of the 3rd International Semantic Web User Interaction Workshop (SWUI '06), 2006.

[8]

J. Brooke. SUS: A quick and dirty usability scale. In Usability evaluation in industry, pages 189--194. Tailor & Francis, London, UK, 1996.

[9]

M. Burnett, J. Atwood, R. Walpole Djang, J. Reichwein, H. Gottfried, and S. Yang. Forms/3: A first-order visual language to explore the boundaries of the spreadsheet paradigm. Journal of Functional Programming, 11:155--206, March 2001.

Digital Library

[10]

B. Cao and A. Badia. SQL query optimization through nested relational algebra. ACM Transactions on Database Systems (TODS), 32(3):18, 2007.

Digital Library

[11]

T. Catarci, M. F. Costabile, S. Levialdi, and C. Batini. Visual query systems for databases: A survey. Journal of Visual Languages & Computing, 8(2):215--260, 1997.

Digital Library

[12]

J. P. Caulkins, E. L. Morrison, and T. Weidemann. Spreadsheet errors and decision making: Evidence from field interviews. Journal of Organizational and End User Computing, 19(3):1, 2007.

[13]

K. S.-P. Chang and B. A. Myers. Using and exploring hierarchical data in spreadsheets. In Proceedings of the 34th Annual ACM Conference on Human Factors in Computing Systems (CHI '16), New York, NY, USA, 2016. ACM.

Digital Library

[14]

S. Chaudhuri, G. Das, V. Hristidis, and G. Weikum. Probabilistic ranking of database query results. In Proceedings of the 30th International Conference on Very Large Data Bases (VLDB '04), pages 888--899. VLDB Endowment, 2004.

Digital Library

[15]

W.-K. Chen and P.-Y. Tu. VisualTPL: A visual dataflow language for report data transformation. Journal of Visual Languages & Computing, 25(3):210--226, 2014.

[16]

C. Clack and L. Braine. Object-oriented functional spreadsheets. In Proceedings of the 10th Glasgow Workshop on Functional Programming (GlaFP '97), 1997.

[17]

E. F. Codd. Relational completeness of data base sublanguages. In Database Systems, pages 65--98. Prentice Hall, 1972.

[18]

R. Collie. The 3rd most common button in data apps is... http://www.powerpivotpro.com/2012/03/the-3rd-most-common-button-in-data-apps-is, March 2012.

[19]

G. Das, V. Hristidis, N. Kapoor, and S. Sudarshan. Ordering the attributes of query results. In Proceedings of the 2006 ACM SIGMOD International Conference on Management of Data, pages 395--406, New York, NY, USA, 2006. ACM.

Digital Library

[20]

E. Díaz-Struck. Ethanol industry battles to keep incentives, May 2013. Investigation for the New England Center for Investigative Reporting and Connectas, available at http://eye.necir.org/2013/05/26/ethanol-industry-battles-to-keep-incentives.

[21]

S. El-Mahgary and E. Soisalon-Soininen. A form-based query interface for complex queries. Journal of Visual Languages & Computing, 29:15--53, 2015.

Digital Library

[22]

R. G. Epstein. The TableTalk query language. Journal of Visual Languages & Computing, 2(2):115--141, 1991.

Digital Library

[23]

R. Fagin. Multivalued dependencies and a new normal form for relational databases. ACM Transactions on Database Systems (TODS), 2(3):262--278, 1977.

Digital Library

[24]

H. Garcia-Molina, J. D. Ullman, and J. Widom. Database Systems: The Complete Book. Pearson Prentice Hall, Upper Saddle River, NJ, USA, 2nd edition, 2009.

Digital Library

[25]

Y. Han, G. Wang, G. Ji, and P. Zhang. Situational data integration with data services and nested table. Service Oriented Computing and Applications, 7(2):129--150, 2013.

Digital Library

[26]

L. Hella, L. Libkin, J. Nurmonen, and L. Wong. Logics with aggregate operators. Journal of the ACM (JACM), 48(4):880--907, July 2001.

Digital Library

[27]

G.-J. Houben and J. Paredaens. A graphical interface formalism: Specifying nested relational databases. In Proceedings of the IFIP TC2 Working Conference on Visual Database Systems, pages 257--276, 1989.

[28]

Y. E. Ioannidis. Visual user interfaces for database systems. ACM Computing Surveys (CSUR), 28(4es), 1996.

Digital Library

[29]

G. Jaeschke and H. J. Schek. Remarks on the algebra of non first normal form relations. In Proceedings of the 1st ACM SIGACT-SIGMOD Symposium on Principles of Database Systems (PODS '82), pages 124--138, New York, NY, USA, 1982. ACM.

Digital Library

[30]

H. V. Jagadish, A. Chapman, A. Elkiss, M. Jayapandian, Y. Li, A. Nandi, and C. Yu. Making database systems usable. In Proceedings of the 2007 ACM SIGMOD international conference on Management of data, pages 13--24, New York, NY, USA, 2007. ACM.

Digital Library

[31]

D. Janvrin and J. Morrison. Using a structured design approach to reduce risks in end user spreadsheet development. Information & management, 37(1):1--12, 2000.

Digital Library

[32]

M. Jayapandian and H. V. Jagadish. Automated creation of a forms-based database query interface. Proceedings of the VLDB Endowment, 1:695--709, August 2008.

Digital Library

[33]

M. Jayapandian and H. V. Jagadish. Expressive query specification through form customization. In Proceedings of the 11th International Conference on Extending Database Technology (EDBT '08), pages 416--427, New York, NY, USA, 2008. ACM.

Digital Library

[34]

S. Kandel, A. Paepcke, J. Hellerstein, and J. Heer. Wrangler: Interactive visual specification of data transformation scripts. In Proceedings of the 2011 annual conference on Human Factors in Computing Systems (CHI '11), pages 3363--3372, New York, NY, USA, 2011. ACM.

Digital Library

[35]

E. Kandogan, E. Haber, R. Barrett, A. Cypher, P. Maglio, and H. Zhao. A1: End-user programming for web-based system administration. In Proceedings of the 18th Annual ACM Symposium on User Interface Software and Technology (UIST '05), pages 211--220, New York, NY, USA, 2005. ACM.

Digital Library

[36]

K. Kowalzcykowski, A. Deutsch, K. W. Ong, Y. Papakonstantinou, K. K. Zhao, and M. Petropoulos. Do-It-Yourself database-driven web applications. In Proceedings of the 4th Biennial Conference on Innovative Data Systems Research (CIDR '09), 2009.

[37]

D. Król, J. Oleksy, M. Podyma, and B. Trawinski. The analysis of reporting tools for a cadastre information system. In Proceedings of the 9th International Conference on Business Information Systems (BIS '06), pages 150--163, 2006.

[38]

M. Levene. The Nested Universal Relation Database Model, volume 595 of Lecture Notes in Computer Science. Springer Berlin/Heidelberg, 1992.

[39]

J. R. Lewis and J. Sauro. The factor structure of the system usability scale. In Proceedings of the 1st International Conference on Human Centered Design (HCD '09)/HCI International 2009, pages 94--103, Berlin, Heidelberg, 2009. Springer-Verlag.

Digital Library

[40]

L. Libkin and L. Wong. On the power of aggregation in relational query languages. In S. Cluet and R. Hull, editors, Proceedings of the 6th International Workshop on Database Programming Languages (DBPL '97), Lecture Notes in Computer Science, pages 260--280. Springer Berlin/Heidelberg, 1998.

Digital Library

[41]

B. Liu and H. V. Jagadish. A spreadsheet algebra for a direct data manipulation query interface. In Proceedings of the IEEE 25th International Conference on Data Engineering (ICDE '09), pages 417--428, April 2009.

Digital Library

[42]

N. Lorentzos and K. Dondis. Query by Example for Nested Tables. In Database and Expert Systems Applications, pages 716--725. Springer, 1998.

Digital Library

[43]

R. M. McCutchen, S. Itzhaky, and D. Jackson. Initial report on Object Spreadsheets. Technical Report MIT-CSAIL-TR-2016-001, MIT Computer Science and Artificial Intelligence Laboratory, January 2016.

[44]

M. Miah, G. Das, V. Hristidis, and H. Mannila. Standing out in a crowd: Selecting attributes for maximum visibility. In Proceedings of the 24th International Conference on Data Engineering (ICDE '08), pages 356--365, Washington, DC, USA, April 2008. IEEE Computer Society.

Digital Library

[45]

A. Nandi, L. Jiang, and M. Mandel. Gestural query specification. Proceedings of the VLDB Endowment, 7(4):289--300, 2013.

Digital Library

[46]

Y. Papakonstantinou, M. Petropoulos, and V. Vassalos. QURSED: Querying and reporting semistructured data. In Proceedings of the 2002 ACM SIGMOD International Conference on Management of Data, pages 192--203, New York, NY, USA, 2002. ACM.

Digital Library

[47]

S. Polyviou, G. Samaras, and P. Evripidou. A relationally complete visual query language for heterogeneous data sources and pervasive querying. In Proceedings of the 21st International Conference on Data Engineering (ICDE '05), pages 471--482, Washington, DC, USA, 2005. IEEE Computer Society.

Digital Library

[48]

L. Qian, K. LeFevre, and H. V. Jagadish. CRIUS: User-friendly database design. Proceedings of the VLDB Endowment, 4(2):81--92, 2010.

Digital Library

[49]

J. Sauro. A practical guide to the System Usability Scale: Background, benchmarks & best practices. Measuring Usability LLC, 2011.

[50]

B. Shneiderman. Direct Manipulation: A step beyond programming languages. IEEE Computer, 16(8):57--69, 1983.

Digital Library

[51]

M. Singh, A. Nandi, and H. V. Jagadish. Skimmer: Rapid scrolling of relational query results. In Proceedings of the 2012 ACM SIGMOD International Conference on Management of Data, pages 181--192, New York, NY, USA, 2012. ACM.

Digital Library

[52]

M. Spenke and C. Beilken. A spreadsheet interface for logic programming. In Proceedings of the SIGCHI Conference on Human Factors in Computing Systems (CHI '89), pages 75--80, New York, NY, USA, 1989. ACM.

Digital Library

[53]

H. J. Steenhagen, P. M. G. Apers, and H. M. Blanken. Optimization of nested queries in a complex object model. In Proceedings of the 4th International Conference on Extending Database Technology (EDBT '94), pages 337--350, New York, NY, USA, 1994. Springer New York.

Digital Library

[54]

C. Stolte, D. Tang, and P. Hanrahan. Polaris: A system for query, analysis, and visualization of multidimensional databases. Communications of the ACM, 51(11):75--84, November 2008.

Digital Library

[55]

C. R. Stolte. Query, analysis, and visualization of multidimensional databases. PhD thesis, Stanford University, Stanford, CA, USA, 2003.

Digital Library

[56]

T. S. Tullis and J. N. Stetson. A comparison of questionnaires for assessing website usability, 2004. Usability Professionals Association (UPA) 2004 Conference.

[57]

J. Tyszkiewicz. Spreadsheet as a relational database engine. In Proceedings of the 2010 International Conference on Management of Data (SIGMOD '10), pages 195--206, New York, NY, USA, 2010. ACM.

Digital Library

[58]

J. Van den Bussche and S. Vansummeren. Translating SQL into the relational algebra. Course notes, Hasselt University and the Free University of Brussels, retrieved April 2016. http://cs.ulb.ac.be/public/_media/teaching/infoh417/sql2alg_eng.pdf.

[59]

L. Wegner, S. Thelemann, J. Thamm, D. Wilke, and S. Wilke. Navigational exploration and declarative queries in a prototype for visual information systems. In C. Leung, editor, Visual Information Systems, volume 1306 of Lecture Notes in Computer Science, pages 199--218. Springer Berlin/Heidelberg, 1997.

Digital Library

[60]

F. Yang, N. Gupta, C. Botev, E. F. Churchill, G. Levchenko, and J. Shanmugasundaram. WYSIWYG development of data driven web applications. Proceedings of the VLDB Endowment, 1(1):163--175, 2008.

Digital Library

[61]

M. M. Zloof. Query-by-Example: A data base language. IBM Systems Journal, 16(4):324--343, 1977.

Digital Library

Cited By

Gatterbauer WDunne C(2024)On The Reasonable Effectiveness of Relational Diagrams: Explaining Relational Query Patterns and the Pattern Expressiveness of Relational LanguagesProceedings of the ACM on Management of Data10.1145/36393162:1(1-27)Online publication date: 26-Mar-2024
https://dl.acm.org/doi/10.1145/3639316
Gatterbauer W(2024)A Comprehensive Tutorial on Over 100 Years of Diagrammatic Representations of Logical Statements and Relational Queries2024 IEEE 40th International Conference on Data Engineering (ICDE)10.1109/ICDE60146.2024.00407(5387-5392)Online publication date: 13-May-2024
https://doi.org/10.1109/ICDE60146.2024.00407
Soni KSachdeva SMinj A(2024)Querying Healthcare Data in Knowledge-Based SystemsBig Data Analytics in Astronomy, Science, and Engineering10.1007/978-3-031-58502-9_4(59-77)Online publication date: 27-Apr-2024
https://doi.org/10.1007/978-3-031-58502-9_4
Show More Cited By

Index Terms

Expressive Query Construction through Direct Manipulation of Nested Relational Results
1. Human-centered computing
  1. Human computer interaction (HCI)
    1. HCI design and evaluation methods
      1. User studies
    2. Interaction paradigms
      1. Graphical user interfaces
  2. Visualization
    1. Visualization systems and tools
2. Information systems
  1. Data management systems
    1. Database design and models
      1. Relational database model
    2. Query languages
      1. Relational database query languages
  2. Information retrieval
    1. Information retrieval query processing
      1. Query representation

Recommendations

Ontology based graphical query language supporting recursion
KES'10: Proceedings of the 14th international conference on Knowledge-based and intelligent information and engineering systems: Part I

Text based queries often lead tend to be complex, and may result in non user friendly query structures. However, querying information systems using visual means, even for complex queries has proven to be more efficient and effective as compared to text ...
Query Lifting: Language-integrated query for heterogeneous nested collections
Programming Languages and Systems
Abstract
Language-integrated query based on comprehension syntax is a powerful technique for safe database programming, and provides a basis for advanced techniques such as query shredding or query flattening that allow efficient programming with complex ... $_{}$
Incremental computation of nested relational query expressions

Efficient algorithms for incrementally computing nested query expressions do not exist. Nested query expressions are query expressions in which selection/join predicates contain subqueries. In order to respond to this problem, we propose a two-step ...

Comments

Information & Contributors

Information

Published In

cover image ACM Conferences

SIGMOD '16: Proceedings of the 2016 International Conference on Management of Data

June 2016

2300 pages

ISBN:9781450335317

DOI:10.1145/2882903

General Chairs:
Fatma Özcan
IBM Research, USA
,
Georgia Koutrika
HP Labs, USA
,
Program Chair:
Sam Madden
Massachusetts Institute of Technology, USA

Copyright © 2016 ACM.

Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than the author(s) must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected].

Sponsors

SIGMOD: ACM Special Interest Group on Management of Data

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 14 June 2016

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Author Tags

Qualifiers

Research-article

Conference

SIGMOD/PODS'16

Sponsor:

SIGMOD

SIGMOD/PODS'16: International Conference on Management of Data

June 26 - July 1, 2016

California, San Francisco, USA

Acceptance Rates

Overall Acceptance Rate 785 of 4,003 submissions, 20%

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

19
Total Citations
View Citations
469
Total Downloads

Downloads (Last 12 months)23
Downloads (Last 6 weeks)5

Reflects downloads up to 14 Oct 2024

Other Metrics

View Author Metrics

Citations

Cited By

Gatterbauer WDunne C(2024)On The Reasonable Effectiveness of Relational Diagrams: Explaining Relational Query Patterns and the Pattern Expressiveness of Relational LanguagesProceedings of the ACM on Management of Data10.1145/36393162:1(1-27)Online publication date: 26-Mar-2024
https://dl.acm.org/doi/10.1145/3639316
Gatterbauer W(2024)A Comprehensive Tutorial on Over 100 Years of Diagrammatic Representations of Logical Statements and Relational Queries2024 IEEE 40th International Conference on Data Engineering (ICDE)10.1109/ICDE60146.2024.00407(5387-5392)Online publication date: 13-May-2024
https://doi.org/10.1109/ICDE60146.2024.00407
Soni KSachdeva SMinj A(2024)Querying Healthcare Data in Knowledge-Based SystemsBig Data Analytics in Astronomy, Science, and Engineering10.1007/978-3-031-58502-9_4(59-77)Online publication date: 27-Apr-2024
https://doi.org/10.1007/978-3-031-58502-9_4
Gatterbauer W(2023)A Tutorial on Visual Representations of Relational QueriesProceedings of the VLDB Endowment10.14778/3611540.361157816:12(3890-3893)Online publication date: 12-Sep-2023
https://dl.acm.org/doi/10.14778/3611540.3611578
Huang ZDamalapati PWu E(2023)Aggregation Consistency Errors in Semantic Layers and How to Avoid ThemProceedings of the Workshop on Human-In-the-Loop Data Analytics10.1145/3597465.3605224(1-7)Online publication date: 18-Jun-2023
https://dl.acm.org/doi/10.1145/3597465.3605224
El-Mahgary SSoisalon-Soininen EOrponen PRönnholm PHyyppä H(2022)OVI-3: A NoSQL visual query system supporting efficient anti-joinsJournal of Intelligent Information Systems10.1007/s10844-022-00742-460:3(777-801)Online publication date: 21-Sep-2022
https://doi.org/10.1007/s10844-022-00742-4
Silva EFidalgo RFerro MFranco N(2022)Visual query languages to design complex queries: a systematic literature reviewSoftware and Systems Modeling10.1007/s10270-022-01071-422:4(1217-1249)Online publication date: 26-Dec-2022
https://doi.org/10.1007/s10270-022-01071-4
Rahman SBendre MLiu YZhu SSu ZKarahalios KParameswaran A(2021)NOAHProceedings of the VLDB Endowment10.14778/3447689.344770114:6(970-983)Online publication date: 12-Apr-2021
https://dl.acm.org/doi/10.14778/3447689.3447701
Burgin MZellweger H(2021)Nested Named Sets in Information RetrievalAdvances in Data Science and Information Engineering10.1007/978-3-030-71704-9_30(451-467)Online publication date: 30-Oct-2021
https://doi.org/10.1007/978-3-030-71704-9_30
Parameswaran A(2019)Enabling data science for the majorityProceedings of the VLDB Endowment10.14778/3352063.335214812:12(2309-2322)Online publication date: 1-Aug-2019
https://dl.acm.org/doi/10.14778/3352063.3352148
Show More Cited By

View Options

Get Access

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Publication

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Media

Figures

Other

Tables

View Table of Contents