There are three handouts relevant to this assignment:

• Using Oracle PL/SQL
• Introduction to Pro*C
• Introduction to JDBC

This week you will experiment with Oracle's proprietary programming language PL/SQL, and you will use either Pro*C embedded SQL or JDBC dynamic SQL to interact with your PDA database from an external program.

1. Write two PL/SQL programs (see Using Oracle PL/SQL) to perform operations on your PDA database. Each program should be complicated enough to involve at least one local variable, more than one SQL statement, and some data modification. In addition, at least one should involve a cursor, and you should demonstrate use of loops and branching. We encourage you to be imaginative. However, here are some 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 semantic constraint on your data by searching your database for violations and fixing them in some way.

   Turn in a listing of your programs, along with 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.

2. Write two PL/SQL stored procedures or functions (see Using Oracle PL/SQL: Procedures). They should perform some data modifications, and at least one should involve more than one SQL statement, but otherwise the procedure bodies can be simple. However, each one should use one or more parameters in a significant way. Turn in listings of your code and scripts showing the procedure called at least once each. Also, show in your script the results of queries that demonstrate that the procedures had their intended effect.

3. Build a user-friendly interactive application program front end to your PDA using the C, C++, or Java programming language. Your program should consist of a continuous loop in which:

   1. A list of at least five alternative options is offered to the user. (An additional alternative should be quit.)
   2. The user selects an alternative.
   3. The system prompts the user for appropriate input values.
   4. The system accesses the database to perform the appropriate queries and/or modifications.
   5. Data or an appropriate acknowledgment is returned to the user.

   You should include both queries and modifications among your options. For example, a UC campus applicant database interface might include in its menu:

   1. A number of useful queries on the database, with both input and output in a format more convenient and pleasing than raw interactive SQL.
   2. Insert a new student record.
   3. Insert a new application record.
   4. Update a student's address, GPA, or SAT.
   5. Update an application's decision field.
   6. Quit.

   Your application code should interact with the database using Pro*C embedded SQL for C or C++ programs, or using the JDBC call-level interface for Java programs. Please refer to the appropriate handout:Introduction to Pro*C or Introduction to JDBC. We are not expecting anything particularly fancy in terms of the interface itself. (In fact, ultimately this interface will be replaced by the Web interface you will create in the final part of the project.) For example, in C a menu printed via printf is fine. Also, handling of SQL errors can be quite simple. You can write a routine that just prints the error message from Oracle, or model your error handler after one of our sample programs.

   Please turn in your C, C++, or Java code along with a script showing an interaction with your program. Each one of your options should be exercised at least once in your script.

Submission

The scripts you turn in for this assignment may show your programs running over your small or your large database. However:

• If you use your small database, be sure to test your programs on your large database as well to make sure they are correct and reasonably efficient.
• If you use your large database, do not submit listings showing hundreds of lines of query results.