LOGISTICS AND LATE POLICY REMINDER

• Turn-in: Please turn in your written work at the beginning of class. 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 electronically.
• Legibility: Please use a word processor or type your homework assignments. E/R diagrams may be neatly hand drawn.

• 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. Written work turned in after the deadline may be handed in at the beginning of the very next class will be accepted but penalized 50%, except that the penalty will not apply to the first two assignments handed in late.

THE ASSIGNMENT

1. This question uses the following ODL classes:

        class Student (extent Students key id) {
            attribute string name;
            attribute int ID;
            relationship Set<Course> enrolledIn
                inverse Course::students;
            relationship Set<Course> CoursesTAing
                inverse Course::TAsOfCourse;
        }
   
        class Course (extent Courses key number) {
            attribute string number;
            relationship Set<Student> students
                inverse Student::enrolledIn;
            relationship Set<Student> TAsOfCourse
                inverse Student::CoursesTAing;
        }
   

   The interpretation should be obvious: students have ID's (key) and names, courses have numbers (key) and TAs, who are themselves students. Students are enrolled in certain courses. Write the following queries in OQL:

   a)

   Find the names of all the TAs of CS180.

   b)

   Find all the students who are enrolled in a course and also TAing that course.

   c)

   Find all the TAs who are enrolled in more than 5 courses.

   d)

   Find all the TAs of the courses that are TAed by the TAs of CS180.

2. For the "auction" database:

        Bids(auctionID, bidder, price, quantity)
        Auctions(auctionID, seller, item, quantity, expires)
        Ratings(seller, stars)
   

   Design an equivalent ODL schema. You may assume that in Bids, a bidder may place several bids for one auction, but all bids by one bidder will have different prices. Indicate keys and extents.

3. Consider the following self-explanatory object-relational schema using SQL3 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 SQL3 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 Santa Cruz.

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

   (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.)