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.
- 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.
- 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.
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.
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.
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
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
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.
- Consider the following relational schema:
- Teams(name, country, fanLevel)
- Players(name, country, teamName, salary)
- Games(teamName1, numGoals1, teamName2, numGoals2, date)
- Goals(playerName, numGoals, date)
Rewrite the schema in SQL, representing the following constraints:
- Team names are unique.
- The fanLevel is either timid or wild.
- All teams from Brazil have wild fans.
- If a player appears in Goals, they must appear in
Players (you may assume name is the key for
- All Bulgarian players have names ending in "ov".
- A player plays for exactly one team.
- 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
- All games are between two different teams.
- Two teams can play more than one game, but on different dates.
- The Goals relation stores information only about players who have scored
at least one goal on the given date.
- 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.
- 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:
- The SQL declaration of your database schema with at least one referential integrity constraint.
- The initial state of your database.
- The sequence of database modifications.
- The state of your database after each modification under each of the three referential-integrity-constraint policies (reject, cascade, set-null).
- Consider the relation Flights(passenger, airline, flightNum, miles).
- 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.
- 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.
Using the schema from Problem 1, write the following in PL/SQL:
- 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.
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.
For this part, you must use a cursor, even though it is possible
to do the task without one.