9.22

Consider the Oracle Virtual Private Database (VPD) feature described in Section 9.8.5 and an application based on our university schema.

  1. What predicate (using a subquery) should be generated to allow each faculty member to see only takes tuples corresponding to course sections that they have taught?

  2. Give an SQL query such that the query with the predicate added gives a result that is a subset of the original query result without the added predicate.

  3. Give an SQL query such that the query with the predicate added gives a result containing a tuple that is not in the result of the original query without the added predicate.

  1. What predicate (using a subquery) should be generated to allow each faculty member to see only takes tuples corresponding to course sections that they have taught?

The following predicate would be associated with the takes relation.

(course_id, sec_id, semester, year) IN (
    SELECT course_id, sec_id, semester, year
    FROM teaches
    WHERE id = syscontext.user_id()
)
  1. Give an SQL query such that the query with the predicate added gives a result that is a subset of the original query result without the added predicate.
SELECT * FROM takes
  1. Give an SQL query such that the query with the predicate added gives a result containing a tuple that is not in the result of the original query without the added predicate.

No such query exists.