Week 10: The relational model and SQL

Relational model examples

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

Class notes: week 10, week 11

SQLite

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 (which is probably hidden; hold the alt key and hit t to show it), 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

  • Letters and numbers, a tiny meaningless database we'll use to see how certain operations work.

  • 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.

  • Chinook database (sqlite). The Chinook database is a sample database available in many common formats. It models a digital media store, both the media (songs, etc.) and customers and invoices -- see the data model.