COP4710 - Introduction To Database Systems

Programming Assignment

E-Bookstore Application

Due: Friday April 11, 2008

Purpose of the project

In this project, you will design, implement and document a database system for a web based electronic bookstore ( ex. ).

Application Requirements

A description of the major functions and data items follows. Some functions will be provided to the customers and others to the manager of the bookstore.


The system should manage information about books in the bookstore, inventory, (registered) users and books they have ordered. It should also store information about user opinions and book ratings. While you are working, keep in mind that these items are a minimal set of requirements.

Functionalities of the (web-based) system:

The following set of events and queries should be handled by the your system:
  1. Registration (5 points): a new user has to provide the appropriate information; he/she can pick a login-name and a password. The login name should be checked for uniqueness. Also, the user should provide all the other additional information.
  2. Searching and Ordering (5 points): After registration, a user can order one or more books. The total amount of the order is reported to him/her. A user may order multiple copies of a book, one or more times. 
  3. User record (5 points): upon user demand, the full record for a user should be printed:
  4. New Book (5 points): The store manager records the details of a new book, along with the number of copies that have arrived in the warehouse.
  5. Arrival of More Books (5 points): The store manager increases the appropriate counts.
  6. Feedback Recordings (5 points): Users can record their feedback for a book. The system should record the date, the numerical score (0= terrible, 10= masterpiece), and an optional short text. No changes are allowed; only one feedback per user per book is allowed.
  7. Usefulness ratings (5 points): Users can assess a feedback record, giving it a numerical score 0,1, or 2 ('useless', 'useful', 'very useful' respectively). A user should not be allowed to provide a usefulness-rating for his/her own feedbacks.
  8. Trust recordings (5 points): A user may declare zero or more other users as 'trusted' or 'not-trusted'.
  9. Book Browsing (15 points): Users may search for books, by asking conjunctive queries on the author, and/or title, and/or keywords, and/or subject. The system should allow the user to specify that the results are sorted (a) by year, or (b) by the average numerical score of the feedbacks, or (c) by the average numerical score of the trusted user feedbacks.
  10. Useful feedbacks (5 points): For a given book, a user could ask for the top n most 'useful' feedbacks. The value of n is user-specified (say, 5, or 10). The 'usefulness' of a feedback is its average 'usefulness' score.
  11. Buying suggestions (10 points): Similar to '', when a user orders a  book 'A', the system should provide a list of other suggested books. Book 'B' is suggested, if there exist a user 'X' that bought both 'A' and 'B'. The suggested books  should be sorted on decreasing sales count (i.e., most 'popular' first); count only sales to users that have bough both books, i.e. users like 'X'.
  12. Statistics (10 points): every semester, the store manager wants
  13. User awards (10 points): At random points in time, the store manager wants to give awards to the 'best' users; the manager hence needs to know

Rules of the Project

Implementation Tools
We will provide you with all the tools you need to complete this project. All queries to the database will be made via SQL statements. For this project, you are expected to be familiar with Java and SQL. Everything else will be explained for you.

The project is to be done in groups of 2 students.  The groups are  self-policing  (e.g. each group is responsible for its own division of labor, scheduling, etc.). NOTE: If an unreconcilable problem arises in your group, it is your responsibility to contact the professor as soon as possible. If you wait until the deadline, it will be too late.

Real world vs. COP4710
For practical reasons, you will not be implementing a commercially viable web site. Instead, this project focuses on some of the main aspects common to all such e-business sites. Some assumptions will be made that do not fit with reality, such as the fact that all members live in the United States. To avoid ambiguities, we will tell you what the facts of the project are, even though what we say may not work in the real world. One example of this is that we are not using secure connections for credit card transactions.

Overview of Project Phases

You will be provided with the infrastructure for this project. We have divided the project into 3 phases. You will be given detailed instructions and examples of all the utilities required for each phase.  However, you need to submit everything together after completing all the phases.

Phase I - Design

Initially, you must design and create a database that organizes and stores data about the books, members, and feedbacks. This includes creating tables to store this data, and designing rules for how these tables relate to each other so that the data they store can be combined in meaningful ways.  As part of the report for this phase, you will turn in an E-R diagram of your database design, and the translation to the relational schema. Also, you have to provide all the constraints and the SQL code that you used to enforce them (CHECK or ASSERTION clauses).

Phase II - API Implementation

In this phase, you should write the SQL queries and some Java or C++ code that implements an interface to your database and executes the queries. This part will be used in the next phase for the web interface.

Phase III - Web Site Extension

Finally, you will create a web portal to your database and add e-commerce functionality. Users will connect to the web site and will be able to  browse, order and rate books. Also, the store manager should be able to use his/her web interface to run decision support queries.

Due date

The due date is:   April 11, 2008.