Mines Logo
CSCI 403: Database Management Spring 2018
CSCI 403 Home · Policies · Schedule · Projects · Help!

Resources

Connecting to the Database

Note: The database server lives on flowers.mines.edu, which is behind the Mines firewall. So, if you are using an off-campus computer, you will need to get inside the firewall via vpn.mines.edu (also see VPN).

psql

psql is the command line query utility from the PostgreSQL folks. If you are running linux on your own personal machine, you can obtain psql by installing the postgresql-client-9.x package (where x may depend on how current your linux install is - 6 is the latest; our server is running version 9.5.8; any 9.x series should work without a problem). Windows and Mac OS X versions are available from the course website (I've extracted them for you from the PostgreSQL server installation for those platforms). The Windows and Mac OS X software are in .zip files which you need to unzip somewhere on your computer. You need to then open a command line environment (Mac OS X Terminal, Windows command prompt or powershell prompt), then navigate to where the executable is.

To use, simply run it as you would any other command line utility, passing in the connection string as a command line parameter as follows:

(if in your path)

        psql postgresql://yourid@flowers.mines.edu/csci403

(from the executable directory on OS X or linux)

        ./psql postgresql://yourid@flowers.mines.edu/csci403

(from the executable directory on Windows in powershell)

        .\psql.exe postgresql://yourid@flowers.mines.edu/csci403

To execute queries, type in the query at the prompt. You can use multiple lines for long query strings; nothing will actually happen until you enter a ';'. The semicolon is the marker for the end of a query in SQL. When you are done, enter \q to exit.

psql is quite powerful, and sufficient for anything you need to do this semester, but it is a purely text-based application, so large query results can be difficult to view. (You can always export to a text or .csv file and view in other software.) As an alternative, or as a supplement to psql, you may wish to use the graphical SQuirreL SQL client (see below). One important feature of psql is that it gives you a function similar to the COPY command of PostgreSQL (see \COPY in the psql reference). This command is often the easiest way to bulk-load large datasets into the database.

SQuirreL SQL

SQuirreL SQL is a client for working with just about any kind of database. It is Java-based (so runs on any platform supporting Java) and provides a GUI. Installation instructions for SQuirreL SQL are here.

When you first launch SQuirreL, you will need to tell it where the jdbc driver file (see links above) is and tell it how to connect to the database. On the left-hand side of the application screen you should see a couple of vertical tabs, one labeled "Drivers" and the other labeled "Aliases". Click on Drivers, then on the + icon in the pane that opens up. You will get a window asking for information on your jdbc driver. Enter the requested information as shown below (substituting the path to your driver file in the Extra Class Path pane):

Click OK. Next, click on the Aliases tab, and again click on the + icon in the pane that opens up. Enter the information as shown below (leave the password field blank):

Click OK to close that screen, then double-click on the newly created CSCI 403 alias to connect to the server. Note that SQuirreL gives you lots of tools for exploring the different objects in the database. To execute queries you need to click on the SQL tab in the main part of the window. Type a query into the pane under the tabs, then either click on the "running man" icon or press Ctrl-Enter to run the query. Your results will appear in the pane below.

Getting Help

First, try Piazza!
First, try Piazza!
First, try Piazza!

You might get help fastest via a classmate on Piazza. I will be looking at Piazza, too, and will usually respond fairly quickly if I am at my computer. When you post on Piazza, please do not post complete programs or solutions; try to ask or answer questions without code first, or use only the smallest amount of code necessary to get your question or point across.

Once you've tried Piazza, if you still have questions, contact me directly, and I will help you. You should also feel free to contact me at anytime about any concerns which you aren't comfortable sharing on Piazza.

Please do not contact the grader with questions about assignments or your grade - contact me directly with any questions about grading.