CS 561   Spring 2004.

Mini-Project 1.

Assigned: Thursday, 22th January 2004

Due: Thursday, 12th Feb. 2004 (at start of class)

Maximum: 100pts.

Collaboration: This project can be done in teams of two; or if requested and discussed with the instructor also on an individual basis. If done in a team, every student in the team must however make sure to learn all aspects of the project, ranging from design decisions, to implementation, and developing the project documentation.

Project Description

The last E-Commerce company in existance 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 widget market by using an object-relational database in their back end, which her research shows, is much cooler to use than a traditional relational databases. The on-line store, called www.TheBestThereIs.com is an on-line catalog system that manages everything about widgets, but it could also easily manage other items such as say automobiles or books or movies.

Obviously, the main requirements for this contract are to use an object-relational database engine (preferably use Oracle 9i at WPI) as your DBMS server, to make use of as many object-oriented modeling features as are supported by your 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 will be dead forever.

Note: This mini project is intended to have everyone in the class have some hands-on experience with at least one database tool beyond just the pure relational data model.
It will likely also help you to get a head start on your course project (for those of you that have not used JDBC before) or at least to brush up on your previous knowledge (should you have used ORACLE and JDBC before).

Database Development

First, you need to design a schema for the www.TheBestThereIs.com. You are free to select your favorite products for this catalog. You may even choose to ignore your boss's requirements and choose a completely different domain. Don't worry you will be paid the same. The main requirements is that you are making use of at least 5 distinct OO features, including features such as for example abstract data types, user-defined types, objects, nested tables, methods, complex types, inheritance, etc.

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

  1. A list of at least six alternative queries and/or updates against your www.TheBestThereIs.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 CEO who will check up on you).
  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 of 5 you could start with are given below, but please do design your own favorite and 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 0. 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 [products] of [brand type] that were manufactured by a given company [ XXXX ] 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 and remove it from your database.
  5. Update a complex object to now have new 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 actual website is being concurrently developed by equally high paid contractors so the CEO is not expecting anything fancy in the way of interface. For example, a 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. Also, please include the file you have used to load your data. (15pts)
  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. (15pts)
  3. Three, you also should provide a pure relational schema definition of this same database, 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 in general. In addition, point to your own schemas and queries above for concrete examples 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. Which one are you going to try and convince your employer to put into production? (20pts)
  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, you 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.
  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 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).