CS145 Assignment #5
Due Wednesday, November 10, 1999
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
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.
You should also check The
Non-SQL2 Guide for some important restrictions on triggers.
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.
The written part of this assignment is based on the same relations as in
the last part of Problem Set 4:
Bids(auctionID, bidder, price, quantity)
Auctions(auctionID, seller, item, quantity, expires)
Write schemas for these relations in SQL2.
Decide on appropriate types for the attributes.
Declare keys as appropriate (your judgement is called for).
Declare foreign keys so that a bid must be on an auction whose ID exists
in Auctions and a seller in an auction must have a rating in
Also, require that:
- The number of stars is always between 1 and 5.
- The expiration date for an auction cannot be null.
Show how to add to your schema from problem (1) the following
tuple-based checks, or SQL2 assertions, as appropriate.
A bid cannot be for more than the total quantity of items available in
the auction for which the bid is made.
The quantity in any auction cannot be negative.
(This constraint does not address the question of whether a bid
can be for a negative number.)
If a seller is rated ``1 star,'' then they cannot hold more than 5
Write the following triggers in either SQL3 standard notation or in the
Oracle trigger language (tell us which!).
When a new auction is entered into the Auctions table, the
seller is listed with a bid of 0 for the entire quantity in the
A bid in an auction for which an equal or larger bid already exists is
Check out Jun Yang's FAQ Guide for an
explanation of how Oracle rejects modifications.
However, it is also possible to clean up (i.e., reverse) the
modification after it is made, rather than rejecting it.