Assignment 0: Simple SQL Practice

Carnegie Mellon Computer Science CS415 S03
Jan. 15, 2003
Due at 1:00PM on Wednesday, Jan. 22nd

DESCRIPTION

NOTE: This is a "Pass/Fail" assignment. You will not receive any points on it, but you have to pass it in order to stay in the course. Read "REQUIREMENTS" carefully!!

The assignment is quite free-form. The purpose is to familiarize yourselves with PostgreSQL and try some simple SQL. Specially, you will design a tiny database for a grocery store, "Little Eagle". The manager wants to keep records for: customers, merchandise, and transactions and allow people to ask queries about the transactions. The example queries might be to find all the products brought by a particular customer, or to find all customers who brought milk, etc.

The design is up to you. The principle here is keeping things simple.
Here is a rough example design to give you a clearer idea about what we expect from you (you can use this design in your homework):

Three Tables:
Customers (C_ID int, C_Name Char(40), C_Addr Char(50))
Products (P_ID int, P_Name Char(40), P_Stack int)
Transactions (T_ID int, T_CID int, T_PID int, T_Amount int)

You need to populate the tables (10 records per table would be enough to show the idea), and ask queries on them.

You should at least define 2 tables, with some data in them. Then, you write at least 2 queries (no more than 5 queries) to go along with your tables. Your queries should return some data.

REQUIREMENTS

  1. NO PARTNERS. You should complete this assignment all by yourself.
  2. The assignment is due at 1:00PM on Wednesday, Jan. 22nd.
  3. We emphasize that late submissions receive a "Fail" in this assignment, which also means you cannot stay in this course. You cannot use slip_days in this assignment.
  4. When you have your tables and queries ready, you need to generate a script to turnin. You should do it via "script" command.
    a) When you are ready, type "script hw0.script" at a shell prompt;
    b) Type "psql dbname" to start psql (replace dbname with your own database name);
    c) For each table you define, run the query "select * from tablename;" so we can see the content of the tables.
    d) For each query you wrote, run that query;
    e) Quit psql via \q or control-d;
    f) Type "exit" to end the script program. It should tell you "Script done, file is hw0.script".
    You can use "cat hw0.script" to see the script.
  5. Briefly describe what your tables and what your queries mean in another file called "readme".

DELIVERABLES

a) Submit printouts of "hw0.script" and "readme" to Minglong Shao at Wean Hall 1315. Slip it under the door if nobody is there.
b) Create a directory "hwk0" under your home directory on the Itanium machines, containing only the files "hw0.script" and "readme".

HINTS

  1. Refer to http://www.cs.cmu.edu/~natassa/courses/15-415/index.html/readme.html on how to set up your database server on our experiment machines: initialize database server, start it, create database, and pose queries on your database.
  2. You need to read the documents about SQL of PostgreSQL in order to create tables, load data and ask queries.
  3. You might want to use pgaccess, a GUI tool for PostgreSQL to help you compose your SQL. It has been installed on our machines.

PASSING CRITERION: The script shows at least 2 tables and 2 queries.

Send all your questions to the course newsgroup, academic.cs.15-415. All questions sent to TAs will be posted to the newsgroup.