UNIVERSITY OF CALIFORNIA
College of Engineering
Department of EECS, Computer Science Division
Assignment 0: Getting Started
Your goal in this assignment is to start up
PostgreSQL, define some tables, and try out some simple SQL. The
assignment is quite free-form, and will be graded on a pass/no-pass
basis (i.e. if you turn in a script showing at least 2 tables and 2
queries, you pass.) If you do not turn in this assignment, you will
not be allowed to continue in the course!
Specifically, your challenge is to design a small
database for a bank, The Bank of Berkeley. TBoB needs to
keep track of its customers and their accounts. TBoB has checking
accounts and savings accounts. Each account is owned by a single
customer, though a customer may have more than one account. One
query that the bank would like to answer are getting a list of people
with checking account balances less than $100, so they can send out an
e-mail warning about bounced checks. Another query they want to
run is to add 1/3% interest to each savings account (something they do
at the end of the month).
For this assignment, you can lay out your data
however you want in Postgres. All we require is that you define at
tables, with some data in them. Then, we ask you to write at least 2
queries to go along with your tables – your queries should
return some data.
You will be required to turn a script showing your data and queries.
You will work individually on this assignment – no
partner is required or allowed.
What, Where and When to Turn In
You will need to use the unix submit program
to hand in your assignment:
This assignment will be due at 5:00 PM on
Thursday, September 4
1. Save your "hw0.script" file in a
directory called "hwk0" [that's a zero at the end, not an "oh"] in your
cs186 home directory.
Note: For this assignment, there will be no
credit for late submissions!! So start early, and plan on getting
done well before the due date.
2. Create a "readme" [all lower case] file in
that same directory. The readme should briefly describe what your tables
are and what your queries mean. Besure to include your
SID and cs186 login somewhere visible in the file.
3. cd into hwk0.
4. Run: submit
Detailed Instructions for Defining Tables &
Queries in Pgaccess
- Using any machine that runs X windows, ssh to one of the
Solaris x86 server machines (e.g. rhombus.cs, pentagon.cs, po.eecs or
torus.eecs). Make sure that your DISPLAY environment variable is set to
the machine you're sitting at. (Info on X Windows for the instructional
machines is available
- The first time you run postgres, you have to
initialize your database directory, and create your database. At
shell prompt, type initdb.
You should get output ending in a message about "success".
- Ignore the instructions at the end of that
Instead, simply type pg_ctl start
to start up the Postgres master process.
- Then type
createdb hw0 to create the database named
"hw0". [That's a zero, not an "oh"].
- Type pgaccess,
to start the graphical front-end to PostgreSQL.
- The first time you run pgaccess, you will need to
tell pgaccess how to connect
to your Postgres server. To do this, go to
the Database menu and choose
the Open command. In the dialog box, you should leave the Host field blank,
accept the default for the "port" field, and type hw0 for the Database field.
can leave the other fields blank. Then press the Open button.
- Pgaccess will present you with a window of two
panes. The left-hand pane should display a hierarchical navigation
widget. Click on the +
next to local (sockets) to
- You will be presented with an icon for the
database for hw0. Click
on the + to open the database.
- You will now be presented with a number of
"leaf-level" icons for the localhost->hw0
database. The only icons that will interest us in this
assignment are the Tables and Queries icons. We will
work with the Tables icon first. Click on the Tables icon to proceed.
- Pgaccess does not use SQL’s create table
command. Instead, go to the Object
menu and choose New to create a new table.
- The resulting dialog allows you to name the
table, to define column ("field") names and types, and set some
properties for each field. Play around with it and you should be able
define fields and corresponding types without much trouble. Note that
the "drop-down" menu for type
enumerates the various types provided by default in SQL. For
example, you could create a table for albums that Bacteria Records
stocks and put fields in the table for artist, type of music, release
date, sales, etc. You may also want to keep track of songs that each
album contains, musicians, and so on. You
can ignore the Inherits,Constraint, and Check options in the dialog box.
- When you’ve got the fields you want set up,
press Create to create
- Repeat this process for all the tables you want
- You can manually insert data via pgaccess very
easily. Simply double-click on a table in the Tables tab, and
will pop up in "Datasheet View". It will show you all tuples
currently in the table, and will allow you to add a tuple at the bottom
of the table by clicking on the starred cells and typing in them. Add
some example data to all your tables.
- Once you have inserted data into your tables,
you are ready to write queries. We will use the psql command-line tool to issue the
queries. However, you may want to play with the pgaccess Visual
Designer interface to help you compose your SQL. (This is
optional). The Visual Designer is available by clicking on the Queries icon, going to the Object menu and choosing New, and then pressing the Visual designer button. You
add tables, drag columns from one table to another, and drag columns
from the tables to the Fields at the bottom of the screen for
output. Press the Show SQL
button to view the SQL you generate. You can type that text into an
editor and use it in the subsequent steps.
- To use psql, return to a shell window, and type
at the prompt. Once psql is running, you can type \help for help with SQL, and \? for help with psql commands
all begin with backslash).
- To try a query in psql, simply enter it at the
prompt. Each query must be terminated by a semi-colon.
- You are advised to edit your queries in a
separate window, and cut-and-paste to the psql prompt. psql will print
out the results of the query on the screen.
- Use the \q
command or control-d to quit psql.
- When you have the tables and queries the way you
like, you need to generate a script to turn in. You will do this
via the script command.
- When you're ready, type script hw0.script at a shell prompt.
- Type psql hw0
to start psql.
- For each table you defined, run the query select * from table; so we can see
the contents of the tables.
- For each query you wrote, run that query.
- Quit psql via \q
- Type exit
to end the script program. It should tell you Script done, output file is hw0.script.
- Follow the instructions above for turning in your
- Feel free to install Postgres and pgaccess on
your own computers -- both should run on various flavors of UNIX
(including Linux, BSD and Mac OS X), or Windows. We're using
Postgres version 7.2.2, available at ftp://ftp.postgresql.org/pub/source/v7.2.2.
We're using pgaccess
version 0.98.8, available at
Windows. If you're installing pgaccess on your own, please replace the
files lib/connection.tcl and lib/preferences.tcl with our
slightly-modified versions: [connections.tcl],
[preferences.tcl]. Note that
pgaccess requires you to install Tcl/Tk
separately, and to install the libgtcl libraries in postgres (in the
src/interfaces/libpgtcl directory of the postgres tree.) For Mac OS X,
you will also need to install X windows (e.g. via fink). The TAs will not be
responsible for helping you get Postgres or pgaccess
running correctly on your machines -- this is strictly your
- You do not have
to use pgaccess in this assignment -- you can issue create table and insert statements directly from
psql if you prefer.
- In your SQL queries, you need to put table and
column names in double-quotes if they include capital letters.
Otherwise you'll get an error from Postgres that it doesn't recognize
the table or column.