DM26, Fall 2005 - Weekly Note 4

All teaching on Friday, October 7, as well as on Wednesday, November 9, is cancelled (due to the Årsfest and the Studievalgsdag, respectively). Officially, the teaching is supposed to take place on Saturdays (October 1, and November 12, respectively). However, I do not consider this a realistic solution for DM26, and the DM26 lectures and exercise classes are simply cancelled these two days. One implication is that the Friday exercise class will lag one week (plus the usual two days) behind the Wednesday exercise class in the period between the two dates.

In this course, we will be using PostgreSQL as DBMS. To use the PostgreSQL system at IMADA, you need to have an IMADA account (to log into IMADA machines) as well as a (separate) username and password for the PostgreSQL system.

Participants with an existing IMADA account will receive an email containing a username and a password for the PostgreSQL system (at their associated IMADA mail-account - be sure to check mail there (possibly using mail forwarding to another mail account, if preferred)).

Participants without an existing IMADA account should first acquire one by contacting Anders Fredslund, (unless they have a MIP account which can be copied to a parallel IMADA account (same login and password) by contacting Brian Truelsen). The lecturer should be contacted afterwards, for creation of PostgreSQL user name and password.

The following note on using PostgreSQL at IMADA explains how to use the PostgreSQL system at IMADA (remote use through ssh is possible).

Lecture September 28

More on SQL (advanced queries, definition of relation schemes, update of relation contents, constraints, triggers, views).

Reading

Ramakrishnan and Gehrke: Rest of Chapters 3 and 5 (you can skip 3.8).

Lecture October 5 (expected contents)

Use of the PostgreSQL system at IMADA. Database application programming, in particular JDBC. Stored procedures. Possibly start on normal forms.

Reading

Note on using PostgreSQL at IMADA. Ramakrishnan and Gehrke: Chapter 6, possibly start on Chapter 19.

Exercises October 5/14

Exercises 3.8, 3.10, 3.19 (only question 1), 5.1, 5.2, and 5.5 in Ramakrishnan and Gehrke.

Note: Table definitions and example data for all tables used in exercises in Chapter 5 can be found at the website of the textbook. I have prepared a version with PostgreSQL syntax (and a few errors removed as well).

The file dbbook.sql contains SQL commands for creating the tables. These can be executed as described in the note on using PostgreSQL at IMADA. The files with extension .txt contain the data. These can be loaded using the \copy meta-command in psql. For instance, the data for the relation "sailors" can be loaded as follows, assuming the data is in the file sailors.txt in the current directory of psql (note that meta-commands are not terminated by a semicolon):

\copy sailors FROM 'sailors.txt' WITH DELIMITER ',' NULL ''

The details of the \copy meta-command are similar to those of the COPY command in SQL (which for safety reasons requires superuser privileges, since it accesses the world as the user running the databaser server program) - see the documentation for COPY.

The data for these exercises cannot be added in arbitrary order, due to the foreign key constraints appearing in the table definitions (try it out and see for yourself).