CS637 Practice Final Exam Solution, Spring, 2017   NAME:_________________________                          
Open books, handouts, solutions, etc. Closed electronic devices.

Show all work on these pages, using backs of pages if needed. Each problem is worth 20 points, except 6 and 7 are worth 15 points.   Total points = 150.

  1. A hospital with a diabetes clinic wants you to provide a glucose measurement web app for its diabetic patients. Each patient has a name (string), unique database id (int), and doctor’s name (string). Several times a day each patient measures their blood glucose level, a number such as 160, and how much insulin they took at that time, for example 10 units. Your web app should accept their id (our feeble attempt at privacy here) and blood glucose level “glucose” and insulin dose “dose” for one particular time, also recorded by using the current time known by the database. The app should also show the most recent (up to) 8 observations (time, glucose, dose).
  1. Using our database-backed web applications technology, how should we hold the data on all the patients and observations to support this application?  Write the SQL that the program needs to use to create empty versions of the tables named patients and measurements.  Use the uniqueness of the patient id appropriately, and use a foreign key constraint. Use auto_increment for the database id for a measurement. Use timestamp for the type of the measurement time column.

 create table patients (pid int primary key, name varchar(40) not null,

   doctor varchar(40) not null);

create table measurements(mid int auto_increment, pid int not null,

  glucose int not null, dose int not null, m_time timestamp not null,

  primary key (mid), foreign key (pid) references patients(pid));

Here "m_time" is short for measurement time, and many other names are possible here, but "time" itself is not a good name since it is a keyword.

  1. Given a patient ‘Joe’ already in the database with id 100, write the SQL statement that adds his measurement of glucose at 167 and dose of 12 units of insulin (at the current time) to the database. Let the database determine the time by using current_timestamp as the value, and let auto-increment set the measurement’s database id, and be sure your create table above specifies auto-increment for this column. 

 insert into measurements (pid, glucose, dose, m_time) values (100, 167, 12, current_timestamp); 


 2. a. Design a model API (function and parameter names) for this app that supports insert as well as the retrieval as described above.  For ch05_guitar_shop, the corresponding API is get_categories(), get_category_name($category_id), get_products_by_category($category_id), and three others on pg. 171. Indicate what each function returns, if anything. In particular, how can we return “up to 8 observations”?

add_measurement($pid, $glucose, $dose)
get_measurements($pid)  Returns an array of up to 8 associative arrays, each with keys 'mid', 'glucose', 'dose', and 'm_time'


     b. Design a two-page web UI for this app (not counting error pages). For each page specify the user controls (links, buttons, forms, etc.), preferably by using a page flow as we have done in homework 3 and in the slides.


SQL.  Note the my_guitar_shop2.sql script on pp 589-571. For this problem, write SQL queries on the database (no PHP here!) to find the following:
  1. For guitar (i.e. product with productCode) “les_paul”, find the total number of sales. If quantity = 10 in an orderItem for this product, that counts as 10 sales.
       select sum(quantity) from products p, orderitems o
         where p.productid = o.productid and p.productcode = 'les_paul';
  1. Find the total number of customers in each state.
      select state, count(*) from customers c, addresses a
      where a.customerid = c.customerid
      group by state


4.      Web background

See the cover of Murach and Harris, upper left corner, for the Murach logo image, assumed to be in file murachlogo.jpg, a 100x100 pixel image.  Suppose the following HTML file is located in the murach directory of the web server’s root directory.

<!DOCTYPE html>
    <title>Page B</title>
     <h1> Murach logo </h1>
     <img src=”images/murachlogo.jpg” width=”100” height=”100”>

     <p> Here it is again: </p>

     <img src=”images/murachlogo.jpg” width=”100” height=”100”>

          <h1> Just an aside </h1>


See the cover of Murach and Harris, upper left corner, for the Murach logo image, assumed to be in file murachlogo.jpg, a 100x100 pixel image.  Suppose the following HTML file isnamed index.html and is located in the murach directory of the web server’s root directory.

Note that the <aside> has no affect on placement: that is done in CSS normally.

b.         Give the sequence of GET commands (one line each, each ending with HTTP/1.1) that occur when you browse to this page.

       GET /murach/index.html HTTP/1.1
     GET /murach/images/murachlogo.jpg HTTP/1.1
and it's OK to repeat the second GET, but most browsers would use the previously-obtained image over again in the display

c.        Suppose you want the text “Murach logo” to appear in red, but all other text on the page should be the same default black. Compose a CSS file to be linked to this page that accomplishes this.

h1 {
  color: red;
aside h1 {
  color: black;

5.      Web App Implementation. Consider the Product Catalog application of pp. 180-187.  Suppose the user is looking at the Product page shown in the lower half of pg. 181 and clicks the “Basses” link.

a.       What HTTP command is issued by the browser? Show the whole command line, including any parameters and the HTTP/1.1 at the end.

    GET /book_apps/ch05_guitar_shop/product_catalog/?category_id=2  HTTP/1.1

b.      What PHP program processes this HTTP request?  Give its name and page number of its code.

   index.php, page 183

c.       Trace the code as it executes in the controller, stopping when it forwards to a view. In particular, give the name and value of each assigned variable when each variable assignment ($var = …)  is executed. Include array values (i.e., show the elements, at least their id and name values). Note that the picture on pg. 181 gives you information on the array contents.

$category_id = 2

$categories = array(array('categoryID'=>1, 'categoryName'=>'Guitars'), array('categoryID'=>2, 'categoryName'=>'Basses'),array('categoryID'=>3, 'categoryName'=>'Drums'))

$categoryName = 'Basses'

$products = array(array('productID' => '7', 'productName' => 'Fender Precision'), array('productID' => '8', 'productName' => 'Hofner Icon' ))

d.      What model calls were involved in the execution you described in c.?

  get_category_name, get_categories, get_products_by_category

6.      Objects

Here is part of cart.php from homework 5:

class Cart {
    // array of $product_id => $qty
    private $itemQty;

    // Create an empty cart
    function __construct() {
        $this->itemQty = array();

    // Add an item to the cart
    function addItem($product_id, $quantity) {
        $this->itemQty[$product_id] = round($quantity, 0);


  a. Give code to create an empty cart called $cart1

$cart1 = new Cart();

   b. Add 2 units of product 3 and 4 units of product 5 to $cart1 by calling its addItem method (shown above)



   c. After the actions of a. and b., give the contents of the array $itemQty, by drawing it as on pg. 337, or showing PHP code to build it.


$itemQty = array(3 =>2, 5=>4);

7.      Multilevel Arrays.  Suppose we build an array $orders by the following code:

$item0 = array(‘productID’=>11, ‘quantity’=>40);
$item1 = array(‘productID’=>12, ‘quantity’=>60);
$item2 = array(‘productID’=>11, ‘quantity’=>60);
$item3 = array(‘productID’=>12, ‘quantity’=>90);
$order1 = array(‘customerID’=> 1, ‘items’ => array($item0, $item1));
$order2 = array(‘customerID’=> 2, ‘items’ => array($item2, $item3));
$orders = array($order1, $order2);

$item = $orders[0]['items'][0];

$q = $orders[0]['items'][0]['quantity'];

$total_quantity = 0;
foreach ($orders as $o) {
    foreach ($o['items'] as $i) {
        if ($i['productID']===11) {
            $total_quantity += $i['quantity'];

8.      Web Services

Suppose proj2_server is installed at /var/www/html/test/proj2_server on topcat.

 GET /test/proj2_server/rest/orders/123 HTTP/1.1



  echo $content

9. Handling Users in bigger websites.

a. Explain how we ensure that passwords cannot be read in transit, for example, by "sniffing" tools that read raw packets off the network.

Answer: by using HTTPS during the password entry.

b. Explain how a web app remembers that a certain user has logged in a few minutes ago, so they are not bothered by repetitive requests for passwords.

Answer: When the user has been authorized by entering a good password, a session variable is created that can later be accessed by the various requests.