CS 564 / Fall 2012: Project Stage 2

Deadline: Wed Oct 3, submit in class, at the start of the lecture.

NOTE: please read this entire document, before starting to work on your project stage 2.

What you are supposed to do

The department runs a database server at postgres.cs.wisc.edu. We have created a database called cs564_f12 for our class. This database can contain multiple schemas, and each schema in turn can contain multiple tables.

You (that is, your team) must connect to this database (cs564_f12), create a schema, create multiple tables in this schema, insert tuples into the tables of the schema, then pose several SQL queries. Note that the tables you will create will be those you have obtained in Project Stage 1.

You then submit a printout of the schema, the SQL queries, and the answers to those queries. We will examine the printout and also the tables in cs564_f12 to determine your grade for this project stage.

NOTE: the fact that the database cs564_f12 contains multiple schemas, rather than just one schema, may sound odd to you, since we have learned in the class that a database technically has just one schema. However, having multiple schemas for a database makes sense in the real world, because that way the DBA (the database admin) can organize the tables into different schemas, and set appropriate access controls so that a user would have access only to certain schemas.

The only real problem, as you will see, is to learn to set access control for schemas and tables, as they are a bit tricky. We will discuss those in detail below.

Step-by-step instruction

In the following, I will refer to your team as Team Badger, which has three members: X, Y, and Z. If you have problems following these instructions, please go to the office hour, or send mail to halit@cs.wisc.edu. Halit is the TA responsible for Stage 2.

1) One member, say X, connects to the database server using the command:

/s/postgresql/bin/psql -h postgres.cs.wisc.edu -p 5432 cs564_f12

NOTE: to log in successfully, you must enter this command using a window of your unix account. If you are using a Windows machine (e.g., from home), then first use ssh to log into your unix account. This typically gives you a Unix window on your Window machine. Then execute the above command in that Unix window.

2) Member X then creates a schema for the whole team. Suppose X wants to name the schema badger_schema. Then execute:

create schema badger_schema;

(Don't forget the ";" at the end of the command.) Note that you must see something like this in return:

cs564_f12=> create schema badger_schema;
CREATE SCHEMA

to indicate a successful execution. If you don't see it, then the command has not been executed, or has not been executed successfully.

3) Member X then grants access and modifying rights to team mates Y and Z, using the commands:

grant all on schema badger_schema to Y;
grant all on schema badger_schema to Z;

Note that Y and Z are the login names of your team mates. Next, X grants access and modifying rights to the instructors and TAs:

grant all on schema badger_schema to anhai;
grant all on schema badger_schema to halit;
grant all on schema badger_schema to conghan;

You must execute the above three commands. They allow the instructors and the TAs to view your schema (for grading purpose). We won't modify your schema.

4) Now team Badger has a schema (called badger_schema) that X, Y, Z can work with. You can think about this schema as representing a "directory" named badger_schema.

Next, you must move to this schema (that is, to the "directory"). To do so, execute:

set search_path to badger_schema;

(Don't forget the word "to".)

Now, you are inside the directory "badger_schema", so to speak, and can create tables (each table in turn can be viewed as a "file" in this directory). Suppose you want to create a table named weather, then you can execute a command such as:

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);

The "---" denotes that anything from then until the end of the line is a comment. (After entering each line, just hit "Enter". The command won't be executed until you have entered ";".)

After creating this table, you can insert tuples into it. See here for how to create tables. See here for how to insert, drop, and modify tuples.

When creating the tables, make sure to specify all applicable primary key and foreign key constraints. For foreign key constraints, make sure to specify that on delete what will happen (cascade, set null, etc.).

NOTE 1: You can view the tables in your schema by executing command "\d". To see the tuples in a table A, just execute the SQL query "select * from A;".

NOTE 2: If X creates a table A, then Y and Z cannot access and modify it yet. To grant these rights, X must also execute the command:

grant all on table A to Y;
grant all on table A to Z;

Furthermore, if X creates table A, *only* X can drop this table.

NOTE 3: You must insert *at least* 8 tuples into each table. You can make up the tuples, but choose something that look realistic. For example, for people name, you can choose "Mike", but not "X".

NOTE 4: Creating relational databases is by nature an interative process. Hence, we don't require that you must create *exactly* the same tables that you specified in Project Stage 1. Perhaps since then your thinking has changed somewhat, and now you want to modify the tables (in Project Stage 1) a little bit. That's fine. But we expect that if we ask, then you can explain why you modify the tables.

5) Now make up several questions, formulate them as SQL queries, then execute them on the above tables. You need to have at least the following ten queries:

two SQL queries over a single table.
two SQL queries over multiple tables (that is, they join the tables).
one SQL query that performs a self-join (that is, joinning a table with itself).
two SQL queries that use either UNION, EXCEPT, or INTERSECT.
three SQL queries that use aggregation.

In addition, at least one query in the above ten must contain DISTINCT. Make sure the queries ask for something reasonable, not something random or meaningless, such as "calculating the average of the social security numbers".

To learn SQL query formats for PostgreSQL, see PostgreSQL documentation, such as this one. IMPORTANT: If after logging into the database, you find that you cannot do anything (e.g., creating tables, inserting tuples, etc.), it is likely that you are not yet in the right schema (that is, right "directory"). Execute the command:

set search_path to badger_schema;

if your schema is badger_schema, to move into that schema. Now you can proceed normally.

What to submit

Cut and paste the followings into a file:

the schema of your team (that is, the names of the tables and the attributes; we don't need to see the tuples). Use the command "\d badger_schema.*" to obtain this schema information (if your schema name is "badger_schema").
the SQL queries that you executed and their results. For each query, please describe in English what it does.

Please submit a hard copy of this file at the start of the lecture on Wed Oct 3, in class. Just put it on the table in the front of the class.

Other useful information

I suggest that after you have logged into the database cs564_f12, play around a little bit to get a feel for how to use PostgreSQL. For this purpose, this tutorial will be very helpful. You can skip Part 1 of this tutorial, but read Part 2 and Part 3 (but only 3.1-3.3). Do this before you start working on your tables.