CS145 Assignment #5
Due Wednesday, November 15, 2000
Step 5 of Your PDA
- (10 pts.) For each of the relation schemas of your PDA, indicate
- A suitable key for the relation.
- 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.
- (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.
Write three PL/SQL programs (See the
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:
- Compute some aggregate value from a relation and use that
value to modify values in that or another relation.
- Create a new relation and load it with values computed from
one or more existing relations.
- 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.
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
Also, show in the script
the results of queries that demonstrate the functions have
had their intended effect.
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.
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,
- 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
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.
- All books that appear anywhere in this database must appear in the
- A book cannot reference a book published in a future year. Neither can
it reference itself.
- Revenue from a book (price times number of copies) must be at least
twice the authors' total earnings for that book.
- No reviewer may review more than 20 books published in the same year.
- 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.
Write the following triggers in either SQL3 standard notation or in the
Oracle trigger language (tell us which!).
Whenever a book gets a new review of score 10, each of the authors earns an
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.
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.
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.)