CS145 Assignment #6

Due Monday, November 24, 1997

Step 6 of Your PDA

1. (20 pts.) Write four PL/SQL programs (See the PL/SQL Guide) to perform operations on your PDA database. Each should be nontrivial, illustrating a feature or features such as local variables, multiple SQL statements, loops, and branches. In addition, at least one should involve a cursor. We encourage you to be imaginative. However, here are some sorts of things you might try if you can't think of something more interesting:

   a)

   Compute some aggregate value from a relation and use that value to modify values in that or another relation.

   b)

   Create a new relation and load it with values computed from one or more existing relations.

   c)

   Enforce a constraint by searching your database for violations and fixing them in some way.

   Hand in a listing of your programs and scripts showing them working. You should demonstrate that the programs had their intended effect by querying (before and after) some relation of your PDA that was changed by the program. These queries may be included in the file that holds your PL/SQL programs for convenience.

2. (15 pts.) Write two PL/SQL stored functions or procedures. At least one should involve more than one SQL statement; you need not follow the other ``nontriviality'' conditions mentioned in (1). Each should use one or more parameters in a significant way.

   Hand in listings of your code and scripts showing them called at least once each. Also, show in the script the results of queries that demonstrate the functions have had their intended effect.

3. (15 pts.) Write two Oracle Triggers. See The PL/SQL Guide for a synopsis of Oracle triggers. You should also check The Non-SQL2 Guide for some important restrictions on triggers.

   Hand in your code and a script showing the triggers declared. Also, the script should show, for each trigger, the effect of two database modifications. One modification should trigger the trigger, and the other not. Show in the script queries that demonstrate that the trigger has an effect in the first case and not in the second.

Problem Set

1. (16 pts.) 3NF and 4NF:

   (a) (8 pts.) Exercise 3.7.1(f) [p. 154], parts (iii) and (iv) only.

   (b) (8 pts.) Exercise 3.8.3(c) [p. 165].

2. (14 pts.) Multivalued dependency rules:

   (a) (7 pts.) Exercise 3.8.7(c) [p. 166].

   (b) (7 pts.) Exercise 3.8.8(b) [p. 166].

3. (10 pts.) Multivalued dependency instance:

   Consider a relation R(A, B, C) containing four tuples as follows. List all nontrivial multivalued dependencies that hold on R .

   A	B	C
   1	2	3
   1	2	4
   5	2	3
   5	2	6

   Note: in general it is not possible to determine that a dependency of any sort holds, just by looking at one instance of a relation. However, we can still ask and answer the question of what the possible dependencies are, given one instance.

4. (10 pts.) BCNF and 4NF:

   Consider a database for a university, including information about courses, professors, departments, academic years (quarters), etc.

   (a) (5 pts.) Specify an example relation for this database and a set of functional and multivalued dependencies over the relation such that the relation is in BCNF bot not in 4NF. Specify all your assumptions and the dependencies that capture them. The assumptions should be realistic in modeling the real world.

   (b) (5 pts.) Decompose your relation from part (a) so that the new relations are in 4NF.