What is DBSnap?

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.

Main Components of DBSnap

Supported Operators

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

Included Datasets

Sample Queries

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) 

Queries and Views

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.

Importing Datasets

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:

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.

Dataset Manipulation

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.

Dataset and Query Visualization

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)

ASU. DBSnap.