CS145 Assignment #5

Due Wednesday, November 15, 2000

Step 5 of Your PDA

1. (10 pts.) 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.

2. (15 pts.) 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 8 than in the SQL2 definition; see The Non-SQL2 Guide 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.
3. (15 pts.) 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.

   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.

4. (10 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.

5. (10 pts.) Write two Oracle Triggers. See The PL/SQL Guide for a synopsis of Oracle triggers and some important restrictions.

   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

In this assignment, we will continue to work with the book database from the previous assignment. Here are the relations again:

• BookAuthor(book, year, author, earnings)
• BookReference(book, year, referencedBook, referencedYear, times)
• BookReview(book, year, reviewer, score)
• BookPublish(book, year, publisher, price, num)

In this database, each book may have one or more authors and each author may make a different amount of money from that book. One book may make reference to other books. One book may be reviewed by different reviewers and get different scores. An author could also be a reviewer and a publisher. Books with the same name but published in different years are considered different books.

1. (20 pts.) Write schemas for these relations in SQL2, with the following constraints. Use attribute-based checks, tuple-based checks, foreign key declarations, or SQL2 assertions, as appropriate.

   (a)

   All books that appear anywhere in this database must appear in the relation BookPublish.

   (b)

   A book cannot reference a book published in a future year. Neither can it reference itself.

   (c)

   Revenue from a book (price times number of copies) must be at least twice the authors' total earnings for that book.

   (d)

   No reviewer may review more than 20 books published in the same year.

   (e)

   If a book is referenced by more than 20 other books, then its average review score must be at least 8, unless there is no review for this book.

2. (10 pts.) Write the following triggers in either SQL3 standard notation or in the Oracle trigger language (tell us which!).

   (a)

   Whenever a book gets a new review of score 10, each of the authors earns an additional $100.

   (b)

   The database should reject insertions into BookAuthor which makes an author earn more than twice as much as a co-author of the same book.
   Note: Check out the Oracle 8i FAQ for instructions on how to reject modifications. In SQL3, it is also possible to clean up (i.e., reverse) the modification after it is made, rather than rejecting it.

3. (5 pts.) Consider the referential integrity constraint in problem 1(a). Suppose we want to enforce the cascade policy for deletions on relation BookPublish. Write one or more triggers in SQL3 that implement this enforcement policy.

4. (5 pts.) Given two relations R(A,B,C) and S(B,C,D), express the outerjoin of R and S using relational algebra. (Without using the outerjoin symbol, of course. Assume that N(V) is a 1-row relation containing one "NULL" item. Give your answer in terms of R, S and N.)