Fall Semester 2007

What do we need?

1. A list of movies; suppose at this time the movies above are all we got.

(MovieNum, Description, Category, Year, (ActorNum, Last Name, First Name), 
                                        (DirectorNum, Last Name, First Name))

2. Each movie is rated by a person.

(MovieNum, Description, Category, Year, (ActorNum, Last Name, First Name), 
                                        (DirectorNum, Last Name, First Name), 
                                        (Username, Last Name, First Name, Comment, Rating))

Questions:

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

Example: how do we plan to add this movie to the database?

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

Exercise: invent a new user, add the user to the database.

Let the user review some of the movies, perhaps including the new one.

Questions:

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

Exercise: 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 parameter)
• individual statistics (average, max, min) per movie and reviewer

Implement this in CGI/Perl or PHP. Which would be easier, better, more appropriate?