• Step 1: Database Design: Each team is required to select a topic for their database project. The project should be comparable to the Student Registration System that you have read (Chapter 3). Imagine that you are developing the application for real!

  Some topics that I come up with:

  • Information System for Supplied Chain Supermarket (e.g. Walmart) [taken - Group 7]
  • Information System for Autopart Store (e.g. Auto Checker) [taken - Group 3]
  • Book Store Information System (e.g. Barnes and Noble) [taken - Group 4]
  • Hotel Management System (e.g. Hilton Hotel) [taken - Group 2]
  • Airline Reservation System (e.g. Southwest) [taken - Group 5]
  • Information System for a Telephone Company (e.g. Qwest)
  • Banking System for a Bank (e.g. Wellsfargo) [taken - Group 1]
  • Geographical Information System (e.g. Mapquest)
  • Human Resource Management System (e.g. University HMR)
  • Information System for Transportation Company (e.g. FedEx)
  • Information System for Airtraffic Control at an Airport (e.g. El Paso Airport)

  Requirement

  Each team is required to write up a report detailing the design of its database system. The report should include:

  1. System Analyst: Write in plain English
     1. what are the requirements for the system?
     2. what are the feeatures that the system can offeer?
     3. who are the intended users of the system?
     4. what can a user do?
     5. what benefits are there for using your system?
     6. what are the ten often asked questions in your application?

     Requirements from the literature are acceptable.

     Do not forget the assumptions you have made when design the database.

     For example: I assume that the library has only one copy of each book when I design a database for the library. Furthermore, I assume that each library item has an ISBN. Therefore, I use ISBN as the key of the entity named 'LibraryItem';

  2. An E/R diagram with constraints. Clearly identify
     1. Entity sets, each their attributes and key(s)
     2. Relationships , each with its attributes, the set of entity sets involved in it, and its multiplicity

  3. Translation of the E/R diagram into relational schema.
  4. How did the ten most often asked questions affect the design of database?

• Step 2: Relational Database Design

  For this step, you need to include in your report the SQL/Oracle command that you use to create the tables in your database.

• Step 3: Creating your dababase application with JDBC

  Develop a JAVA application based on the examples (in /oracle/app/oracle/9.2.0.1.0/sqlj/demo) that comes with Oracle. It will be sufficient to have a stand alone application that

  1. accommodates all of the intended the users,
  2. allows the updating the database information, and
  3. provides an interface for query answering.

  It is hoped that you come up with a system that is user friendly (menu driven, GUI etc.).

The directory /oracle/app/oracle/9.2.0.1.0/sqlj/demo on pilsner contains a number of examples written in JAVA or SQLJ that can help you in the development of your application. In particular, you can create a table, insert a row, delete a row, update the information in the database. In fact, all you need to do is to develop an interface, that glues these components together.

A simplest way to learn how to It is suggested that you copy all the files into your directory and following the README file.

In order to compile and run the examples, you have to set the environment variables correctly. You can do so by run the command:

source /oracle/conf/cshrc

after rlogin (or ssh) to pilsner.