CS422 Final

FINAL
CS422, Summer 2006

Please upload all your files to CSNS by the end of the exam. Note that file uploading will be disabled automatically after 10:00PM, and late submissions will not be accepted. Before turn in your programs or scripts, please make sure that they are at least syntactically correct; otherwise there will be an automatic 50% credit deduction, no matter how "close" they seem to be correct.

[Schema] Consider the following database schema:

categories( id, name, parent_category_id )
products( id, name, description, price, quantity, category_id )
customers( id, first_name, last_name, address )
orders( id, customer_id, date_ordered, date_shipped )
order_details( order_id, product_id, quantity )

Scripts final-create.sql and final-clean.sql are provided for you to create or remove the schema in your Oracle database. You may want to populate the schema with more data for testing purposes, but note that you may not change the schema itself (table names, column names, column types etc.).

[SQL] Complete the following SQL queries:

Q1. (5pt) List the names of all the products ordered by the customer Joe Smith.
Q2. (5pt) List the monthly sales last year.
Q3. (10pt) List the names of the top three best-selling products of this year in terms of quantity sold.
Q4. (10pt) List the names of the products that belong to the Hardware category or its subcategories.

[Stored Procedures] (20pt) Implement a stored procedure which takes an category id as parameter and removes the category from the database. In particular, the procedure first checks whether the category has a parent category, and if not, the procedure outputs a message "Cannot remove the top-level category." and terminates; otherwise, the procedure performs the following operations (for clarity, we will refer to the category to be removed as C and its parent category as P):

assign P to be the category of the products that currently belong to C.
assign P to be the parent category of the categories that are currently under C.
remove C from the categories table.

[Triggers] Use triggers to enforces the following constraints:

(15pt) A customer may not change their contact information (first name, last name, and address) if he or she has an order that is not shipped yet.
(15pt) The price of the most expensive product in a category cannot be higher than twice the price of the second most expensive product in the same category.

[Client Application] (20pt) Write a Java program which looks up the database for product information. In particular, the program takes a search phrase from user input, and outputs the id, name, description, and price of the products whose names or descriptions contains the search phrase as a substring (case-insensitive). For example, if the search phrase is "intel", products with names or descriptions like "Intel P4 Motherboard" or "Microsoft IntelliMouse 2.0" should be in the results. Note that in order to prevent SQL injection attacks, you must use PreparedStatement or equivalents.