DBIS Lab 3, Aug 8, 2003

        Assignment due on Mon August 18th 1.00 PM.  You can 
        drop off the assignments with the TAs in Infolab. 

	Can be done in groups of 2.  
	Absolutely no copying between groups.

Schema for Film Information
---------------------------

people(person_id, name, year-of-birth);
	-- year-of-birth: 4 digit numeric

movies(movie_id, name, year)

role(movie_id, person_id, role, character_name)  
	-- role:  actor, director, ...; string of up to 10 characters
        -- character_name:   name of character played by the person; string
        --                   of up to 20 characters

review(movie_id, reviewer_name, review_location, score, review_text)
	-- score: number with one digit before and one digit after fraction


Things for you to do
--------------------

Write each query below, and submit (a) a file with all the queries, 
and (b) an execution session containing all the queries along with 
their results.

NOTE:  Sample data will be provided under the user kanti (e.g. kanti.people,
kanti.movies, etc).   We will also provide sample answers under the 
same area, as relations called result1, result2, etc (for queries that 
require you to create a relation, that relation will be created instead 
of resulti).   These will be ready by Monday 11 Aug.

1) List all actors who have not directed any movie

2) List all reviewers who have reviewed every movie released in 2002

3) List for each decade, the number of movies released in that decade.
   (Note: You cannot hardcode the decade, you have to output decades
    based on movie release years.  You can omit decades with no movies.)

4) Create a relation called reviewer_avg(reviewer, avg)
   containing for each reviewer, the average score given by the reviewer across
   all movies.

5) Create a relation called   movie_avg_rev(movie_id, avg_rev)   
   containing the average review score (across all reviewers) of the movie.

6) Create a relation called   reviewed_movie_avg(reviewer, avg_of_avg)
   which contains for each reviewer, the average, across all movies 
   reviewed by the reviewer, of the avg_rev score from movie_avg_rev.

7) List for each reviewer, a harshness score, computed as the value 
   for that reviewer from Query 4 divided by the value for that reviewer
   from Query 6.

8) Show all review scores, but scaled by the harshness score for that
   reviewer, got from Query 7.

9) Create a relation new_people  with the same schema as people
   and containing the same data

10) Suppose several people have the same name, but assume no two people
	with the same name have the same year of birth.
        Update the name of each person in     new_people 
        by concatenating the number of people with the same name, born earlier.
	-- e.g. with two people named Bush, the senior one becomes Bush0
        -- and the other one becomes Bush1.  Don't worry about number 
        -- formatting, I will accept formats like Bush000001!