CSC443
Database
Management
Spring 2006
Major Project #2
Assigned: April 7
Due:May 5
This assignment could be done in group of at most 2 students. Each
group
should turn in one copy of the project.
To turn in the project: Email the project to me at pepper@adelphi.edu
Design a database for a customer, product, order, with order detail
as
separate entity set. The order must
have a customer and date. The customer includes basic information:
customer
number, name, street, town, post-code, current limit, current balance.
The
product includes product code, description, product category, listed
price,
quantity on hand, reorder level, reorder quantity, and anything else.
The order
includes order no, order date, customer number. A detailed order could
include
product code, order quantity, and total price for the order. (Note:
Your
product should be a product that interests you. You can use sqlldr to
import
your parts list if you want.)
Design an E-R diagram first and then convert it into tables. Create your own data for all tables and implement the database including the following queries in Oracle SQL.
For extra credit, include a java menu to execute
the
queries.
The files turned in include
1) Project Description
2) E-R diagram and tables converted from the diagram.
3) An Oracle database file with all tables and initial values
implemented.
4) A text file containing all the SQL representations of the queries,
the
results of queries, and simple explanations.
Queries to be implemented:
1) List the names of all customers.
2) If the quantity on hand falls below the reorder level then we want
to make a
new batch of the amount indicated by reorderquantity. This query
tells us
which products need to be replenished and how many should be made.
3) List the names of all customers who ordered a particular product in
a
particular month (and you can hard code one product and month right
into your
query)
4) List the towns where the average credit limit is more than $1000.
5) For each product, list the product's lowest and highest ever order
line price. (order line price is the quantity * the price)
6) On what days in a particular month did the sum of the value of all
orders
taken on that day exceed $1000?
7) For each product ordered in a particular month, list the product
code and
the number of customers who ordered that product.
8) What product costs the most to reorder? The cost to remake a product
is its
list price times the quantity to reorder.
9) On what date did we get our most valuable order ever? The value of
an order
is indicated by its order price times its order quantity.
10) List the names of customers who did not order in a certain year.
11) List the names of all customers who have made orders on a certain
date.
12) List the product code and description of those products that have
at some
time been sold below list price.
13) List any customers that have never ordered.
14) List the product code and description of all those products that
have been ordered in a particular month and year.
15) List the product descriptions and customer names for all orders
made by
customers in a particular zip code.
For example (results are omitted here):
Query 1:
SQL
---
SELECT cust_name
FROM CUSTOMERS
EXPLANATION
-----------
List the names of all customers
by selecting all customers in the CUSTOMERS table.
Query 6:
SQL
---
SELECT order_date
FROM ORDERS, ORDER_DETAILS
WHERE ORDERS.order_no = ORDER_DETAILS.order_no and
order_date > '30-may-91' and
order_date <
'01-july-91'
GROUP BY order_date
HAVING SUM(order_qty * order_price) > 1000
EXPLANATION
-----------
On what days in June 1991 did the sum of the value of all orders
taken on that day exceed $1000?
The where clause does a join and checks that the order date
was in June 1991. The phrase "value of all orders" indicates
order_qty * order_price. Checking the sum for "that day"
indicates that we need GROUP BY... HAVING...