Assignment 3 Solution

In this assignment you will be developing and implementing web­based software with a database back­end, as outlined in the case study below.

 

Your assignment solution must be demonstrated by you during your week 12 workshop. Tutors will also have questions about your solution and code. You may be asked to change your code in order to demonstrate your knowledge! You will be marked on the completion of the task (5 marks) and your ability to explain your solution (5 marks).

 

Your software is to support the running of the business described in the case study.

 

You must implement your solution using PHP, MySQL, HTML and CSS. Your task is to write working code using these basic tools. You should use SQL transactions where appropriate.

 

Submission

 

You need to submit a zip file of all your code. Your code should contain a file called “read­me.txt” which contains the URL of each part of your functioning web application.

 

For this Assignment you must ensure that you thoroughly test your code in the University environment! This must be done BEFORE submission of your work. This is especially important for those of you who coded the application off­site, or used your own personal MySQL servers. You should check to make sure your PHP code works on the Database Systems web server and your SQL works on the subject database server before you make your submission.

 

We will ONLY accept work that runs within the university servers that have been provided and viewed from web browsers on engineering lab computers.

 

Your running database needs to contain some sample data in order for you to demonstrate your working application. Your sample data needs to include:

 

1.    At least 5 spatulas that are currently in stock. These must be of varying names, types, sizes, colours and prices.

 

2.    At least 5 spatulas that are currently out of stock. These must also be of varying names, types, sizes, colours and prices.

 

3.    At least 5 orders. The first order should be for one particular spatula, the second order for two different spatulas, the third for three and so on. You must be recorded as the responsible staff member for each of these orders.

 

Case Study

 

Spatula Town is a company that specialises in the selling of spatulas. Spatula Town sells spatulas for use in cooking, drug preparation and handling, painting and plastering. Spatula Town stocks thousands of spatulas for customers to choose from, in every shape, size and colour. They cater to all your spatula needs! Now in 7 locations nationwide!

 

The CEO, Mr. Greenblom, has recently purchased the company and would like to have a comprehensive online store. However, Mr. Greenblom acknowledges the complexity of the task and as such has asked you to make two prototypes as a first step towards his spectacular vision. The two prototypes should not be susceptible to SQL injections.

 

 

Prototype Web Pages Part 1

 

The first prototype is to be used on a trial basis by staff taking orders from customers over the phone. As such, the page needs to display all items that are currently in­stock. A textbox next to each product should allow a staff member to enter a quantity to be ordered. The default of this textbox should be zero.

 

There needs to be a single text area where a staff member can enter all customer details for an order. The staff member will also need to input their name so it is clear which staff member is responsible for creating the order. The page could look something like the following:



The staff member should be able to submit the entire order at once. The staff member should then be taken to a new web page which tells them if the order has been successfully placed. If the order was not successfully placed, the system should display the reason/s why. For example, if the quantity in stock changes because someone else placed an order for the same items at the exact same time a message should tell the user what items are out of stock. All error messages should be understandable by non­Software Engineers.

 

Prototype Web Pages Part 2

 

The second prototype is for browsing products. A search box should allow a user to search by any combination of:

 

1.    Spatula name.

 

2.    Type ('Food', 'Drugs', 'Paints', 'Plaster').

 

3.    Size (measured in cm).

 

4.    Colour.

 

5.    Price.

 

It should be apparent to the user which user interface elements correspond to which attributes of the spatulas. Your user interface might look something like this:



 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Submitting a search should take users to a new results page where they can see only the spatula names matching their search. Each spatula name must a be a html link to a new page that displays all the details of the item that the user clicked on. These details include the quantity currently in­stock of the item.

 

The prototype described in this section will be used as the basis for future versions that customers will use to browse products.

 

Data Model

 

Mr. Greenblom is an avid database designer and has created the following ER diagram in MySQL Workbench. You are to implement this design rather than re­designing the database yourself. It would hurt Mr. Greenblom’s feelings too much if you changed the design for the prototypes!