UNIVERSITY OF CALIFORNIA
College of Engineering
Department of EECS, Computer Science Division

CS186
Joe Hellerstein
Spring 2003
Assignment 0

Assignment 0: Getting Started with Postgres

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 record store, Bacteria Records. Bacteria needs to keep track of its merchandise (records, CDs, tapes), and allow people to ask queries about the merchandise. Some example queries might be to find all albums by a particular artist, find all artists who play a particular instrument, etc.

For this assignment, you can lay out your data however you want in Postgres. All we require is that you define at least 2 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.

Partners?

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 Tuesday, January 28

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.
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 name, SID and cs186 login somewhere visible in the file.
3. cd into hwk0.
4. Run:  submit hwk0
Note: For this assignment, there will be no credit for late submissions!! So start early, and plan on getting things done well before the due date.

Detailed Instructions for Defining Tables & Queries in Pgaccess

  1. 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 here).
  2. The first time you run postgres, you have to initialize your database directory, and create your database.  At a shell prompt, type initdb.  You should get output ending in a message about "success".  
  3. Ignore the instructions at the end of that message.  Instead, simply type pg_ctl start to start up the Postgres master process.
  4. Then type createdb hw0 to create the database named "hw0".  [That's a zero, not an "oh"].
  5. Type pgaccess, to start the graphical front-end to PostgreSQL. 
  6. 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.  You can leave the other fields blank.  Then press the Open button.
    database->open

  7. 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 open it. 
  8. You will be presented with an icon for the database for hw0.  Click on the + to open the database.
  9. 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.
  10. Pgaccess does not use SQL’s create table command. Instead, go to the Object menu and choose New to create a new table.
  11. 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 to 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.
    creating a table in PGAccess
  12. When you’ve got the fields you want set up, press Create  to create the table.
  13. Repeat this process for all the tables you want to define.
  14. You can manually insert data via pgaccess very easily. Simply double-click on a table in the Tables tab, and it 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.
    inserting data into Tables
  15. 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 can 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.
  16. To use psql, return to a shell window, and type psql hw0 at the prompt.  Once psql is running, you can type \help for help with SQL, and \? for help with psql commands (which all begin with backslash).
  17. To try a query in psql, simply enter it at the prompt.  Each query must be terminated by a semi-colon.
    queries in psql

  18. 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.
  19. Use the \q command or control-d to quit psql.
  20. 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.
  21. When you're ready, type script hw0.script at a shell prompt.
  22. Type psql hw0 to start psql.
  23. For each table you defined, run the query select * from table; so we can see the contents of the tables.
  24. For each query you wrote, run that query.
  25. Quit psql via \q or control-d.
  26. Type exit to end the script program.  It should tell you Script done, output file is hw0.script.
  27. Follow the instructions above for turning in your hw0.script file.

Hints