CS 317: Assignment #6

"Micro-Amazon-Part 1"

General Info

Out: Thursday, May 23rd, 2002

Due: Thursday, May 30th, 2002

Changed: 5/23/2002

In this assignment you will be laying the groundwork for you final projects. The first task is to form groups of 4-6 (most of you have done that in class this week). Then, you will choose 5 pieces of functionality from Amazon and for each one: describe (in story form) how it is used, design the relations needed to support it, and write queries that would return the appropriate results.

Assignment History

Assignment Checklist

Part 1: Forming groups

In class on Tuesday and Thursday I gave you the opportunity to form groups of four to six students for the final project. If you aren't in a group yet, I encourage you to get started on the assignment anyway, choosing one or two pieces of functionality and writing about them. On Tuesday, we should be able to finalize our project groups. Here is the current list of groups and their members:

(No name given yet)
Kevin Johnson
Malik Thompson
Firat Incroglu
Patrick Brown
Brie Brown

World Cup
Mauricio Gutierrez
Alejandro Arguelles
Sabrina Duncan
Alvaro Ramirez
Enrique Mayorga
Burcu Gobuluk

Team Domination
Matt Baumgartner
Edwin Li
Vince Hui
Mike Charnota
Kent Su

J Money's Group
Jeff Wilen
Drew Sickinger
Abby Hillyard
Renae Bailey
Tyler Cowan

Group 0
Mickey Kellerman
Genevieve Law
Vidya Nagashwaran
Amy Schuman
Deepti Juturu

Shinie Shaw
Nia Castro
Luke Austin

(No name given yet)
Chris Webber
James Finley
Courtney Lytle
Katie Chou
Jay Lynch

Terry Fearnhead
Konica Mitra
Greg Burrell
Dave Choate

Part 2: Choosing pieces of functionality

Below is a list of some pieces of functionality from Amazon that seem reasonable to include in your final projects. If there is something that you'd like to do that doesn't appear in this list, please send me an e-mail or talk to me or the TAs during our office/lab hours. You must choose at least five pieces of functionality.

  1. Most popular CDs (based on Artist Name / based on Genre)
  2. "New and Notable CDs" (based on Artist Name / base on Genre)
  3. Various sorting options: year, alphabetically, ...)
  4. Keeping track of the last several bands, CDs, and genres that a customer has looked at. This would prove very useful for...
  5. "The page you made." Take the information from item #4 and then give the results from a set of queries. (Look at Amazon for the sorts of things that could be returned.)
  6. Lists. If you are looking at a CD that appears in some lists, return the lists that it appears in.
  7. "Essential CDs." Given the band or genre that a person is looking at, recommend a set of CDs that the person should buy that are somehow representative of that band or genre.
  8. "Explore Similar Items." When you are looking at a CD, return other CDs that have something in common with it. (See Amazon for some of the sorts of similarities they find interesting.)

Part 3: Writing about them

For each piece of functionality you are going to do several things: tell a story describing how it is used, design the relation(s) needed to support it, and write the SQL queries needed to get the results you want.

The story should a paragraph narrative describing what happens when a particular user (it's often helpful to give the user a name) uses this functionality. You need to give particulars: what CD, band, genre is the user looking at; what particular information does he get back, and in what order. Phrases like "a CD" or "some related CDs" are not specific enough; you need names and titles.

In some cases the database relations may seem obvious. In such cases (and if you feel confident and competent) you can just write out the relation schema. For most of the pieces of functionality, however, you would be well-served by going through the effort of drawing an E/R model and getting the relations from that. The graders will be much more sympathetic to a team that builds a E/R model and messes it up somehow than they will be to a team that just gives bad relational schema. Note: all of the pieces of functionality should make use of the four schema from Assignment #5.

For your SQL queries, imagine that you have constructed the tables for your relations and that they are filled with all the necessary data. (For the final project, you will be constructing those tables and filling them with enough data to produce the results in your stories.) Then write SQL queries that would produce the results described in your stories. Think about what attributes should be returned, which tuples should be returned, how many tuples should be returned, and in which order.

You should hand in your results (in paper form) as a team, listing all team members and compiling the stories, relations (including how they were derived) and SQL queries. As always, they will be due at the start of class.