Project 2
CS 355 – DBMS Design
Project submitted by D.
Bozarth
12 December 2003
Project 2
CS 355 – DBMS Design
David. Bozarth
12 December 2003
- point the web browser to:
http://localhost:8095/cs355/myDB/enterAccountInfo.html
see an interface flow diagram at:
http://www.sonoma.edu/~dbozarth/FlowDiagram.html
Two of the tables in the Dental Rate Quote database are Account and Contact. The Account table stores identifying information used to manage a single customer relationship. This table contains several attributes that each identify a person. These are each foreign keys related to the primary key of the Contact table, which stores contact information on the customer and certain account officials.
Account tuples have primary key Account ID, and Contact tuples have primary key Contact Name. For this project, only three attributes of each table are exposed for update.
The interface is supposed to decide, based on the entry in the Customer Name field of the Account entry form, whether to require a secondary insertion of a new record to the Contact Table. In other words, if I enter the name of a person the database does not yet know, the interface will require me to create a new Contact record for that person.
The entry form presents text boxes for Account ID, Customer Name, and #Enrollees.
Any alphanumeric text will work in the ID and Name fields. The #Enrollees must be numeric, with value greater than zero.
As of this writing there is no interface-level validation implemented, but Oracle/SQL
constraints exist, so the database server will reject any entry that violates a constraint, and will provide an error message indicating a constraint was violated.
The JSP interface checks for an existing Contact record matching the Customer Name you enter. If no match is found, a second form pops up, requesting entry of info for a new Contact record. Any alphanumeric text will be accepted in the three fields of this form, unless the Contact Name entry is not matched to a primary key in the Contact table.
When you submit the newly entered info, your entry is updated to the Contact table, and the info from the first form is updated to the Account table.
This part works fine. As of this writing, however, problems remain. The second form will pop up in any case (even if a match was found in the Contact table). Then, if the Contact form entry (in the Contact Name field) matches the key of an existing Contact record, you will get an Oracle error indicating the ‘unique’ constraint was violated.
There are one html file and two sets of JSP files, serviced by a directory of Java class files. One set of JSP activates a session-scope bean on the Account table, validates the Account input form, invokes a Java connection to the database, and checks for a matching Customer Name entry from the Account input form. JSP then makes a decision (needs work to fix this, as indicated above) whether to invoke the second set of JSP pages that manage a second bean with request-scope. That system manages the second entry form, and after validation and update of the Contact table, returns control to the Account session bean. This system then completes updating the Account table.
I coded the first part of a refinement which would pass a parameter from the Account system to the Contact system. This parameter value references the Customer Name, so that the Contact entry form could populate its Contact Name field with the correct customer’s name for the new record to be created. Unfortunately, this enhancement had problems and did not go to completion before the project deadline.
(continued on next page …)
David Bozarth
CS 355 – Database Design
Fall 2003
I was able to fix some problems from the previous submission, and to provide a flow diagram for the interface mechanism.
The secondary form (for entry of a new Contact record) now appears only when the Customer Name entry on the primary form is not matched to a primary key in the Contact Table. This form shows a message indicating that the required fields need filling in. The user’s entries are posted directly to a new tuple in the Contact table, since there is no restriction on the data format for these fields.
The session-scope system then compares the Account form entry for Account ID with the primary keys in the Account table. If a match is found, the session is invalidated and the user is told that the new Contact was added, but the Account is active and can not be updated. Otherwise, a copy of the Account entry form is returned with its original user entries, and the user may then Submit the update for the Account table. The interface then renders a listing of the updated contents of the Account table, and the session becomes invalid.
If, on the other hand, in original Account form entry contains a Customer Name that is matched in the Contact table, the session-scope system then compares the Account form entry for Account ID with the primary keys in the Account table. If a match is found, the session is invalidated and the user is told that the Account is active and can not be updated. Otherwise, the form info is posted to a new tuple in the Account table. The interface then renders a listing of the updated contents of the Account table, and the session becomes invalid when the user reloads the Account entry form (html page), or closes the browser.