CS145 Assignment #5

Due Wednesday, November 11, 1998

Step 5 of Your PDA

1. (10 pts.) For each of the relation schemas of your PDA, indicate

(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. (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 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.
3. (15 pts.) 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.

   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.

4. (10 pts.) 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 once each. Also, show in the script the results of queries that demonstrate the functions have had their intended effect.

5. (10 pts.) 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.

Problem Set

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.

1. (3 pts.) Write an SQL2 declaration for the Department relation that expresses the following constraint: "Each department has one unique DNo."

2. (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. (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."

4. (3 pts.) Do 1) again by using a tuple-based check constraint in SQL2.

5. (3 pts.) Do 2) again by using an attribute-based check constraint in SQL2.

6. (5 pts.) Use an assertion in SQL2 to specify: "Each manager should have spent at least 100 hours on some project(s)."

7. (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."

8. (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.

9. (5 pts.) Do 8) again. You can only use the "for each statement" option.

10. (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.