CSc 8711. Databases and the Web
Spring 2011, Programming Project 2 (Multi-Dimensional Database Search Engine)
Due: 20 February, 2011

Consider the database schema available at dbSchema.sql and a sample meta data for college data available at collegeMetaData.sql. This database stores information about a large number of entities in a "fact" table. Each entity is described by a number of properties and the information about these properties are also recorded in the database.

Build a Web-based system in Java/JSP/Servlets and Oracle database that will allow users to search the fact table data using multiple criteria. The user should also be able to bookmark popular searches. Each user will sign in (through a simple sign-in screen) and perform searches in the database. They should have the ability to "bookmark" a particular search that they would like to return to in a later session.

The search interface should have 3 separate sections: A bookmarks section that displays users bookmarks, a workspace section where at any time 3 properties are displayed for the user to choose from, and a results section where the results of a search are displayed. The results section may be a separate window.

• The bookmarks section should display each bookmark as a hyper-link. Upon clicking the bookmark, the corresponding selections should be displayed in the workspace area (starting with the first 3 properties). A "Remove" button/link placed next to the hyper-link would allow the user to delete a bookmark.
• The workspace section should have 3 properties listed followed by a "Get Results" button, a "Start Over" button, a "Next Page" button, a "Previous Page" button, and a "Bookmark" button.
• The results area should display the hyper-links of "entities" that satisfy the search conditions. These results should be displayed only when the user clicks the "Get Results" button in the workspace.

Consolidated college data is available at collegeData.sql

Besides the "Search Interface" each of you will build a data entry page for the fact table. In addition to showing the fields to be collected from the user, this form should also request name of the fact table. Using this data entry page, each of you will generate 10 rows in the fact table for an automobile database. The automobile meta data is available at autoMetaData.sql. We will accumulate all these facts and generate a large enough data set. For the autoID field, so that we do not get duplicates, please number them A1000, A1001,..., B1000, B1001, ... etc where the starting letter will depend on your position in the class roster (i.e. person first in the class roster will use A1000, A1001, etc, the next person B1000, etc and so on). Since we have only 20 registered students we should not run out of letters!. Please place this data file (containing at least 10 SQL insert statements) in your public_html directory under file name data.sql.

To enable Java JSP/Servlet access on tinman, please execute the following commands as soon as possible on tinman:

chmod 711 ~
mkdir ~/webapps
mkdir ~/webapps/WEB-INF
mkdir ~/webapps/WEB-INF/classes
mkdir ~/webapps/WEB-INF/lib
mkdir ~/webapps/WEB-INF/src
chmod -R 711 ~/webapps
chmod 700 ~/webapps/WEB-INF/src

Simple Java program to access Oracle database on tinman

Oracle 11g JDBC Drivers: ojdbc6.jar and ojdbc6_g.jar.