CS145 Assignment #6

Due Tuesday, May 27, 1997

Step 6 of Your PDA

1. (20 pts.) Write two PL/SQL programs (See the PL/SQL Guide) to perform operations on your PDA database. Each should be nontrivial, in the sense that it involves at least one local variable and more than one SQL statement. 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. (20 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. (20 pts.) Write two Oracle Triggers. These are similar to the SQL3 triggers in Section 6.6 of the text, but with two significant differences:

   a)

   The FOR EACH ROW comes before the when-clause, rather than at the end.

   b)

   The body (action) of the trigger is a PL/SQL statement.

   Here is an example trigger:

   CREATE TABLE T1(
   	a INTEGER,
   	b CHAR(10)
   );
   
   CREATE TABLE T2(
   	c CHAR(10),
   	d INTEGER
   );
   
   CREATE OR REPLACE TRIGGER Trig
   	AFTER INSERT ON T1
   	FOR EACH ROW
   	WHEN(new.a <= 10)
   		BEGIN
   			INSERT INTO T2 VALUES(:new.b, :new.a);
   		END Trig;
   .
   run;
   

   It says: ``whenever there is an insertion into relation T1 with a first component equal to or less than 10, also insert this tuple, reversed, into relation T2.

   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. (10 pts.) Exercise 6.2.1(a, b, c) [p. 335]. Note: these problems only are concerned with what happens when a modification to MovieExec is made. You do not have to be concerned with modifications to Movie. Also, in part (c), "deletion" should be "deletion or update".

2. (10 pts.) Exercise 6.3.2(c, e) [p. 341]. You do not have to write the entire CREATE TABLE statement for a relation, but you should indicate the relation in whose schema the check appears.

3. (5 pts.) Exercise 6.4.2(c) [p. 346-7]. Hint: Remember that subqueries are OK in tuple-based checks and may access anything.

4. (15 pts.) Exercise 4.2.2(a, f, g) [p. 200, but refers to Exercise 4.1.3 on p. 190-1].