This week you will experiment with Oracle's facilities for views, constraints, and triggers. View definitions and constraint declarations largely follow the SQL2 standard although there are some restrictions; please see the previous handout Oracle 8i SQL for discussion. Triggers are discussed in some detail in the previous handout Using Oracle PL/SQL. A few frequently-asked questions regarding constraints and triggers appear on the Oracle 8i: Frequently Asked Questions Web page, recently posted.

1. Create two useful views on top of your PDA database schema. Show your CREATE VIEW statements and the response of the system. Then for each view show a query involving the view and the system response (but as usual you should truncate the response if more than a few tuples are produced).

2. For this problem you will recreate your PDA schema, adding specifications for additional keys, referential integrity, and other constraints.

   (a) Modify your PDA CREATE TABLE statements as follows.

   • For each relation in your schema, if the relation has any keys in addition to the PRIMARY KEY that you already declared, specify the additional keys as UNIQUE. If there are no additional keys, add a comment to that effect.

   • For each referential integrity constraint that should hold in your schema, specify the constraint using a REFERENCING clause within the appropriate CREATE TABLE statement. You may use the default option for handling referential integrity violations (violations will generate an error). We expect that everyone's PDA should include at least one referential integrity constraint. If your PDA has no natural referential integrity constraints, then it probably is either far simpler than we asked for, or a poor design - please contact one of the course staff.

   • Add at least 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 SQL2 standard; see the handout Oracle 8i SQL.

   Please turn in a .sql file containing all your CREATE TABLE statements, and a script showing their successful execution in Oracle.

   (b) Reload your small PDA database. Did you get any key, referential integrity, or CHECK constraint violations? Please turn in the .log file generated by the Oracle bulk-loader.

   (c) Reload your large PDA database. Did you get any key, referential integrity, or CHECK constraint violations? Please turn in the .log file generated by the Oracle bulk-loader.

   (d) You don't necessarily need to modify your program for generating data if it creates violations. However, for this part of the problem you should start with a database (small or large) that does not create violations. Write data modification commands to illustrate the following seven scenarios:

   1. An INSERT command creating a key violation
   2. An UPDATE command creating a key violation
   3. An INSERT command creating a referential integrity violation
   4. A DELETE command creating a referential integrity violation
   5. An UPDATE command creating a referential integrity violation
   6. An INSERT command creating a CHECK constraint violation
   7. An UPDATE command creating a CHECK constraint violation

   Please turn in a .sql file containing all seven commands, and a script showing their unsuccessful execution in Oracle.

3. Create at least two "interesting" triggers for your PDA. For each one, show your CREATE TRIGGER statement, its successful execution, and the effects of two database modifications. One modification should cause the trigger to fire, and the other should not. Show in your script the results of queries demonstrating that the trigger had an effect in the first case but not the second.

4. This problem is somewhat open-ended, however you must make some attempt at this problem in order to receive full credit.

   You are to do some sleuth work: Your task is to determine what criteria exactly Oracle uses in deciding whether a view is updatable, i.e., whether it is possible to perform INSERT, DELETE, and/or UPDATE statements on the view. While your sleuth work could involve sifting through HELP pages or Oracle books, we prefer that you do it experimentally. Write a series of views along with modification commands on the views to determine when Oracle allows views to be updated and when it does not. As discussed in class and in the textbook, some SQL views are obviously updatable, some are obviously not updatable (due to ambiguities), and some are theoretically updatable but it is difficult for a system to determine the correct update translations. In your solution to this problem you should attempt to provide a concise characterization of those views that Oracle allows to be updated, and you should support your claim by demonstrating:

   • views meeting the criteria that can be updated, and
   • views not meeting the criteria that cannot be updated.

   If separate criteria apply for INSERT, DELETE, and UPDATE commands then these should be included in your solution. You may use your PDA schema and data for this problem if you like, or you may use a separate, simpler database.

Submission

Unless otherwise specified, the scripts you turn in for this assignment may show operations running over your small or your large database (or in the case of Problem 4, another database altogether). Note:

• If you use your small database, be sure to test on your large database as well to make sure everything is working.
• If you use your large database, do not submit listings showing hundreds of lines of query results.