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:
- The instructional support staff have created public_html
directories for all the class accounts. These directories have
special permissions to allow the webserver to run your php files, but
other students will not be able to read them. DO NOT DELETE YOUR PUBLIC HTML
DIRECTORY! If you do, only the instructional staff will be
able to recreate it.
- cd to the ~/public_html directory and copy over the
initial files using:
gtar zxvf
/home/ff/cs186/fa03/Hw2/hw2.tar.gz
This creates a subdirectory hw2 which
contains the initial scripts.
- To
ensure that your scripts can be read and executed by the web server, cd
to ~/public html and type
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.
- When you first get started, you must reinitialize the database:
- delete the $PGDATA directory
- run initdb at the shell to
initialize the database directory at $PGDATA.
- pg_ctl start and pg_ctl stop will start and
stop Postgres respectively.
- After starting Postgres using pg_ctl start,
cd to the public_html/hw2 directory and use our provided script
loadDB.sh to create the database ’hw2’ and load
in some test data. You only have to run loadDB.sh once unless you want
to reinitialize your database. Subsequently, when you are testing your
scripts, make sure that Postgres is started or the PHP scripts will not
be able to make database connections.
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.
- Customer (login: varchar(10), name: varchar(50), password:
varchar(10), street: varchar(50), city: varchar(30));
Information about a customer, including
a password so they can log in to the system.
- Orders(trackingNumber: integer, orderDate: date,
customerLogin:
varchar(10), orderStatus: varchar(20));
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".
- OrderedBook (orderNumber: integer, bookID: integer,
quantity:
integer)
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.
- Inventory (bookId: integer, title: varchar(30), ISBN:
varchar(30), cost: real, quantity: integer)
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.
- Author (authorId: integer, firstName: varchar(20),
lastName:
varchar(30))
Books are written by one or more
authors. This table holds author names.
- AuthoredBy(bookId: integer, authorId: integer)
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.
- updateInventoryInformation.php
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.