Week 10 agenda -- quiz, relational models, SQLite
20 Oct 2011Quiz
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.
SQLite
To query the databases, here are two good options that are available for most operating systems:
- The official 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, 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, andusers. Each user may have ranked many movies. The ratings table is linked to the others through themovie_idanduser_idfields.
Links
- 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.