There's one similar on the site but this one contains 3 JSP files and 3 servlets instead of 2
Microsoft Word - project3.docx Page 1 Title: “Project Three: Developing A Three-Tier Distributed Web-Based Application” Points: 100 points (bonus problem potentially adds 15 points – see page 14.) Due Date: Thursday August 4, 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 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 3) 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 keys. 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 be three different JSP pages, one of which handles root-level user clients, another which handles non-root-level clients, 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 a dedicated data entry form for entering new shipment records into the shipments table. The front-ends of all three applications (and only the front-end) 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 form and issue the SQL command in the background. CNT 4714 – Project Three – Summer 2022 Page 2 The front-ends will run on any web-based browser that you would like to use. The applications will connect to the backend database via properties files dependent on which front-end page is utilized. This connection must be handled using properties read from a properties file. You will have three different properties files, one for the root-level users, one for the client-level users (all the same as project 2 except for the different database), and one for the data entry-level users. The second-tier servlets, are in charge of handling the SQL command interface for the users. The root- level user app (and the data entry level app – see below), 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 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 data entry-level servlet will provide the user a template (form) for the shipments table data to be entered and will execute the necessary updating command by extracting the parameters from the form and issuing a prepared statement update to the database. You may want to refer to the JDBC notes from Module 3 to refresh your memory of how the preparedStatement() interface differs from the normal Statement() interface. 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 – Summer 2022 – Project Three Assignment title: A Three-Tier Distributed Web-Based Application Date: August 4, 2022 */ Special Note: Due to end of semester time constraints this will be a hard deadline. Page 3 Input Specification: The suppliers/part/jobs/shipments database (named project3) that is created/populated by the script project3dbscript.sql, is the back-end to this application. All other input comes from the front-end user submitted to the application server based servlet entered as either queries or updates to this database. There are three sets of commands that you are to execute against this database included in the project3rootcommands.sql, project3clientcommands.sql, and project3dataentrycommands.sql available on WebCourses under Project 3. As with Project 2, your client-level user will have only select privileges on the project3 database. The data entry-level user will have only select, insert and update privileges on the project3 database. Also, as with Project 2, 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 project3dbscript.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 2, run all commands in sequence from the project3rootcommands.sql script file (total of 20 different commands), followed immediately by all commands in sequence from the project3clientcommands.sql script file (total of 4 different commands), followed immediately by all commands in sequence from the project3dataentrycommands.sql script file. Deliverables: (1) You should submit your entire Project3 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 August xx, 2022. (2) The following 20 screen shots from the project3rootcommands.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 Page 4 p. Command 6 q. Command 7 r. Command 8 s. Command 9 t. Command 10 (3) The following 4 screenshots from the project3clientcommands.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