IST 210: Organization of Data

 	                  Fall 2008

                 Proj #3 (DUE: Dec. 9, 9:45AM)

           Last Updated: Mon Nov 3 09:12:28 EDT 2008


Part 1. Fix-up and more data population

Fix up all errors that were pointed out in your proj #2. Revise your
ER design and relational schema.

Next, generate more tuples for following tables:

1. Employee: at least 20 different employees (at least 5 employees per
store) including a top-level manager, mid-level managers, and regular

2. Transaction: during 2007 and 2008, for each quarter, populate at
least 5 transactions (total at least 40 transactions for eight
quarters). Also, make sure to have at least 1 return transaction per
each quarter.

3. Product: at least 5 products per product type.

4. By going over scenarios below, populate all necessary tuples. For
instance, the scenario #2 below mentions the game "Wii". Then, your
product table must have a product about Wii (in "Game" category).

Part 2. SQL

Write SQL queries for the following ten 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.

1. Retrieve the model #, brand, price of all TVs with HD features and
cost less than $1,000.

2. Find out the store-hour and address of a store that sells computer
games "Wii".

3. 10 copies of "Leona Lewis"'s CD "Bleeding Love" arrive from the
distribution warehouse Y to the store located at "220 S. Atherton" on
"1/1/2008", yielding 20 copies in stock. (Note that you need to use
INSERT statements to support this scenario).

4. How many copies of "Norah Jones"'s CD (that's on sale with more
than 5% discount) does the store located at "220 S. Atherton"
currently have?

5. "John Doe" purchases 2 copies of "Wii" Game Y for $54.50 each (with
20% discount) and 3 copies of game Z for $23.00 each (with 30%
discount). The employee "Mary Jane" helped "John Doe" to process the
purchase. The 10 digit transaction number for the purchase is
assigned. (Note that again you need to use INSERT statements to
support this scenario).

6. Identify the names of best employee (and her immediate manager) per
each store who has helped the most number of customers during October
2008. Whether items are returned or not, if an employee helps during
the transaction, it is counted as one help. The answers would look
something like this:

StoreID    BestEmployee    ManagerName   Num
100        Mary Jane       Hank Foley    13
200        Lee Kim         John Yen       5
300        John Smith      Mike McNeese  25  

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

Year    NumItems    TotalAmount
2005    3           $230
2006    5           $450
2007    1           $20
2008    3           $1120

8. Generate a quarterly report showing the total sales of each
electronics category (e.g., 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 TotamSales should include $50 (actual sale price),
not $100 (original price). The final answers would look like:

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

9. Find out all pending orders that have NOT been delivered for more
than 2 weeks since the purchase date.

10. "John Doe" purchases a Blue Ray Player X for $1000.50 with the
help of an employee "Mary Jane", and comes back next day for a return
and picks up another player Z with the help of an employee "Jane
Smith". Original purchase and subsequent return are treated as a
separate transaction and thus different transaction numbers are
assigned. Return after 30 days of original purchase is refused. (Use
INSERT or UPDATE statements).

Part 3. PHP-based Web Interface

For the above scenarios (except those require INSERT), provide a
PHP-based web interface using the template given in class. Tutorial on
PHP and web interface to Database will be given on ** Nov. 11 **.

Part 4: Presentation Slide

Each team prepares 10-minute presentation of Proj #1 -- #3 in
powerpoint or keynote. The presentation should have live demo of 2-3
scenarios using SQL Server.

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
- SQL for all 10 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.