CS 257: Software Design

Web Application, Phase 4: Database and API design

Designing your database and your API

Now that you have chosen data to work with, written a first draft of your feature list (via user stories), 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. (Note that the API you are designing is intended to give access to your database. Your web app will not be accessing any external API not written by you.)
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 4, you will do steps A, C, and D from this list.

What to hand in

1. Add a file webapp/doc/phase4.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 );
   • 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/authorname/<author_name>/ RESPONSE: a list of dictionaries, each of which describes one book with keys 'title' and 'publication_year' EXAMPLE: http://whatever.com/books/authorname/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/tests/api_tests.py containing tests using the unittest module, covering all your API's query types. As with your books unit tests, you're after thorough coverage of the typical and boundary cases for each query type.
3. Tag your repository with "webapp_phase4"

Some notes

• Design your tables considering the principles we will discuss in class 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.