CNT4714 Project 4- A Three-Tier Distributed Web-Based Application Solution

Title: “Project Four: Developing A Three-Tier Distributed Web-Based Application”


Objectives: To incorporate many of the techniques you’ve learned so far this semester into a distributed three-tier web-based application which uses servlets and JSP technology running on a Tomcat container/server to access and maintain a persistent MySQL database using JDBC.

Description: In this assignment you will utilize a suppliers/parts/jobs/shipments database (creation/population script available on Webcourses under Project 4) as the back-end database. Frontend access to this database by end users will occur through a single page displayed in the client’s web browser. The schema of the backend database consists of four tables with the following schemas for each table:

suppliers (snum, sname, status, city) //information about suppliers parts (pnum, pname, color, weight, city) //information about parts jobs (jnum, jname, numworkers, city) //information about jobs
shipments (snum, pnum, jnum, quantity) //suppliers ship parts to jobs in specific quantities

The first-tier (user-level front-end) of your application will be two JSP pages, one of which handles rootlevel user clients, the other which handles non-root-level clients, to enter SQL commands into a window (i.e. a form) and submit them to a server application for processing. The front-end (and only the frontend) will utilize JSP technology. The front-end will provide the user a simple form in which they will enter a SQL command (any DML, DDL, or DCL command could theoretically be entered by the user, however we will restrict to queries, insert, update, replace, and delete commands). The front-end will provide only three buttons for the user, an “Execute Command” button that will cause the execution of the SQL command currently in the input window, a “Reset Form” button that simply clears any content currently in the form input area, and a “Clear Results” button that will erase the currently displayed data (user optional). The front-end will run on any web-based browser that you would like to use. You can elect to have a default query or not, it is entirely your decision. The application will connect to the backend database as either a root-level or non-root-level user depending on which front-end page is utilized. This connection must be handled using only one of two techniques. Either the connection is established using properties read from a properties file, or the connection is established via initialization parameters located within the web.xml file of the Project4 webapp. You can use whichever technique you prefer for this project (you can use one of each if you’d like).

The second-tier servlets, ae in charge of handling the SQL command interface for the user. The rootlevel user servlet will also implement the server-side business/application logic. This logic will increment by 5, the status of a supplier anytime that supplier is involved in the insertion/update of a shipment record in which the quantity is greater than or equal to 100. Note that any update of quantity >= 100 will affect any supplier involved in a shipment with a quantity >= 100. The example screen shots illustrate this case. An insert of a shipment tuple (S5, P6, J7, 400) will cause the status of every supplier who has a shipment with a quantity of 100 or greater to be increased by 5. In other words, even if a supplier’s shipment is not directly affected by the update, their status will be affected if they have any shipment with quantity >= 100. (See page 14 for a bonus problem that implements a modified version of this business rule.) The business logic of the second tier will reside in the servlet on the Tomcat web-application server (server-side application). This means that the business logic is not to be implemented in the DBMS via a trigger.

The third-tier (back-end) is the persistent MySQL database described above and is under control of the MySQL DBMS server. You will create and maintain this database via the creation/population script. See the important note below concerning when/how to re-run this script for your final submission.

References:
Notes: Lecture Notes for MySQL installation and use. Documentation for MySQL available at: http://www.mysql.com. More information on JDBC can be found at: http://www.oracle.com/technetwork/java/javase/jdbc/index.html . More information on Tomcat can be found at http://tomcat.apache.org. Lecture Notes for Servlets. Lecture Notes for JSPs.

Restrictions:
Your source file shall begin with comments containing the following information:
/* Name:
*/

Input Specification: The suppliers/part/jobs/shipments database (named project4) that is created/populated by the script project4dbscript.sql, is the back-end to this application. All other input comes from the front-end client submitted to the application server based servlet entered as either queries or updates to this database. There are two sets of commands that you are to execute against this database included in the project4rootcommands.sql and project4clientcommands.sql available on WebCourses under Project 4. As with Project 3, your client-level user will have only select privileges on the project4 database. Also, as with Project 3, your front-end cannot execute the entire script at one time. You’ll need to execute the commands in this script one at a time in your application (copy and paste!). You can run the scripts in the MySQL Workbench if you’d like to compare/see the result sets for each user command.

Output Specification: All output is generated by the servlets and should appear in the user’s browser as a text/html page presented to the user. All MySQL-side errors should be caught and reported to the user via the interface. IMPORTANT: Be sure to re-run the project4dbscript.sql database creation/population script before you begin creating your screen shots for submission. By doing so you will ensure that the database is in its initial state so that all update operations will produce the values we are expecting to see in your result outputs. Then, as with Project 3, run all commands in sequence from the project4rootcommands.sql script file (total of 20 different commands), followed immediately by all command in sequence from the project4clientcommands.sql script file (total of 4 different commands).




Deliverables:
(2) The following 20 screen shots from the project4rootcommands.sql script file must be submitted as part of the deliverables for this project. (You can include the screenshots in the top-level of your webapps folder if you’d like, just be sure to include a note that you’ve done so.)
a. Command 1
b. Command 2A
c. Command 2B
d. Command 2C
e. Command 3A
f. Command 3B
g. Command 3C
h. Command 3D
i. Command 3E
j. Command 4
k. Command 5A
l. Command 5B
m. Command 5C
n. Command 5D
o. Command 5E
p. Command 6
q. Command 7
r. Command 8
s. Command 9
t. Command 10
(3) The following 4 screenshots from the project4clientcommands.sql script file must be submitted as part of the deliverables for this project. (You can include the screenshots in the top-level of your webapps folder if you’d like, just be sure to include a note that you’ve done so.)
a. Command 1
b. Command 2
c. Command 3
d. Command 4

Additional Information:
Be very careful when setting up the directory structures required for the web applications running under your server (Tomcat 10.0.16 or later). See the course notes on servlets for the exact directory structure that must be developed. Be sure that your development IDE and the JVM running under Tomcat are of the same vintage. Attend/watch Q&A sessions for more information and project details.

Important: Please name your webapp: Project4. Let the TAs know if you are doing the bonus problem by attaching a note to your WebCourses submission.





Schematic Overview of Project Components:




Suggested project development approach
1. Install Tomcat and run some of the examples from the notes to ensure that Tomcat is installed and configured properly before beginning any steps on the project itself.
2. Develop front-end .html files rootHome.html and clientHome.html. These will be later converted to .jsp files (see x below). This can be done in any editing environment of your choice. Do not specify a specific action for your form submission at this point (use a null string for the action).
3. Construct basic Project4 webapp framework inside Tomcat webapp folder. Deploy files from step 1 above and test/refine in browser of your choice.
5. Begin development of the servlets. Basic operation of the root-level servlet and the client-level servlet are the same, with only a small difference (more later). So develop the root-level servlet first and copy and paste with modifications for the client-level servlet later. As we will discuss in the Q&A sessions, the initial servlet (for testing) should do nothing more than simply return “Hi”.
6. Load servlet test files into Tomcat Project4 webapp in correct location and perform initial integration testing of complete package.
7. Further develop servlet code to complete the basic functionality of the servlets. This includes modification of the front-end interfaces to become .jsp files so that all results are returned to a single page via a targeted location and not require either a complete browser page refresh or the user to employ the browser “back” button. These techniques will be explained later in the JSP notes and also Q&A sessions.
8. Add business logic to the rool-level servlet – develop non-bonus version first.
9. Optional: implement the bonus-version of the business logic.
10. Recreate the project4 database.
11. Run through the user command scripts and generate screenshots from your application running these commands.


Some screen shots illustrating the application.


Main root-level user initial JSP page (initial configuration):













Main client-level user initial JSP page (initial configuration):





















The following several screenshots illustrate operations from the rool-level user interface.

User simply clicks the “Execute Command” button and the SQL command in the form is executed:







User makes a mistake entering an SQL command:














Current state of the suppliers table (only partial screen shown to allow viewing entire results table): Note the current status of supplier number S5. (Also note status of S1, S12, S17, S21, S22, S3, S44, and S6.)


User issues the following insert command:



Alert message when an update to the quantity field in the shipments table has caused an update of a supplier’s status in the supplier table. Note that the application will use this alert message any time the business logic is tested even if it did not trigger any updates. This means that this message would appear with different values even if no rows are updated (more examples below).










After executing update command (the previous insert), the user re-runs select * from suppliers.
Note that S5’s status has been increased by 5, but so too has S1, S12, S17, S21, S22, S3, S44, and S6. Allowing the previous insert command to affect only supplier S5’s status is handled by the bonus version of this project (see below).

Notice on page 9 (in the original suppliers table) that supplier S5 had a status of 4. After this update, the business logic has increased supplier S5’s status by 5, so it is now 9.

Notice too, that suppliers S1, S12, S17, S21, S22, S3, S44, and S6) also had their status increased by 5, since they already recorded with a shipment in which the quantity was >= 100 when the insert command was issued, even though the issued command did not affect them directly.. See bonus problem below for a
“fix”.



Example of an update command that does not trigger the business logic.







Example of an update command that triggers the business logic but results in no changes to any supplier’s status.









BONUS PROBLEM: 15 points

Instead of allowing any update/insert of a quantity >= 100 to affect any supplier with a shipment involving a quantity >= 100, adjust the business logic portion of your application so that an insert/update of a quantity greater than 100, causes a change to the status of only those suppliers directly affected by the update. For example, using the case shown above, when inserting the row (S5, P6, J7, 400) into the shipments table, only the status of supplier S5 should be increased by 5 (see screen shot below). However, an update such as: UPDATE shipments SET quantity = quantity + 50 WHERE pnum = “P3”, would increase by 5 the status of every supplier who ships part P3 in a quantity >= 100 after the update has been issued.

NOTE: If you elect to do the bonus problem, submit only this version of your application. Do not also submit the non-bonus problem version. Let the TAs know if you’ve elected to do the bonus problem or not.

I will provide many hints for the bonus problem during the Q&A sessions for this project.

With the correct business logic (the bonus version) in place, issue the original insert command as above (on page 10), we now get the correct effect for our update command.