COP4710 - Introduction To Database Systems
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. http://www.barnesandnoble.com/
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.
- Book Data: An ISBN for each book, title, author(s), year of publication,
edition, number of copies in inventory, price, format (Paperback, Hardcover,
on cassette or CD), keywords, and subject. You can add additional information
if you want.
- Customer Data: For each registered customer, you need to maintain:
his/her full name, login name, password, major credit card number, address,
phone number, books he/she has ordered and information regarding the order.
- Opinions: Users can provide 'feedback' for a book, as a score (1-10)
along with optional short text. Users can also rate other users' feedback
as 'useless', 'useful', 'very useful'; finally, they are allowed to declare
other users as 'trusted' or 'not-trusted'.
Functionalities of the (web-based)
The following set of events and queries should be handled by the your system:
- 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
- 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.
- User record (5 points): upon user demand, the full record for a
user should be printed:
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.
Arrival of More Books (5 points): The store manager increases the
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.
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.
Trust recordings (5 points): A user may declare zero or more other
users as 'trusted' or 'not-trusted'.
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.
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'
Buying suggestions (10 points): Similar to 'amazon.com',
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'.
Statistics (10 points): every semester, the store manager wants
- all his/her personal data
- the full history of sales (book title, number of copies, date)
- his/her full history of feedbacks
- the list of all the feedbacks he/she ranked with usefulness
- the login names of the 'trusted' and 'not-trusted' users.
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
- the list of m (say m=5) most popular books (in terms of
copies sold in this semester),
- the list of m most popular authors and
- the list of m most popular subjects
- the m most 'trusted' users (the trust score of a user is the count
of users 'trusting' him/her, minus the count of users 'not-trusting' him/her)
- the m most 'useful' users (the usefulness score of a user is the
average 'usefulness' of all of his/her feedbacks combined)
Rules of the Project
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
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.
The due date is: April 11, 2008.