Exam, Web Publishing with Databases, Fall 2000

Exam, Fall 2000

for Web Publishing with Databases, Fall 2000

by Martin Elsman and Arne John Glenstrup.

All aids are permitted, except computers.

The exam is a written exam, four hours, and is evaluated on a scale from 0 til 100 percent.

The exam consists of five exercises, all of which should be solved.

One word of advice: Read through the entire text before starting answering any of the exercises.

Introduction

In this exam you must implement parts of a web based pizza ordering system. The idea is that customers can order pizzas and other food on the web, and then the orders can be seen by the people working in the pizza shop. In order to make it easier for the customers to choose their food, you must also implement a meter indicating the popularity of the individual pizzas.

Exercise 1 (20 percent) - HTML

The following figure shows the main page of the web based ordering system that meets the customers:

The figure displays the main page for a local pizza shop and allows the customers to choose their desired food by selecting it from the SELECT box, entering their telephone number in the text box and then click the ``Afsend Bestilling'' button.

Exercise 1.1

Write down the HTML code for the form shown in the preceding text. Your answer should only contain the HTML code for the form and not the HTML code for the entire page:

  <form action=order.php>
    <input type=hidden name=store_id value=1>
    ...
  </form>

As well as the hidden form variable store_id that indicates the identity of the restaurant, the form must also include three form elements:

A SELECT box with an attribute name="dish_id". On the right the form is displayed with the unfolded SELECT box; the values for each of the options in the SELECT box should be 1, 2 and 3.
A text box with attributes name="phone" and size="8".
A submit button with an attribute value="Afsend Bestilling".

Exercise 1.2

In this exercise you must write down the HTML code that surrounds the form of the ordering page shown in the preceding figure. You must ensure the following:

The page header must be implemented as a table - with a red background color - containing two columns: one left aligned and one right aligned.
The picture located at the URL http://didim.dk/didim.jpg must be displayed to the right of the form.
There must be a link to the popularity meter from this page; you can assume that the popularity meter is implemented for the restaurant by using the hyperlink
```
  statistics.php?store_id=1
```
There must be a footer with the address of the restaurant

Note: the frame surrounding the figure is not a part of the HTML code that you must write. To reduce the amount of writing in exercise 1.2 you can simply write *FORM* to refer to your answer to exercise 1.1.

Exercise 2 (20 percent) - PHP

Exercise 2.1

Write down the HTML code resulting from calling the PHP function star_graph shown below, giving the following array as argument:

  array (array ("Pizza no. 9", 5), array ("Pizza no. 5", 2))

function star_graph ($list) {
  $res = "<table width=100%>";
  for ($i = 0; $i < count($list); $i++) {
    $name  = $list [$i] [0];
    $count = $list [$i] [1];
    $n = $count;
    $bar = "";
    while ($n > 0) {
      $bar = $bar . "*";
      $n = $n - 1;
    }
    $res = $res . "<tr><td width=50%>$name</td>
                       <td width=50%>$count: $bar</td>
                   </tr>";
  }
  return "$res</table>";
}

Exercise 2.2

Write a PHP function max_count that takes as argument an array of orderings and returns the maximum number of times a pizza has been ordered.

Hint: calling the function with the following array should return the value 5:

  max_count (array (array ("Pizza no. 9", 5), array ("Pizza no. 5", 2)))

It can be advantageous (but not required) to use the foreach command to traverse the array.

Exercise 2.3

You must now write down a new function star_graph2 by modifying the function star_graph in Exercise 2.1 so that, even considering several thousand orders, it is possible to make a graphical comparison of the number of orders. The solution must display 10 stars for the most popular pizza.

Hint: The number n of desired stars for a single pizza can be computed using the formula

n = 10 * count / max

where count is the number of orders for an individual pizza, and max is the maximum number of times a pizza has been ordered. It can be advantageous to use the function max_count when writing star_graph2 to return the maximum number of times a pizza has been ordered.

Exercise 3 (10 percent) - Regular expressions

Exercise 3.1

The regular expression [0-9] matches one occurrence of the digits 0-9. Write down a regular expression that matches telephone numbers consisting of 8 digits.

The telephone numbers 26122212 and 21332223 must thus match the regular expression.

Exercise 3.2

Write a PHP function pz_check_phone for checking whether a telephone number is of the form described in Exercise 3.1. If the telephone number that is transferred to the function as argument has the correct form, the function must return immediately. Otherwise, the function must display an error message in the user's browser and stop the program by calling the exit function.

You must use the PHP function ereg to check the form of the telephone number.

Exercise 4 (20 percent) - SQL

The data model of the pizza ordering system consists of three tables:

store - contains data concerning the restaurants (shops)
dish - contains data concerning the food offered by the individual restaurants
dish_order - contains orders connected to the individual restaurants.

In order to simplify the system we assume in the following that each order is connected to exactly one kind of pizza. The data model is displayed as an E/R diagram in the following. The boxes represent the entities (tables), while the ovals represent attributes (fields) of the entities. Furthermore, one-to-many relationships are indicated by forks.

The tables store and dish are created using the following SQL commands:

  CREATE TABLE store (
    id      int          PRIMARY KEY AUTO_INCREMENT,
    name    varchar(400) NOT NULL,
    pic_url varchar(400),
    address varchar(400)
  ) TYPE = InnoDB;

  CREATE TABLE dish (
    id       int          PRIMARY KEY AUTO_INCREMENT,
    store_id int          NOT NULL, INDEX (store_id),
    name     varchar(400) NOT NULL,
    FOREIGN KEY (store_id) REFERENCES store (id)
  ) TYPE = InnoDB;

In the following you can assume that these commands have been executed:

  INSERT INTO store (id, name, pic_url, address)
  VALUES (1, 'Didim Pizza Pasta og Grill', 'http://didim.dk/didim.jpg',
	  'Vibevej 35, 2400 Kbh. NV. Tlf. 38100571 - Så har vi det klar når De kommer');

  INSERT INTO dish (id, store_id, name)
  VALUES (1, 1, 'No 9: Pepperoni. Kr. 38,-');

  INSERT INTO dish (id, store_id, name)
  VALUES (2, 1, 'No 24: Hawaii. Kr. 38,-');

  INSERT INTO dish (id, store_id, name)
  VALUES (3, 1, 'No 36: Didim Bøf. Kr. 40,-');

Exercise 4.1

Write down the result of executing the following SELECT command:

  SELECT id, name FROM dish;

Exercise 4.2

Write down the SQL command for creating the table dish_order. The table must consist of five columns:

id - a unique integer for identifying an order (primary key). If a row is inserted without indicating the value of id, it should be generated automatically.
dish_id - a reference to the dish table
order_date - the date of the order (use the date datatype)
phone - the telephone number of the person ordering the pizza
deleted_p - a boolean that is set to 0 (false) when the order is accepted and set to 1 (true) when it is collected.

Use the NOT NULL constraint to express the fact that the fields order_date and phone must contain some data.

Exercise 4.3

Write down two SQL commands for creating the following orders in the dish_order table - assuming the previous insertions in the store and dish tables:

No 9: Pepperoni at Didim Pizza Pasta og Grill for telephone number 26122212 5th January, 2001 (2001-01-05)
No 36: Didim Bøf at Didim Pizza Pasta og Grill for telephone number 21332223 6th January, 2001 (2001-01-06)

Exercise 4.4

Construct an SQL command for extracting uncollected orders (deleted_p = 0) for the ``Didim Pizza Pasta og Grill'' restaurant. The following columns must be included in the result: order_date, dish_id, name (i.e., dish.name), phone, order_id (i.e., dish_order.id). The rows must be sorted according to the ordering date.

Exercise 4.5

Construct an SQL command for extracting statistics concerning pizzas ordered at the ``Didim Pizza Pasta og Grill'' restaurant. The resulting columns must be name (i.e., dish.name) and count (i.e., the number of orders of the given dish). Collected as well as non-collected orders must be part of the statistics.

Hint: The SQL command must obtain data from the two tables dish and dish_order, and also use the GROUP BY SQL construction.

Exercise 5 (30 percent) - Web service

Two site maps for constructing the PHP files that implement the restaurant ordering system have been created: one for customer interaction and one for adminitrator interaction. The boxes in the diagrams represent web pages (implemented as PHP files), the arrows without annotations represent links, and the annotated links represent transactions. The following figure shows the site map for customer interaction (just read '.php' wherever it says '.tcl'):

Exercise 5.1

In the following you will find a template for a function pz_return_page that can be used as a framework for returning web pages to the users. The function takes a database handle db and a store identifier store_id, and constructs an appropriate header and footer for the pages:

function pz_return_page ($db, $store_id, $body) {
  mysql_select_db ("ns_db", $db);

  // Fetch store name and address from the database
  $query = " ... ";
  $rows = mysql_query ($query);

  // Return a page (cf. Exercise 1.2)
  if ($row = mysql_fetch_row ($rows)) {
    $name    = $row [0];
    $address = $row [1];

    echo "<html>";
    echo "  ... $name ... $body ... $address ...";
    echo "</html>";
  }

Write down the PHP code for defining the PHP variable $query above, so that when the mysql_query command has been executed, the name and address of the restaurant is available in the $name and $address variables shown above.

Note: You are not expected to write down the HTML code that is returned; you have almost done that in Exercise 1.2.

Exercise 5.2

In this exercise you must construct the file order.php, which is executed when a customer issues an order. We will not consider constructing the file index.php, but you can assume that the form variables phone, dish_id and store_id are transferred to the file order.php (cf. Exercise 1.1).

The file order.php must insert into the database an order of the pizza dish_id for the restaurant store_id, ordered by a person with telephone number phone. The data inserted into the database must include the date of the order.

Note: You can assume that the functions pz_check_id and pz_check_phone can be used to ensure that a string contains a number and a telephone number, respectively. Furthermore, the function pz_return_page is used to return a ``Thanks for your order'' page to the customer, with an option for clicking back to the index.php page for ordering more pizzas. The file index.php expects store_id to be transferred as form variable.

Exercise 5.3

In this exercise you must construct parts of the file statistics.php based on the following template:

  // Set the form variable 'store_id'
  pz_check_id ($store_id);

  $db = mysql_connect ("mysql.itu.dk", "W2_F2004", "ns");
  mysql_select_db ("ns_db", $db);

  $query = " // YOUR ANSWER FOR EXERCISE 4.5 WITH store_id = 1
             // REPLACED WITH store_id = '$store_id' ";

  $rows = mysql_query ($query);

  $dishes = array ();
  $i = 0;
  while ($row = mysql_fetch_row ($rows)) {
    $name  = $row [0]; // dish name
    $count = $row [1]; // number of orders of this dish

    // APPEND A PAIR (ACTUALLY AN ARRAY OF LENGTH 2) OF
    // name AND count TO THE ARRAY dishes
    ##A## ...
  }

  $graph = star_graph2 ($dishes);
  
  // RETURN A PAGE TO THE USER
  ##B## ...

The exercise consists of writing down code for the program points marked ##A## and ##B## in the preceding template.

Exercise 5.4

The following figure shows the site map for the restaurant administration functions (just read '.php' wherever it says '.tcl'):

To simplify the system, no password is required to get access to the restaurant administration functions.

Consider the following template for the file admin.php:

  // check the form variable 'store_id'
  pz_check_id ($store_id);

  $db = mysql_connect ("mysql.itu.dk", "W2_F2004", "ns");
  mysql_select_db ("ns_db", $db);

  $query = " -- YOUR ANSWER FOR EXERCISE 4.4 WITH store_id = 1
	     -- REPLACED BY store_id = '$store_id' ";

  $rows = mysql_query ($query);

  // INITIALIZE THE PHP VARIABLE body
  ##A## ...

  while ($row = mysql_fetch_row ($rows)) {
    $order_date = $row [0];
    $name       = $row [1];
    $phone      = $row [2];
    $order_id   = $row [3];

    // APPEND AN ORDER TO THE PAGE BODY
    ##B## ...
  }

  // APPEND SOME HTML-CODE TO body
  ##C## ...

  $header ="
    <meta http-equiv=\"Refresh\" 
     content=\"5;url=http://hug.it.edu:8002/pz/admin.php?store_id=$store_id\">";

  pz_return_page_with_header ($db, $store_id, $header, 
   "<h2>Orders</h2>
    <a href=index.php?store_id=$store_id>Home</a> :: Order list
    <hr>
    $body");

Write down the code for the program points ##A##, ##B## and ##C## in the preceding template. Remember to include a link to the file delete.php, which expects the form variables $store_id and $order_id to be set.

Note: The code for the file admin.php uses a modified version of the function pz_return_page that allows a meta tag to be inserted in the HTML header; the meta tag used here makes the browser reload the page admin.php every fifth second so that the restaurant staff are notified immediately about new orders!

Exercise 5.5

Construct the PHP file delete.php.

Note: The order must not be deleted from the database, as this would prevent any statistics to be generated. Rather, the field deleted_p must be updated to '1' (true) by using SQL's update command. Finally, the PHP file must redirect the user to the admin.php file by using the PHP command Header.

mael@it.edu