Exercise 4 (Indexing, 4 pts) Count the costs of operations and find an optimal s

Question

Exercise 4 (Indexing, 4 pts)

Count the costs of operations and find an optimal selection of indexes for the Movies table in Movie Database.

Operations

Insertion I (70%): INSERT INTO Movies VALUES (A,B,C,D)

Update U (30%): UPDATE Movies SET Rating=X WHERE Title=Y

Assumption about data

Data for the Movies table is stored in 10 blocks.

The Title attribute is a key for the Movies table.

Any index for the Movies table is stored in 1 block.

Two cases to analyze

No indexes.

Index on Title.

Movies Title Year Runtime Rating Django Unchained 2012 165 8.5 Goodfellas 1990 146 8.8 Pulp Fiction 1994 154 9 The Departed 2006 151 8.5

Explanation / Answer

For each movie, return the title and the 'rating spread', that is, the difference between highest and lowest ratings given to that movie. Sort by rating spread from highest to lowest, then by movie title.

Find the difference between the average rating of movies released before 1980 and the average rating of movies released after 1980. (Make sure to calculate the average rating for each movie, then the average of those averages for movies before 1980 and movies after. Don't just calculate the overall average rating before and after 1980.)

Some directors directed more than one movie. For all such directors, return the titles of all movies directed by them, along with the director name. Sort by director name, then movie title. (As an extra challenge, try writing the query both with and without COUNT.)

Find the movie(s) with the highest average rating. Return the movie title(s) and average rating. (Hint: This query is more difficult to write in SQLite than other systems; you might think of it as finding the highest average rating and then choosing the movie(s) with that average rating.)

Find the movie(s) with the lowest average rating. Return the movie title(s) and average rating. (Hint: This query may be more difficult to write in SQLite than other systems; you might think of it as finding the highest average rating and then choosing the movie(s) with that average rating.)

For each director, return the director's name together with the title(s) of the movie(s) they directed that received the highest rating among all of their movies, and the value of that rating. Ignore movies whose director is NULL.

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.