System support for CS645 includes:

Installing PostgreSQL

PostgreSQL will be used for some of the programming exercises. We recommend that students use PostgreSQL that has been installed on Edlab machines. See detailed instructions below. Students may choose to install PostgreSQL on their own machines. Instructions on installing PostgreSQL on students' own machines can be found here.

Edlab Machines and Accounts

Edlab has 7 Linux machines (elnux1.cs.umass.edu - elnux7.cs.umass.edu) available for remote logins. The important information for Edlab can be found at:
http://www-edlab.cs.umass.edu/

All students have an account. The user name is the same as a student's OIT username used by Spire. The initial default password is

ELxxxaaa
- where xxx is the last 3 digits of the ID number
- where aaa is the first 3 characters/digits of the username

(unless he/she had an account last semester, then it will be the password she/he set last semester).

Connecting to PostgreSQL Server

The Postgres database server is setup in the Edlab. The simplest way is to use a terminal window with the following commands. Alternatively, a student can try to use a GUI called pgadmin. But this is left to the discretion of the student.

First, connect to an Elnux machine, with the following example assuming that the user name is "yanlei" (please replace it with your own user name).

$ ssh yanlei@elnux1.cs.umass.edu

After you log into an elnux machine, you can type the following command to log into your own PostgreSQL database:

elnux1> psql -h cs645db.cs.umass.edu -p 7645

If the database name is not explicitly specified, by default the database named with your username will be connected. The username and password are those for your Edlab account.

If the students later want to access a different database, e.g., used in a joint project, you can use:

elnux1> psql -h cs645db.cs.umass.edu -p 7645 -d 'databasename'

Step 3: You are now ready to issue queries.

You can also execute a SQL query directly from command prompt as:

elnux4> psql -h db-edlab -p 7645 < sql-commands

where the file sql-commands contains the SQL commands to be executed.

PostgreSQL Commands

A complete list of psql instructions can be found here. Some of the userful commands are listed below.

Connecting to a database

\connect (or \c) [ dbname [ username ] ]

    Establishes a connection to a new database and/or under a user name. The previous connection is closed. If dbname is - the current database name is assumed.

    If username is omitted the current user name is assumed.

    As a special rule, \connect without any arguments will connect to the default database as the default user (as you would have gotten by starting psql without any arguments).

Viewing the list of databases and the list of relations

\list List all available databases.

\d  lists all the relations in the current database.

\d [relation_name] lists schema of the relation relation_name in the current database.

Creating a relation

You can spread a command over several lines of input.

testdb=> CREATE TABLE my_table (
testdb(>  first integer not null default 0,
testdb(>  second text)
testdb-> ;
CREATE TABLE

Loading data into relations

\copy "relation_name" FROM file_name with delimiter as ','

data will be read from file file_name and loaded into the relation. The file contains the data row by row. The default delimiter is tab.

Running Queries

testdb=> SELECT * FROM my_table;

Quitting psql

\q allows you to quit psql.

CMPSCI 645: Database Design and Implementation