CS637 Practice Final Exam Solution, Fall 2020   NAME:_________________________                          
Open books, handouts, solutions, etc., including online docs. Attend class and keep your video on. Submit your solution to Gradescope, or if problems with that, email to me. You can ask short questions during the exam by speaking up, or send me email at elizabeth.oneil@umb.edu. Show all work by editing this document (preferred option), or on separate pages with problems clearly marked and in order. Each problem is worth 20 points, except 6 and 7 are worth 15 points.   Total points = 150.

Note that this is too long for an actual final exam, but shows the kinds of problems the real final may have. Note that two problems require drawings, as has the homework, so you should have practice in adding a drawing to your document at the right place. See "Gradescope info" on the class web page under Resources for some pointers.Show all work by editing this document (preferred option), or on separate pages with problems clearly marked and in order. 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.

pageflow

 

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 our pizza projects for the pizzapie image, in file pizzapie.jpg, a 100x100 pixel image.  Suppose the following HTML file is named index.html and is located in the test directory of the web server’s root directory.

<!DOCTYPE html>
<html>
  <head>
    <style type = "text/css">
      * { margin: 0; padding: 0}
      body {
           width: 500px;
           border: black solid 3px; }
      h1 {
            background:lightblue;
          }
     </style>
   </head> 
<body>
   <h1> Pizza Pie </h1>
   <img src="pizzapie.jpg" alt="pie" width="100" height="100">
   <p>
     Here it is again:
   <img src="pizzapie.jpg" alt="pie" width="100" height="100">
   </p>
  </body>
</html>

a.     Show the display to proper scale on the screen for this page, in a browser window of 600x600 pixels. Indicate the browser window with a drawn square, and then put the images and appropriate text inside it, with the border.  Use cross hatching to show the blue background.

 

 pic of index.html

Note these features: the <body> element's width is 500px, 5/6 of the window's. Its height is determined by its contents. It has a black border all the way around. The bottom border is supposed to be horizontal. The <h1> element has Pizza Pie as text, but itself stretches all the way across the <body>, and has the lightblue background. The first pizzapie image fits in 100x100 below the <h1> element, itself a block element so it's at the left edge of the <body>, with no margin. The text "Here..." starts at the left hand edge of the <body> since it's in a <p> element. The second pizzapie image is inline with this text, and its height forces that line of text down by 100 px.

Resulting page Note that the "Here..." text actually fits in about 100px, not the 200px shown above.

 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 /test/index.html HTTP/1.1
        GET /test/pizzapie.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

5.      PHP 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 designed to represent a simple shopping cart in a webapp:

<?php
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)

 

$cart1->addItem(3,2);

$cart1->addItem(5,4);

 

   c.  Is it possible to access the Cart's $itemQty from code outside cart.php?  Explain how or why not.

 

No, private properties are hidden (encapsulated) from external code, as in Java.

 

7.      Web Services.Suppose pizza2_server is installed at /var/www/html/test/pizza2_server on pe07.

a.      What HTTP command (ending in HTTP/1.1) would be used to get the current JSON representation of order 12?

       GET /test/pizza2_server/api/orders/12 HTTP/1.1

b.      If $content holds the JSON content for the body of the response to be returned by the Slim server (set up as we have done), how does the server get it sent back to the client?  Give the line of code. (This can be done in two ways, with and without $response)

echo $content;     or   $response             
                   ->withStatus(200)
                   ->write(json_encode($order));

 

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

 

json_decode

 

d.   Under what circumstances should the server return HTTP 400?

 

When the request itself is defective in some way.

 

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

 

 

    9. Javascript Web App Implementation

a. Write a small HTML file that has a body, with a main, and inside main, a section containing "Hello" in an p element. It should also have a script element for Javascript in app.js. The section element should have an attribute named "id" of va

<!DOCTYPE html>
<html>
  <body>
   <section id="spot">  
     <p> Hello
     </p>
    </section>
    <script src="app.js"></script>  

  </body>

</html>

b. Write JS code for app.js that locates the section and adds another p element containing "Goodbye".

"use strict";
let section = document.querySelector("#spot");
section.innerHTML += "<p>Goodbye</p>";

 

or


"use strict";

document.querySelector("#spot").innerHTML += "<p>Goodbye</p>";

 

c. Add an event handler to the section element so that when a user clicks on the section, another p element containing "Goodbye" is added below the preceding ones

  "use strict";

                let section = document.querySelector("#spot");
        section.innerHTML += "<p>Goodbye</p>";
        section.addEventListener("click", () =>

     section.innerHTML += "<p>Goodbye</p>");


     

        Note that this does not provide a visible button, but still clicking on the "Hello" or "Goodbye" runs the event handler, since they are in the <section> element.