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

CS186
Prof. Joe Hellerstein
Dr. Minos Garofalakis
Fall 2005
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 pet store, Fuzzy Friends.  Fuzzy Friends needs to keep track of its merchandise and allow people to ask some basic queries about the pet merchandise.  For example, you may want to find all companies that sell food for a particular pet, or all toys in a particular category, 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 in 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 11:59 PM on Monday, September 5th

1. Save your "hw0.script" file in a directory called "hw0" [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.   Be sure to include your name, SID and cs186 login somewhere visible in the file.
3. cd into hw0.
4. Run:  submit hw0
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). You can run the command 'clients' to find a list of x86 server machines. 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. Type printenv PGPORT and make a note of the port number.  This is the port you will use to communicate with PostgreSQL.
  3. 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".  
  4. Ignore the instructions at the end of that message.  Instead, simply type pg_ctl start to start up the Postgres master process.
  5. Then type createdb hw0 to create the database named "hw0".  [That's a zero, not an "oh"].
  6. Type pgaccess, to start the graphical front-end to PostgreSQL.  (note: you don't have to use pgaccess in this assignment, you can also enter CREATE TABLE and INSERT commands directly using psql if you are more comfortable with the comand line.)
  7. 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,  enter the port number you got in step 2 above (if it's not already showing), and type hw0 for the Database field.  You can leave the other fields blank.  Then press the Open button.
    database->open

  8. 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. 
  9. You will be presented with an icon for the database for hw0.  Click on the + to open the database.
  10. 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.
  11. Pgaccess does not use SQL’s create table command. Instead, go to the Object menu and choose New to create a new table.
  12. 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 pet food that Fuzzy Friends stocks and put fields in the table for brand, pet species, food format (bagged, canned), sales, etc. You may also want to keep track of information about supply companies or customers, and so on.  You can ignore the Inherits,Constraint, and Check options in the dialog box.   NOTE: it's best to create all your table names and column names using only lower-case letters.  This will save you from having to type quotation marks in all your queries in the later steps.
    creating a table in PGAccess
  13. When you’ve got the fields you want set up, press Create  to create the table.
  14. Repeat this process for all the tables you want to define.
  15. 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
  16. 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.
  17. When you are done with pgaccess, be sure to close the database connection (under the Database menu) before you quit pgaccess. This way, the connection to your Postgres server will be closed properly.
  18. 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).   You can list the tables in your database by typing \d and list the attributes of a table by typing \d <tablename>
  19. To try a query in psql, simply enter it at the prompt.  Each query must be terminated by a semi-colon
    queries in psql

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

Hints