CS186	Joe Hellerstein
Spring 2003	Assignment 0

Your goal in this assignment is to start upPostgreSQL, define some tables, and try out some simple SQL. Theassignment is quite free-form, and will be graded on a pass/no-passbasis (i.e. if you turn in a script showing at least 2 tables and 2queries, you pass.) If you do not turn in this assignment, you willnot be allowed to continue in the course!
 
Specifically, your challenge is to design a smalldatabase for a record store, Bacteria Records. Bacteria needs tokeep track of its merchandise (records, CDs, tapes), and allow people toask queries about the merchandise. Some example queries might be to findall albums by a particular artist, find all artists who play aparticular instrument, etc.

For this assignment, you can lay out your datahowever you want in Postgres. All we require is that you define at least 2tables, with some data in them. Then, we ask you to write at least 2queries to go along with your tables – your queries shouldreturn some data.  You will be required to turn a script showing your data and queries.

Partners?

You will work individually on this assignment – nopartner is required or allowed.

What, Where and When to Turn In

You will need to use the unix submit programto hand in your assignment:
This assignment will be due at 5:00 PM onTuesday, January 28

1. Save your "hw0.script" file in adirectory called "hwk0" [that's a zero at the end, not an "oh"] in yourcs186 home directory.
2. Create a  "readme" [all lower case] file inthat same directory.    The readme should briefly describe what your tablesare 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:  submithwk0

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 toinitialize your database directory, and create your database.  At ashell 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 startto 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 connectto your Postgres server.  To do this, go tothe Database menu and choosethe 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.  Youcan leave the other fields blank.  Then press the Open button.
   
   
   
7. Pgaccess will present you with a window of twopanes. The left-hand pane should display a hierarchical navigationwidget.  Click on the +next to local (sockets) to openit. 
   
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->hw0database.  The only icons that will interest us in thisassignment are the Tables and Queries icons. We willwork with the Tables icon first.  Click on the Tables icon to proceed.
   
10. Pgaccess does not use SQL’s create tablecommand. Instead, go to the Objectmenu and choose New to create a new table.
11. The resulting dialog allows you to name thetable, to define column ("field") names and types, and set someproperties for each field. Play around with it and you should be able todefine fields and corresponding types without much trouble. Note thatthe "drop-down" menu for typeenumerates the various types provided by default in SQL.  Forexample, you could create a table for albums that Bacteria Recordsstocks and put fields in the table for artist, type of music, releasedate, sales, etc. You may also want to keep track of songs that eachalbum contains, musicians, and so on.  Youcan ignore the Inherits,Constraint, and Check options in the dialog box.
    
    
12. When you’ve got the fields you want set up,press Create  to createthe table.
13. Repeat this process for all the tables you wantto define.
    
14. You can manually insert data via pgaccess veryeasily. Simply double-click on a table in the Tables tab, and itwill pop up in "Datasheet View". It will show you all tuplescurrently in the table, and will allow you to add a tuple at the bottomof the table by clicking on the starred cells and typing in them. Addsome example data to all your 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 thequeries.  However, you may want to play with the pgaccess VisualDesigner interface to help you compose your SQL.  (This isoptional).  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 canadd tables, drag columns from one table to another, and drag columnsfrom the tables to the Fields at the bottom of the screen foroutput.  Press the Show SQLbutton to view the SQL you generate. You can type that text into aneditor and use it in the subsequent steps.
    
16. To use psql, return to a shell window, and type psql hw0at the prompt.  Once psql is running, you can type \help for help with SQL, and \? for help with psql commands (whichall begin with backslash).
    
17. To try a query in psql, simply enter it at theprompt.  Each query must be terminated by a semi-colon.
    
    
18. You are advised to edit your queries in aseparate window, and cut-and-paste to the psql prompt. psql will printout the results of the query on the screen.
19. Use the \qcommand or control-d to quit psql.
    
20. When you have the tables and queries the way youlike, you need to generate a script to turn in.  You will do thisvia the script command.
21. When you're ready, type script hw0.script at a shell prompt.
22. Type psql hw0to start psql.
23. For each table you defined, run the query select * from table; so we can seethe contents of the tables.
24. For each query you wrote, run that query.
25. Quit psql via \qor control-d.
26. Type exitto end the script program.  It should tell you Script done, output file is hw0.script.
27. Follow the instructions above for turning in yourhw0.script file.

Hints

• Feel free to install Postgres and pgaccess onyour own computers -- both should run on various flavors of UNIX (including Linux, BSD and Mac OS X), or Windows. We're usingPostgres version 7.2.2, available at ftp://ftp.postgresql.org/pub/source/v7.2.2.  We're using pgaccessversion 0.98.8, available athttp://www.pgaccess.org/download/pgaccess-0.98.8.tar.gz for UNIXvariants, orhttp://www.pgaccess.org/download/pgaccess-0_98_8-DLLS.zip forWindows. 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/Tkseparately, and to install the libgtcl libraries in postgres (in thesrc/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 beresponsible for helping you get Postgres or pgaccessrunning correctly on your machines -- this is strictly yourresponsibility.
• You do not haveto use pgaccess in this assignment -- you can issue create table and insert statements directly frompsql if you prefer.
  
• In your SQL queries, you need to put table andcolumn names in double-quotes if they include capital letters. Otherwise you'll get an error from Postgres that it doesn't recognizethe table or column.