CS145 Assignment #2

Due Tuesday, April 22, 1997

Reminder for on-campus students: Assignments are due at 5:00 PM on Tuesday. 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 Tuesday must be timestamped by the Wednesday 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)

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

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

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

(d)
If Oracle is available significantly before April 22, we shall ask you each to log in and try some simple commands. If not, we'll expect this step as part of Assignment #3. No credit will be given in either case, 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.) Exercise 2.5.3(a) [p. 71].

  2. (6 pts.) Suppose there are two entity sets E1 and E2, and there are many-one relationships from E1 and E2 to a third entity set E. There does not necessarily have to be a many-one relationship between E1 and E2 in either direction. Demonstrate this fact with an example from real life (please give details if the course staff might not be familiar with this slice of ``real life'').

  3. (8 pts.) Exercise 2.6.2 [p. 76]. This exercise depends on Exercise 2.6.1, whose solution is at URL http://www-db.stanford.edu/~ullman/fcdbsols/sol2.html#sol26.

  4. (8 pts.) Exercise 2.7.1(b) [p. 80]. Here, you must refer to the problem of Exercise 2.1.3 [p. 38], which does not have a published solution. Do your network design directly, rather than doing an ODL design as an intermediate.

  5. (8 pts.) Exercise 2.7.2(b) [p. 80]. That is, do the same design in the hierarchical model.

  6. (8 pts.) Exercise 3.1.2(b) [p. 90].

  7. (8 pts.) Exercise 3.2.2 [p. 103]. Do not forget to consider the three modifications mentioned in Exercise 2.1.8; i.e., you need to give four relational database schemas.

  8. (8 pts.) Exercise 3.3.3(b) [p. 112]. This exercise depends on Exercise 2.6.1, whose solution is at URL http://www-db.stanford.edu/~ullman/fcdbsols/sol2.html#sol26.