CS636 Practice Final Exam, Fall, 2017             NAME:_________________________                      

Open books, handouts, solutions, etc., closed electronic devices.

Problems are marked with points, Total points = 150. 

Note the music project database schema on the last page, for reference.


1.      (15) Important Examples

a.       In the Download application of Chapter 7, available in project ch07download, there are these notable components:

A servlet, DownloadServlet.java     M  V  C

File-accessing code, UserIO.java    M  V  C

Various JSP files                    M V  C

This is an MVC application, although not as layered as our main projects. Classify these parts of the application by circling M for model, V for view and C for controller.


b.      In the Cart application of Chapter 7, available in project ch07cart, allowed a user to manipulate the contents of a shopping cart, adding, removing, and updating its items on various requests.  Through what mechanism provided by tomcat (and other application servers) is an app allowed to keep around an object in memory through the various requests by one user. Specifically:


Mechanism name:

Brief description of how it works, and what software in involved:



2.       (15) Concurrency Suppose a MVC web application (pizza3 or music3) running in tomcat has 6 user sessions active, and of these, 3 (with all different users) are doing concurrent requests, and of these, 2 are doing concurrent transactions at this moment. 


a.       How many JDBC Connections are active and communicating with the database?

b.      What object manages these connections as a group for the app, optimizing short-term use of JDBC connections? Specifically:


Class name of object:

What code creates this object:


c.       How many Session objects are in tomcat’s memory?


d.      Is there any worry that the two requests with concurrent transactions for different users of pizza3 will be sharing a PizzaOrder object in memory? Explain.


3. (30) Application Design and Layers. Currently the products in the music project are unchanging. Suppose you have been asked to add an admin capability to change the price of any specific product. This change will make Products mutable, but don’t worry here about those mutable objects being in the presentation layer.


a.       Explain why


void changePrice(Product updatedProduct) throws ServiceException


 is not a good choice for a new service layer method. Hint: what code would provide the “updatedProduct” object?





b.      Show a good choice for the changePrice service method signature.





c.       Describe any changes needed to the DAO API for your design, or explain why none are needed.





d.       Describe any changes needed to the database schema for your design





e.       In your design, is there a new transaction?  If so, what does it do?




(30) 4. The Database Model and SQL.

Suppose we want to enhance the music project to allow users to submit votes (or “likes”) for songs, i.e., tracks.  A user vote is submitted by a certain registered user for a certain track. A user can’t undo their vote once made, and each user can only vote at most once for each track, that is, additional votes for the same track and user will be just ignored. We also want to record the time the vote was submitted (the one that counted, not discarded duplicate votes). Note the core schema at the end of the exam.


a.       Give the create table statement for your new vote table, complete with any appropriate foreign key constraints.











b.      Show the new Vote domain class, with references to related domain objects corresponding to any foreign keys you specified above. As usual, you can put “// getters and setters…”





c.       Write SQL to find the number of votes each CD has received, that is, summed over all their tracks.  List CD product code and number of votes.



d.      Write SQL to find users who have voted for a track but never downloaded that track.






(30)  5.  Consider the application scenario from the practice midterm. 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, when the PC arrived at the store.)  At sale-time, the salesperson will enter a model number and get back the serial number of the system to sell (in case of ties for oldest, any one of the oldest.)


  1. Write a business layer API that allows entry of new PCs by inventory clerks and retrieval of the next one to sell by salespeople.  Don’t forget the exceptions.





  1. You are asked to write a web app for the salesperson to use. It has a form page with a form with method=”POST” action=”lookupPC.html” and an input text field with name “model” for the model number. It also has a result page found_pc.jsp, also in the top-level directory. Following our MVC webapp design, we would write a DispatcherServlet that handles the form submission. In its init() method, it calls the equivalent to PizzaSystemConfig.configureServices() and then calls to get a reference to your service API, and saves it in private static InventoryService inventoryService.  Write doPost() to handle the URL from the form submission, calling your API and forwarding to the result page. Don’t worry about errors here, and assume an appropriate web.xml. Use the back of the previous page.


  1. Write found_pc.jsp, using data you attached to the request object in DispatcherServlet.



Basic music project schema (no admin tables)


    product_id INT NOT NULL,
    product_code VARCHAR(10) NOT NULL,
    product_description VARCHAR(100) NOT NULL,
    product_price DECIMAL(10,2) NOT NULL,  
    UNIQUE (product_code),   
    PRIMARY KEY (product_id)
--create track before download: download has FK to track
  product_id INT NOT NULL,
  track_number INT NOT NULL,
  title varchar(100) NOT NULL,
  sample_filename varchar(100) NOT NULL,
  FOREIGN KEY (product_id) REFERENCES product (product_id),
  UNIQUE (product_id, track_number)

CREATE TABLE download (
    download_id INT NOT NULL,
    email_address VARCHAR(50) NOT NULL,
    download_date TIMESTAMP NOT NULL,
    track_id INT NOT NULL,
    PRIMARY KEY (download_id),
    FOREIGN KEY (track_id) REFERENCES track (track_id)

CREATE TABLE site_user (
    user_id INT NOT NULL,
    firstname VARCHAR(50) NOT NULL,
    lastname VARCHAR(50) NOT NULL,
    email_address VARCHAR(50) NOT NULL,
    company_name VARCHAR(50),
    address1 VARCHAR(50),
    address2 VARCHAR(50),
    PRIMARY KEY (user_id)

    invoice_id INT NOT NULL,
    user_id INT NOT NULL,
    invoice_date TIMESTAMP NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    is_processed CHAR(1) NOT NULL,
    PRIMARY KEY (invoice_id),
    FOREIGN KEY (user_id) REFERENCES site_user (user_id)

CREATE TABLE lineitem(
    lineitem_id INT NOT NULL,
    invoice_id INT NOT NULL,
    product_code VARCHAR(10) NOT NULL,
    quantity INT NOT NULL,  
    PRIMARY KEY (lineitem_id),
    FOREIGN KEY (invoice_id) REFERENCES invoice (invoice_id)