CS 474 GP3: Example Queries
James Madison University, Spring 2017 Semester

Electronic submission due Mar 16th at 11:59 PM.
Each group creates a queries directory on GitHub.

Overview

The main goal of this assignment is for you to finish designing your database and determine what type of results your project will display. We will begin implementing the front-end (i.e., the web-based interface) in GP4. You have three main objectives at this point:

  1. Revise your schema design, based on your experience writing queries for HW3 and initial feedback from the instructor about GP2.

  2. Design and implement five or more queries that show compelling results. Your queries should be both interesting and comprehensive.

  3. Run your queries to populate example Google Charts for your app. The chart must directly address your educational research question.

These three steps should be an iterative process. You may find, while writing queries, that you are missing some of the data you need. In that case, you should revise and rerun your scripts from GP2. Be sure to commit any changes you make to your code in GitHub, and document each revision with a "-m" message.

SQL Functions

Create a queries directory in your git repository, and create a separate .sql file for each query. Like before, each member of your group must make at least one commit, and all commits should have a brief "-m" message in the git log.

Write each query as an SQL function. See  deccnt_state.sql for an example of how your source files might look. The file name should match the function name, and each file should contain a brief comment at the top that explains the query in English.

Here are several ideas of queries you may need to write, in no particular order:

Google Charts

In your queries directory, create a file named example.html. Copy one of the code snippets from the Chart Gallery and modify it to use actual data returned from your queries. Eventually your app will do this step automatically, but for now you should just hard code an example.

The chart data must be returned by a single function call. If you need to call multiple functions or call the same function multiple times, then your design will be incorrect.

Above the chart, show the (single) SQL statement in a pre and the query results in table (see HW4). Feel free to arrange and style these elements however you like, for example:

    SELECT dis_type, dec1_cnt
    FROM deccnt_state(2012, 12)
    ORDER BY dec1_cnt DESC, dis_type
Disability Type Dec 1st Count
SLD 4833
OHI 2330
ID 1467
AUT 1073
EMN 1033
MD 505
HI 120
SLI 100
OI 67
TBI 49
VI 44

Your chart file must be named example.html (all lowercase). Display your group name and research question at the top. You may use the following template: example.html

Although it's not required, feel free to include more than one chart. The more results you can show at this point, the more useful the feedback from your mentor and instructor will be.

Mentor Report

Before completing this assignment, you should meet with your mentor and present your initial results. Make sure your queries, data, and charts tell a compelling story. Do NOT wait to get started until the night before the deadline!

In addition to committing your code on GitHub, you will submit via Canvas a 1-page report with the following three sections:

  1. Briefly describe your client, where they work, what they do, their connection to Apps4VA, etc.
  2. Summarize your discussion, what questions your mentor asked, and the feedback you received.
  3. What insights you learned from the discussion, and what changes you will make to your project.