CS 4604 Homework #5 Solution Sketches

1. pi_title (sigma_{director_name = "Steven Spielberg"} Movies)
   
   
2. One solution is:
   
   (SELECT title
   FROM Movies
   WHERE producing_studio = "Paramount"
   AND format = "DVD")
   
   INTERSECT
   
   (SELECT title
   FROM Movies
   WHERE producing_studio = "Paramount"
   AND format = "Video Cassette");
   
   The following will not work:
   
   SELECT title
   FROM Movies
   WHERE producing_studio = "Paramount"
   AND format = "DVD"
   AND format = "Video Cassette";
   
   since there is no way a given tuple can satisfy both conditions in the WHERE clause. The only way you can write this query with one SQL block (SELECT/FROM/WHERE) is if you did a join of the Movies relation with itself.
   
   
3. 
   IsInDebtby(x,y) <- Customer(x,_,_,_,y), y<0.
   // IsInDebtby contains customers and their negative balances.
   // Everybody in this relation is in debt.
   
   CannotbeMostNegative(x) <- IsInDebtby(x,y), IsInDebtby(z,m), x<>z, y > m.
   // x cannot be the one in most debt because z has a balance (m) that is less
   // than x's balance (which is y).
   
   Answer(x) <- IsInDebtby(x,_), NOT CannotbeMostNegative(x).
   
   
4. 
   (SELECT movieid FROM Movies)
   EXCEPT
   (SELECT movieid FROM Rented);
   
   
5. 
   AllDVDMovies = pi_movieid (sigma_{format="DVD"} Movies);
   //this contains all the DVD movies
   
   AllCustomers = pi_customerid (Customers);
   //this contains all the customers
   
   Ideally = AllCustomers X AllDVDMovies;
   // ideally, all customers have rented all DVD movies
   
   Reality = Rented NaturalJoin AllDVDMovies;
   // in reality, only those in the Rented relation actually happened;
   // we join it with AllDVDMovies to make sure we only have DVD movies;
   // just an extra precaution, really
   // notice that Reality will have only two columns and the same schema as Ideally
   
   BadCustomers = pi_customerid (Ideally - Reality);
   // what is in Ideally but not in Reality, reflects customers
   // and the movies they didn't see; these customers cannot be what we want
   
   GoodCustomers = AllCustomers - BadCustomers;
   // so everybody else must be it!
   
   Answer = pi_name (GoodCustomers NaturalJoin Customers);
   // we join GoodCustomers with Customers to get the names!
   
   
6. 
   // since you know the story, the comments come before the commands now!
   // first create a handy relation consisting of all the DVD movies
   CREATE VIEW DVDMovies(movieid) AS
   SELECT movieid
   FROM Movies
   WHERE format = "DVD";
   
   // in the ideal case, every DVD movie has been seen by every customer
   CREATE VIEW Ideally(movieid,customerid) AS
   SELECT movieid, customerid
   FROM DVDMovies, Customer;
   
   // the reality is, of course, given by the actual rentals that have taken place
   CREATE VIEW Reality(movieid,customerid) AS
   SELECT movieid, customerid
   FROM Rented, Movies
   WHERE Rented.movieid = Movies.movieid
   AND Movies.format = "DVD";
   
   // the difference is clear
   CREATE VIEW NotRentedby(movieid, customerid) AS
   SELECT * FROM Ideally
   EXCEPT
   SELECT * FROM Reality;
   
   // if some movie is not rented by somebody,
   // then this movie cannot be in the answer!
   CREATE VIEW BadDVDMovies(movieid) AS
   SELECT movieid
   FROM NotRentedby;
   
   // so, the rest must be the answer!
   CREATE VIEW Answer(movieid) AS
   SELECT * FROM DVDMovies
   EXCEPT
   SELECT * FROM BadDVDMovies;
   
   // hold on, the question wanted the titles!
   CREATE VIEW WhatyouReallyWanted(title) AS
   SELECT title
   FROM Movies, Answer
   WHERE Answer.movieid = Movies.movieid;
   
   
7. This query is impossible unless we use recursion and/or perform computations within Datalog queries.