CMPSCI 645: Database Design and Implementation

System support for CS645 includes:


Installing PostgreSQL

PostgreSQL will be used for some of the programming exercises. Students can choose to install PostgreSQL on their own machines or use the Edlab machines. Instructions on installing PostgreSQL on students' own machines can be found
here.

Edlab Machines and Accounts

To verify your accounts you can log into Edlab (elnux.cs.umass.edu) using ssh as follows:
ssh yourUsername@elnux.cs.umass.edu

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. There are two ways to connect to the database. You can use: (1) a GUI called pgadmin, a nice tool that we would recommend using, (2) psql from a terminal window. Both are explained below.

The relevant database connection info:

  • database server: cs645db.cs.umass.edu
  • port 7645

pgadmin

pgadmin is a nice GUI that allows you to connect the database server and run queries. Setting it up requires 3 steps.

  1. Download and install pgadmin from the official site. The current version is 1.8.2.
  2. Set up an ssh tunnel. (more details are provided below)
  3. Connect to the database server with pgadmin. (details below)

Because the edlab machines restrict access to IPs within the edlab, an ssh tunnel must be setup to provide remote access to the database server from your non-edlab machine. The ssh tunnel can be setup as follows where yanlei is the username:

$ ssh -L 7645:elnux.cs.umass.edu:7645 -N yanlei@elnux.cs.umass.edu

The -L 7645:elunx.cs.umass.edu:7645 is in the form of -L local-port:host:remote-port.

Now you can connect to the database server with pgadmin. Open pgadmin and go to File -> Add Server ... Fill in the form as shown in the screenshot below:

pgadmin

  
 Notably, the hostname is cs645db.cs.umass.edu, port is 7645, and database is named using your login.

You should now be all set.

psql

psql is a terminal-based front-end to PostgreSQL. It enables you type in queries interactively, issue them to PostgreSQL, and see the query results.

Step 1: ssh into the elnux machine.

Step 2: Run the psql command on an elnux machine:

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

This connects to the specified database. Each user has a database with their own username. If you skip the database name, then by default the database named with your username will be connected. The username and password are those for your edlab account.

Step 3: You are now ready to issue queries.

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

elnux4> psql -h cs645db -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. Before logging into the edlab machines, you might want to scp the assignment files (data, sql queries etc.) to the edlab machine.

Some of the useful commands in PostgreSQL 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.