{"status":"ok","message-type":"work","message-version":"1.0.0","message":{"indexed":{"date-parts":[[2024,8,8]],"date-time":"2024-08-08T00:13:28Z","timestamp":1723076008805},"reference-count":57,"publisher":"Association for Computing Machinery (ACM)","issue":"1","funder":[{"DOI":"10.13039\/https:\/\/doi.org\/10.13039\/100000001","name":"NSF","doi-asserted-by":"publisher","award":["IIS-1762268, IIS-2145382, and IIS-1956096"],"id":[{"id":"10.13039\/https:\/\/doi.org\/10.13039\/100000001","id-type":"DOI","asserted-by":"publisher"}]}],"content-domain":{"domain":[],"crossmark-restriction":false},"short-container-title":["Proc. ACM Manag. Data"],"published-print":{"date-parts":[[2024,3,12]]},"abstract":"Comparing relational languages by their logical expressiveness is well understood. Less well understood is how to compare relational languages by their ability to represent relational query patterns. Indeed, what are query patterns other than \"a certain way of writing a query\"? And how can query patterns be defined across procedural and declarative languages, irrespective of their syntax? To the best of our knowledge, we provide the first semantic definition of relational query patterns by using a variant of structure-preserving mappings between the relational tables of queries. This formalism allows us to analyze the relative pattern expressiveness of relational language fragments and create a hierarchy of languages with equal logical expressiveness yet different pattern expressiveness. Notably, for the non-disjunctive language fragment, we show that relational calculus can express a larger class of patterns than the basic operators of relational algebra.<\/jats:p>\n Our language-independent definition of query patterns opens novel paths for assisting database users. For example, these patterns could be leveraged to create visual query representations that faithfully represent query patterns, speed up interpretation, and provide visual feedback during query editing. As a concrete example, we propose Relational Diagrams, a complete and sound diagrammatic representation of safe relational calculus that is provably (i) unambiguous, (ii) relationally complete, and (iii) able to represent all query patterns for unions of non-disjunctive queries. Among all diagrammatic representations for relational queries that we are aware of, ours is the only one with these three properties. Furthermore, our anonymously preregistered user study shows that Relational Diagrams allow users to recognize patterns meaningfully faster and more accurately than SQL.<\/jats:p>","DOI":"10.1145\/3639316","type":"journal-article","created":{"date-parts":[[2024,3,26]],"date-time":"2024-03-26T22:51:32Z","timestamp":1711493492000},"page":"1-27","source":"Crossref","is-referenced-by-count":1,"title":["On The Reasonable Effectiveness of Relational Diagrams: Explaining Relational Query Patterns and the Pattern Expressiveness of Relational Languages"],"prefix":"10.1145","volume":"2","author":[{"ORCID":"http:\/\/orcid.org\/0000-0002-9614-0504","authenticated-orcid":false,"given":"Wolfgang","family":"Gatterbauer","sequence":"first","affiliation":[{"name":"Northeastern University, Boston, USA"}]},{"ORCID":"http:\/\/orcid.org\/0000-0002-1609-9776","authenticated-orcid":false,"given":"Cody","family":"Dunne","sequence":"additional","affiliation":[{"name":"Northeastern University, Boston, USA"}]}],"member":"320","published-online":{"date-parts":[[2024,3,26]]},"reference":[{"volume-title":"Foundations of Databases","author":"Abiteboul Serge","key":"e_1_2_2_1_1","unstructured":"Serge Abiteboul, Richard Hull, and Victor Vianu. 1995. Foundations of Databases. Addison-Wesley. http:\/\/webdam.inria.fr\/Alice\/"},{"key":"e_1_2_2_2_1","doi-asserted-by":"publisher","unstructured":"Azza Abouzied Joseph M. Hellerstein and Avi Silberschatz. 2012. DataPlay: interactive tweaking and example-driven correction of graphical database queries. In UIST. ACM 207--218. https:\/\/doi.org\/10.1145\/2380116.2380144","DOI":"10.1145\/2380116.2380144"},{"key":"e_1_2_2_3_1","doi-asserted-by":"publisher","DOI":"10.14778\/2367502.2367542"},{"key":"e_1_2_2_4_1","doi-asserted-by":"publisher","DOI":"10.12688\/wellcomeopenres.15191.2"},{"key":"e_1_2_2_5_1","unstructured":"Amazon Mechanical Turk (MTurk). 2023. https:\/\/www.mturk.com."},{"volume-title":"Database Theory: Querying Data. Open source at https:\/\/github.com\/pdm-book\/community.","year":"2022","author":"Arenas Marcelo","key":"e_1_2_2_6_1","unstructured":"Marcelo Arenas, Pablo Barcelo, Leonid Libkin, Wim Martens, and Andreas Pieris. 2022. Database Theory: Querying Data. Open source at https:\/\/github.com\/pdm-book\/community."},{"key":"e_1_2_2_7_1","doi-asserted-by":"publisher","DOI":"10.1145\/2882903.2915210"},{"key":"e_1_2_2_8_1","doi-asserted-by":"publisher","unstructured":"Leilani Battle Danyel Fisher Robert DeLine Mike Barnett Badrish Chandramouli and Jonathan Goldstein. 2016. Making Sense of Temporal Queries with Interactive Visualization. In CHI. ACM 5433--5443. https:\/\/doi.org\/10.1145\/2858036.2858408","DOI":"10.1145\/2858036.2858408"},{"key":"e_1_2_2_9_1","volume-title":"II. Proceedings of the IFIP TC2\/WG 2.6 Second Working Conference on Visual Database Systems. (IFIP Transactions","volume":"421","author":"Catarci Tiziana","year":"1991","unstructured":"Tiziana Catarci. 1991. On the Expressive Power of Graphical Query Languages. In Visual Database Systems, II. Proceedings of the IFIP TC2\/WG 2.6 Second Working Conference on Visual Database Systems. (IFIP Transactions, Vol. A-7). North-Holland, 411--421. https:\/\/dblp.org\/rec\/conf\/vdb\/Catarci91"},{"key":"e_1_2_2_10_1","doi-asserted-by":"publisher","DOI":"10.1006\/jvlc.1997.0037"},{"key":"e_1_2_2_11_1","doi-asserted-by":"publisher","DOI":"10.1016\/0306--4379(93)90006-M"},{"key":"e_1_2_2_12_1","doi-asserted-by":"publisher","DOI":"10.1109\/TSE.1985.232223"},{"key":"e_1_2_2_13_1","doi-asserted-by":"publisher","DOI":"10.14778\/3236187.3236200"},{"key":"e_1_2_2_14_1","doi-asserted-by":"publisher","DOI":"10.1016\/0378--7206(94)90098--1"},{"volume-title":"Begg","year":"2015","author":"Connolly Thomas M.","key":"e_1_2_2_15_1","unstructured":"Thomas M. Connolly and Carolyn E. Begg. 2015. Database Systems: A Practical Approach to Design, Implementation and Management, Global Edition (5 ed.). Pearson Addison Wesley. https:\/\/www.pearson.com\/en-gb\/subject-catalog\/p\/database-systems-a-practical-approach-to-design-implementation-and-management-global-edition\/P200000003964\/"},{"key":"e_1_2_2_16_1","doi-asserted-by":"publisher","DOI":"10.1111\/cgf.14826"},{"key":"e_1_2_2_17_1","doi-asserted-by":"publisher","DOI":"10.4324\/9780203807002"},{"key":"e_1_2_2_18_1","doi-asserted-by":"publisher","unstructured":"Jonathan Danaparamita and Wolfgang Gatterbauer. 2011. QueryViz: Helping Users Understand SQL queries and their patterns. In EDBT. ACM 558--561. https:\/\/doi.org\/10.1145\/1951365.1951440","DOI":"10.1145\/1951365.1951440"},{"key":"e_1_2_2_19_1","unstructured":"Christopher J. Date. 2003. An introduction to database systems (8 ed.). Pearson\/Addison Wesley Longman. https:\/\/dl.acm.org\/doi\/10.5555\/861613"},{"key":"e_1_2_2_20_1","unstructured":"Jan Van den Bussche and Stijn Vansummeren. 2009. Translating SQL into the relational algebra. Course notes Hasselt University and Universit\u00e9 Libre de Bruxelles. https:\/\/dipot.ulb.ac.be\/dspace\/bitstream\/2013\/198813\/1\/sql2alg_eng.pdf"},{"key":"e_1_2_2_21_1","doi-asserted-by":"publisher","DOI":"10.1109\/TVCG.2021.3114756"},{"key":"e_1_2_2_22_1","doi-asserted-by":"publisher","DOI":"10.1007\/978--3--319--26633--6_13"},{"key":"e_1_2_2_23_1","unstructured":"Pierre Dragicevic. 2018. Can we call mean differences \"effect sizes\"? https:\/\/transparentstatistics.org\/2018\/07\/05\/meanings-effect-size\/"},{"key":"e_1_2_2_24_1","doi-asserted-by":"publisher","DOI":"10.1080\/01621459.1987.10478410"},{"volume-title":"Fundamentals of database systems (7 ed.)","author":"Elmasri Ramez","key":"e_1_2_2_25_1","unstructured":"Ramez Elmasri and Sham Navathe. 2015. Fundamentals of database systems (7 ed.). Addison Wesley. https:\/\/dl.acm.org\/doi\/book\/10.5555\/2842853"},{"key":"e_1_2_2_26_1","doi-asserted-by":"publisher","DOI":"10.14778\/2850583.2850592"},{"key":"e_1_2_2_27_1","doi-asserted-by":"publisher","DOI":"10.1007\/978--1--4419--8047--2"},{"volume-title":"Database systems: The complete book (2 ed.)","author":"Garcia-Molina Hector","key":"e_1_2_2_28_1","unstructured":"Hector Garcia-Molina, Jeffrey D. Ullman, and Jennifer Widom. 2008. Database systems: The complete book (2 ed.). Prentice Hall Press. https:\/\/dl.acm.org\/doi\/book\/10.5555\/1450931"},{"key":"e_1_2_2_29_1","doi-asserted-by":"publisher","DOI":"10.14778\/3402755.3402805"},{"key":"e_1_2_2_30_1","doi-asserted-by":"publisher","DOI":"10.14778\/3611540.3611578"},{"volume-title":"A Comprehensive Tutorial on over 100 Years of Diagrammatic Representations of Logical Statements and Relational Queries","author":"Gatterbauer Wolfgang","key":"e_1_2_2_31_1","unstructured":"Wolfgang Gatterbauer. 2024. A Comprehensive Tutorial on over 100 Years of Diagrammatic Representations of Logical Statements and Relational Queries. In ICDE. IEEE. Tutorial page: https:\/\/northeastern-datalab.github.io\/diagrammatic-representation-tutorial\/."},{"key":"e_1_2_2_32_1","unstructured":"Wolfgang Gatterbauer and Cody Dunne. 2023. Supplemental material for \"On the reasonable effectiveness of Relational Diagrams\". Homepage: https:\/\/relationaldiagrams.com\/. Main suplemental material folder on OSF: https:\/\/osf.io\/q9g6u\/. Online appendix with all proofs further illustrations and study materials: https:\/\/arxiv.org\/pdf\/2401.04758. Textbook analysis: https:\/\/osf.io\/u7c4z. User study tutorial: https:\/\/osf.io\/mruzw. Stimuli-generating code: https:\/\/osf.io\/kgx4y. The stimuli: https:\/\/osf.io\/d5qaj. Stimuli\/schema index CSV: https:\/\/osf.io\/u8bf9. Stimuli\/schema index JSON: https:\/\/osf.io\/sn83j. Server code for hosting the study: https:\/\/osf.io\/suj4a. Collected data: https:\/\/osf.io\/8vm42. Executed user study analysis code: https:\/\/osf.io\/f2xe3. Preregistered user study: https:\/\/osf.io\/4zpsk\/."},{"key":"e_1_2_2_33_1","first-page":"47","article-title":"Principles of Query Visualization","volume":"45","author":"Gatterbauer Wolfgang","year":"2022","unstructured":"Wolfgang Gatterbauer, Cody Dunne, H. V. Jagadish, and Mirek Riedewald. 2022. Principles of Query Visualization. IEEE Data Eng. Bull. 45, 3 (2022), 47--67. http:\/\/sites.computer.org\/debull\/A22sept\/p47.pdf","journal-title":"IEEE Data Eng. Bull."},{"key":"e_1_2_2_34_1","doi-asserted-by":"publisher","DOI":"10.1145\/2532641"},{"volume-title":"QBE, and DFQL as query languages for relational databases. Master's thesis. Naval Postgraduate School","author":"Girsang Paruntungan","key":"e_1_2_2_35_1","unstructured":"Paruntungan Girsang. 1994. The comparison of SQL, QBE, and DFQL as query languages for relational databases. Master's thesis. Naval Postgraduate School, Monterey, California. https:\/\/core.ac.uk\/download\/pdf\/36723678.pdf"},{"key":"e_1_2_2_36_1","doi-asserted-by":"publisher","DOI":"10.1109\/MIS.2009.36"},{"key":"e_1_2_2_37_1","doi-asserted-by":"publisher","DOI":"10.2307\/2687775"},{"key":"e_1_2_2_38_1","doi-asserted-by":"publisher","DOI":"10.1007\/978--3--540--39597--3_13"},{"key":"e_1_2_2_39_1","doi-asserted-by":"publisher","unstructured":"Aristotelis Leventidis Jiahui Zhang Cody Dunne Wolfgang Gatterbauer H. V. Jagadish and Mirek Riedewald. 2020. QueryVis: Logic-based Diagrams help Users Understand Complicated SQL Queries Faster. In SIGMOD. ACM 2303--2318. https:\/\/doi.org\/10.1145\/3318464.3389767","DOI":"10.1145\/3318464.3389767"},{"key":"e_1_2_2_40_1","doi-asserted-by":"publisher","DOI":"10.1145\/7239.384276"},{"key":"e_1_2_2_41_1","doi-asserted-by":"publisher","DOI":"10.1109\/VL"},{"volume-title":"EBNF: A Notation to Describe Syntax. https:\/\/ics.uci.edu\/~pattis\/misc\/ebnf2.pdf. (accessed on","year":"2013","author":"Pattis Richard E.","key":"e_1_2_2_42_1","unstructured":"Richard E. Pattis. 2013. EBNF: A Notation to Describe Syntax. https:\/\/ics.uci.edu\/~pattis\/misc\/ebnf2.pdf. (accessed on September 21, 2021)."},{"key":"e_1_2_2_43_1","doi-asserted-by":"publisher","unstructured":"Charles Sanders Peirce. 1933. Collected Papers. Vol. 4. Harvard University Press. https:\/\/doi.org\/10.1177\/000271623417400185","DOI":"10.1177\/000271623417400185"},{"volume-title":"Database Management Systems (3 ed.)","author":"Ramakrishnan Raghu","key":"e_1_2_2_44_1","unstructured":"Raghu Ramakrishnan and Johannes Gehrke. 2002. Database Management Systems (3 ed.). McGraw-Hill, Inc., USA. https:\/\/dl.acm.org\/doi\/book\/10.5555\/560733"},{"key":"e_1_2_2_45_1","doi-asserted-by":"publisher","DOI":"10.1145\/356835.356837"},{"key":"e_1_2_2_46_1","doi-asserted-by":"publisher","DOI":"10.1145\/1499949.1500036"},{"key":"e_1_2_2_47_1","unstructured":"Relational Diagrams. 2023. https:\/\/www.relationaldiagrams.com."},{"key":"e_1_2_2_48_1","doi-asserted-by":"publisher","DOI":"10.1016\/0898--1221(92)90127--4"},{"key":"e_1_2_2_49_1","doi-asserted-by":"publisher","DOI":"10.1017\/CBO9780511574696"},{"key":"e_1_2_2_50_1","doi-asserted-by":"publisher","DOI":"10.7551\/mitpress"},{"key":"e_1_2_2_51_1","unstructured":"Avi Silberschatz Henry F. Korth and S. Sudarshan. 2020. Database System Concepts (7 ed.). McGraw-Hill Book Company. https:\/\/www.db-book.com\/db7\/index.html"},{"key":"e_1_2_2_52_1","unstructured":"Souffl\u00e9. 2023. https:\/\/souffle-lang.github.io\/rules."},{"key":"e_1_2_2_53_1","doi-asserted-by":"publisher","DOI":"10.14778\/3551793.3551818"},{"volume-title":"Principles of Database and Knowledge-base Systems","author":"Ullman Jeffrey D.","key":"e_1_2_2_54_1","unstructured":"Jeffrey D. Ullman. 1988. Principles of Database and Knowledge-base Systems, Vol. I. Computer Science Press, Inc. https:\/\/dl.acm.org\/doi\/book\/10.5555\/42790"},{"key":"e_1_2_2_55_1","doi-asserted-by":"publisher","DOI":"10.1002\/cpa.3160130102"},{"key":"e_1_2_2_56_1","doi-asserted-by":"publisher","DOI":"10.1080\/00031305.1999.10474474"},{"key":"e_1_2_2_57_1","doi-asserted-by":"publisher","DOI":"10.1147\/sj.164.0324"}],"container-title":["Proceedings of the ACM on Management of Data"],"original-title":[],"language":"en","link":[{"URL":"https:\/\/dl.acm.org\/doi\/pdf\/10.1145\/3639316","content-type":"unspecified","content-version":"vor","intended-application":"similarity-checking"}],"deposited":{"date-parts":[[2024,8,7]],"date-time":"2024-08-07T22:40:27Z","timestamp":1723070427000},"score":1,"resource":{"primary":{"URL":"https:\/\/dl.acm.org\/doi\/10.1145\/3639316"}},"subtitle":[],"short-title":[],"issued":{"date-parts":[[2024,3,12]]},"references-count":57,"journal-issue":{"issue":"1","published-print":{"date-parts":[[2024,3,12]]}},"alternative-id":["10.1145\/3639316"],"URL":"https:\/\/doi.org\/10.1145\/3639316","relation":{},"ISSN":["2836-6573"],"issn-type":[{"type":"electronic","value":"2836-6573"}],"subject":[],"published":{"date-parts":[[2024,3,12]]}}}