CS637 Practice Midterm Exam Solution, S17   NAME:_________________________                               

Note that this exam has no SQL queries on the databases we studied, or any PHP for you to write. The actual exam may have these things, though only short snippets of PHP for you to write.

1.    Suppose you are automating operations for a store selling PCs.   All PCs of a certain model number are equivalent as far as buyers are concerned, but the store wants to sell them in FIFO (first-in, first-out) order so that no box gets too worn-looking.  Each PC has a model number (integer), serial number (10-char string), vendor id (integer), and delivery date (integer day number.) 

  1. Using our webapp database applications technology, how should we hold the data on all the PCs to support this application?  Write the SQL that the program needs to use to create an empty version of the data holder. 

All long-term changeable knowledge should be in the DB (except per-user data). This means we need a table to hold the PC information:

create table pcs(model int, serial_no int, vendor int, delivery_date int);

(normally we would have a PK set up, probably serial_no, but it's not required by the problem statement)

  1. Given a model number, say 1234, for the next PC to sell, write the SQL query that determines the serial number of the PC that should be selected for the customer.

   select serial_no from pcs
     where model=1234 and delivery_date = (select min(delivery_date) from pcs where model=1234)

This may result in several serial_nos. To make it unique we could select for min(serial_no) instead of just serial_no.

c.    Design a model API (function and parameter names only) 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.

   void insertPC($model, $serial_no, $vendor_id, $delivery_day)

   findOldestPC()    (returns serial_no of oldest PC)

2.       (14) In the same scenario as problem 1, once a PC is sold, we need to keep track of the customer for each individual PC.  Each PC has a unique customer but a single customer can purchase multiple PCs. Each customer has a string name and a social security number (ssn, an integer).
  1. How should we hold the data on customers?  As in 1a, show the creation of the data holder using SQL.

    create table customer( name char(20), ssn int primary key);

(The primary key part is really for b.)

Also, add a custid column to table pcs.


  1. What foreign key constraint can be added to this system to improve its integrity?

The FK needs to go from PCs to customer, because a PC has a unique customer, the target of the FK:

Answer: Add FK from custid of pcs to ssn of customer. 

3.  Web Background

  1. Write two simple web pages a.html and b.html that reside in the same directory with local path /testmsg on a certain web server.  Page a links to page b, and page b has a form with a single text input with name “msg”, no ACTION specified, but with a submit button. 

a.html

<DOCTYPE html>
<html>
  <head>
    <title>Page A</title>
  </head>
  <body>
     <a href="b.html">Link to B</A><br>
  </body>
</html>

b.html

<DOCTYPE html>

<html>
  <head>
    <title>Page B</title>
  </head>
  <body>
    <form>
      <input type="text" name="msg">
      <input type="submit">
    </form>
  </body>
</html>

  1. Suppose a user browses to page a.html as served by the web server, follows the link to b.html, and then fills in “hello” in the text box and clicks the submit button.  Give the sequence of HTTP request and responses to this web server that would happen, stopping after the request that carries the “hello” data back to the server (using b.html’s URI, the default ACTION URI.)

Since headers were not asked for, you can omit them--
GET /testmsg/a.html HTTP/1.1
response: text of a.html
GET /testmsg/b.html HTTP/1.1
response: text of b.html
GET /testmsg/b.html?msg=hello HTTP/1.1

Note: b.html does not know how to process the msg=hello information, so all that happens is that the text of b.html gets returned again here.

 

4.  HTML/CSS. Consider the following page.  Most of its PHP is given on pg. 145.

product manager page

  1. Draw rectangles on the above picture showing the boxes owned by the aside and section elements.
  2. What is the containing element of these two elements?  main
  3. Make ovals around all data that came from the database. (Consider what would happen if more rows were inserted in the tables.)
  4. What just happened (what user interaction) to cause the URL in the browser address window?
  5. User clicked the delete button on an item (the item is no longer in the table)

  6. What mechanism in HTML or CSS or both allows the two-column format of this page?

        The CSS specifies the “float:left” property for section and aside elements.

 

5. Arrays. Consider the following PHP fragment.

$a = array('id' => 12, 'name' => 'Joe", 'age' => 23);

$b = array('id => 13, 'name' => 'Sue', 'age' => 25);

$friends = array($a, $b);

Draw the resulting $friends array using the pictures on pg. 337 as a guide.


array of two aas


 


6.    System implementation.  In pizza1_setup_soln consider the index.php listed below, in pizza1/topping of the project. 

  1. What calls into the model code can occur during the execution of this method?  List the function names.
  2.        get_toppings, delete_topping, add_topping

  3. What view files are forwarded to in this code? List their names.
  4.  database_error.php, topping_add.php, topping_list.php, error.php

  5. Why does the require of topping_db.php  need  “../model” before its name on the third line?
  6.  The directory of this code is toppings relative to the top-level directory, while topping_db.php is in model/topping_db.php relative to the top-level directory. To get from the first to the second, you need to go up one directory levels with ../, then down to model, ../model, to get to the second file’s directory.

  7. Suppose this project is installed on topcat in /var/www/html/pizza1and its subdirectories. Note that this is slightly different than your own deployment. Give the HTTP GET command to execute this file with parameter “action” of value “show_add_form”. You don't need to show the  HTTP headers, just the line starting with GET and ending with /1.1.

 GET /pizza1/topping/index.php?action=show_add_form HTTP/1.1              or

 GET /pizza1/topping/?action=show_add_form HTTP/1.1              or

 GET http://topcat.cs.umb.edu/pizza1/topping/?action=show_add_form HTTP/1.1

<?php

require('../model/database.php');
require('../model/topping_db.php');

$action = filter_input(INPUT_POST, 'action');
if ($action == NULL) {
    $action = filter_input(INPUT_GET, 'action');
    if ($action == NULL) {
        $action = 'list_toppings';
    }
}

if ($action == 'list_toppings') {
    try {
        $toppings = get_toppings($db);
        include('topping_list.php');
    } catch (PDOException $e) {
        $error_message = $e->getMessage();
        include('../errors/database_error.php');
    }
} else if ($action == 'show_add_form') {
    include('topping_add.php');
} else if ($action == 'add_topping') {
    $topping_name = filter_input(INPUT_POST, 'topping_name');
    if ($topping_name == NULL || $topping_name == FALSE) {
        $error = "Invalid topping name";
        include('../errors/error.php');
    } else {
        try {
            add_topping($db, $topping_name);
        } catch (PDOException $e) {
            $error_message = $e->getMessage();
            include('../errors/database_error.php');
            exit();  // needed here to avoid redirection of next line
        }
        // Redirect back to index.php (see pp. 164-165)
        // (don't include index.php inside index.php)
        header("Location: .");
    }
}