CS 386/586 Introduction to Database Systems

Accounts

CS 586 students will need an account to log in on one of the various Unix servers cecs.pdx.edu or cs.pdx.edu.  If you don't already have an account, go here and click on Accounts for instructions. You will have to go to one of the CAT front desks in person to activate your account and get your password.

Note: You do not need a CECS account to use the PhpPgAdmin interface.

Sample Database

Spy database: Description ; Schema

A text file of commands to create the database in PostgreSQL ( If you have your own database, run "psql -e < SpyDB.sql" from your shell): Spy .  You may need to customize null values.

How to Access these databases on PostgreSQL

You can access an installation of PostgreSQL via a command line or GUI interface.  Both are explained on http://www.cat.pdx.edu/web/database-services.html . On that page you can also learn how to get your own Postgres database on the CECS server.  In order to access the shared copies of the databases that we will all use, there are a few changes to those directions.

• From any cat-managed machine, get to the command line interface with

% psql –h dbclass.cs.pdx.edu –U introdb_readonly introdb_spy

and use the password introdb .

• Access the GUI from any browser via https://dbclass.cs.pdx.edu/phppgadmin/.
• Click on the Database Class instance in the left pane to get a login prompt.
• Enter the user / password introdb_readonly / introdb . To get past the first page of the GUI, click on the name of the database you want to access.

Other Example Databases

Library database: Description ; Schema; Creation Commands

Sailors database: Description in textbook; Schema; Creation Commands

Technical Information

This material is partially out of date – will be updated shortly.

PostgreSQL information:

• PostgreSQL 8.0 documentation. (An earlier version has a nice SQL reference)
• PostgreSQL on Windows.

Other Resources:

• SQL 92 ((Second Informal Review Draft) ISO/IEC 9075:1992, Database Language SQL- July 30, 1992
  (from http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt)
• SQL References
• Answers to odd numbered questions in the text.