CS 4604 Homework #7 Solution Sketches

1. (10+6=16 points) The solution for the first part is given by:

   AtleastTwo(x) <- Advisor(y,x), Advisor(z,x), y<>z.
   AtleastThree(x) <- Advisor(y,x), Advisor(z,x), Advisor(w,x),
                      y<>z, y<>w, z<>w.
   Two-Advisors(x) <- AtleastTwo(x), NOT AtleastThree(x).
   // it is important that your query be "safe"; i.e., all variables appearing
   // with a NOT should also appear without a NOT on the right side of
   // the definition
   

   The other part of the question can be solved by:

   WantedStudents(x) <- Advisor(x,y), NOT Two-Advisors(y).
   

2. (25 points: 5 points for each) All operators are monotone except the difference operator. Since, in the expression "A-B", if you add more tuples to B, then the difference might end up "losing" some of the tuples it had before.
   
   
3. (9 points) The hint given in the question was meant to unify the definition of median (across lists which have an even number or an odd number of entries). Lets take it up:
   
   

   CREATE VIEW Doubledup(salary) AS
          SELECT starting_salary from GraduatingStudents
          UNION
          SELECT starting_salary from GraduatingStudents;
   
   // we now need to find the two middle salaries in Doubledup
   // we can do this by mimicking a for-loop using a correlated subquery
   // (covered in class on Monday, Dec 9)
   // the basic algorithm is the following:
   //   foreach salary 
   //       find the number of salaries that are greater than or equal to it
   //           this number should be at least the number of the
   //           undoubled list,
   //       find the number of salaries that are less than or equal to it
   //           this number should be at least the number of the
   //           undoubled list,
   //       and both these conditions should be satisfied!
   //  we are using the fact that the list is a doubled one, containing
   //  repeated entries!
   
   CREATE VIEW Middleones(salary) AS
          SELECT salary
          FROM Doubledup
          WHERE (SELECT COUNT(*) FROM GraduatingStudents) <=
                (SELECT COUNT(*)
                FROM Doubledup AS D1
                WHERE D1.salary >= Doubledup.salary)
          AND   (SELECT COUNT(*) FROM GraduatingStudents) <=
                (SELECT COUNT(*)
                FROM Doubledup AS D2
                WHERE D2.salary <= Doubledup.salary);
   
   SELECT AVG (DISTINCT salary)
   FROM Middleones;
   // this gives you the median!