CS145 Assignment #5
Due Wednesday, November 11, 1998
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 7.3.2 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.
You will be working with the following schema for the rest of this
assignment. These tables are the same as those in Assignment #3.
Employee(SSN, name, salary, DNo)
Department(DNo, DeptName, MgrSSN)
Project(PNo, location, ProjName)
HourLog(SSN, PNo, hours)
The Employee relation provides a list of employees with their SSN, name,
salary, and department number (DNo). The SSN is unique for each
employee. Each employee belongs to only one department. The Department
relation contains a list of the departments for the company. Its schema
includes a unique department number called DNo. It also includes the name
of the department (DeptName) and the social security number of the
department's manager (MgrSSN). Each department has a only one manager. The
Project relation includes a unique project number (PNo), location and the
project name (ProjName). Each project has at least one person assigned
to it. Finally, the HourLog relation lists for each project the number of
hours of work for each employee who is assigned to that project. The key
of this relation is SSN and PNo.
- (3 pts.) Write an SQL2 declaration for the Department relation that
expresses the following constraint: "Each department has one unique
- (3 pts.) Write an SQL2 declaration for the HourLog relation
that expresses the following constraint: "All employees that appear in the
HourLog relation must also appear in the Employee relation."
- (3 pts.) Write an SQL2 declaration for the Employee relation
that expresses the following constraint: "Each employee must have a non-NULL
name, and his/her salary must be no less than $30,000."
- (3 pts.) Do 1) again by using a tuple-based check constraint
- (3 pts.) Do 2) again by using an attribute-based check
constraint in SQL2.
- (5 pts.) Use an assertion in SQL2 to specify: "Each manager
should have spent at least 100 hours on some project(s)."
- (5 pts.) Use an assertion in SQL2 to specify: "Each employee
who works for at least 200 hours on some project(s) should get a salary
higher than $50,000."
- (5 pts.) Write one trigger in SQL3 that prevents the average
salary of the employee in the "CS" department from dropping below $60,000 by
any deletion. You can only use the "for each row" option.
- (5 pts.) Do 8) again. You can only use the "for each
- (5 pts.) Consider the referential integrity constraint in 2).
Suppose we want to enforce the cascade policy for deletions on
relation Employee. Write one or more triggers in SQL3 that implement
this enforcement policy.