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

Objectives: To incorporate many of the techniques you$$ve learned 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, named

project$4($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

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 MySQL DBMS will enforce referential integrity via foreign key constraints. The primary key for the

shipments table is a composite key consisting of three foreign keys $($the primary keys in the suppliers, parts,

and jobs tables$).$ Referential integrity means that a shipment record cannot exist unless it links back $($via

referential integrity$)$ to existing entities on all foreign key values. Thus, a shipment record cannot exist

unless the referenced snum, pnum, and jnum already exist in their respective tables.

The first$-$tier $($user$-$level front$-$end$)$ of your web$-$application will consist of an HTML landing page which

is used to authenticate end users. The authentication of users is handled via a servlet in the webapp that

validates the user entered credentials with those in another database named credentialsDB maintained on

the MySQL DB server. $($The credentialsDB is created and populated using the

project$4$UserCredentialsScript.sql$.).$ This servlet is running as a system$-$level application

with root user privileges $($more later$).$ The credentialsDB contains a single table named usercredentials

of usernames and their associated passwords. Validation consists of matching both the user entered name

and password against the values stored in the usercredentials table. If the user entered credentials do not

match an entry $($row$)$ in the usercredentials table, the user will be denied access to the system. If a match

is found, the authenticated the user will be automatically redirected to one of four different JSP pages. One

which handles root$-$level user clients and one which handles non$-$root$-$level clients, that allow the users to

enter arbitrary SQL commands into a window $($i$.$e$.$ a form$)$ and submit them to a server application for

processing. The third JSP page will be consist of dedicated data entry forms for entering new records into

the four tables in the database. This third page will only be used by special data entry users who do not

directly enter SQL commands into the interface, but only enter data into specific tables in the database via

parameterized commands. Finally, a fourth JSP page will be reserved for accountant$-$level users, who,

similar to data$-$entry users, do not enter SQL commands directly, but rather execute remote stored

procedures $($RPCs$)$ that reside on the database server.

CNT $4714$ Project Four $$ Spring $2024$

Page $2$

The front$-$ends of all four user applications will utilize JSP technology. The front$-$ends for the root$-$level

and client$-$level users, 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$).$ These two front$-$ends 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 third front$-$

end will be utilized only by na$$ve data$-$entry users by filling in a form. The data$-$entry users will not enter

SQL commands to accomplish their tasks. Rather, their web$-$application will use the preparedStatement

interface and extract the parameters from their forms and issue the SQL command in the background. The

data$-$entry level front end will have two buttons on each form, one for entering the data and one for clearing

data and results. Finally, the fourth front$-$end, for accountant$-$level users, will consist of a selection of

$$reports$$ that can be run based on their selection. The accountant$-$level user will simply select an option

from a list of possible $$