CS 317: Assignment #7

"The Final Project"

General Info

Out: Thursday, May 30th, 2002

Due: Tuesday, June 11th, 2002 at midnight

Changed: 6/6/2002

In this, the final, assignment you will be finishing the work you started in Assignment #6. The basic idea is that you will build the database backend for a miniature Amazon.com, implementing the five pieces of functionality from A6 and adding a shopping cart and some sort of recommendation system. You will not be building the web-pages and so your system will not look like Amazon.com--though once you have got your project working, you can talk to me or the TAs about using ASP (or PHP or JSP) to use your code to construct actual web pages and to get some extra credit!

Assignment History

Assignment Checklist

General Notes

Most of your work in this project is going to be in designing and filling relations and then writing SQL queries that demonstrate how those relations are used. For each piece of functionality you will give three examples, each of which will require one or more queries. The Access "mdb" file you turn in will include all of these queries and your stories of how the functionality works need to mention which queries produce the described behavior.

You will need to start with the database of CDs and then add tables, queries and forms to that.

Part 1: Shopping carts

The most crucial piece of functionality for any web-vendor is that which enables them to sell their products. Most companies employ techniques based upon a shopping cart metaphor: you put the items you want into your cart and then, when you are done shopping, you check out, purchasing the items. For the project, you will:

  1. Design a relation to store shopping cart contents. Attributes for this relation should include the shopper's name, the cd's id, and whatever else you think is necessary.
  2. Write a query (INSERT INTO) for adding items to the shopping cart. You can have Access prompt you for the shopper's name and the cd's id using the "[prompt]" expression. Access will ask the user to enter a value which will then be used in place of the expression. For example, to list all of the CDs by a band given by a user, you could enter the following query:
    SELECT title FROM CD WHERE bandName=[Enter the band you want];
  3. Write a query for listing the CDs in a given user's shopping cart. You should list each CD's title, bandName and price.
  4. Write a query that will compute the cost for purchasing everything in a particular shopper's cart. You can use a "[prompt]" expression to prompt for the shopper's name. Also, as far as the cost of CDs, you may either add an attribute somewhere that holds a CD's cost or just assume that all CDs cost the same amount (say $15.98).
  5. Design a relation to store what CDs each user has purchased. (This relation might also be useful in making recommendations.)
  6. Write a query (INSERT INTO) that will add a tuple to the purchases relation (from 5) for each item a particular shopper has in his shopping cart. You can use a "[prompt]" expression to prompt for the shopper's name.
  7. Write a query (DELETE FROM) that will remove all the items from a particular particular shopper's cart. You can use a "[prompt]" expression to prompt for the shopper's name.

These relations and queries provide much of the functionality needed to support CD shopping (ignoring many things like inventory, shipping, and exchanging money).

Part 2: Recommendation

Given that a shopper (Mary) is interested in one CD, how do we decide what other CDs she might be interested in? One technique that is employed by numerous web-vendors is to look at other shoppers' purchases. If another shopper (John) bought Mary's CD and some other CDs as well, we can recommend John's other purchases to Mary.

A simple approach to this would be to find all of the other shoppers who bought the CD, and then return all the CDs that any of those shoppers purchased. However, if there are a lot of shoppers who have purchased a lot of CDs this will potentially return the entire CD library! I'd suggest sorting the set of recommendations by the number of shoppers who bought both it and the other CD (think of how to use GROUP BY and aggregation to do this).

Note: this is only one of numerous possible ways of recommending CDs. If you can think of a technique you like better, use it. But, if you employ a riduculously simple approach that returns obviously poor recommendations, your score for this part is likely to suffer.... If you are in doubt, e-mail me or the TAs.

Part 3: Putting all the pieces together

For each piece of functionality (including the five from Assignment #6) you are going to do several things: give three examples of how it is used (i.e. tell three stories), add any necessary tables to Access to support it, write and save the SQL queries needed to get the results in the examples (mention the names of the appropriate query(s) in each of your examples descriptions), and add enough entries to the tables that these queries return the results in the descriptions.

Your three stories for each piece of functionality may differ only in what CD (or genre or band) the character was interested in and in what results are returned. Just make sure that you provide a good level of detail for all three stories (e.g. name the CDs).

Part 4: Turning it in

You will need to turn in two things: a MS Word document that includes your stories, schema and any other information you think would be useful in grading your project; and a MS Access file (e.g. CD.mdb) that includes your new relations, queries, and forms. Since there are likely to be many queries, please give meaningful names to your queries.