Microsoft Word - project4.docx
Page 1
Title: “Project Four: Developing A Three-Tier Distributed Web-Based Application”
Points: 100 points (bonus problem potentially adds 15 points – see page 14.)
Due Date: Sunday April 24, 2022 by 11:59 pm (WebCourses time)
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
containe
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. Front-
end access to this database by end users will occur through a single page displayed in the client’s web
owser. 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 root-
level 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 front-
end) 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 cu
ently in the input window, a “Reset Form” button that simply clears any content
cu
ently in the form input area, and a “Clear Results” button that will erase the cu
ently displayed data
(user optional). The front-end will run on any web-based
owser 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
ackend 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 root-
level 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 >= XXXXXXXXXXSee 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
CNT 4714 – Project Four – Spring 2022
Page 2
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 client-level servlet will handle the SQL command interface, just as the root-level servlet does,
however, due to the restrictions on the client-level privileges, no business-logic will be implemented in
this application.
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:
Course: CNT 4714 – Spring 2022 – Project Four
Assignment title: A Three-Tier Distributed Web-Based Application
Date: April 24, 2022
*/
Special Note: Due to end of semester time constraints this will be a hard deadline.
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
owser
as a text/html page presented to the user. All MySQL-side e
ors 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).
Page 3
Deliverables:
(1) You should submit your entire Project4 webapp folder from Tomcat for this program. If you
submit the entire folder, then all of the files necessary to execute your web application will be
included with the directory structure intact. Submit this via WebCourses no later than 11:59pm
Sunday April 24, 2022.
(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
. 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
. 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
. 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 XXXXXXXXXXor 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
y attaching a note to your WebCourses submission.
Page 4
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.