DM505, Spring 2006, 4th Quarter - Weekly Note 4

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 student email address).

Participants without an existing IMADA account should first acquire one by contacting Anders Fredslund. 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 April 26

More on relational algebra. Start on SQL.

Reading

Ramakrishnan and Gehrke: Section 4.2, Sections 5.1-4.

Lecture May 1 (Expected contents)

More on SQL: aggregations, NULL values, outer joins, general constraints, triggers, views. Use of the PostgreSQL system at IMADA. Start on functional dependencies and normal forms.

Reading

Ramakrishnan and Gehrke: Sections 5.5-9, 3.6, and 19.1-3. Note on using PostgreSQL at IMADA.

Lecture May 4 (Expected contents)

More on functional dependencies and normal forms. Database application programming using JDBC. Stored procedures.

Reading

Ramakrishnan and Gehrke: Sections 19.4-7, 6.3, and 6.5. Note on using JDBC with PostgreSQL at IMADA.

Exercises May 3

From last weekly note: exercises 4.3 (only the relational algebra versions of the queries) and 4.4 in Ramakrishnan and Gehrke.

Discussion of project, stage 2 (if needed).

Exercises 3.8, 3.10, 3.19 (only question 1), and 5.1 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).