CS145 Assignment #5

Due Tuesday, May 20, 1997

Step 5 of Your PDA

1. (15 pts.) Reconsider your relation schema in the light of the theory of normalization and BCNF. Remind us of your chosen database schema. For each of your current relations, tell whether its relation schema is in BCNF. If not, then either redesign your schema so the relation is in BCNF, or give a rationale for leaving in in non-BCNF form (e.g., it is in 3NF and breaking it would damage your ability to maintain dependencies, or the amount of redundancy introduced is minimal, and splitting the relation would cause some reasonable queries to become multirelational). Indicate your final choice of design, whether or not you choose to decompose one or more relations.

2. (15 pts.) For each of the relation schemas of your PDA (as modified in part 1), indicate

   (a)

   A suitable key for the relation.

   (b)

   Any foreign key (referential integrity) constraints that you expect will hold for the relation.

   Modify your database schema to include the declaration of keys for all relations and at least one foreign-key constraint for some relation (even if you decided that no such constraints should logically hold -- we assume almost every PDA will have some natural foreign-key constraints). Show us the resulting database schema and the result of successfully declaring these relations to the database system.

3. (15 pts.) Add two attribute- or tuple-based CHECK constraints to relations of your database schema. Show the revised schema, its successful declaration, and the response of Oracle to inserts that violate the constraints. You may combine this part with the previous part if you like, to avoid repeating the schema.

4. (15 pts.) Create two views on top of your database schema. Show your CREATE VIEW statements and the response of the system. Also, show a query involving each view and the system response (but truncate the response if there are more than a few tuples produced). Finally, show what happens when you try to update your view, say by inserting a new tuple into it. Are either of your views updatable? Why or why not?

Problem Set

1. (10 pts.) Exercise 4.6.5(c) [p. 238].

2. (5 pts.) Exercise 5.2.3(c) [p. 263]. You do not have to show the result, just the query.

3. (5 pts.) Exercise 5.3.1(e) [p. 269].

4. (10 pts.) Exercise 5.5.2(c) [p. 278]. You do not have to show the result, just the query.

5. (5 pts.) Exercise 5.8.1(b) [p. 303].

6. (5 pts.) Exercise 5.9.3 [p. 312].