Comp 353 Assignment 4

Comp 353 Assignment 4 Due: by final exam, April 30

I've provided for you a somewhat "rough" version of the program for creating new records in the STUDENT table, using both standalone java and web_pl/sql. You are to take either approach, and complete the steps below.

You should try to decide early whether you will use the standalone_java or the web_pl/sql approach. The web_pl/sql approach makes layout easier, simply because you can use html forms to handle the layout instead of a javax.swing layout manager (for which I recommend BoxLayout). However, the advantage to standalone_java is that most of you are probably more familiar with java than with pl/sql, and everything is in one file (versus at least four for the pl/sql approach). Bear in mind that you apparently cannot use the web_pl/sql method if you're using oraserver.cs.luc.edu; that installation does not support web_pl/sql. You can only use web_pl/sql if you have access to an oracle xe installation.

Here are the changes to my versions that you should implement:

1. Add support for entering street address and zipcode (IGNORE city and state).

2. For the fields representing foreign keys (STARTTERM, FACULTYID, MAJORID), create selection menus (rather than text boxes), so it is in effect impossible to enter illegal values or to omit values (there is a risk here that the default advisor will end up advising everyone). Also, for FACULTYID and MAJORID, make sure the menus include the text description of the selection, not the numeric form; that is, the faculty member's last name or major.majordesc. (For STARTTERM, termids are already in the reasonably self-explanatory form of SP03, FL04.) The java way to do this is the JComboBox; I added one to Standalone.java (as part of a new LabeledCombo class). Be aware that, in general, this approach to fields where the value must come from a specific list starts to become impractial when the list has more than a few dozen items.

3. If the STUDENTID field is left blank, fill it in with the value

	1 + select max(studentid) from students;

4. All other fields are to require values. If any are left blank, return the user to the form with the missing values highlighted. The simplest way to do this is to insert the string MUST BE FILLED IN into the textbox. (A better way is to change the text color of the textbox label to red, like "real" forms, but this is more complicated.)

5. If the record creation worked, show the user a form that says this and which provides an OK button.

There are other things that might be done to improve the user interface. You might wish to clean up some of the extraneous buttons. Also, be sure that the newly added record shows up in the full table of records that appears lower down.

For the java standalone version, the files are Standalone.java and ojdbc14.jar. Remember that the latter must be in your CLASSPATH!!

For the web_pl/sql version, the files are as follows (that some have a 2 in them is a developmental accident):

form2.sql, the initial form
formcatcher2.sql, the ACTION for the above
new_student2.sql, the form for entering column values
make_new_student.sql, the ACTION form for new_student2.

If you're using oracle XE and web_pl/sql, you have to create a Database Access Descriptor to tie an oracle account to a url. If the oracle account name is "foo", then the following will make foo's pl/sql procedure "bar" available as localhost:8080/foo/bar:

        BEGIN
        DBMS_EPG.create_dad (dad_name => 'foo', path => '/foo/*');
        END;

You will probably have to type the password for account foo the first time you access; there's a way around this but I didn't look it up.