CS145 Project Part 6

CS145 - Introduction to Databases
Spring 2000, Prof. Widom

Personal Database Application: Part 6
Due Monday May 15

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.

  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:

    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.


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:

As always, your assignment must be turned in on paper, you should provide comments for any operations that are not crystal clear, and it is an Honor Code violation to edit scripts before turning them in.