INFO20003 Assignment 3 solution




5/5 - (3 votes)

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.
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
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