CMPS180 - Winter 04

Project Assignment 2

Due midnight at the end of Wednesday, Feb 4th

Logistics and General Information

Turning in your work: Unless otherwise specified, this lab assignment and all subsequent ones will be turned in electronically. We will use submit. Submit is accessed from your cats account. For information on using submit, enter "submit -m" from the console. Please keep in mind that the deadlines for submitting lab assignments are separate from the deadlines for written homework assignments. The following late policy applies to this and subsequent lab assignments:
Lab assignment work must be submitted electronically by midnight at the end of the day that it is due. Programming work submitted after the deadline but before the "late deadline" -- midnight 48 hours after the deadline -- will be accepted but penalized 50%. No lab assignment work will be accepted after the late deadline.
PostgreSQL (lack of) backups: PostgreSQL is not backed up, so anything you need long-term should be saved in the cats file system. Some suggestions for general management of your database application are given below. CATS-Instructional Computing warns: "Although system backups are performed, YOUR STUDENTS MUST BE SURE TO PERFORM THEIR OWN BACKUPS, as I will not restore anything from my own backups except in case of emergency and/or complete system failure." `
SQL on PostgreSQL: If you decide to test your solutions to the SQL homework assignment exercises using PostgreSQL, or experiment with running some SQL queries on your database, note that PostgreSQL's implementation of SQL may differ somewhat from the SQL standard as covered in the textbook and class. See PostgreSQL feature compatibility list from MySQL's crash-me program.
PostgreSQL for CS180: Students wishing to use PostgreSQL must connect with their CATS accounts to 'db.ic.ucsc.edu'. Students must use ssh (both versions 1 and 2 will work), as telnet is not enabled. SSH is an encrypted (safer) method of connecting to a remote host, as opposed to telnet which transmits and receives data unencrypted. Free and legitimate SSH software is available for almost all computing platforms. Once connected to db.ic.ucsc.edu, students will have full access to their CATS home directories via AFS, much the same way as when logged onto teach.ic.ucsc.edu and hawking.ic.ucsc.edu.

PostgreSQL

Familiarize yourself with the PostgreSQL relational DBMS by reading the document Managing a Database in the Interactive PostgreSQL documentation, logging into PostgreSQL, trying some of the examples in the document, and experimenting with the various commands. You don't need to turn anything in for this part. The PostgreSQL web page has links to documentation and other information. See PostgreSQL: Introduction and Concepts, by Bruce Momjian, which is available in printed form at the bookstore and on reserve at the library.

Note: For system maintenance reasons, you are not allowed to create a new database by using the createdb command. A database has already been created for you with the same name as your user-name.

You can access your database with the command:

psql -a

which will start a command line interface to PostgreSQL connected to the database with your user-name. (The -a option will cause the input to be echoed to the console for saving a script log. If you don't want your input echoed, you can omit the -a, but please do include it when you are running an execution script to hand in.)

The assignment

For this assignment, you will create a relational database for MP3s'R'Us based on your E-R design (from project assignment 1). You will write the table definitions in SQL, populate the tables with some test data, and then demonstrate to El Cheapo that your database can answer a set of queries that they provided.

Part A

Description: Translate your E-R diagram to an appropriate relational schema in your PostgreSQL database. To do the translation, use the procedure outlined in Chapter 3 of the textbook. Use the CREATE TABLE command to specify each relation, its attributes, and its attribute types; see CREATE TABLE from the PostgreSQL interactive documentation. If you have an attribute that represents a date and/or time, you may want to look at the page on FAQ: Working with Dates and Times in PostgreSQL. In your table definitions, include the appropriate PRIMARY KEY, FOREIGN KEY, UNIQUE, and NOT NULL clauses to denote the constraints of your relational schema.

For each relation in your database, create an execution script file containing a COPY command and a few records of "realistic" data. More specifically, your test data should adhere to the following requirements:

At the very least, the database contains 5 customers, 10 albums, 2 songs per album, 5 artists, and 2 recording firms.
At the very least, each customer has bought 2 songs, and has rated 3 songs, 2 artists, and 5 albums.

Note that these are the minimal requirements for your database. If you wish, you can include more tuples or even write a program that creates synthetic data. In any case, pay attention to the following points:

Make sure not to generate tuples that violate the key constraints.
Make sure to generate tuples that join with each other in order to express the relationships outlined in the requirements. For example, each customer tuple must join with at least 2 transaction tuples, with at least 3 song rating tuples, etc.

Once you have created the script file, run it from the psql command line using the \i option. Please see Creating execution script files below for more details.

What to turn in: Please see Recording Your Session below for a guide to preparing output to be submitted for this and subsequent project parts. In this and all subsequent project parts, the material you turn in should be clearly formatted and delineated, and should include comments for any aspects that are not crystal clear. Poorly assembled or documented material will not receive full credit, even if it is correct. You also will not receive full credit if you turn in your entire large data files (or large query results in later assignments) when we ask for small samples. Other than comments, truncation, and simple formatting, it is Academic Dishonesty to edit scripts before turning them in.

For this part of the assignment the following files should be turned in electronically using your cats account and the submit program:

README Please give your name, cats account, project part number, date, a list and description of the files you are submitting, and any other information that will be useful for the grader.

create.script The execution script file you used to create tables for the relational database.

create.log A record of your session creating your tables.

data.script The execution script file you used to populate your small database.

data.log script log (i.e., a recording) of your session using data.script to populate your DB.

To submit these files use the syntax:

submit cmps180-np.w04 proj2-part1 README create.script create.log data.script data.log

or proj2-late if your project part is late.

Part B

Description: You have a meeting with an executive from El Cheapo in order to give a demo of your relational database for MP3s'R'Us. More specifically, the executive asks you to answer the following queries:

Find all the songs bought by a specific customer (you are free to choose which customer).
List all songs by a specific artist (you are free to choose which artist).
Find all the songs performed by artists from a specific recording firm (you are free to choose which firm).
Get a listing of song titles, ordered by their total ratings (the rating of the song is equal to the sum of ratings from all customers, where 4 is the highest rating and 1 is the lowest).
Get a listing of songs, ordered by the number of purchases (songs with a higher number of purchases should appear near the top of the list).
Find the customer who has paid the most in purchases.
Find the total amount of purchases that were recorded during a week (you are free to choose which week).

For each of the previous questions, write and execute and SQL query that retrieves the answer from your database.

What to turn in: Please see Recording Your Session below for a guide to preparing output to be submitted. For this part of the assignment, files should be turned in electronically using your cats account and the submit program:

README Please give your name, cats account, project part number, date, a list and description of the files you are submitting, and any other information that will be useful for the grader.

queries.script The execution script file that contains the SQL queries, in the same order as the questions.

queries.log A record of running queries.log and the results returned from the database.

To submit these files use the syntax:

submit cmps180-np.w04 proj2-part2 README queries.script queries.log

or proj2-late if your project part is late.

Maintaining your databases

For the duration of the project, we suggest that you establish some kind of routine that includes reloading your database from the files created in this project part each time you want to get a "fresh" start with PostgreSQL. Remember to delete the contents of each relation (or destroy and recreate the relations) before reloading. Otherwise, unless there is a declared key (or you take APPEND out of your control file), PostgreSQL will happily append the new data to your old relation, causing your relation size to double, triple, quadruple, etc. To get rid of a table called T, issue the command:

drop table T;

If you want to get rid of all tuples in T without deleting the table itself, issue the command:

delete from T;

Recording Your Session

There are several methods for creating a typescript to turn in for your programming assignments. The most primitive way is to cut and paste your terminal output and save it in a file (if you have windowing capabilities). Another method is to use the Unix command script to record the terminal interaction. The script command records everything printed on your screen. The syntax for the command is:

    script [ -a ] [ filename ]

The record is written to filename. If no file name is given, the record is saved in the file typescript. The -a option allows you to append the session record to filename, rather than overwrite it. To end the recording, type.

    exit

For more information about script, check out its man page.

Creating and running script files from psql

You will be using the psql command line interface to interact with your data base. See Managing a Database for information on starting using psql. Script files are text files of psql commands which can be executed like a batch file using the the \i command in psql. The syntax is \i filename entered following the psql prompt, where file name is the complete (case sensitive) name of the script file you desire to run. To run your execution script data.script and save the script log in the file data.log, do the following:

`script data.log`	to start saving the script log
`psql -a`	to run PostgreSQL's command line interface using your database
`\i data.script`	to import an execution script
`\q`	to exit pqsl (PostgreSQL's command line interface)
`exit`	to stop saving the script log

The execution script file you create can consist of most any series of commands which you could enter following the psql prompt. This includes all of the SQL commands which you will be using to create, modify and test your data base. Examples include the CREATE TABLE and SELECT commands. Just like when using the psql command line interface you must terminate each SQL command in your script file with a semicolon. \i and \q do not need semicolons.

If you are recording your session using the script command described above into a script log then it is useful to start psql using the -a option so that all commands included in your execution script file will be echoed to the console and thus to your script log file. Here is an example script file that creates a table (relation) named products:

	CREATE TABLE products (
	  productID INT,
	  name VARCHAR(80),
          price NUMERIC(10,2),
          retailPrice NUMERIC(10,2)
        );

Here is an example script file that loads four tuples into the table (relation) named products created using the previous script file:

	COPY products FROM stdin USING DELIMITERS '|';
	1419|American Greetings CreataCard Gold V4.0|21.49|25.24
	1424|Barbie(R) Nail Designer(TM)|20.74|25.99
	1427|Panzer Commander|21.99|30.24
	1431|Riven: The Sequel to Myst|31.99|40.24
	\.

This is the format you will use for the files that load data into your tables. The USING DELIMITERS '|' and the use of '|' as a delimiter is optional. The default delimiter is the tab character. The delimited data on each line must match the attributes and their types in your table in a one to one manner and in the order they were defined in your CREATE TABLE commands. The COPY data must be terminated with '\.' For testing, some students have found it convenient to have a separate script file to populate each of the tables. I also find it convenient have a single script file to create all of my tables and another one to drop all of my tables. Look for examples at the end of this document.

Sample Script Files

createbeers.script

-- Sample Script file to Create a BEERS DB 

-- print out the current time

SELECT timeofday();

-- Create Tables

CREATE TABLE Beers (
  name VARCHAR(30),
  manf VARCHAR(50)
);

CREATE TABLE Bars (
  name VARCHAR(30),
  addr VARCHAR(50),
  license VARCHAR(50)
);

CREATE TABLE Sells (
  bar VARCHAR(20),
  beer VARCHAR(30),
  price REAL
);


CREATE TABLE Drinkers (
  name VARCHAR(30),
  addr VARCHAR(50),
  phone CHAR(16)
);

CREATE TABLE Likes (
  drinker VARCHAR(30),
  beer VARCHAR(30)
);

CREATE TABLE Frequents (
  drinker VARCHAR(30),
  bar VARCHAR(30)
);

-- print out the current time
SELECT timeofday();

databeers.script

-- Sample Script file to Populate a BEERS DB 

-- print out the current time

SELECT timeofday();

-- Populate the tables

COPY Beers FROM stdin USING DELIMITERS '|';
Coors|Adolph Coors
Coors Lite|Adolph Coors
Miller|Miller Brewing
Miller Lite|Miller Brewing
MGD|Miller Brewing
Bud|Anheuser-Busch
Bud Lite|Anheuser-Busch
Michelob|Anheuser-Busch
Anchor Steam|Anchor Brewing
\.

COPY Bars FROM stdin USING DELIMITERS '|';
Joe's|123 Any Street|B7462A
Sue's|456 My Way|C5473S
\.

COPY Sells FROM stdin USING DELIMITERS '|';
Joe's|Coors|2.50
Joe's|Bud|2.50
Joe's|Bud Lite|2.50
Joe's|Michelob|2.50
Joe's|Anchor Steam|3.50
Sue's|Coors|2.00
Sue's|Miller|2.00
\.

COPY Drinkers FROM stdin USING DELIMITERS '|';
Bill Jones|180 Saint St.|831-459-1812
Kelly Arthur|180 Alto Pl.|650-856-2002
Fred|1234 Fifth St.|831-426-1956
\.

COPY Likes FROM stdin USING DELIMITERS '|';
Bill Jones|Miller
Bill Jones|Michelob
Kelly Arthur|Anchor Steam
Fred|MGD
\.

COPY Frequents FROM stdin USING DELIMITERS '|';
Bill Jones|Joe's
Bill Jones|Sue's
Kelly Arthur|Joe's
\.

-- print out the current time
SELECT timeofday();

querybeers.script

-- print out the current time
SELECT timeofday();

-- Execute some SELECT queries--

SELECT * FROM Bars;
SELECT * FROM Drinkers;

-- print out the current time
SELECT timeofday();

dropbeers.script

DROP TABLE Beers;
DROP TABLE Bars;
DROP TABLE Sells;
DROP TABLE Likes;
DROP TABLE Frequents;
DROP TABLE Drinkers;

`README`	Please give your name, cats account, project part number, date, a list and description of the files you are submitting, and any other information that will be useful for the grader.
`create.script`	The execution script file you used to create tables for the relational database.
`create.log`	A record of your session creating your tables.
`data.script`	The execution script file you used to populate your small database.
`data.log`	script log (i.e., a recording) of your session using `data.script` to populate your DB.

`README`	Please give your name, cats account, project part number, date, a list and description of the files you are submitting, and any other information that will be useful for the grader.
`queries.script`	The execution script file that contains the SQL queries, in the same order as the questions.
`queries.log`	A record of running `queries.log` and the results returned from the database.