CS145 Assignment #2

Due Wednesday, Oct 15, 1997

Reminder for on-campus students: Assignments are due at 5:00 PM on Wednesday. You are allowed one lateness of up to 48 hours; use that privilege carefully. Assignments should be turned in during class or at the course secretary's office: Gates Building room 495.

Reminder for SITN students: Assignments due on Wednesday must be timestamped by the Thursday morning courier to be considered on-time. Like the on-campus students, you have one free 48-hour exception.

Step 2 of Your PDA (Personal Database Application)

(15 pts.) Consider the ODL schema you designed for your PDA in Assignment #1. Please attach a copy of the schema. Use the method described in the text and in class for translating an ODL schema to relations to produce a set of relations for your database design. Please specify your relational schema using notation of Section 3.1.2, and underline key attributes. In cases where there are alternative mappings for ODL constructs (such as for sets and relationships), you may use whichever mapping you prefer.

(15 pts.) Consider the Entity-Relationship diagram you designed for your PDA in Assignment #1. Please attach a copy of the E/R diagram. Use the method for translating an E/R diagram to relations described in class and the text to produce a set of relations for your database design. Again, specify your relational schema using the notation of Section 3.1.2, and please be sure to underline key attributes.

(10 pts.) Which of the two relational schemas you obtained in parts (a) and (b) of this problem do you like better? Is there anything you still don't like about the schema (e.g., attribute names, relation structure, duplicated information, etc.)? If so, modify the relational schema to something you prefer. You will be working with this schema quite a bit, so it's worth spending some time to make sure you're happy with it.

Login to Oracle, using your Oracle account, which should be set up shortly after Oct. 6. Use the Oracle Introductory Guide to see how to login and change your password. Try some simple commands, such as help or table (relation) creation. No credit will be given, but it is important that people try logging in as soon as there is a good chance your login will be recognized. We'll need time to handle login problems such as someone who thinks they are registered for the class and isn't.

Don't forget to save a copy of your PDA for reference as you do Step 3 of the PDA.

Problem Set

  1. (6 pts.) This question is based on Exercise 2.5.3 in the text [p. 71]. In particular, the notion of ``smallest key'' is defined there. Consider a three way relation R
  2.             E1 <----- R ----- E2

    where Key for Ei is Ki. Give the smallest posiible key for R

  3. (6 pts.) Suppose there are three entity sets E, E1 and E2, and there are many-one relationships from E1 to E and from E to E2. Prove that there exists a many-one relationship from E1 to E2. Recall that a many-one relationship means there is ``at most one,'' not that there is ``exactly one.''

  4. (8 pts.) We would like to design a database to maintain some information about books stocked in various libraries. The information includes: