DM26, Fall 2004 - Weekly Note 4

Lecture September 22

More on SQL (definition of relation schemes, update of relation contents, constraints, triggers, views). Use of the PostgreSQL system at IMADA.

Reading

Ramakrishnan and Gehrke: Sections 3.3-4, 3.6-7, 5.7-9.

Note on using PostgreSQL at IMADA.

Remarks

As explained in the note on using PostgreSQL at IMADA, you need to have an IMADA account (to log into IMADA machines) as well as a username and a password for the PostgreSQL system.

Participants with an existing IMADA account should have received an email containing a username and a password for the PostgreSQL system. If not, please contact the lecturer. Participants without an existing IMADA account should first acquire one by contacting Anders Fredslund or Longina Przybyszewska, (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.

Lecture September 29 (Expected Contents)

Normal forms.

Reading

Ramakrishnan and Gehrke: Sections 19.1-8.

Exercises September 27

Exercises 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).

Do try (some of) your solutions for exercises in Chapter 5 on these data.

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 SQL command. For instance, the data for the relation "sailors" can be loaded using the following command (assuming the data is in the file /home/rolf/Temp/sailors.txt - change this to your own needs):

COPY sailors FROM '/home/rolf/Temp/sailors.txt' WITH DELIMITER ',' NULL '';

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

For the full details of the COPY command, see the documentation.

Exercises October 4

Exercises 3.8, 3.10, 3.19 (only question 1), 5.7, and 5.3 (in that order) in Ramakrishnan and Gehrke.