Week 10 agenda -- quiz, relational models, SQLite

20 Oct 2011


Quiz on Excel! You don't have to worry about complicated stuff like simulation, but be prepared to use Excel in a variety of more basic ways (like the more straightforward parts of the homework).

Relation model examples

In class we'll go over the relational model. We'll be using databases in SQLite format.


To query the databases, here are two good options that are available for most operating systems:

SQLite Manager usage

After installing the add-on, you can find it in the Tools menu, or under the main menu in the Web Developer section. After opening a database, when you execute SQL statements (in the Execute SQL panel), you can save all resulting rows to a file, or you can copy selected rows. To select multiple rows, hold control and click to select individual rows, or hold shift and click to select a range of rows.

Example databases

  • Several tables of UN data, from UNdata.

    Each table contains data for all (or almost all) countries for a range of years. The tables can be linked through the country and year fields. All fields are in the obvious units, except for precip, which is in millions of cubic meters.

  • 100k movie ratings, from MovieLens. The database link will be removed after the course is over, but this script will generate the sqlite database from the original MovieLens data set.

    The database contains tables movies, ratings, and users. Each user may have ranked many movies. The ratings table is linked to the others through the movie_id and user_id fields.


  • SQL Zoo: online exercises starting from basics but working up through advanced concepts.
  • SQL for Web Nerds: more advanced and detailed tutorial; maybe revisit after we do more programming.
  • Database in Depth: advanced book on the relational model.