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
employees.
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.