The Storage And Query Manager Layer

Published Date: 02 Nov 2017

1.1 Introduction

A pizzeria specialized in custom made pizzas is currently taking orders by phone. The current system where the customer calls the pizzeria takes time of employees to answer the phone and is more work consuming than necessary. They want to allow customers to customize and order their pizzas online. The pizzeria also aims to increase the sales, due to the easy to use order online website. The system will give the employees more time to "work" rather then to accept orders by phone, also the

potential increase in customers are enough reason for the pizzeria to accept the change (website where customers can order their customized pizzas).

Goal

Our goal is to deliver a database with a user interface (website) where customers can select various ingredients for their own pizza and place their order. The order will be sent to the "kitchen" where the pizza will be made. The focus is to create an "easy to use" website, which will allow a first time customer to complete their order with ease.

Existing solutions

There are many different web based ordering systems. Those systems do not offer all the functionality that is needed for a pizzeria. Ordering systems usually allow people to add products and separate them in different categories and subcategories.

Pizzeria sells pizzas, and most of them also offer clients to customize their own pizzas by picking their own ingredients. At the same time they offer other products that do not need customization like the side orders. That does not fit into the general category-subcategory differentiation.

Our solution

Our solution is to make an ordering system that separates ordering pizzas from ordering the side dishes (non-pizza products) in an intuitive way.

The ordering system provides the user with three tabs: one for customizing pizzas, one for side orders, and one for delivery details. Customers can switch to any tab any time. Hence the system will decrease workload of the employees and benefit the pizzeria due to the database / information system. Information will be stored in the system and can be viewed at any time. The system will be able to guide a user through the website and make then complete their pizza order. When they are done with filling in all information regarding their order they can complete the order to send it to the pizzeria.

Technical constraints

The constraints of this case are the network connectivity which is required and a website located on the internet. Customers will have to be able to customize their pizzas. However there is a limit to the customization of the pizza, technical constraints, functions of the database and user interface. The system should make use of open-source software, since the pizzeria does not want an expensive system.

1.2 Use Cases

Three main use cases are identified: ordering, order processing and administration. The following paragraphs describe each use case in more detail.

Ordering

The first use case is ordering. To order, a potential customer must first know what he can choose from ("What’s on the menu?"). For ordering custom pizzas a customer will have to know the ingredients he can choose from and how choosing ingredients will influence the total price. Customers pick ingredients for their pizza and sometimes they want a double portion of specific ingredients. When a customer is done choosing his pizza, he may want to order several pizzas of the same configuration. A customer may also want to order non-pizza side orders, such as drinks and salads. Also the customer will have to provide his name, address and telephone number. A customer can also pick another delivery date and time if they wish for the order to be delivered on a later date and time. Customers can order up to two weeks in advance.

Order processing

When an order has been confirmed by a customer order processing begins. Throughout the order processing the order status will be updated and the customer will be notified. New orders will show up in a list for the employees in the kitchen. They need to state which products are ordered and which ingredients are chosen for the custom pizzas. The kitchen employees have to look at the order and decide if they can prepare the order or not. When the decision is made, the order status will change and the customer will be notified. After preparing the order the order status will be updated and the delivery employees will take over. They will look up the name and address, and deliver the order. The customer will also be notified when the order is prepared and ready to be delivered. When delivery has been completed or failed, the order status will be changed once more.

Administration

Custom pizzas are made by selecting several ingredients, the list of available ingredients and their prices are administrated by an employee, for instance the manager. Administration includes added, editing and deleting ingredients. Besides the administration of ingredients, the non-pizza side orders must also be administrated. Administrators also need to have logs of previous orders.

1.3 System specification

Functionality specification

The functionality specification is closely related to the use cases. The three main functions are ordering, order processing and administration. A description of each of the functionality will follow. It includes the output of the system and the input it expects from the users.

Ordering involves customizing pizzas, selecting products and entering customer information. The system will have to provide the user with data about the ingredients and the non-pizza products such as name and price. Users will provide an order which

consists of custom pizzas by selecting ingredients, non-pizza products, and customer information like name, address and telephone number. For every custom pizza and non-pizza product users can also specify an amount if they wish to order more than one of them.

Order processing is involved in preparing and delivering orders. The system needs to provide the kitchen employees with a list of pending orders. Of each order the ordered products and pizzas must be shown, together with the amount of each product. For each custom pizza the selected ingredients must also be shown. The kitchen employees will first view the order and decide if they are going to process the order or not. The decision will be entered by changing the status of the order to the respective status. An e-mail will be sent to customer to keep him informed of the ordering status. After the order is prepared, the order will be passed to the delivery employees who will use the system to retrieve the customer details.. The status of the order will change to "being delivered" and an new e-mail will be sent to the customer. When the order is delivered, the delivery employee can mark the order as "delivered", or in case he failed to deliver it he will make it as "failed to deliver".

Administration includes adding, editing and deleting available ingredients, non-pizza products and employees and viewing previous orders. The system will be able to show a list of ingredients and a list of products. For each ingredient and non-pizza product a name, price and picture can be specified. The system also needs a function to show previous orders.

Technical specification

The system is made up of three layers. At the top there is the GUI (Graphical User Interface) layer, the middle layer is the storage and query manager, the bottom layer is the underlying database.

GUI layer

The GUI layer allows users to access the system. All the functionalities of the system must be available through the GUI. There are two separate GUI’s. One is for the customers to create orders and one is for employees for processing orders and administration purposes. With scripts, user input will be used to invoke queries from the storage and query manager layer to provide the user with various pages. The GUI should prevent input errors and in case of errors that could not be prevented, provide clear error messages.

Nowadays people have usernames and passwords for a lot of websites and services. It is not practical to have users to remember information for a pizzeria. It is much easier for customers to type in their name and address than to have to remember the username and password. Therefore, customers do not have accounts to log on to. However customer information will be stored into the system to allow employees view previous orders by customers.

The GUI for employees is on a separate URL. Users need a username and a password to gain access to the system. The administrator has access to add users to the system to give them access.

Storage and query manager layer

The storage and query manager layer is responsible for information storage, retrieval, authorizations and error checking. This layer allows selecting, adding, updating, deleting entities and relations in the database by using different queries. Some

instructions are limited to users with authorization, such as deleting data from the database which should only be allowed by the administrator.

Database layer

The database layer contains all the data of entities and their relationships.

1.4 Requirements

This section describes different types of users of the system in 3.3.1. In the following paragraph 3.3.2, the functional requirements for each type of user are listed. 3.3.3 Lists the non-functional requirements of the system.

Users

Three types of users should be able to use the system: customer, employee and administrator. Customers are users who visit the website and can create orders by customizing pizzas, selecting products and entering customer details. Employees are the group of users that work with the ordering system on a daily basis. Employees will have their own accounts to log on to. They are the ones responsible for processing orders. Since Customer users do not need a log in, employees who process telephone orders can use the system as a Customer and enter the telephone order directly into the system as they take the order from the calling customer.

The administrator, or super user, has the ultimate control of the system, he can add, change or delete ingredients and products, as well as add, change, or delete employee accounts.

Functional requirements

1 Customers

The user must be able to create a new order.

The user must be able to customize a pizza by:

The user must be able to view a list of available ingredients.

The user must be able to add an ingredient to a custom pizza

The user must be able to remove an ingredient from a custom pizza

The user must be able to get graphical feedback from selecting ingredients. A photo of a pizza will contain the newly selected ingredient combined with previous selected ingredients.

The user must be able to add a custom pizza to an order.

The user must be able to view a list of available non-pizza products.

The user must be able to add non-pizza products to an order.

The user must be able to see a list of custom pizzas and non-pizza products that are added to the order.

The user must be able to change the amount of a custom pizza.

The user must be able to change the amount of a non-pizza product.

The user must be able to delete a custom pizza from an order.

The user must be able to delete a non-pizza product from an order.

The user must be able to see the total price of an order.

The user must be able to choose a delivery date and time that is up to two weeks ahead.

The user must be able to add the name and address of the customer.

The user must be able to clear the current order to start a new one.

1.15 The user must be able to confirm the order.

2 Employees

The employee must be able to log in and out.

The employee must be able to view a list of available orders and their custom pizzas.

The employee must be able to mark orders as "prepared".

The employee must be able to mark order as "delivered"

The employee must be able to mark order as "failure to deliver"

Only users with respective rights (employee) must be able to use all these "Employees" features.

3 Administrators

The administrator must be able to log in and out.

The administrator must be able to add/delete/edit orders.

The administrator must be able to add/delete/edit ingredients.

The administrator must be able to add/delete/edit non-pizza products.

The administrator must be able to add/delete/edit other users.

The administrator must be able to view an order log.

Only users with respective rights (administrators) must be able to use all these "Administrators" features.

Non-functional requirements

As an operational requirement, the system will run as a database with a website as user interface. As performance requirement the system must be accessible 24 hours a day, seven days a week. Due to the nature of the system as an ordering website, the system must have a low response time, preferably shorter than second, with a maximum of five seconds. The exception is viewing order logs which could have a higher response time (of seconds) as the log increases in size over time.

Due to the low complexity of the system, no problems with response time are expected. Customers who visit the website to order will get a sessionID for their visit, which is used to identify them while using the system. For every action they take, a timestamp is stored. From time to time a service on the server will scan sessionID’s and timestamps. SessionID’s which have not been active for more than three hours will be deleted along with the corresponding ordering information.

Chapter 2: Approach

Our solution to make the pizzeria more efficient will be a website to create orders online, which should be come with a good user interface. Also it should be "fast" as in the customer will be able to order his pizza in a matter of minutes. We stress the importance of "easy to use" user interface here because some customers will leave if the website is too complicated. In the case of a website where a user goes to for personal interest a less user friendly interface is not a big problem since he will try and try to get the information due to his interest in the site.

To create the website we will first analyze the current processes of the pizzeria. This will be recorded in the use cases. From the use cases we derive the system specification which describes what the system should be able to do. It will contain the needed functionalities and the technical specification. Then we will analyze the different users and list their functional requirements and non-functional requirements for the system in detail.

With all the specification and requirements we will create an information model using an E-R diagram to describe the entities and the relations between the entities. From the E-R diagram we will derive a relational schema which will be used for the final database design. The E-R diagram will be made in Visio.

GUI prototypes will follow using screen mockups in the form of images. These mockups will be created using Photoshop.

To support implementation, a list of functions will be created describing what actions these functions need to perform. The list will also include the SQL statements that are needed to perform these actions.

During implementation, test scripts will be created. After implementation is complete, the test scripts will be run by a few people to detect implementation errors. The project will end when the implementation errors are fixed.

2.1 Technical Approach

During the process of selecting the tools and methods to be used during the process, we took a look at our current expertise and the requirements of the project.

As a programming paradigm, ASP.NET would have been the best choice at first, but the fact that this is not an open source environment, forced us to start looking for a different approach. Because the database was going to be a mysql driven database, php showed to be the right choice. Php has an excellent integration with mysql. Because php is an open source system, there are a huge amount of hosts available.

For managing the mysql database, phpmyadmin was used. This was the logical choice because it is free, and it integrates so well. Creating the code, the program Macromedia Dreamweaver was used. This is a multipurpose xhtml editor, supporting a lot of different syntax languages. We used this program because we already had experience using it.

The system is divided into two main parts. The smaller administration area, and the larger customer area. Both parts have their own folders, for example: localhost/pizza is the folder containing the customer site, and localhost/pizza/admin is the folder containing the admin page.

Both parts have been designed around the index.php page. This page is dynamically being changed based on values in the POST and the html Query String. Different types of pages are being included when needed. We divide the include pages by, library pages and include pages. Library pages are pages which only execute code, but do not write html code. Include pages execute code and write html.

Finally, some of our limited graphic design has been done using Macromedia Fireworks. This because we already had good experience using this tool combined with Macromedia Dreamweaver.

To sum it up:

Database Managing System: MySQL

Tool to access the database: phpmyadmin

Programming paradigm: PHP

Tool used to edit php: Macromedia Dreamweaver

Tool used for graphics design: Macromedia Fireworks

Chapter 3: Results

Contains the information architecture in the form of ERD and relational schema. 3.5 includes the GUI specification and finally 3.6 contains a list of functions with implementation steps.

3.1 Architecture

Information Architecture

This paragraph describes the information architecture for the database system. The data model consists of an Entity Relationship Diagram (ERD) and relational schemas

ERD

Diagram 1: ERD

We chose for a separate Non-Pizza and Pizza table, because it is far more clear to program. If we would combine both the tables, we would get confusing situations,

because Pizza records are being created by the customers, and non-pizza records are being created by the administrators. The attributes might be very similar but a pizza record is always connected to a bunch of ingredient records, where this is not the case for the non-pizza records. The price of a pizza is based on the individual ingredient prices. Upon completing the pizza, the price is being inserted in the Pizza table.

Diagram 1 shows the Entity-Relationship Diagram (ERD). In this paragraph every relation between entities is described.

Customer --- Places --- Order

A customer can place several orders; the customer entity holds attributes describing properties concerning the customer. The order entity holds attributes such as OrderStatus to track the status of the order (new/prepared/delivered/failed to deliver).

Employee --- finalizes --- Order

An employee is responsible for the multiple or one order. By tracking this, it is possible to look up which employee finalized which order.

Employee --- Adds New --- Non-Pizza

Through this relationship it is possible to track which non-pizza product has been added to the database by which employee.

Employee --- Adds New --- Ingredients

Through this relationship we track which ingredient has been added by which employee.

Pizza --- PizzaIngredients --- Ingredients

This relation describes which ingredients belong to the pizza creations (created by customers during their shopping process). A pizza can have 1 or multiple ingredients. It is impossible for a pizza to have zero ingredients.

Order --- OrderNonPizza --- Non-Pizza

This tracks which non-Pizza products have been selected by the customer during the shopping process. An Order can have 1, multiple or none Non-Pizza products.

Order --- Has --- Pizza

This relation tracks the pizza’s which belong to an order created by a customer.

Relational schema

Diagram 2 shows the relational schemas of the database. It is derived from the ERD.

Diagram 2: Relational schemas

As visible in the Relational schema, a new attribute has seen the light. This attribute is named Sessionid. The sessionid is a random id which is assigned to a user when he enters the website, during the visit he will have the same sessionid. This enables, multiple users to place orders at the same time. If we would not have used a sessionid, the users could see and change the process of other customers, causing a lot of inconvenience.

System Architecture

As one of the constraints of the project, the database system will run on Open Source software. A powerful solution for a database system such as the pizza ordering website is LAMP. LAMP is a software bundle which enables running dynamic websites with an underlying database. This acronym stands for Linux, Apache, MySQL and PHP. A server will run on Linux as the OS. Apache is the web server. The database will run on MySQL and PHP serves as the programming language. This combination is cheap and provides all the functionality which is needed. This combination is also offered by a large portion of the hosting companies, making it easy to find a place to put the system on.

3.2 GUI Specification

The GUI is web-based and will use web-based technologies such as HTML, JavaScript and PHP in combination with images to display pages. The following paragraphs will describe several pages from the system.

Screenshot 1: Customize Pizza screen

Screenshot 1 shows the first page for customers. In the middle of the screen are all the available toppings for custom pizzas. Next to each ingredient are two checkboxes. This allows users to select zero, one or double portions of the ingredient which corresponds to how pizzas are generally ordered. A big pizza is shown on the left. The image will be updated every time the user selects or removes an ingredient. The image will show the selected ingredients on the pizza and it looks like a photo of an actual pizza. The realism will increase the appetite of the user when he is creating his order. The total price will update every time an ingredient is selected. There is a button that allows the user to create a new pizza.

On the right there is a list of currently ordered pizzas and products. For every item the user can adjust the amount by clicking on the – or + buttons and users can also delete the item from the order by clicking on the red cross.

On top of the screen there is a tab-like interface. The first tab is "Customize Pizza", the middle tab is "Side Orders" for drinks, salads and deserts, the last tab is "Delivery Information" where delivery information such as name, address and time is entered. A tabbed interface allows users to browse through several in the order they want to. An example: a user adds pizzas, picks side orders, enters customer information and then decides he wants to order another pizza. He can do it by clicking on the

Customiza Pizza tab and add a new pizza. In a wizzard interface the user would need to click Next and Back buttons to find the page he was looking for.

Another alternative was to have a menu of three hyperlinks under each other. A tabbed interface is the better choice, with only three tabs it shows clearly that there are three different pages corresponding to the three steps of ordering.

Screenshot 2: Side Order

Screenshot 2 shows the Side Order tab. This page shows non-pizza products ordered by category. Clicking on the shopping cart will add the product to the order list. Just like the previous page, tabs are shown on top of the page and the order list is shown on the right.

Screenshot 3: Delivery Information

Screenshot 3 shows the delivery information tab. Customers can fill in their first name, last name, street name, house number, city and phone number. Optionally if they want the order to be delivered on another date or time, they can specify it too.

On all pages, under the order list there is a clearly visible button available to confirm the order. However, to confirm an order, there must be products on the order list and customer information must be filled in.

When "Confirm Order" is pressed and there are no products ordered, a text message will notify the user that he has to order something first.

When "Confirm Order" is pressed and the customer information page is not filled in, the system will show the Delivery Info page for users to fill in.

When "Confirm Order" is pressed and all the information is entered, a confirmation page will show the delivery information and the order details. The user can click on "OK" to confirm the order or "Cancel" to continue ordering.

3.3 Function implementation queries

This paragraph contains a table which list all the needed functions with steps that the scripts needs to take and what DB steps are required. Another table contains the actual DB queries.