Due Wednesday May 24

Note: This assignment covers only object-oriented design and OQL. Object-relational SQL will be covered in the next assignment.

*Observe the similarity between this problem and Problem 4 on Written
Assignment #1. You may or may not find it advantageous to use
your solution or the sample solution to that problem as a guide when
solving this one - it's up to you.*

You are to design a database for an insurance company. The data will include information about customers (name, address, and phone number), information about insured cars (make, model, year, vehicle-ID#, license#, and insurance rate), information about insured homes (address, insured value, and insurance rate), and information about claims made on insured cars and homes (date of claim, date of settlement, and amount of settlement). You may assume that all insured cars and homes are owned by a single customer, but you should allow a customer to own several cars and homes. Please state any additional assumptions you make about the real world in your design.

**(a)** Specify an ODL schema for this database. In
addition to class definitions with attributes and relationships, don't
forget to include keys and inverse relationships.

**(b)** Using the method for translating an ODL design into
relations, produce a set of relations for your database design. In
the cases where different mappings to relations are possible, choose
whichever one you think is more appropriate. Be sure to specify
(underline) keys for all relations.

*Note: There is no single right answer to this problem by any
means, although some answers may be better than others.*

**(a)** Specify an ODL schema for this database. In
addition to class definitions with attributes and relationships, don't
forget to include keys and inverse relationships.

**(b)** Using the method for translating an ODL design into
relations, produce a set of relations for your database design. In
the cases where different mappings to relations are possible, choose
whichever one you think is more appropriate. Be sure to specify
(underline) keys for all relations.

*Note: This problem is even more "free form" than Problem 1, so
there are numerous good answers. As usual, please state any
assumptions you make about the real world in your design.*

**(a)** Give an example of an ODL class definition and a key for
the class such that the key includes at least one relationship, and
there is no natural key for the class that includes only attributes.
Use any real-world domain you find appropriate.

**(b)** What concept in the entity-relationship model
corresponds to an ODL class where a relationship is needed in the key?

`
interface Student (extent Students, key ID) {
attribute integer ID;
attribute Struct{string first, string last} name;
relationship Set`

`
interface Internship (extent Positions, key (company, city)) {
attribute string company;
attribute string city;
relationship Set`

Write OQL queries for each of the following.

**(a)** Find the ID's of all students whose last name is "Smith".

**(b)** Find the ID's and last names of all students who have applied to
an internship at a company in Palo Alto. Do not repeat (ID,last-name)
pairs in the result, even if the student has applied to many
internships in Palo Alto.

**(c)** If you used `distinct` in your answer for part (b), rewrite
the query so you don't need to use `distinct`. Conversely, if you
didn't use `distinct` in your answer for part (b), rewrite the
query so you do need to use `distinct` in order to guarantee that
duplicates are eliminated.

**(d)** Find the names of all companies in Palo Alto such that at
least one student `S` (say) with ID between 25 and 50 has
applied for an internship at that company, and all internships student
`S` has applied for are in Palo Alto or San Jose.

**(e)** Recall that the result of an OQL query or subquery is a
set or a bag. OQL allows two sets (bags) to be compared using =,
where two sets (bags) are equal if they contain exactly the same
objects. Find all pairs of student ID's such that the two students
have applied to internships at the exact same set of companies in Palo
Alto. (The students may have applied to different internships at
companies in other cities.) Return each pair of ID's exactly once,
and order the final result based on the last name of the first student
in each pair.

**(f)** Can you write the query in part (e) without using set or bag
equality? If so, write it. If not, explain why not.