CS437/637 Homework 3 Pizza Project, Page Flows, Comm Diagrams, more SQL

Problems 1-6 constitute homework 3, 10 points, due Wed., Oct. 21, on Gradescope

Problem 7 is also known Project 1, first delivery, or pa1a for short,  also 10 points, due Tuesday midnight, Oct. 20, in /var/www/html/cs637/<username>/pizza1 on pe07.

1. To try out your new directory in pe07's web server site, put a simple hello.html in your new directory at /var/www/html/cs637/<username>. User joe has /var/www/html/cs637/joe, a private directory in the web server’s site, which is everything under /var/www/html/ (this is the "document root" of the web server). Note that everything under /var is visible only on its own host, i.e., pe07, so be sure to login to pe07 before trying to access this directory. To see your hello.html, make sure your tunnel from localhost:8000 to pe07.cs.umb.edu:80 is working (see AccessToCSHosts.html for directions) and simply browse to http://localhost:8000/cs637/username/hello.html. To create this file, transfer it from home to /var/www/html/cs637/username/hello.html on pe07, or, if hello.html is in your cs637 homework directory, it can be copied here with "cp ~/cs637/hello.html .". Your web-server directory is private to you except for access by the instructor or grader or of course the web server. All you need to do for this problem is have a little valid hello.html in your webserver directory at /var/www/html/cs637/<username> on pe07, and state in your homework paper what URL you used to display it with your browser. Also specify what lynx command on pe07 worked to display it. 

2. Page flows--UI analysis and design. Read about the Pizza Project.  See pizzaUI for images of the 9 major pages (not including error pages) of the web app. Study the pages and see how a user can navigate among them. Read pp. 463-464 in Duckett about the related topic of wireframes. Draw a page flow graph of the pages as navigated, with arrows showing use of links or buttons to get from one page to another. Each page is drawn as a rectangle with its links drawn as line segments and buttons as little boxes. Text inputs show as narrow rectangles. Radio buttons and check boxes also show. In other words, show all user input controls, plus important data tables, but don't worry about representing the lines of text of the static HTML. Draw the arrow from the link or button to the outer rectangle representing the other page, or back to this page. For an example, see Chapter 4 product manager page flow (just the bold parts). There are 9 major pages in this pizza app: Home, Manage Toppings, Add Topping, Manage Users, Add User, Manage Orders, Manage Days, Student Welcome, Pizza Order Form, so there should be 9 nodes (rectangles) in your graph, with arrows between them as appropriate.You can skip representing the links to Home and Student Orders that appear on every page (to simplify the diagram), but you should think about how they allow additional page navigation to users.  

3. Communications Diagram. Now that you understand how the pizza app is supposed to work, think about the request-response cycles that will happen as it executes. Suppose a user is looking at the Home page and clicks Topping Manager. Then that user sees the Manage Toppings page and clicks Add Topping. When the Add Topping page is displayed, the user fills in a new topping name and submits the form by clicking Add. The user then sees the Manage Toppings page again, with the new topping in the list. Assume the form submits using POST. Draw the communications diagram using slide 24 of the Chapter 5 slides as a model. You can drop the "server execution" note, as resulting in what shown in this image. (The following is wrong, there can reasonably be a REDIRECT) Note there is no REDIRECT involved in this interchange, but there would be if we considered the delete-topping scenario. 

4. Study the script for building my_guitar_shop2 on pp. 568-571 (574-577 in ed. 3)and online in book_apps/_create_db. The online version has inserts as well as create tables.

  1. Find all the foreign keys (FKs) and list them in order seen in this file, by table and column. Note that they do not have FK constraints.
  2. Analyze the relationships in this database as 1-1, N-1, or N-N (one to one, many to one, or many to many). If it is N-1, say which side is the "many" side, and what foreign key expresses the relationship.
    1. orders and orderItems
    2. orders and customers
    3. orders and products
    4. products and categories
    5. customers and addresses

5. SQL. Load both textbook databases into your mysql using _create_db/create_db.sql if you haven’t already. Write queries on my_guitar_shop2 as follows and show the queries and their output in your hw paper. You can use phpMyAdmin to try out the queries, or write a .sql file and use mysql at the command line. For queries, report the SQL and results, if any.

  1. Find all customerIDs with email addresses ending with "gmail.com".
  2. Find all order-items of order 2 (orderID = 2)and list their product id and quantity.
  3. List all order ids and order dates for the customer with email christineb@solarone.com.
  4. Find all product ids purchased by the customer with email christineb@solarone.com (i.e., find the orderitems)
  5. Find all products (product codes) where the purchase preceded product’s date-added (to check data consistency). Avoid duplicates in the result.
  6. Find the product that was bought the highest number of times.
6.  Pizza1_setup.
  1. Download pizza1_setup.zip and unzip it, then copy it to your htdocs/cs637/xxxx/pizza1 for development, where xxxx is your pe07 username. This way you won't have to edit it to get it to work on pe07 when you deliver it there. Try it out by browsing to localhost/cs637/xxxx/pizza1 to display the Home page, and see that it isn't using its CSS. Fix this by editing pizza1/view/header.php as directed in comments. Redisplay the home page and see that it now looks better.
  2. Use pizza1’s database/dev_setup.sql and database/createdb.sql to load you own mysql pizzadb. Report your commands and success or problems. Show the resulting output of showdb.sql.
  3. In pizza1, edit database.php to replace xxxx with your cs.umb.edu username. (This will be used for executions on pe07.) Browse to localhost/cs637/xxxx/pizza1 to try it out again and report success or problems. It should be able to list the toppings, but not much else.
  4. Set up Netbeans projects for pizza1_setup (only used for looking at files) and pizza1 (the one used in testing, development, etc.).  To avoid ending up with two projects of the same name, you can edit nbproject/project.xml and specify their names properly before using Netbeans Open Project. Then show the edit of database.php done in c. as a file “diff”.by selecting both file versions in Projects, right-clicking and using Tools>Diff.  Report on success or problems. Also run the project from Netbeans and report on success or problems.
More on Netbeans setup: Make sure you are using Chrome as the browser with Netbeans. This should happen automatically if your system default browser is Chrome, but if necessary set the Netbeans default browser to Chrome by using Tools>Options>General WebBrowser. You can right-click a project and select Run and expect to see its first page in a new Chrome window. But for this to work, Project Properties>Run Configuration needs a good Project URL, here http://localhost/cs637/xxxx/pizza1. The rest of the Project Properties should be OK by default. Note that the "official" way to execute the project is independent of Netbeans, that is, use Chrome to browse to http://localhost/cs637/cs637/username/pizza1 or http:localhost:8000/cs637/cs637/username/pizza1 once delivered to pe07 (with tunnel in place).

7.  A Little start on pizza1. pizza1_setup has a start on the toppings manager. Finish this part of the project by adding the capability of adding a new topping. Follow the implementation of adding a product to ch05_guitar_shop: add a function add_topping to topping_db.php.  See product_db.php, listed on pg. 171 (either edition). Then call add_topping from topping/index.php, just as add_product is called from product_manager/index.php, shown on pg. 173. Compose a new file topping_add.php for the needed form, like product_add.php.

By midnight, Tuesday, Oct. 20, deliver your project to your Apache directory on pe07.cs.umb.edu, /var/www/html/cs637/<username>. See problem 1 for more information about accessing this directory.  Once it’s running, add another topping of your choice.

If you have trouble with the delivery, please see me during office hours Wednesday, Oct. 21. 

Make sure that your directory structure looks like this:
/var/www/html/cs637/joe   –top level directory for user joe, with hello.html for problem 1
/var/www/html/cs637/joe/pizza1 – top of pizza1 project
/var/www/html/cs637/joe/pizza1/topping/index.php  one of the project files
… all the other files of pizza1