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 should be drawn using a er diagramming tool or graphics editor.
• Deadline: The on-time deadline for all students is in class on the due date.

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