Title: Project 5: Developing A Three-Tier Distributed Web-Based Application Using PHP and Apache
Objectives: To incorporate many of the techniques youve learned this semester into a distributed web-based application which uses PHP running on an Apache server to access and maintain a persistent MySQL database.
Description: In this project you will utilize a suppliers/parts/jobs/shipments database similar to the one that was developed for Project 4 (well use a new script and create a different version of the database for this project) and access this database through a client front-end. The first-tier (clientlevel front-end) of your application will be a simple HTML page that allows the client to enter SQL commands into a window and submit them to the database for processing. Note that the client frontend must provide for the login of the user so that any of our clients (see below) can login to the database to execute operations. The client front-end will provide the user the option of querying the database or updating the database (see Projects 3, and 4). The client front-end will run on any webbased browser that you would like to use. The second-tier will use PHP technology to extract values from the client-side form and submit the SQL command to the MySQL database server for processing. The results returned by the MySQL database will be formatted by PHP into an HTML document to be displayed in the users browser. Well implement the same business logic in this assignment that we used in Project 4 and increment the status of any supplier by 5 anytime any shipment in which the quantity is greater than or equal to 100 is entered into the database through insertion or update. 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. (You can also employ the business logic used in the bonus problem of Project 4 if you choose for a 10 point bonus for this project.) The business logic of the second tier will reside on the Apache web-application server (server-side application in PHP). The third-tier (back-end) is the persistent MySQL database and is under control of the MySQL server.
We will assume that the front-end will allow any client to connect to the database, so like Project 3, you must provide the user the ability to enter login credentials on the initial screen (see screen shots below). Once a user has logged into the system, your main page should show the name of the currently logged in user and provide them the ability to logout (disconnect). The initial screen (application home page, should explain a bit about the application and explain the user login procedure. Once a user has logged into the system, the main application page should appear which provides the user the ability to issue SQL commands against the database. While your application screens do not need to look exactly like mine, they must provide the same functionality including the ability to submit both queries and updates, clear result windows, and login and logout of the system.
References: Notes: Lecture Notes for MySQL Documentation for MySQL available at: http://www.mysql.com. Lecture Notes for PHP. Projects 3, and 4.
Restrictions: Your source file shall begin with comments containing the following information: /* Name: Course: CNT 4714 Fall 2015 Assignment title: A Three-Tier Distributed Web-Based Application Using PHP and Apache Due Date: November 29, 2015 */
Input Specification: The database for this project is created by running the script titled: prog5dbscript, which is available on WebCourses. Note that this is a new database and not the same one that was developed in project 4. The script creates a database named project5. All other input comes from the front-end client form submitted to the application server and processed by PHP entered as either queries or updates to this database.
The first step in this assignment is to login as the root user and execute/run the script to create and populate the backend database. This script is available on the course assignment page and is named prog5dbscript.sql. This script creates a database named project5. You can use the MySQL Workbench for this step, or the command line whichever you prefer.
The second step is to create authorizations for two client users (in addition to the root user) named client1 and client2. By default your root user has all permissions on the project5 database. Use either SQL Grant statements from the command line or the MySQL Workbench (see separate document for details on how to accomplish this task) to check and set permissions for the clients as follows:
Register the new user named client1 (assign them an easy to remember password ignore the MySQL warning on weak password setting) and assign to this user only selection and insertion privileges on the project5 schema. Next, register the new user named client2 and assign to this user only selection and update privileges on the project5 schema.
Output Specification: All output is generated by the MySQL server, formatted by the PHP serverside application and should be processed into an HTML table or text (error pages) and returned by PHP to the users browser as a text/html page. Output will be generated for three different users, the root user, client1, and client2.