CS637 Practice Final Exam   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.













  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 to insert, and let auto-increment set the measurement’s database id, and be sure your create table above specifies auto-increment for this column. 








 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”?






 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.








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







  1. Find the total number of customers in each 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 named index.html and 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>



a.          Show the display to proper scale on the screen for this page, in a browser window of 400x400 pixels. Indicate the browser window with a drawn square, and then put the images and appropriate text inside it.  Show where “Page B” is seen. There is no CSS file here.











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





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.




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. The view code for the Product page is on pg. 185. The important tables are shown on pg. 99.


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.




b.      What PHP program processes this HTTP request?  Give its name, directory, and page number of its code in the text.



c.       Consider the code as it executes in the controller after the user clicks the "Basses" link, until the point it forwards to a view. In particular, give the name and final value of each assigned variable. 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, as does pg. 99.

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


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

   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.


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);


a.       From $orders alone (don’t use $order1, etc.), make $item have the value of the first item of the first order in $orders.

b.      From $orders alone, make $q have the value of the quantity of the first item of the first order in $orders

c.       Write a loop through $orders, adding up the quantities of productID 11 and leave the total in $total_quantity


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

a.       What HTTP command (ending in HTTP/1.1) would be used to get the current delivery status of order 123?



b.      If $content holds the needed content for the body of the response to be returned by the server, how does the server get it sent back to the client?  Give the line of code.

c.      What PHP function do we call in the client to convert the string returned in the response into a PHP array?

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.

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.