CS145 - Introduction to Databases
Spring 2000, Prof. Widom

Written Assignment #6
Due Wednesday May 24

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

Problem 1

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.

Problem 2

You are to design a database maintained by a library. The data should include information about books, employees, and customers. It should include a log of all books checked out by an employee to a customer, including if and when books were returned.

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

Problem 3

Specify an ODL class definition for any real-world domain of your choosing such that the class realistically includes a relationship that is its own inverse. Please do not use an example that was given in lecture or in the help session.

Problem 4

Recall that a key for an ODL class C is a set of attributes {A1,A2,...,An} of C such that no two objects in C will ever have the same value for all attributes {A1, A2,...,An}. Although technically ODL does not allow relationships in keys, it is certainly reasonable for a key to include relationships as well as attributes. In this case, a key for a class C is a set of attributes and/or relationships {A1,...,An,R1,...,Rm} of C such that no two objects in C will have the same value for all attributes and relationships {A1,...,An,R1,...,Rm}.

(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?

Problem 5

Consider the following ODL schema for a database of students applying for summer internships.

interface Student (extent Students, key ID) {
attribute integer ID;
attribute Struct{string first, string last} name;
relationship Set applied
inverse Internship::applicants;
}

interface Internship (extent Positions, key (company, city)) {
attribute string company;
attribute string city;
relationship Set applicants
inverse Student::applied;
}

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.