CS 561   Spring 2007.

ORDB Project.

Assigned: Thursday, 18th January 2007

Due: Thursday, 8th Feb. 2007 (at start of class)

Maximum: 100pts.

Collaboration: 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!

If the 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.

Project Description

The last internet startup company on this planet has just hired you as a consultant (it's your lucky day). They are willing to pay you the big $$$ to build their new online product store. In fact, the CEO claims the entire company's future depends on this store. She says 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 store, called 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 object-relational 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.

Note: 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 project or maybe to brush up on your previous knowledge (should you decide to use ORACLE and/or JDBC for your project).

Database Development

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 collection hierarchies. The more different ones you use, the more they will pay you.

Second, you should develop some very basic application support for www.TheBestEver.com. Namely, your program should perform a continuous loop in which:

  1. 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. 
    2. 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).
  2. The user selects an alternative.
  3. The system prompts the user for appropriate input values.
  4. The system accesses the database to perform the appropriate queries and/or modifications.
  5. Data or an appropriate acknowledgment is returned to the user.

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.

  1. Insert this particular [product information] into the catalog as an "object".
  2. 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.
  3. Look up the widget with the [cheapest price] that is of [this] category that also was made by company [ zzz. ].
  4. Purchase a given item that is a complex nested object composed of several parts, including this small video, and remove it from your database.
  5. Update a complex object to now have new subcomponents and properties.
  6. Give me a listing of all [products] of [brand type] that share at least 5 common attributes.
  7. Quit.
For above, items that have square brackets around them denote actual parameters.

Note, that the website is being concurrently developed by a highly qualified contractor so the CEO is not expecting anything fancy in the way of interface. In fact, a simple menu printed via System.out.println is perfectly fine. 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.)

  1. 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. (20pts)
  2. 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. (20pts)
  3. Three, you also should provide a pure relational schema definition of this same database content, again including a detailed design explanation, a UML or ER diagram, and the SQL schema definition statements. (10pts)
  4. Four, you should also provide the equivalent queries from above now directly expressed on your pure relational schema. (10pts)
  5. Five, list the differences between pure relational and object-relational solutions. 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? (10pts)
  6. 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. (10pts)
  7. 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 proper documentation. (20pts)

Resources you may want to consult

  1. 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.)
  2. Then you should review the basic Oracle JDBC Introduction, 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.
  3. Other useful sources of information are Sun's Basic JDBC Tutorial and Sun's JDBC API Documentation.
  4. A good on-line interactive SQL tutorial web site is here.
  5. 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 machine. 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 from " ;Oracle JDBC Driver Download page" (Note that this page require you to register as an OTN member).