CS 561 Spring 2007.
Assigned: Thursday, 18th January 2007
Thursday, 8th Feb. 2007 (at start of class)
This project is to be done either
in teams of two, or on an individual basis.
The students are encouraged to discuss the
projects with each other, as they develop them,
and to share the problems they encounter as well as solutions
related to both modeling and technology.
But do not copy designs or code from each other!
project is done in a team, every student in the team
would have to
assure to learn and be actively involved all
aspects of the project, ranging
from design decisions, to implementation, evaluation, and
development of the project documentation.
The last internet startup company on this planet has just hired you as a consultant (it's your lucky day).
are willing to pay you the big $$$ to build their new online product store.
fact, the CEO claims the entire company's future depends on this store. She
they will take over the online entertainment market by using an
object-relational database in their back end, which her research shows, is
much more powerful to use than a traditional relational databases. The on-line
www.TheBestEver.com, or whatever else name you pick,
is an on-line catalog system that manages
everything about your favorite entertainment items, be it
be it movies, interactive video games, independent films, art,
or any other gadgets of your own choosing.
The main requirements for this contract are to use an
database engine (preferably use Oracle 10g at WPI)
as your DBMS server, to make use of as
many object-oriented modeling features as are supported
by your chosen database server,
and preferably to use Java and JDBC for the application development of
your store. If you do not do so, they will
not pay you a dime and E-Commerce's future may be at risk.
This mini project is intended to have
everyone in the class
have a little hands-on experience
with a DBMS tool beyond just
the pure relational DBMS servers.
It may also help you to
get a head start on your course
or maybe to brush up on your previous knowledge
(should you decide to use ORACLE and/or JDBC
for your project).
First, you need to design a schema
for the www.TheBestEver.com. You are free
to select your favorite products for this catalog.
The main requirements is that you are making use of
as many as possible (at least 6 distinct) OO features.
Aim to make use of OO features in as much
as possible, including
abstract data types, constructed types,
nested tables, user-defined types, objects, object-identifiers,
references to objects, methods, inheritance, and
The more different ones you use, the more they will pay you.
Second, you should develop some very basic application support for
Namely, your program should perform a continuous
loop in which:
- A list of 6 alternative queries and/or updates
against your www.TheBestEver.com
database are offered to
the user in the form of some simple menu.
One of those alternatives should be quit.
Another one of those alternatives should be a free SQL-like
query for the advanced user (such as the future high-school
kids looking for their special deals).
- The user selects an alternative.
- The system prompts the user for appropriate input values.
- The system accesses the database to perform the appropriate
queries and/or modifications.
- Data or an appropriate acknowledgment is returned to the
You should include both queries and modification statements.
Ideas for possible queries in the mix you could start
with are given below, but please do design your own
more interesting ones. Please
make sure the queries will actually produce meaningful results
for your chosen data set, i.e. the
result size won't be zero. The database used should be of reasonable size,
i.e., contain at least 50 tuples.
For above, items that have square brackets around them
denote actual parameters.
- Insert this particular [product information]
into the catalog as an "object".
- Give me a listing of all [movies] of [horror] that
were manufactured by a given company [ XXXX ] and that
have at least 3 actors in them, sorted by last name.
- Look up the widget with the [cheapest price]
that is of [this] category that also was made by company [ zzz. ].
- Purchase a given item that is a complex nested object composed
of several parts, including this small video,
and remove it from your database.
- Update a complex object to now have new subcomponents and properties.
- Give me a listing of all [products] of [brand type] that
share at least 5 common attributes.
Note, that the website is being
concurrently developed by a highly qualified contractor so the CEO is not
anything fancy in the way of interface.
In fact, a simple menu printed via System.out.println is perfectly
Also, handling of SQL errors can be quite simple.
Either write an sqlerror
routine that just prints the error message from Oracle or copy
the error handler from a sample program (of course you should
acknowledge this just as you would acknowledge any other
use of someone else's work in your homework or project!!!).
What to Turn in: (They all must be turned in on paper,
unless otherwise noted as being an electronic turnin.)
You should hand in your object-relational schema definition for your
database, complete with a detailed design explanation,
a UML or an ER diagram,
and the SQL schema definition statements.
Make it a fancy, complete and interesting one excercising the
various OO features.
Also, please include the file you have used to load your data.
Two, you should turn in a file containing a list of queries that
are embedded in your java code. State in English the meaning of each
query. Indicate whether it is runnable in the code.
Three, you also should provide a pure relational schema
definition of this same database content,
a detailed design explanation, a UML or ER diagram,
and the SQL schema definition statements.
Four, you should also provide the equivalent queries
from above now directly expressed on your pure relational schema.
Five, list the differences between pure relational and object-relational
For this, point to your own two versions of schemas and queries
above for concrete examples of each of those features.
You should provide some discussion of the object-oriented
features that your first design includes and why and when
those may be chosen over the pure relational design.
Compare and contrast your two designs in terms of their
relative advantages and disadvantages.
Finally, after this experience, which of
your two solutions are you more proud off, and
you are planning to convince your
employer to put into production?
Six, you should turn in a DEMO-SCRIPT.txt file
showing a test drive (script) of
your application program
using the object-relational store,
meaning you pick a point from an application and
drive through it until you reach another point. The purpose of a test
drive is to review some of the scenarios and identify the input fields
(e.g. menu prompt for the input), any exceptions and flow, along the
ride. Your code should be able to allow some users interaction
via your command line support.
Please make sure to display the actual SQL statements being generated
and executed, so that your script is self-explanatory.
Seven, you should provide a link where your source code can be downloaded (.zip
file is preferred).
If you do not have webspace anywhere (but you should at wpi),
you also can submit the code on a CD.
Also include any SQL scripts used to populate or create the database.
Include a README.txt that will explain how to build and run your program.
A description of what does and does not work must also be provided.
Your java program and embedded SQL statements must
be documented with appropriate comments to practice good software
engineering. Some portion of the score will be given for
Resources you may want to consult
First read up on the OO
Features of Oracle.
(Note that each commercial DBMS out there has their own restrictions
and idiosyncrocies on how they employ OO principles into their relational
engine. So don't go directly by our text book chapter or any of the
other readings on OO, but consult the above description of OO for Oracle
or go to the Oracle website directly for more info on the latest
version of their ORDB features.)
Then you should review the basic
which contains specifications, full examples,
and connection and driver information for the local oracle version
at WPI. This information should be sufficient for getting this
homework accomplished. The above document is essentially
a version of the Oracle JDBC Introduction produced at
Stanford University localized for WPI. A thank you goes to them.
Other useful sources of information are
Sun's Basic JDBC Tutorial and
Sun's JDBC API Documentation.
A good on-line interactive SQL tutorial web site is here.
Oracle Technology Network:
Useful comprehensive technical information on Oracle. It is free
but you need to register first.
You eed to download and set up the Oracle JDBC on your
The JDBC driver for ORACLE can be downloaded
as noted in the URL above at
JDBC Instructions URL.
However, they could also better be downloaded
from the ORACLE web
site at "Oracle
Download PAGES to get ORACLE software such as JDBC drivers" .
Note that on this page, you need to click on DOWNLOADS under
SOFTWARE and then you need to register with ORACLE OTN before
selecting the JDBC DRIVERS option from the right menu.
Once registered as OTN member, you can also grab the JDBC driver directly
JDBC Driver Download page" (Note that this page require you to register
as an OTN member).