Skip to content

AssignmentThree

Jump to bottom
Joe Hellerstein edited this page Dec 8, 2015 · 1 revision

Assignment 3

Do this assignment BY YOURSELF! No group efforts on this one.

Tools for the assignment

Your course account has been set up with access to PostgreSQL 8.2.4 binaries. These were compiled for Solaris, so you must run on one of the following machines:

  • rhombus
  • pentagon
  • cube
  • sphere
  • po
  • torus If you have modified your PostgreSQL environment variables in your course account (e.g. in ~/.cshrc) you MUST remove those modifications or you may find that PostgreSQL will not work. We provide a copy of the original .cshrc for student accounts in the hw3 svn checkout directory.

You can use PostgreSQL on your own machine if you like. You'll need to install PL/Ruby as well. Be aware that we will test your queries on the version of PostgreSQL used by the class accounts, and you will not get credit unless it runs in that version. So it's wise to double-check your code on the class accounts before turning in.

Setting up the database

First, clear your previous postgres setup from ~ directory: the src checkout, pgsql and pgdata.

> initdb
> pg_ctl -l logfile start
> createdb
> psql

Check out the homework, set up plruby, and load data

> svn co file:///home/ff/cs186/sp09/hw3
> psql < hw3/plruby.sql
> psql < hw3/create_tables.sql
> psql < hw3/populate_tables.sql

Get to work!

> psql

The database

Consists of six tables:

  • customer(customer_id, title, fname, lname, addressline, town, zipcode, phone, profile)
    • primary key is customer_id
  • item(item_id, description, cost_price, sell_price)
    • primary key is item_id
  • barcode(barcode_ean, item_id)
    • primary key is barcode_ean
    • foreign key on item.item_id
  • stock(item_id, quantity)
    • primary key is item_id
    • foreign key on item.item_id
  • orderinfo(orderinfo_id, customer_id, date_placed, date_shipped, shipping)
    • primary key is orderinfo_id
    • foreign key on customer.customer_id
  • orderline(orderinfo_id, item_id, quantity)
    • primary key is (orderinfo_id, item_id)
    • foreign key on orderinfo.orderinfo_id
    • foreign key on item.item_id
  • dirty_dict(word)
    • word is the primary key

Write these queries

You may not use user-defined functions of any kind unless we note otherwise.

  1. List customer_id, first and last names of customers who have never bought anything
  2. Which customer(s) made the biggest order (total cost + shipping)? List customer_id, first and last name.
  3. What is the total retail value of the current contents of the inventory, in terms of cost_price?
  4. List all distinct item_id's bought by all persons with the last name "Matthew"
  5. List all item_ids and their in-stock quantity, from least to most quantity.
  6. Find customers who have not enjoyed "free shipping" (shipping = $0), including those who have not bought anything at all. List customer_id.
  7. Find all customer whose orders average more than two dollars per item. The price of an order is determined by the sell_price of each item in that order, plus the shipping price. List customer_id, average_cost. Find all customer whose purchased items average more than two dollars each. Account for shipping costs as well. List customer_id, average_cost.
  8. Customers are unhappy if an order takes more than 5 days to ship. Who is unhappy? List customer_id, longest_shipping_time.
  9. List all customer_id, first and last name of unhappy customers who have made large purchases (sell price(s) + shipping > $20).
  10. List all customer_ids and profiles where the profile contains a dirty word (i.e. a word in the dirty_dict) table. You may want to either write a user-defined function in Ruby, or consult PostgreSQL manuals online for information on the built-in PostgreSQL regular expression matching and string concatenation.

Extra Credit: EC. Create a view called customer_public, in which the user profile is filtered for profanity: i.e., modify the user profile so that any word that appears in the dirty_dict table is replaced with the string !@#$%&*. You may want to write a user-defined function (or functions) in Ruby to do this.

What to turn in

Create a single text file containing all solutions; name it hw3.sql. We provide a hw3_skeleton.sql file to help you get started. In the skeleton code you'll find for each assignment question, a CREATE VIEW statement specifying a particular view name (like queestion2) and list of column names (like customer_id, name). The view name and column names constitute the interface against which we will grade this assignment. In order words, don't change or remove these names. Your job is to fill out the SELECT statement.

For example, consider a query for "Count the number of customers". {{{ -- skeleton we provide CREATE VIEW question0 AS SELECT count

-- solution you provide CREATE VIEW question0 AS SELECT COUNT(*) as count FROM customer; }}}

Note:

  • You DO NOT have to turn in the results you get from executing your queries
  • This homework will be graded automatically. If your hw3.sql produces an error on psql < hw3.sql you will receive no credit.

Getting started

Tips:

  • We provide demo data above, use it! However, be aware your queries have to work on any DB instance, and we will grade accordingly.
  • Feel free to use other views if you need them; they are not required, but can provide additional clarity to your code.
  • Be careful with NULL values, empty strings, and duplicates in the data.
  • If you choose to write Ruby, first get it working as standalone Ruby code, then figure out how to interface with Postgres. This will make debugging much easier. The ruby-debug tool may be helpful to you.
Clone this wiki locally