LOGISTICS

• Turn-in: Please turn in your written work before the beginning of the class due date. Please put your name and preferred email address on every page that you turn in - you'd be surprised how many times assignments mysteriously come apart. Please remember that written work will not be accepted. All submissions are to be made  electronically via the MOODLE.
• Legibility: Please use a word processor or type your homework assignments. E/R diagrams should be done using a suitable ER diagramming tool or a graphics editor..
• Deadline: The on-time deadline for all students is in class on the due date.
• Late policy: All written work is to be turned in during class on the day that it is due. All assignments turned in late will be penalized at the rate of 10% per day for each unexcused late day.

THE ASSIGNMENT

1. Consider the following self-explanatory object-relational schema using SQL row types and references:

    create row type AddressType as (street string, city string)
    create row type StudentType as (name string, address AddressType)
    create row type CollegeType as (name string, city string)
   
    create table Student of type StudentType
    create table College of type CollegeType
    create table Attends(student ref(StudentType), college ref(CollegeType),
    tuition integer)
    create table Roommates(student1 ref(StudentType), student2 ref(StudentType))
   

   Write SQL queries for each of the following. You may assume that student and college names are unique, that all students have exactly one address, attend one college, and have one roommate, and that all colleges are located in exactly one city.

   (a) Find the names of all students who live in Palo Alto.

   (b) Find the names of all students who attend Stanford.

   (c) Find the names of all students who live in the same city as the college they attend and pay tuition of at least $10,000.

   (d) Find the names of all students who live in the same city and on the same street as their roommate. (Let's assume that addresses in the database are home addresses and not college addresses - otherwise this query would return everyone.)

2. A database system, containing of two objects A and B (these could be components of two tuples, for example) executes three transactions: T1, T2 and T3. Initially A has a value of 10, and B a value of 20.
   • T1 first writes a value of 30 for A; then changes B to 40. Finally, T1 commits. T1 runs with isolation level SERIALIZABLE.

   • T2 starts by changing A to 50, then modifies B to 60. At this point T2 does a rollback, and all changes are undone. T2 runs with isolation level SERIALIZABLE.

   • T3 is a read-only transaction that first reads A and then reads B. The isolation level of T3 is discussed below.

   We do not know in what order these three transactions execute.

   a)

   Assume that T3 runs with isolation level SERIALIZABLE. What are all the possible A, B values that T3 can read? Give each answer as a pair [a,b], where a is the A value read, and b is the B value read by T3.

   b)

   Assume that T3 runs with isolation level READ COMMITTED. What additional A, B values can T3 read? [Do not list pairs given in part (a).]

   c)

   Assume that T3 runs with isolation level READ UNCOMMITTED. What additional A, B values can T3 read? [Do not list pairs given in parts (a) or (b).]

3. Consider a set of users U, V, W, X, and Y. Suppose that user U creates a relation R(A) and is thus the owner of relation R. Now suppose the following set of statements is executed in order:

   stmt	user	operation
   1	U	grant select on R to V,W with grant option
   2	V	grant select on R to W
   3	W	grant select on R to X,Y
   4	U	grant select on R to Y
   5	U	revoke select on R from V restrict
   6	U	revoke select on R from W cascade

   Show the grant diagram after steps 4, 5, and 6. Since all of the nodes in the diagrams will be for privilege "select on R," you may omit writing it in each node.