DM26, Fall 2006 - Weekly Note 6

Note: Week 42 is the fall break, and no teaching is taking place.

Announcement: Friday, November 3, the next Imada party (Matalogifest) will take place. Sign up at the secretaries office.

Lecture October 9

More on SQL, query part.

Reading

Ramakrishnan and Gehrke: Rest of Sections 5.1-5.

Lecture October 13 (Expected Contents)

Finishing of SQL. JDBC. Stored procedures. Start on normal forms.

Reading

Ramakrishnan and Gehrke: Rest of Chapter 5. Section 3.6. Section 6.3. Section 6.5. Sections 19.1-3.

Exercises October 23

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. It is more fun, and will prepare you for the project. If you have trouble using PostgreSQL on the Imada system, instructor Rune will be available in the room "Balkonen" (across from the Imada terminal room) Friday, October 13, between 9 and 11 (he will often be there at other times, too).

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