Assignment 4

Due date: Thursday, April 2, 11:59PM.
NOTES:
This assignment is meant to give you practice with JDBC, Oracle and Oracle system catalog tables.

Project description

In this project you will develop a Java Command Line project using the Order Management (OM) database. You will develop a menu based Java application with the following features:
  1. The menu is always present on the screen. After each execution, with or without errors, you will display the menu so that user can select another option.
  2. Each item in the menu is preceded by a number from 0 to 9, follow by a description.
  3. The menu has the following items:
    1. Create OM database
    2. Insert records in table [user specified table]
    3. Delete all records table from table [user specified table]
    4. Find user by [user specified criteria]
    5. Drop all tables
    6. Exit
  4. Item (1),(5) and (6) are self-explanatory.
  5. For (2) and (3), your program will display another menu with the tables in the database. The tables are dynamically retrieved from Oracle system catalog (You need to study how to retrieve them.) Do not hardcode them in your source code. You will not get any points if you do so. For example, if the user selects item 2, then your program will display another menu, such as:
    1. customers
    2. items
    3. orders
    4. order_details
    5. cancel
    the user needs to select one of these items. Once the user selects the table then you will get the insert statements for that table from the file and insert them and execute them from your java program. After each execution, you report to the user whether the execution of the SQL statements was successful or not. You will also give the number of records affected. If an error is encountered you report it to the user, and your program will continue to run.
  6. For item (4), you will show to the user the columns of the table customers in the form of the menu similar to the one above. Again, the columns are dynamically retrieved from the catalog. For example, if the user selects the column city, then you will prompt the user to input the city. You will use a simple SELECT * FROM customer WHERE city LIKE '% user_specfied_value %' to retrieve the records. You proceed similarly for the rest of the attributes, with the exception of customer_id which is an integer.
Get SQL script from here.

Deliverables

  1. the Java project.
  2. a document with clear instruction of to run the project.
  3. each of you will do a 10 minutes demo with the TA during the Lab.
Upload the document and java source code in blackboard.