CS636 Midterm Exam Solution, for practice, F17    NAME:_________________________                              

1.       (24) 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 (int), serial number (10-char string), vendor id (int), and delivery date (int day number.) 

  1. Using our client-server 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 domain class and DAO API (method signatures only) for this app that supports insert as well as the retrieval as described above.

domain class PC: just a POJO with the attributes as fields:  you don’t have to write out the getters and setters, just indicate them

public class PC {
   private String serialNo;
   private int model;

   private int vendor;
   private int deliveryDate;

  // getters and setters for the 4 fields


void initializeDB() throws SQLException;

void insertPC(PC pc) throws SQLException;

PC findOldestPC() throws SQLException;

(also close() is a good idea, to close the Connection)

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, we need to 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.  (24) 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. 



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


    <title>Page B</title>
      <input type="text" name="msg">
      <input type="submit">

  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.) Assume that a.html and b.html have no images or CSS files.

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.  (14) Ant and command line tools.

  1. Consider pa1b. If "ant build" and "ant oraSysTest" work, but "ant hsqlSysTest" fails to execute (never even does one command), give two possible reasons for this behavior (missing files, wrong file content, something you forgot to do, for example.)


This is probably a HSQLDB-specific problem.  The hsqldb.jar file might be missing or in the wrong place.  Or the target hsqlSysTest might be mis-configured in build.xml.  Perhaps the hsqldb server is not running: this must be started explicitly by the user.  
  1. Consider JdbcCheckup.java. Suppose your current directory is your own jdbc directory on topcat containing JdbcCheckup.java and the driver jar files, previously copied from /data/htdocs/cs636/jdbc. Give the command to compile JdbcCheckup.java, and then the command to run it to connect to Oracle on dbs3. You don't need to show how to enter information into the running program.

     javac JdbcCheckup.java

   java -cp ojdbc6.jar:. JdbcCheckup

    (24) System implementation.   In pizza1 consider the service method listed here:  

    // return all orders for this room, for today, in order by id
    public List<PizzaOrderData> getOrderStatus(int roomNumber) throws ServiceException {
        List<PizzaOrder> pizzaOrders = null;
        List<PizzaOrderData> pizzaOrders1 = new ArrayList<PizzaOrderData>();
        try {
            pizzaOrders = pizzaOrderDAO.findOrdersByRoom(roomNumber, adminDAO.findCurrentDay());    
            for (PizzaOrder order: pizzaOrders) {
                pizzaOrders1.add(new PizzaOrderData(order));
        } catch (SQLException e) {
            throw new ServiceException("Error in getting status" + e, e);
        return pizzaOrders1;

  1. What calls into the DAO layer occur during the execution of this method?  List their method names in execution order.
  2. findCurrentDay  (called first as arguments are evaluated)


  3. Explain why this method does not qualify as a “getter”, even though its name starts with “get”.
  4. It has a method parameter in its signature.

  5. How do we know that there is no UI (user interface action) during the execution of this call?
  6. This is a layered system, with no upcalls, that is, calls from a lower layer to an upper layer.  This service-layer code only calls down to the DAO and also to the domain object methods (plus library methods).  The domain objects are all self-contained, or at worst, calling other related domain objects, and thus never calling any app methods outside of their package. The DAO never calls up the layers, so in particular not to the top layer, the presentation layer, which has all the UI code. Thus there is no way for execution to reach the presentation code while executing this method.

  7. In this code, what code takes a PizzaOrder object and returns the corresponding PizzaOrderData object?   
  8.      new PizzaOrderData(order);

  9. What is the advantage of the PizzaOrderData object over the PizzaOrder object (holding the same order) for transporting information to the presentation code?
  10.         The PizzaOrderData object is immutable, safe from presentation code meddling.