CS 4604 Homework #6 Solution Sketches

1. (10 points) It is clear that we need to do a join of 7 copies of the Acts relation. Here's a solution:
   
   SELECT T1.movie, T2.star, T3.movie, T4.star, T5.movie
   FROM Acts AS T1, Acts AS T2, Acts AS T3, Acts AS T4, Acts AS T5, Acts AS T6
   WHERE T1.star="WoodyHarrelson"
   AND T1.movie=T2.movie
   AND T2.star=T3.star
   AND T3.movie=T4.movie
   AND T4.star=T5.star
   AND T5.movie=T6.movie
   AND T6.star="EddieMurphy"
   
   The answer you will get for this query is:
   (Natural Born Killers, Juliette Lewis, Cape Fear, Nick Nolte, 48 Hours).
   
   
2. (20 points) A straightforward approach is to create a one-column relation called Digits(num) containing all the 10 digits. We can then write the query as:
   
   

   SELECT S, E, N, D, M, O, R, Y
   FROM Digits AS S, Digits AS E, Digits AS N, Digits AS D, Digits AS M,
        Digits AS O, Digits AS R, Digits AS Y
   WHERE 1000*S + 100*E + 10*N + D + 1000*M + 100*O +10*R + E
        = 10000*M + 1000*O + 100*N + 10*E + Y
   

   If you find that the query takes laboriously long to execute, Deept Kumar suggests the creation of many VIEWs. The first VIEW determines candidate values for D, E, and Y based on the condition that (D+E) mod 10 = Y. Then, join this VIEW with candidates for N and R using the second decimal place conditions; this will further restrict the space of possibilities. And so on, continue till you find all desired values.
   
   
3. (20 points) Here's an approach. The stumper is supposed to be the fact that (i) we do not care if a person knows himself or herself, and (ii) sometimes people are listed as knowing themselves, but sometimes not. The BloatedReality relation below is supposed to take care of these cases.
   
   

   // get a master list of all the people
   CREATE VIEW AllPeople(name) AS
   SELECT this_person_knows FROM AKnowsB
   UNION
   SELECT this_other_person FROM AKnowsB;
   
   // ideally everybody knows everybody
   CREATE VIEW Ideally(name1,name2) AS
   SELECT X.*, Y.*
   FROM AllPeople AS X, AllPeople AS Y;
   
   // in reality, though ...
   // notice that we add "spurious tuples" as if everybody
   // knows themselves
   CREATE VIEW BloatedReality(name1,name2) AS
   (SELECT * FROM AKnowsB)
   UNION
   (SELECT * FROM Ideally
   WHERE name1 = name2);
   
   // the "do not knows";
   // name1 does not know name2,
   // so name2 cannot be a celebrity
   CREATE VIEW DoNotKnow(name1,name2) AS
   SELECT * FROM Ideally
   EXCEPT
   SELECT * FROM BloatedRealiry;
   
   // so the final list of celebrities are
   CREATE VIEW Celebrities(name) AS
   SELECT * FROM AllPeople
   EXCEPT
   SELECT name2 FROM DoNotKnow;
   

   Another way to solve this problem is to, rather than "inflate" the Reality to get BloatedReality, "deflate" the Ideally so that we subtract the tuples about people knowing themselves.