Fall Semester 2007

Let me first explain there is a site I am trying to emulate (in some scaled-down sense).

Let me start being more specific on the data.

1. We need a few movies.

My movies:

• Saving Private Ryan (ryan1998)
• Spongebob: The Movie (spongebo)
• Johnny English (johnnyEn)
• The Incredibles (incredib)
• The Princess' Bride (princess)
• The English Patient (nglishpt)

  and later

• Reign Over Me (reignovr)

2. Each movie is rated by a person.

My people:

• Lary Bird (lbird)
• Toni Kukoc (tkukoc)
• Mark Jackson (mjackson)
• Michael Jordan (mjordan)
• Chris Mullen (cmullen)

  and joining the class a little bit later

• Jalen Rose (jrose)

In terms of rating, the users will produce this:

	lbird	tkukoc	mjackson	mjordan	cmullen	jrose
ryan1998	100			92
spongebo	92	10		80
johnnyEn	20	95		68	92
incredib				92
princess		82			68
nglishpt	80			60
reignovr						(*)

Regarding (*) above:

• We will first set up the database.
• Add new movie: now there is a movie with no rating.
• Add Jalen Rose, new user: now there is a user with no rating.
• Jalen Rose rates the new movie: now there is movie, user with just one rating.

• how do we add a new movie to the database?
• where do we store the pictures (for posters)?

My current answers to these questions:

• We will try to store the images for the posters in the database.
• For this we will need to learn how to upload a file, using a form.
• Users should be able to ask for a movie to be uploaded (mini bulletin board).
• A new movie can only be uploaded by the admin of the site (password controlled).

• how do you add a new user to the database of reviewers?
• what is involved in a reviewer's submission of his/her review?

My current answers to these questions:

• users should also be able to ask for an account
• accounts can be created only by the admin, like adding new movies
• user accounts are password protected, passwords are initially communicated to users
• users should be able to later change their passwords

• can a reviewer update his/her review(s)?
• what (information) do we need from this database?

My current answers to these questions:

• when users login they are presented with two lists: existing reviews, unreviewed movies
• users get to choose one movie, from either of these two categories, and then:
• either change one of existing reviews (which loads upon selection) or add a new movie review

Write SQL code to retrieve:

• the most active, passive reviewer
• the movies with a rating above average
• the movies with more than one reviewer
• the movies with no reviewer
• the best, worst n movies (n is a parameter)
• individual statistics (average, max, min) per movie and reviewer

This is what we need (for now) from this website.