IST 210: Organization of Data

 	                  Spring 2009

                 Proj #3 (DUE: Apr. 28, 1PM)

           Last Updated: Fri Apr 3 09:12:28 EDT 2009


Part 1. Fix-up and more data population

Fix up all errors that were pointed out in your proj #2. If needed,
revise your ER design and relational schema. Then, generate at least
**10 tuples** per each table. Make sure there are enough variety of
tuples for a few tables. For instance, for scenario #8 or #9 below,
there should be enough number/variation of transaction tuples and
product tuples, respectively. Your populated tuples should be able to
demonstrate the correctness of your SQL queries.

Part 2. SQL

Write SQL queries for the following scenarios. If you can write a
single SQL statement that captures the scenario entirely, then that's
great.  If you can't, otherwise, try to break the scenario into
several steps, and write a SQL statement for each step.

Again, note that all quoted values like "Kim" or "Foo" can be replaced
by proper values in your own project scenario. Feel free to change
them to fit your own setting.

1. A first-time customer "Kim" first registers her information and
obtains ID/PWD. Next, she comes back and logs in again. (This scenario
can be supported by INSERT statement for registration and SELECT
statement for log-in)

2. At the warehouse "W1", retrieve all products that were delivered
from at least two suppliers after "1/1/2009". Show product name,
quantity, delivery date, and supplier name.

3. For all products that have old and new versions, retrieve those
whose prices have changed but the product name remains intact.
Retrieve the name of product, old price, and new price.

4. How many copies of the product whose name starts with "AB" (that's
on sale with more than 5% discount) does the warehouse located at "220
S. Atherton" currently have? Retrieve the product name, price,
discount rate, and its # of copies in stock.

5. A customer "Kim" purchases 2 copies of a product "Foo" for $54.50
each (with 20% discount) and 3 copies of a product "Bar" for $23.00
each (with 30% discount) in a transaction on "1/1/2009" (Use INSERT
statement to support this scenario).

6. For each product that "Kim" purchased AND returned, retrieve which
supplier supplied the product. Retrieve the name of the product, the
name of supplier, and delivery date from the supplier.

7. For all products that belong to the category "X", the special
promotion of 50% discount lasts from "1/1/2009" to "1/31/2009" (use
UPDATE statement to support this scenario).

8. For each year, find out the number of products (not the number of
transactions) and the total amount of those products that the customer
"Kim" has purchased so far. If products were purchased and returned,
then show those # of returns but those returned items must NOT be
included in total amount. Assume that TotalAmount only consider the
original prices of products (ignoring the discount).  The answers
would look like:

Year    NumItems    NumReturns   TotalAmount
2005    30          10           $230
2006    50          2            $450
2007    11          3            $20
2008    13          1            $1120

So, for 2005, for instance, "Kim" has purchased 30 different products,
and returned 10 of those, resulting in a total spending of $230.

9. Generate a quarterly report showing the total sales of each product
category (e.g., for electronics product, TV-Video, Audio, Music, Game)
per year for 2007 and 2008. (HINT: use datepart() function in SQL to
get the quarter information of a date). Again, total sales should NOT
include those products returned. Furthermore, total sales should use
only the actual sale price, not the original price of products. For
instance, if a product "X" originally costs $100 but was sold for $50
with a 50% discount, then the TotalSales should include $50 (actual
sale price), not $100 (original price). The final answers would look

Year   Quarter   Category  TotalSales
2007   1         TV-Video  $1.5M
2007   1         Audio     $1.2M
2007   1         ...
2007   2         TV-Video  $2.5M
2007   2         Audio     $0.2M
2007   2         ...
2008   4         TV-Video  $1.4M
2008   4         Audio     $1.12M
2008   4         Music     $3.2M
2008   4         Game      $2.1M

10. Your own scenario and SQL query that can show off your database

Part 3. PHP-based Web Interface

For the above scenarios (except those require INSERT or UPDATE),
provide a PHP-based web interface using the template given in
class. Three tutorials on PHP and web interface to Database will be
given by TA in April.

Part 4: Presentation Slide

Each team prepares 20-minute presentation of Proj #1 -- #3 in
powerpoint or keynote. The presentation should have live demo of **3**
scenarios using SQL Server that demonstrate each team's proj best.

Part 5: Proj #3 Report

Your report should consist of at least the following:

- On the cover page, list project team name (e.g., Team-A), and all
  member names
- FIXED ER and/or relational schema (if any)
- SQL for all scenarios
- One screen shot of web interface
- Detailed log of team activities like:

Date    Absent Members       Activity
11/3    John Doe             brainstorming
11/4    All present          initial SQL sketch

Part 6: Turn-In of Slide and Report

Do ** BOTH **:

1. Drop both report and slide to ANGEL drop-box.
2. Turn in a hard-copy of the report (but not slide) to me.

If your project team has some problems (eg, some members don't
come to project meetings), you need to inform me early so that I
can intervene. Otherwise, all team members share the identical
scores for the projects.