CS 257: Software Design

Web Application, Phase 2

Database setup and REST design

Now that you have chosen data to work with, written a first draft of your feature list, and produced some mockups, we're going to develop a database-driven HTTP-based API to be used by your eventual web app. This will involve several steps:

1. Design the table structure for your database
2. Load your data into the database using that table structure
3. Design your API's query structure and response structure
4. Write tests for the API
5. Implement the API in Python using flask (which maps URLs to Python functions) and the psycopg2 module (which enables Python programs to query PostgreSQL databases).

For Phase 2, you will do steps A, C, and D from this list.

What to hand in

1. Add a file webapp/phase2.txt to your cs257 repository. This file should contain:
   • For each table in your database design, show the SQL CREATE statement that you use to create the table. Here's an example from my authors & books database:
     CREATE TABLE authors ( id SERIAL, last_name TEXT, first_name TEXT, birth_year INT, death_year INT, PRIMARY KEY(id) );
   • For each API query type, briefly describe the query, show its syntax, and show the expected JSON response. Like this:
     SYNOPSIS: Get a list of all the books written by the specified author. QUERY (GET): /books/<author_name>/ RESPONSE: a list of dictionaries, each of which describes one book with keys 'title' and 'publication_year' EXAMPLE: http://whatever.com/books/Austen/ [{'title':'Pride and Prejudice', 'publication_year':1813}, {'title':'Sense and Sensibility', 'publication_year':1813}, {'title':'Emma', 'publication_year':1815}]
2. Add a Python program webapp/api_tests.py containing tests using the unittest module, covering all your API's query types. As with your WordGameAssistant unit tests, you're after thorough coverage of the typical and boundary cases for each query type.
3. Tag the cs257 repository with "webapp2"

Some notes

• Design your tables considering the principles we discussed in class on 4/20 and some introductory readings (e.g. this good example of a normalized database and the Wikipedia discussion of normalization).
• Similarly, design your API considering the REST principles we discussed in class on 4/13 and 4/20.
• Here's a quick unittest example: primechecker.py and primecheckertests.py.