Web-based application development consists of numerous components and produces what is therefore known as a multi-tiered application. A multi-tiered application is best thought of as consisting of four separate tiers as follows:

Client Tier

This consists of the web browser. The web browser is typically installed on a local machine (this can be a desktop computer, a mobile device, a laptop etc. – anything on which you can install a browser). The browser is programmed with an ability to request information from a web server, with two-way communication implying use of HTTP, the foundational protocol for the worldwide-web (WWW) where data is transferred within its physical substrate, the Internet. Browsers have the ability to execute .html mark-up and render the contents visually in the browser for human ‘consumption’.

Web Tier

This consists of the HTTP Server. The HTTP server is typically a separate machine which will contain documents and data relating to the static content of a website. Static content refers to content such as .html files, images (that are called/tagged from with the html content) and cascading style sheets (.CSS), files that describe the appearance of the Website in questions. This content is often referred to as static. The Web Server may receive a message from the browser to run an executable script, which is then relayed to the business tier …

Business Tier

This includes an application server. It will receive a message to execute a script/program from the Web Server, upon which it does so and returns a result from the process. During the execution of the script, the program might require access to data. If so, then this part of the process is communicated to a Data Server, in the form of a query.

Data Tier

This includes the database server and the database itself. As a consequence of being queried, the Database server will return a response, via communication with the database store, back to the business tier.

The positioning of all of these components is therefore more complicated than the mere user-interface of the browser. The process as a whole is summarised in Figure 1.

Figure 1: The browser and the server-side stack.

The web tier, the business tier and the data tier are known as the server-side stack or server stack. In Table 1 we summarise the technologies typically associated with these tiers, depending on the operating system being used for development – i.e., windows or the Unix based OS’ Mac OS and Linux.

Tier / Server	Relevant technologies
Web	Apache (Windows, Unix) Internet Information Services (Windows)
Business / Application	PHP (Windows, Unix) ASP.net (Windows)
Data	MySQL (Windows, Unix) Oracle (Windows, Unix) SQL Server (Windows)

Table 1: The server stack

In enterprise development (industrial software settings), different companies will use different development technologies. Nevertheless, the general configuration outlines above will be relevant to many of them running relational databases.

A - Install Apache (Web Server)

1. Install V C++ Redistributable

2. Download Apache and extract to an apache home folder of your choice. For example:

<C:\apache>

3. Depending on the location of Apache and the folder name chosen in step 2., configure the httpd.conf file to connect to localhost.

4. Start the server. Once done, the server is now in a state in which it can communicate with a web browser

5. Browser interface test: type localhost into a browser. If a request is successfully sent to and received from, the server, then “It works!” is rendered by you browser.

6. Know your htdocs document root folder. This is the default place for the web content:

CSSS, HTML, Images, etc.

The folder is here, assuming the above paths:

<C:\apache\htdocs>

B - Install (PHP)

1. Download PHP to your system

2. Extract the contents to a php home folder of your choice. For example:

<C:\php>

3. Configure your Apache server to ‘be aware’ of PHP. Relevant changes are made to the following configuration file:

<C:\apache\conf\httpd.conf>

Test: place a basic php script into the htdocs root folder of Apache server. For example:

<C:\apache\htdocs\phpinfo.php>

Ensure that the server is running, then call the script from the browser by typing, for example:

<http://localhost/phpinfo.php>

Into the browser.

Information should be rendered in the browser.

At this point you have simulated the browser -web server - communication in Figure 1, as shown.

Install (MySQL)

1. Download and install MySQL community edition, the full size package

2. Simply launch the installer and follow the instructions from Lynda.com

i. Keep the default port 3306

ii. Set-up a MySQL root account and password

3. Launch MySQL Workbench

i. Connect to the localhost root account instance.

For the following examples we have created two sets of data, stored as .csv files. The structure of the files is fairly well organised, and reflect what you might want to see from a well organised set of data, contained in files stored on a disk. These files relate to Employee data and Department data:

Header Files:

<Department_header.csv>.

Contains the ‘header’ information, the fields names, for the set of columns contained in Department_data.csv. We will draw on the names of these fields when we write a script to create the Department table.

<Employee_header.csv>

Contains the ‘header’ information, the fields names, for the set of columns contained in Employee_data.csv. We will draw on the names of these fields when we write a script to create the Employee table.

Data files:

<Department_data.csv>

Contains the actual data of interest with respect to the Department table. We will read this data into our Department table, after it has been created, again using a script.

<Employee_data.csv>

Contains the data of interest with respect to the Employee table. We will read this data into our Employee table, after it has been created, likewise using a script.

Figure 2: .csv files 'schemas' and data.

Our aim is to convert this example .csv data into a MySQL database representation, then run some typical queries over the data and view the outputs. Finally, we will delete the database. These steps will introduce you to some of the typical commands that you will expect to use, and cover aspects of both the DDL and DML aspects of MySQL.

In the following example, the SELECT and FROM commands are used. The SELECT word does exactly what it says, and is used to select data from a table. Here, we simply select every (*) column FROM the Employee table.

mysql> SELECT * FROM Employee;

+--------------------------------------------------------------------------------+
|  EmployeeID |  FirstName |  LastName   |  Department |  Salary |  DepartmentID |
+--------------------------------------------------------------------------------+
|           1 |  Richard   |  Young      |  Sales      |   50000 |             2 |
|           2 |  Sarah     |  Campbell   |  Marketing  |   40000 |             1 |
|           3 |  Brian     |  Johns      |  Payroll    |   18000 |             4 |
|           4 |  James     |  Stephens   |  ShopFloor  |   15000 |             4 |
|           5 |  Danielle  |  Humphreys  |  ShopFloor  |   30000 |             3 |
|           6 |  Peter     |  Jeffries   |  Payroll    |   19500 |             4 |
|           7 |  Mary      |  Smith      |  Marketing  |   40000 |             2 |
|           8 |  Lee       |  Williams   |  Sales      |   32700 |             4 |
|           9 |  Alison    |  Williamson |  Sales      |   32700 |             4 |
+--------------------------------------------------------------------------------+
9 rows in set (0.00 sec)

…and similarly FROM the Department table…

mysql> SELECT * FROM Department;

+-------------------------------------------+
|   DepartmentID |   DeptName  |   Postcode |
+-------------------------------------------+
|              1 |   Sales     |   EH1 3SP  |
|              2 |   Marketing |   EH1 3SP  |
|              3 |   Payroll   |   N1C 4QL  |
|              4 |   ShopFloor |   N1C 4QL  |
+-------------------------------------------+
4 rows in set (0.00 sec)

SELECT is often also used in conjunction with the WHERE command. WHERE can be used to specify Boolean conditions on the rows that are returned. In the following, for example, the Boolean condition is <Salary > 30000>, such that each row this condition evaluates to ‘true’ is returned from the data. Note the Employees who are missing and their salaries (from the previous displayed Employee table.

So, as is expected from a query language it is quite easy to return only the specific data you are interested in. We are only using relatively simple examples, here, but you are getting a feel for the powerful nature of query based programming statements. One thing you might want to try, just for fun, is to write a program (say in Java), which reads the same information from the .csv files provided. Otherwise, you can simply trust that the task will be much, much more time consuming and cumbersome that simple executing the statement below.

SELECT * FROM Employee WHERE Salary > 30000;

+--------------------------------------------------------------------------------+
|  EmployeeID |  FirstName |  LastName   |  Department |  Salary |  DepartmentID |
+--------------------------------------------------------------------------------+
|           1 |  Richard   |  Young      |  Sales      |   50000 |             2 |
|           2 |  Sarah     |  Campbell   |  Marketing  |   40000 |             1 |
|           7 |  Mary      |  Smith      |  Marketing  |   40000 |             2 |
|           8 |  Lee       |  Williams   |  Sales      |   32700 |             4 |
|           9 |  Alison    |  Williamson |  Sales      |   32700 |             4 |
+--------------------------------------------------------------------------------+

In the examples that follow, the input commands build on the previous ones by including the use of the ORDER BY words. The first example orders the results by Salary, such that the Salary field determines the order in which the rows appear, in ascending order, from lowest to highest (using the ASC word). The second example used the DESC word, to ensure that the order by salary is in descending order. Notice, if you omit ASC (or DESC) then the default is ascending.

SELECT * FROM Employee WHERE Salary > 30000 ORDER BY Salary;
SELECT * FROM Employee WHERE Salary > 30000 ORDER BY Salary ASC;

+--------------------------------------------------------------------------------+
|  EmployeeID |  FirstName |  LastName   |  Department |  Salary |  DepartmentID |
+--------------------------------------------------------------------------------+
|           8 |  Lee       |  Williams   |  Sales      |   32700 |             4 |
|           9 |  Alison    |  Williamson |  Sales      |   32700 |             4 |
|           2 |  Sarah     |  Campbell   |  Marketing  |   40000 |             1 |
|           7 |  Mary      |  Smith      |  Marketing  |   40000 |             2 |
|           1 |  Richard   |  Young      |  Sales      |   50000 |             2 |
+--------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

SELECT * FROM Employee WHERE Salary > 30000 ORDER BY Salary DESC;

+--------------------------------------------------------------------------------+
|  EmployeeID |  FirstName |  LastName   |  Department |  Salary |  DepartmentID |
+--------------------------------------------------------------------------------+
|           1 |  Richard   |  Young      |  Sales      |   50000 |             2 |
|           2 |  Sarah     |  Campbell   |  Marketing  |   40000 |             1 |
|           7 |  Mary      |  Smith      |  Marketing  |   40000 |             2 |
|           8 |  Lee       |  Williams   |  Sales      |   32700 |             4 |
|           9 |  Alison    |  Williamson |  Sales      |   32700 |             4 |
+--------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

When you order a text value, such as Department field, then the default is ascending alphabetic order:

SELECT * FROM Employee WHERE Salary > 30000 ORDER BY Department;

+--------------------------------------------------------------------------------+
|  EmployeeID |  FirstName |  LastName   |  Department |  Salary |  DepartmentID |
+--------------------------------------------------------------------------------+
|           2 |  Sarah     |  Campbell   |  Marketing  |   40000 |             1 |
|           7 |  Mary      |  Smith      |  Marketing  |   40000 |             2 |
|           1 |  Richard   |  Young      |  Sales      |   50000 |             2 |
|           8 |  Lee       |  Williams   |  Sales      |   32700 |             4 |
|           9 |  Alison    |  Williamson |  Sales      |   32700 |             4 |
+--------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

It is also possible to order by one column (primary ordering column), and then by another (secondary ordering column). For instance, in the following example, we order primarily by Department, so we get the same ordering in the Department column, but because we then order by LastName, the order of the rows can be seen to alter according to this field within. The set of ‘Sales’ Employees:

SELECT * FROM Employee WHERE Salary > 30000 ORDER BY Department, LastName;

+--------------------------------------------------------------------------------+
|  EmployeeID |  FirstName |  LastName   |  Department |  Salary |  DepartmentID |
+--------------------------------------------------------------------------------+
|           2 |  Sarah     |  Campbell   |  Marketing  |   40000 |             1 |
|           7 |  Mary      |  Smith      |  Marketing  |   40000 |             2 |
|           8 |  Lee       |  Williams   |  Sales      |   32700 |             4 |
|           9 |  Alison    |  Williamson |  Sales      |   32700 |             4 |
|           1 |  Richard   |  Young      |  Sales      |   50000 |             2 |
+--------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

Any number of separate ordering can be done in this nested way, which makes it very useful for organising large amounts of rows.

Where we are not necessarily interested in the specific details of the data itself, we might want to use some of the aggregate functions available in MySQL. These functions allow us to count, find minimum, maximum and average values on those aspects of the data we are interested.

For a very simple example, you can simply count the number of rows in a table…

SELECT COUNT(*) FROM Employee;

+------------+
|   COUNT(*) |
+------------+
|         9  |
+------------+
1 row in set (0.01 sec)

SELECT COUNT(*) FROM Department;

+------------+
|   COUNT(*) |
+------------+
|         4  |
+------------+
1 row in set (0.00 sec)

Or, if you are interested in counting a subset of the data (like the number of Employees who earn over 30000) you can count on this subset.

SELECT COUNT(*) FROM Employee WHERE Salary > 30000;

+------------+
|   COUNT(*) |
+------------+
|         5  |
+------------+
1 row in set (0.00 sec)

Min and Max can be used. Who earns the least…

SELECT MIN(Salary) FROM Employee;

+---------------+
|   MIN(Salary) |
+---------------+
|         15000 |
+---------------+
1 row in set (0.00 sec)

…and who earns the most…

SELECT MAX(Salary) FROM Employee;

+---------------+
|   MAX(Salary) |
+---------------+
|         50000 |
+---------------+
1 row in set (0.00 sec)

What the total salary of all employees is:

SELECT SUM(Salary) FROM Employee;

+---------------+
|   SUM(Salary) |
+---------------+
|        277900 |
+---------------+
1 row in set (0.01 sec)

the average…

SELECT AVG(Salary) FROM Employee;

+---------------+
|   AVG(Salary) |
+---------------+
|   30877.7778  |
+---------------+
1 row in set (0.00 sec)

The following use of the function count is undertaken on a set of groups, grouped by salary.

SELECT COUNT(Salary), Salary FROM Employee GROUP BY Salary;

+----------------------------+
|  Count(Salary) |   Salary  |
+----------------------------+
|              1 |     15000 |
|              1 |     18000 |
|              1 |     19500 |
|              1 |     30000 |
|              2 |     32700 |
|              2 |     40000 |
|              1 |     50000 |
+----------------------------+
7 row in set (0.00 sec)