Relational 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:
- The official sqlite command line interface.
- SQLite Manager, an add-on for Firefox. If you don't have Firefox installed, you need to install that first, and then navigate to the SQLite Manager site to install the add-on.
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.
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.
- 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.
- Last fall's homework and solutions.