Homework 2: Application Development

CS186: Introduction to Database Systems
UC Berkeley
September 21, 2003
Due: October 5, 2003 (7pm)

1 Introduction

In Homeworks 0 and 1 you worked with Postgres, and learned something about its internals.  This homework will give you a taste of using an SQL database like Postgres for application development. A common way to build interfaces to a database application is though the web. In this assignment, you will be using PHP, a popular web scripting language, to build an application using a Postgres database as a backend.

The application you will be developing for this assignment is a simplified interface to a fictitious online bookstore based at Berkeley called Bearazon.org.  This application allows allows their customers to create accounts, to see what books are available, to order books, and to see the status of their orders. More complete examples of web-based tracking systems include those at amazon.com, barnesandnobles.com, etc. In this assignment, we have greatly simplified the system and you will only be required to implement a limited subset of features you see on the commercial sites.

1.1 Learning PHP

You should be able to pick up any PHP skills you need on the fly – it’s really quite easy to learn. You can find an extensive PHP manual at http://www.php.net/manual/en. To help you get started on learning PHP, we have provided some sample source code for the group registration application which is written in PHP. The source code is available at: 
/home/ff/cs186/fa03/hw2/groupreg.tar.gz
You should also step through the simple tutorial:
http://www.php.net/manual/en/tutorial.php 
while reading the sample source code that we provide.

We are giving you much of the PHP code and all of the HTML templates for the webpages; you only need to add the PHP code that interfaces to the database and gets the results back. We have provided some screenshots at:
http://inst.eecs.berkeley.edu/~cs186/hwk2/screenshots.html
and you can use these as a guide when building your web application.

1.2 Getting Started

The instructions are as follows:
gtar zxvf /home/ff/cs186/fa03/Hw2/hw2.tar.gz

This creates a subdirectory hw2 which contains the initial scripts.
chmod -R 755 hw2

We have also tried to ensure that your work cannot be seen by other students. Make sure that you place the code in only the ~/public_html/hw2 directory or it risks being seen by other groups. Also, do not delete the ~/public_html directory.
We have ensured that the scripts are working correctly on the instructional machines Pentagon and Rhombus, and we will continue to provide support. We will not provide support on any other platforms/machines. Before submitting, you MUST make sure your code runs on the instructional machines.

2 Database

By now, you should be familiar with how to create and start the Postgres DBMS. We will refresh your memory with some basic instructions.

2.1 Database Schema

In schema.sql, we have provided the DDL statements to create all database tables and load in the initial test data. This will be executed when you first run loadDB.sh. Do study the DDL carefully, especially the primary, foreign keys and value constraints. Some of the constraints are not implemented in the schema, but must be imposed by the application logic in your scripts. We call these application-level constraints. You will have to implement these constraints which we highlight in bold below. There are four relations in the schema. Columns in the primary key are underlined.
Information about a customer, including a password so they can log in to the system.
Each Order has a unique trackingNumber. This tracking number is auto-incremented using a special PostgreSQL feature called a SEQUENCE. SEQUENCEs are essentially database-persistent counters that can be atomically incremented, and are used to guarantee you get a unique numerical identifier. See the PostgreSQL command reference for CREATE SEQUENCE if you want all the details, e.g. at http://www.postgresql.org/docs/7.2/static/sql-createsequence.html. See schema.sql for the declaration of the SEQUENCE named trackid, and its use in the Order table. Note that trackid starts at 10 to account for the Orders we have preloaded in your initial testdata. The next Order that you add to the system will start from a tracking number of 6.  Each Order must have a registered customer associated with it. OrderStatus is a string indicating the status of the order, it is either "In Progress" (meaning the customer is still creating the order), "To be Verified" (meaning that payment must be verified), and "Shipped".
An Order may have any number of books associated with it.  This table has a tuple for each different book that is part of an Order, and includes how many copies of the book were ordered by the customer.
The Inventory records all books carried by the bookstore, and how many copies are on-hand.  A customer may not order the book if the quantity is zero.

Note: Book names are not case sensitive. I.e., “SAN FRANCISCO RESTAURANTS” and “San Francisco Restaurants” refers to the same book. This matters when you are comparing book names while implementing the application-level constraints. For simplicity, you should store all book names and do the comparisons in UPPER CASE.
Books are written by one or more authors.  This table holds author names.
This table indicates which authors are associated with a given book.

2.2 PHP Postgres Interface

The PHP language provides a rich set of functions for several tasks; among those are functions to communicate with Postgres. For details, see http://www.php.net/manual/en/ref.pgsql.php. We have placed some initial database interface code inside database.php. Currently, this includes the code for connecting to the Postgres database and all the Postgres interface code for show_books.php. To connect to the Postgres database properly, set the USER variable in database.php to your login, and the PORT variable to your assigned port (do a echo $PGPORT to figure out your port number). The HOST variable is set to the machine you are using to run your database. If you start your Postgres on Pentagon, define that variable as pentagon.cs.berkeley.edu. If you use Rhombus, set that to rhombus.cs.berkeley.edu instead.

You are free to place the database interface code in database.php or elsewhere, whichever suits you most. You will at least need to use the functions pg_query, pg_connect, pg_num_rows, pg_affected_rows and pg_fetch_array. Whenever you insert or update a row, use the pg_affected_rows function to make sure that the operation is successfully carried out, or else output the appropriate error message.

As we will explain in the next section, some of our scripts require you to formulate your queries with the correct ORDER BY clause. Please remember to put these ORDER BY clauses in!!

3 Web Frontend

In this project, you only need to worry about the application logic and not the presentation. We have provided you with nearly all of the necessary code to generate HTML output. You only need to “fill in the blanks” and make the scripts actually communicate with the database to produce the output. To help you get started, we have provided you our solution to show_books.php.

There are “begin” and “end” delimiters in the code that should guide you in modifying the appropriate places in the code you are given. Apart from these delimiters, we have also placed dummy for loops, which you will alter to iterate over result rows, at the locations marked in the code. You are free to place your code elsewhere or in other scripts apart from those that we recommend.

The subsequent sections give short description on each PHP script. Refer to our screenshots for some examples.

3.1 Main Customer Pages

These pages are viewable by customers. These pages are as follows:
This site has a form that allows customers to login, and it also provides links to show_books.php (icon) and register.php.
After a customer logs in, this page will display all the status of all the customer’s orders, including the order status, and all the book titles and quantities in the order.
This site allows a new user to register as a customer.
Confirmation page after a user successfully registers as a customer.
This page allows customers to order books.  It displays a list of all books for all Order(s) that are currently "In Progress" for the customer.  If no such Order exists, it creates an empty order marked "In Progress".  The page also includes a text field where they can enter a BookId to add a book to the current Order.  The page has a button where the customer can submit the Order(s), changing them from "In Progress" to "To Be Verified", and also a button where the customer can cancel the Order(s).  Finally, it displays a list of all books from which the customer  can choose BookIds for their order..
Show a list of all books in the database.
Search for books by author or title.
Show the books found in the search_books page.

3.2 Administrative Pages

These pages are viewable only by the staff at Bearazon.org, and are used by them to add new books and verify submitted orders.
The site that the staff first visits. This site has links to the following PHP scripts below.
Add a new author to the database.
Add a new book to the database.
Associate an author with a book.
Shows all the customers registered with Bearazon.org ordered by login.
When a customer submits an order, an administrator must verify the order using this page.  That moves the order from "Awaiting Verification" status to "In Shipping" status.  This page also shows a list of all orders in "To be Verified" status in the same order as the "viewAllOrders" page.
Confirmation page after a new order is successfully verified. The tracking number of the new order is also displayed.
Provides a form to enter new inventory information or update existing inventory information.
Unlike the track.php, this allows the display of ALL the orders ORDER BY orderStatus and trackingNumber. For each order, display all the books that are part of the order,  ORDER BY title.

3.3 Error Handling

In the process of generating the web page based on the PHP scripts, errors may be encountered. Some of them will be due to internal errors in the database, while the others will mostly be due to erroneous data entered by users. It is important in writing a web application to handle these errors gracefully.

To standardize error output, in each PHP script, we provide HTML code that will print out $MSG in red whenever there is an error. We have also provided standardize error messages that we expect you to output in each PHP script. Whenever you encounter an error, simply set $MSG to the appropriate error message. For example, in PHP script register.php, we have provided error messages if the registration form is incomplete, the two passwords entered do not match, or if the login chosen has been used by others. For general database errors (such as cannot connect to database for whatever reasons, unable to insert or delete a tuple, unable to issue a query, etc), output the generic error message $DB_ERR_MSG that we have predefined in database.php. You must handle all the error messages that we provide at the top of each php script.

4 What to turn in

You will work in your respective groups in this project as you have done in homework 1. When you are ready to submit the project, create a directory called Project2. Put all your PHP files into that directory. Include a README that contains you and your group members’ logins and SID, and also indicate what works and what does not. cd into that directory and type submit Project2 to submit the files.

At the same time, remember to submit the review for your group. Write your reviews in a file called prj2_review and place that in a directory called prj2_review. Use submit prj2_review to submit the review. Use the same format as you have done in homework 1 in the reviews.