Objectives: To incorporate many of the techniques youve learned so far this semester into a distributed three-tier web-based application which uses servlets running on a Tomcat 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 the course assignment page) as the back-end database. Frontend access to this database by the client will occur through a simple page displayed in the clients web browser. The schema of this 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 (client-level front-end) of your application will be a simple HTML page that allows the client to enter SQL commands into a window (i.e. a form) and submit them to the server application for processing. The front-end (and only the front-end) will utilize JSP technology. The client frontend will provide the user a simple form in which they will enter a SQL command (any DML, DDL, or DCL command could be entered by the user). The front-end will provide only two buttons for the user, an Execute button that will cause the execution of the SQL command they enter, and a Reset button that simply clears any content in the form input area. The client front-end will run on any webbased browser that you would like to use. You can elect to have a default query or not, it is entirely your decision.
The second-tier servlet, in addition to handling the SQL command interface will also implement the 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 suppliers shipment is not directly affected by the update, their status will be affected if they have any shipment with quantity >= 100. (See page 8 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. All you need to do with the database is run 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.
Input Specification: The suppliers/part/jobs/shipments database 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. The set of commands that you are to execute against this database are included in the project4commands.sql file available on the course homework page. I do not expect your front-end to execute the script. Youll need to execute the commands in this script one at a time in your application (copy and paste!). Ive put them into a script file for convenience and so that you can run the script in the MySQL Workbench if youd like to compare/see the result sets for each user command.
Output Specification: All output is generated by the servlet and should appear in the users browser as a text/html page presented to the user. IMPORTANT: Be sure to re-run the project4 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.