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.
(a) Modify your PDA CREATE TABLE statements as follows.
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:
Please turn in a .sql file containing all seven commands, and a script showing their unsuccessful execution in Oracle.
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: