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
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
10. Your own scenario and SQL query that can show off your database
design.
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.