CS 4604 Homework #5

For all of the questions below, consider the following schema used in a video rental store (the key attributes are emphasized in bold, in addition to being underlined):

Movies(movieid, title, director_name, rating, producing_studio, format)
Rented(customerid, movieid)
Customer(customerid, name, address, age, balance)


---

These tables have their obvious meanings. Movies is the set of movies owned by the video store. Each tuple contains the title of the movie, the name of the director, the rating (e.g., "PG", "PG-13"), the studio (e.g., "Miramax"), and the format of the movie (e.g., "Video Cassette", "DVD", or some other funky thing). The attribute "movieid" is meant to be the key (presumably assigned by the store). Notice that a given movie that is available in many formats will have different movieids, one for each format. An alternate key for this relation is the two attributes: (title, format). In other words, knowing the title and format functionally implies everything else, such as the movieid, director's name, rating, and studio. Don't worry about the fact that "year" is not a part of the key or the relation (that is not our business; presumably the store is a small store that doesn't run into this problem).

Customer is a table that identifies the customers of the store, their name, age, address (e.g., "123 Main Street, Blacksburg"), and balance (e.g., "$30" or "-$15").

Rented is a history of who has rented what in the past.

---


Write the following queries in the notation indicated:


1. (10 points) Relational Algebra; Find the titles of movies that are made by director "Steven Spielberg".
   
   
2. (10 points) SQL; Find the titles of movies released by the "Paramount" Studio that are available in both "DVD" format as well as "Video Cassette" format.
   
   
3. (10 points) Datalog; A customer is in debt with the video store if he has a negative balance (meaning, he owes the store). Find the id of the customer that has the most negative balance, and is thus the one who is most in debt.
   
   
4. (10 points) SQL; Find the ids of movies that have not been rented at all (by anybody). We are interested in the id's here, to account for situations where the DVD version may have been rented by somebody but the video cassette version is still collecting dust. In this case, we want only the video cassette version's id to be listed in the answer.
   
   
5. (20 points) Relational Algebra; Find the names of customers who have rented all the DVD movies in the video store.
   
   
6. (20 points) SQL; Find the titles of DVD movies that have been rented by all the customers.
   
   
7. (20 points) Datalog; Find the titles of video cassette movies that have been rented by the most number of people. We don't know what the most number of people is - it could be "all the people", it could be "all but one", it could be 345, and so on.