#### Click [`Read More`](https://osf.io/q9g6u/wiki/home/) to see the whole page. This permanent OSF wiki page describes of all the materials required to **reproduce** or **replicate** the studies reported in the SIGMOD 2024 paper on [Relational Diagrams](https://relationaldiagrams.com/): > *On the reasonable effectiveness of Relational Diagrams: explaining relational query patterns and the pattern expressiveness of relational languages*. Wolfgang Gatterbauer and Cody Dunne. Proc. ACM on Management of Data (SIGMOD/PACMMOD), Volume 2, Issue 1, Article No.: 61, Pages 1-27. 2024. (Best Paper Honorable Mention.) DOI: [10.1145/3639316](https://doi.org/10.1145/3639316). Full version at [arxiv:2401.04758](https://arxiv.org/abs/2401.04758 ). If you use or learn from our resources (in particular, if you find the description of our human subject study design useful; we had thought the details quite a bit), please cite our paper: ```tex @Article{Gatterbauer2024ReasonableEffectiveness, author = {Gatterbauer, Wolfgang and Dunne, Cody}, journal = {Proc.\ ACM on Manag. Data (SIGMOD/PACMMOD)}, title = {On the reasonable effectiveness of {Relational Diagrams}: explaining relational query patterns and the pattern expressiveness of relational languages}, year = {2024}, number = {1}, volume = {2}, articleno = {61}, numpages = {27}, doi = {10.1145/3639316}, url = {https://doi.org/10.1145/3639316}, note = {Full version at \url{https://arxiv.org/abs/2401.04758}. Supplemental material at \url{https://osf.io/q9g6u/}.}, } ``` ## High-level Description & Organization This OSF folder and associated components contains all necessary supplemental material to: 1. reproduce the textbook analysis from Section 6.1 (i.e., see all the collected queries and our analysis of which of the alternatives can pattern-represent each query); 2. reproduce all analyses and figures Section 6.2, using the data collected from the user study; 3. reproduce the creation of the (randomized and fully parameterized) user study stimuli; and 4. replicate the full user study experiment design, analysis plan, analysis code, and deploy the user study stimuli on [**Amazon Mechanical Turk** (AMT)](https://www.mturk.com/), extract the collected data, and approve/reject/pay participants. All our files can be viewed below under `Files` and in the right panel under `Components`. Click on any file or folder then `Download` or `Download as zip` at the top of the files pane. The project components each have linked GitHub repositories: see `GitHub` in the `Files` view, vs. `OSF Storage` which we use for long-term archival. `GitHub` repos can be opened by clicking on them, then clicking on `Open` at the top of the `Files` view. Here is an overview of the supplemental materials. Each of the GitHub repositories and code snapshots include `README.md` files with instructions for deployment and use. * **Project homepage**: describes the high-level motivation and links to all resources (including videos) * <https://relationaldiagrams.com/> * **Full version of the paper with all appendices**: contains all proofs, all study materials, and further illustrations: * [arXiv preprint](https://arxiv.org/abs/2401.04758) / [OSF preprint](https://osf.io/8xnrs) (both PDFs are identical) * **Main suplemental material project** on OSF ([this project you are reading](https://osf.io/q9g6u/)). It contains four key pieces: 1. [Textbook analysis (as PDF on OSF)](https://osf.io/u7c4z) 2. [Data Analysis Code](https://osf.io/ykfwa/) component ([GitHub](https://github.com/VisDunneRight/rel-diag_mturk_analysis), [snapshot](https://osf.io/gu478)) 3. [Stimuli-generating code](https://osf.io/au57t/) component ([GitHub](https://github.com/northeastern-datalab/diagrams_pattern_learning_user_study/), [snapshot](https://osf.io/kgx4y)) 4. [MTurk Server and Data Collection Code](https://osf.io/8vx4u/) component ([GitHub](https://github.com/VisDunneRight/rel-diag_mturk/), [snapshot](https://osf.io/suj4a)) It also contains specific files with OSF links, some excerpted from elsewhere: * [Immutable, timestamped study preregistration](https://osf.io/4zpsk/). Note: what peer reviewer saw was this [anonymized link](https://osf.io/4zpsk/?view_only=e1af8373416446d49ae658f196f05d3c). * [User study tutorial](https://osf.io/mruzw) * [The stimuli](https://osf.io/d5qaj) * [Stimuli/schema index CSV](https://osf.io/u8bf9) * [Stimuli/schema index JSON](https://osf.io/sn83j) * [Collected data](https://osf.io/8vm42) * [Executed user study analysis code](https://osf.io/f2xe3) ## Detailed Availability, Reproducibility, and Replicability Instructions We suggest following steps to validate (and possibly extend) our studies. ### Reproducing analyses and figures The paper contains two sections that are relevant for the SIGMOD Reproduciblity submission: Sect. 6.1 Relational Diagrams for Textbook Queries, and Sect. 6.2 Controlled user study. #### Sec. 6.1 Relational Diagrams for Textbook Queries * **Artifacts Available:** Check that our [our textbook analysis slides](https://osf.io/u7c4z) are available in a publicly accessible archival repository, ideally one that complies with the [FAIR principles](https://doi.org/10.1038/sdata.2016.18). I.e., it ensures the Findability, Accessibility, Interoperability, and Reuse of digital assets. (OSF is a FAIR repository, GitHub likely is not.) * **Artifacts Evaluated — Reusable:** Examine the contents of the slides and ensure there is sufficient documentation, completeness, and consistency. * **Results Validated — Reproduced:** Check the validity of the analysis and compare with the paper (est. 3 hours). #### Sec. 6.2 Controlled user study There are several ways in which our controlled experiment can be reproduced. A simple way would be to: 1. Verify that the data we collected as part of the user study is available and understandable, 2. Deploy the data analysis code in a Python environment (est. 20 minutes), and 3. Reproduce the data analysis and creating the results and visualizations presented in the paper (est. 5 minutes, 20 minutes to verify). Our previous and similarly designed user study for QueryVis ([Leventidis et al., SIGMOD, 2020](https://doi.org/10.1145/3318464.3389767)) received one of three SIGMOD 2021 Most Reproducible Paper Awards. Its reproducibility report is available at ([Fernandez, SIGMOD ARI, 2020](https://reproducibility.sigmod.org/rep_rep/2020/RR-1.pdf)), but does not include time estimates. Applying Fernandez's steps to reproduce the 2020 paper to this paper (with our time estimates) would include: 1. Verifying preregistration timestamps (est. 2 minutes). 2. Following the tutorial (est. 6 minutes). 3. Taking the controlled experiment (user study) pre-screening questions (est. 10 minutes). 4. Studying the data analysis results (est. 1 hour). 5. Deploying the data analysis code in a Python environment (est. 20 minutes). 6. Reproducing the data analysis and creating the results and visualizations presented in the paper (est. 5 minutes, 20 minutes to verify). Additionally, a reproduction could include: 7. Validating the provided stimuli (est. 10 minutes) 8. Generating and comparing generated random stimuli to the ones used in the controlled experiment (est. 15 minutes) ### Replicating the controlled experiment (This goes beyond current SIGMOD reproducability) The [SIGMOD reproducibility & availability initiative](https://reproducibility.sigmod.org/) currently does not involve replicating controlled experiments like our user study). However, replicating our controlled experiment could help validate the initial results. There are many steps that are necessary for replicating the data collection on MTurk and data analysis. One would need to prepare the experiment approvals, and software; perform pilot testing; perform data collection; perform data analysis; and reflect on results. We estimate it would take 27–69 hours of effort + several steps that require waiting (such as data collection) to accomplish. Specific steps would include: 1. Determine whether the existing study design and analysis is appropriate and satisfies the goals of your replication (est. 2–8 hours). 2. Obtain IRB or research ethics board approval. This would require writing up the IRB protocol (est. 2 hours) and waiting for approval (est. 2 weeks—3 months depending on institution). Alternatively, we can add the replication team to our IRB protocol (est. 30 minutes + 2 weeks for approval)—they must have each completed the CITI Human Subjects Research Course (est. 1 hour). If the CITI training is unavailable through the replication team's institutions, we can investigate whether Northeastern can provide access. 3. Write the new study design and analysis plan to be preregistered and ensure all your co-authors approve. This could be a quick extension of our preregistration (est. 1 hour) or require substantial rewrites if the replication study design or analysis plan is different (time varies). 4. Contact the authors of the original study to request feedback on your replication design (time varies). 5. Make the immutable, timestamped preregistration (est. 10 minutes, but 30 minutes to validate everything before submission). 6. Register for accounts on <https://aws.amazon.com/> for payment, <https://requester.mturk.com/> for deployment, and <https://requester.mturk.com/developer/sandbox> for testing (est. 10 minutes). 7. Estimate the funding required for participants, ensure MTurk allows that much payment in a month (est. limit 500 USD) and ask for increased fund limit if necessary, then fund the MTurk requestor account. We spent approx. 600 USD. (est. 15 minutes). 8. Create private GitHub repositories for the server and analysis code by copying or forking our code (est. 5 minutes). 9. IMPORTANT: Create `.gitignore` files in the repositories that ensure no participant data such as MTurk IDs are uploaded, nor environment variables with your private keys (you can copy ours) (est. 5 minutes). 10. Create MTurk qualifications that 1. Require passing the qualification and 2. Disallow previous participants. We can provide MTurk IDs—these must not be shared publicly or beyond the replication team (est. 20 minutes). 11. Set up the local development environment including installing Python packages, the Heroku CLI, and filling in environment variables (est. 30 minutes, could be long if issues arise). 12. Run locally and test. Ensure hard-coded variables and set and content is updated, including the IRB consent form and contact information (est. 2 hours, could be long if issues arise). 13. Register for account on Heroku, provide payment information, and set up Postgres for data storage and Papertrail for logging. This cost us approx. $22 USD per month (est. 20 minutes). 14. Set up the deployment environment including installing the Heroku CLI for deployment, PGAdmin for monitoring the database, creating the remote deployment environment variables for Heroku, configuring Heroku to deploy upon GitHub commits to the server repository, and testing deployment (est. 1 hour). 15. Have 6+ pilot testers you know try the experiment, report on any questions or issues they had, and do a sanity check on the logs from Papertrail and the data in the Postgres database (est. 2 hours + time for testing and any corrections that need to be made). 16. Ensure the sandbox MTurk environment variables are set correctly, all scripts are updated with the correct hard-coded information, deploy the qualifications and HIT on the MTurk sandbox, then test the deployment/HIT management/analysis code (est. 1 hour). 17. Ensure the live MTurk environment variables are set correctly, all scripts are updated with the correct hard-coded information, then deploy the qualifications and HIT on MTurk live (est. 1 hour). 18. Collect data on Postgres, check Papertrail logs, and respond rapidly to any participant emails re: issues to ensure everything is working correctly (est. 4 hours + 27 hours–96 hours to run—it took us 27 hours). 19. Ensure the hard-coded variables in the HIT management scripts are correct, then grade, approve, and reject HITs. If there are issues such as participants with technical problems, be prepared to accept/reject on a case-by-case basis (est. 2 hours). 20. Request reimbursement from MTurk for excess funds (est. 5 minutes). 21. Export the data from Postgres, anonymize participant information (MTurk IDs), and transform into the needed analysis format. Remember that participant data should be anonymized and must not be uploaded to public repositories (est. 25 minutes). 22. Analyze the data based on your analysis plan, reflect on any possible data issues, reflect on differences between the original study and your replication, and examine the results to see whether you can reach similar conclusions with similar effect sizes to the original study (est. 3 hours). 23. Ensure sufficient supplemental replication is made available to reproduce and replicate your study. Check again that you are not including participant or confidential information, then post on a FAIR repository (Findable, Accessible, Interoperable and Re-usable) such as OSF. See ([Wilkinson et al., Scientific Data, 2016](https://doi.org/10.1038/sdata.2016.18)) (est. 25 minutes). 24. Write up your replication results for sharing publicly and submit for peer review (either by your conference/community organization or a reputable venue) (at least 4 hours, time required could vary substantially). ### Systems This code was run on a Windows 11 machine, version 10.0.226.31.4112, sometimes using WSL 2.2.4.0 and Ubuntu 20.04.6 LTS. Much of the code used Python version 3.12.4 (Windows) and Python 3.11.7 (Ubuntu). The study was deployed using Heroku and the Heroku CLI, version 7.61.1 and node win32-x64 14.19.0. Heroku versions were the standard deployments between 2023-07-06 and 2024-07-13 ## Contact information Please reach out to [Wolfgang Gatterbauer](https://orcid.org/0000-0002-9614-0504) AND [Cody Dunne](https://orcid.org/0000-0002-1609-9776) with any questions.