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

Points: $100$ points $($bonus problem potentially adds $15$ points $$ see page $28.)$

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 database 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 a credentials file $($a text file$)$ maintained on the server$-$

side. The credentials file contains an unknown number of lines where each line contains a username and

password separated by a comma $($csv file format$).$ Validation consists of matching both the user entered

name and password against the values in the credentials file. If the entered credentials do not match an

credentials in the credentials file, 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 fourth 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.

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

CNT $4714$ Project Four $$ Fall $2023$

Question help: Develop the servlet file connecting to mysql database, execute query if "select" convert data to an html table. forward it to a jsp file that will display table when execute button is clicked.

If query is anything else, send the command to mysql and let the DBMS handle it$.$