DBSnap is a web-based application to build database queries, particularly relational algebra queries, by snapping blocks. One of DBSnap’s key and differentiating features is that it uses a tree-based query structure that is highly similar to the intuitive query trees used by many textbooks and educators. DBSnap is also a highly interactive tool that shows the query result and equivalent relational algebra expression while a query is being built.

Selection	Projection	Aggregation	Rename	Natural Join
Theta Join	Cross Product	Union	Intersection	Difference

Query in Relational Algebra	Query in DBSnap
Query: (Professors) PRODUCT (Courses) Description: Display every possible combination of Professors and Courses.
Query: (Students A) UNION (Students B) Description: List the students who belong to Student A or Student B.
Query: (Students A) INTERSECTION (Students B) Description: List students that are common to both Students A and Students B.
Query: (Students A) DIFFERENCE (Students B) Description: List students that are in Students A that are not in Students B.
Query: SID, LEVEL GROUP count(CID) ((Students) NJOIN (Course_Student)) Description: List each student's ID, the level of that student and how many courses they are taking.
Query: CID GROUP count(SID) ((Course_Student) NJOIN (SELECT LEVEL="Senior" (students)) Description: List how many seniors are taking each course.

Query in Relational Algebra	Query in DBSnap
Query: SELECT Age > 21 (Students) Description: List the Students older than 21.
Query: PROJECT SID, FNAME (Students) Description: Display the student ID and first name of Students.
Query: LEVEL GROUP avg(AGE) (Students) Description: Group the Students by level and find the average age for each level of Students.
Query: RENAME Lname -> LastName (Students) Description: Rename attribute 3 to Lastname and rename table Students to Learners
Query: (Courses) NJOIN (Course_Professor) Description: For every Course, list all the Course_Professors teaching it.
Query: (STUDENTS) THETA JOIN (T_Assistants) Description: List the student details of all students who are teaching assistants. (T_Assistants data set is available to be imported)

DBSnap can save and import queries, as well as create Views.

Saving Queries

• To save a query, click on the "Save Query" button (floppy disk).
• Queries may also be saved by right clicking on the query block within the query area and selecting "Save Query".
• Both of these methods save the query as a .dbs file. The file will be saved to the computer.

Importing Queries

• A query file stored on the disk (.dbs) can be imported by clicking on the “Import Query” button in the toolbar.

Creating Views

• To create a view, click on the "Create View" button in the toolbar.
• Views may also be created by right clicking on the query block within the query area and selecting "Create View".
• The created view will appear as a View block in the Dataset panel.

To import a dataset (table or relation) from a file, either drag and drop the file unto the app or click on the "Import Data Set" button in the toolbar. The file should have a .txt extension and the data must use a CSV-like format. Instead of using a comma as the delimiter, use a pipe "|" (example files are provided below). At a minimum, there must be three lines:

1. The name of the new dataset
2. The column names and types (columnName(TYPE))
3. The values for a single row in the new dataset

Column Types

The currently supported value types are the following:

• STRING
• NUMBER
• DOUBLE

Column Names

Valid characters for column names are all alphabet characters A-Z, all digits 0-9, and dashes "-".

Example Datasets

As an example, provided is a subset of the TPC-H benchmark data set including 100 records from both the Customer and Orders tables.

• Customer
• Orders
• Teaching Assistants (used in the "Theta Join" operator example. )

• Life Expectancy, Fertility Rate, and Population Data

DBSnap allows for editing of datasets, within the app. Add, remove, or update elements of a dataset before connecting to an opperator.

Adding New Records

• To add a row, left click on the "Add Row" button (plus icon) in the upper-right corner of the "Node Result" window.
• A popup window will appear, requesting values corresponding to the headers of the dataset table.

• Fill in the required data, with appropriate datatypes, and click "Update Table" to add a new row with the inputed data.

Deleting Records

• To delete a row from the dataset, click on the trashcan on the right side next to the desired row to be deleted.
• Data may also be deleted from within the "Open Table" view.

Updating Values

• To update a value in the grid, click on the value to be updated and it will be highlighted blue.
• From here, type in a value to replace the previous value.

DBSnap allows for different chart types to visualize datasets and queries.

Creating Charts

• To display a chart of a dataset/query result, left click on the "Open Chart" button (bar graph icon) in the upper-right corner of the "Node Result" or "Query Result" window.
• This opens a pop-up window, asking for inputs of chart type(Area, Bar, Column, Histogram, Line, or Pie), x-axis, and y-axis. Fill in the desired chart values and click on "Create Chart" to create the chart.
• Example Charts:
• 1. Number of seniors taking each course

• 2. Percentage of students that are each level

• 3. Average price of each day's orders (using orders dataset from example datasets)