Assignment #1
Due Wednesday October 9

Reminder for on-campus students: Assignments are due at 5:00 PM on Wednesday. Late assignments received by 12:00 noon on Friday will be penalized 33%. Assignments will not be accepted after noon on Friday. Assignments should be turned in during class or at the course secretary's office: Gates Building room 419. This late policy will be strictly enforced.

Reminder for SITN students: Assignments due on Wednesday must be timestamped by the Thursday morning courier. Late assignments timestamped by the Friday morning courier will be penalized 33%. Assignments timestamped later than Friday will not be accepted. Assignments not sent by SITN courier are subject to the late policy for on-campus students specified above. This late policy will be strictly enforced.

1. You are to design a fictional database maintained by an insurance company. The data should include information about customers (such as their name, address, and phone number), information about insured cars (such as their make, model, year, vehicle-ID#, license#, and insurance rate), information about insured homes (such as their address, insured value, and insurance rate), and information about claims made on insured cars and homes (such as 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)

   Specify an entity-relationship diagram for this database. Don't forget to underline key attributes and include arrowheads indicating the arity of relationships.

   Note that there is no single right answer to this question, although some answers may be better than others.

2. You are to design a fictional 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. This question is deliberately more ``free form'' than Question 1, so that you can appreciate the flexibility and choices involved in designing a database. Again, please state any assumptions you make about the real world in your design.

   (a)

   Specify an ODL schema for this database. Don't forget to include keys and inverse relationships.

   (b)

   Specify an entity-relationship diagram for this database. Don't forget to underline key attributes and include arrowheads indicating the arity of relationships.

3. 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 class C is a set of attributes and/or relationships {A1,A2,...,An,R1,...,Rm}. of C such that no two objects in C will have the same value for all attributes and relationships {A1,A2,...,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?

4. Recall that a key for an entity set E is a set {A1,A2,...,An} of attributes of E such that no two entities will ever have the same value for all attributes {A1,A2,...,An}. Although we did not discuss it in class, relationship sets in the entity-relationship model also have a notion of key: A key for a relationship set is a set of attributes {A1,A2,...,An} such that no two relationships will ever have the same value for all attributes {A1,A2,...,An}.

   The attributes that form a key for a relationship set may be attributes of the relationship set itself, but the key also usually includes attributes of the entity sets over which the relationship set is defined. For example, suppose we have a many-many relationship set Took between a Students entity set and a Courses entity set, and Took has two attributes Quarter and Grade. Assuming that a student can take a course only once, then a key for the relationship set is {Student-ID, Course#}. If a student can take a course more than once, then a key for the relationship set is {Student-ID, Course#, Quarter}.

   Let E1 and E2 be entity sets with key attributes K1 and K2, respectively. Let R be a relationship set from E1 to E2, and suppose for now that R has no attributes. For each of the following scenarios, specify a key for R. In each case your key should be minimal; that is, attributes that are not needed in the key should not be included.

   (a)

   R is many-to-many.

   (b)

   R is many-to-one from to .

   (c)

   R is one-to-many from to .

   (d)

   R is one-to-one.

   Now suppose relationship set R does have at least one attribute. The second Students-Courses example above illustrates a many-many relationship set where an attribute of the relationship set (Quarter) is needed in the key.

   (e)

   Give an example of a many-one relationship set where an attribute of the relationship set is needed in the key. Use any real-world domain you find appropriate.

5. Although we did not discuss it in class, in ODL it is possible for a relationship to be its own inverse.

   (a)

   Give a brief definition of what it means for a relationship in an ODL class to be specified as its own inverse. Your definition should be in terms of objects in the class and how they relate to each other. (A one-sentence answer is sufficient.)

   (b)

   Give an example of an ODL class that includes a relationship that is its own inverse. Use any real-world domain you find appropriate.

6. Personal Database Application (PDA)

   As the course progresses you will be building up a substantial database application for a real-world scenario of your choosing. You will design schemas for the database, and you will create an actual database using a relational database management system. You will populate the database with sample data, write interactive queries and modifications on the database, and develop user-friendly tools for manipulating the database.

   Your first step is to identify the scenario you would like to manage with your database, and to construct ODL and entity-relationship schema designs for the data. We suggest that you pick an application that you will enjoy working with, since you'll be stuck with it for the whole quarter! In previous years, students who built a database about something they were interested in--a hobby, material from another course, a research project, etc.--got the most out of this part of CS145.

   Try to pick an application that is relatively substantial, but not too enormous. For example, when expressed in the entity-relationship model, you might want your design to have in the range of five or so entity sets, and a similar number of relationships. Note that this is a ballpark figure only! You should certainly include different kinds of relationships (e.g., many-one, many-many) and different kinds of data (strings, integers, etc.), but your application need not necessarily require advanced features such as subclassing in ODL, or weak entity sets or roles in E/R.

   (a)

   Write a short (approximately one paragraph) description of the database application you propose to work with throughout the course. Your description should be brief and relatively informal. If there are any unique or particularly difficult aspects of your proposed application, please point them out. Your description will be graded only on suitability and conciseness.

   (b)

   Specify an ODL schema for your proposed database. As always, don't forget to include keys and inverse relationships.

   (c)

   Specify an entity-relationship diagram for your proposed database. As always, don't forget to underline key attributes and include arrowheads indicating the arity of relationships.

   If you are having trouble thinking of an application, or if you are unsure whether your proposed application is appropriate, please feel free to consult with one of the course staff.