- For each of the relation schemas of your PDA, 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.
- Add two attribute-based and two tuple-based CHECK
constraints to relations of your database schema. Remember that these constraints
are more limited in Oracle
than in the SQL definition; see The
Guide to Nonstandard Oracle Features for details.
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.
-
Write three 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.
Submit 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.
-
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 (3).
Each should use one or more parameters in a significant way.
Submit 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.
-
Write two Oracle Triggers.
See
The Triggers Guide for a
synopsis of Oracle triggers.
You should also check The
Guide to Nonstandard Oracle Features for some important restrictions on triggers.
Submit 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.