CS145 Project Part 5

CS145 - Introduction to Databases
Spring 2000, Prof. Widom

Personal Database Application: Part 5
Due Monday May 8

There are three handouts relevant to this assignment:

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:

    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:

As always, your assignment must be turned in on paper, you should provide comments for your queries or operations unless they are crystal clear, and it is an Honor Code violation to edit scripts before turning them in.