CS 317: Assignment #7
"The Final Project"
Out: Thursday, May 30th, 2002
Due: Tuesday, June 11th, 2002 at midnight
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!
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.
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:
These relations and queries provide much of the functionality needed to support CD shopping (ignoring many things like inventory, shipping, and exchanging money).
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.
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).
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.