CS145 Assignment #5

Due Tuesday, November 13, 2001

Step 5 of Your PDA

As previously, we would like you to use the submit script for the PDA part, and hand us written work for the problem set.
  1. For each of the relation schemas of your PDA, indicate

    2. (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. 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 than in the SQL definition; see The Guide to Nonstandard Oracle Features 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. 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.

    Submit 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. 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.

    Submit 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. Write two Oracle Triggers. See The PL/SQL Guide for a synopsis of Oracle triggers. You should also check The Guide to Nonstandard Oracle Features for some important restrictions on triggers.

    Submit 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

  1. Consider the following relational schema: Rewrite the schema in SQL, representing the following constraints:

    a)
    Team names are unique.

    b)
    The fanLevel is either timid or wild.

    c)
    All teams from Brazil have wild fans.

    d)
    If a player appears in Goals, they must appear in Players (you may assume name is the key for Players).

    e)
    All Bulgarian players have names ending in "ov".

    f)
    A player plays for exactly one team.

    g)
    A foreign player (one whose own country differs from the country of his/her team) has a salary that's at least 20% greater than the salary of any domestic player playing for the same team.

    h)
    All games are between two different teams.

    i)
    Two teams can play more than one game, but on different dates.

    j)
    The Goals relation stores information only about players who have scored at least one goal on the given date.

    k)
    All goals are represented in the Goals relation, i.e., the number of goals scored by a team on a given date should be the same as the sum of the number of goals scored by its players on that date.

  2. Specify a scenario where the result of a particular sequence of database modifications (inserts, deletes, and updates) is the same regardless of the referential-integrity-constraint policies but some of the intermediate states differ depending on the actual policies. You need to show:

  3. Consider the relation Flights(passenger, airline, flightNum, miles).

    (a)
    Write a trigger that doubles the miles for a flight that a passenger takes on an airline on which they have already accumulated more than 100,000 miles.

    (b)
    Write a trigger that resets the miles to 500 for any passenger-flight for which there is an attempt to change the miles to less than 500 or increase them more than tenfold.

  4. Using the schema from Problem 1, write the following in PL/SQL:

    a)
    A procedure Trade(player, team1, team2), which checks that the player is currently on team1, and if so, changes his/her team to team2 and gives them a 10% raise in salary.

    b)
    Examine the Goals relation and, each time a player scored two goals on a single day, give them a $1000 raise in salary. If they scored three goals on one day, give them a $10,000 raise, and if they scored four or more goals, give them a $100,000 raise. Note: For this part, you must use a cursor, even though it is possible to do the task without one.