IST 511: Information and Technology

                      Fall 2008

                Proj #2 (DUE: Oct. 29)

       Last Updated: Mon Oct  1 08:28:52 EDT 2008

____________________________________________________________

Your second team project will be to build a substantial database
application for a real-world domain. You will design a schema on
paper, and then you will create an actual database using Microsoft SQL
Server provided by IST Infrastructure. You will populate the database
on your own, write interactive queries and modifications on the
database, create programs that manipulate the database.

The goal of Proj #2 is to design a database-backed application for the
"GoodBuy" store, a local retailer of consumer electronics, that hopes
to outweigh the BestBuy someday. As of 2008, there are 10 store
branches of GoogBuy at Pennsylvania.

Your database design should be able to handle the following scenarios:


1. A user wants to search for "Samsung" HDTVs with arbitrary
conditions (eg, model, price, company, feature). The same search
should be supported for other consumer electronics that GoodBuy
carries.

2. "John Doe" also likes to find out the store hour and address of a
store that carries certain computer games.

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.

4. All copies of "Norah Jones"'s CDs go on sale with 50% discount,
effective from 1/1/2008 for a week.

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.

6. GoodBuy selects the best employee (and her immediate manager) per
store who has helped the most number of customers in the transactions
during October 2008.

7. A top-level manager "Hank Foley" likes to know the number of items
and the total amount that the customer "John Doe" has purchased during
2008 (GoodBuy has a hierarchy of managers where each employee must
report to one immediate manager, except CEO "Graham Spanier").

8. "Hank Foley" also likes to have the quarterly report showing the
average sales of each electronics category (e.g., TV-Video, Audio,
Camera, Computer, Music, Movies, Games) per year for the last 5 years.

9. "Mary Jane" wants to list all pending orders that have NOT been
delivered for more than 2 weeks since the purchase. 

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.


Note that in the above descriptions, any quoted names such as "John
Doe" and "Samsung" or variables such as X, Y, Z can be replaced by
any other arbitrary names.

______________________________________________________________________

Part 1: E/R Design

With the above scenarios and reasonable assumptions, each team first
needs to design an E/R diagram.

Draw an E/R diagram using MS Word or Visio or any drawling tool (do
NOT hand-write). List all your assumptions for things that are NOT
explicitly specified or ambiguous (e.g., all items can be associated
with some discounts or some employees are not manager and thus have no
subordinates to manage, etc.)  Otherwise, we will grade your design
based on the common sense, and you may lose points if ours and yours
are different. Don't forget to specify key attributes by * symbol for
each entities. Also, specify cardinalities and modalities for all
relationships.

You goal is to design a good E/R that supports all 10 scenarios.  If
your design cannot support some scenarios since you don't have proper
entities/relationships or your cardinality/modality is not sufficient
enough, then you will lose points. Some E/R design, even though all 10
scenarios can be supported, may have a lot of unnecessary or redundant
components in it or the support of a scenario is not very efficient
(e.g., if one has to use 7-8 entities to support a single scenario,
then in practice, this will result in a lot of JOIN operations and may
take a while to process it). Therefore, I'll also consider how "good"
your E/R design is as well. In short, the main grading rubrics are:

- Support of 10 scenarios   : 50%
- Goodness of an E/R design : 30%
- Misc (report, assumption) : 20%

______________________________________________________________________

Part 2: Schema/Data Population

Once your E/R design is done, then, next step is to convert the
E/R design into real tables, attributes and tuples. According to
tutorial, together with team members, you need to execute the
following tasks for each table:

- Attribute name, data type, and nullable or not 

- Determine a Primary Key (PK): note that primary key attribute
can't be null

- Pay extra attention to those "Relationship" -- depending on
whether they are unary/binary/ternary or cardinality/modality,
the resultant table schema may vary.

- Specify every Foreign Key (FK) and Referential integrity
constraint (ie, delete-cascade, delete-restrict, or delete-null
policy).

- For each table, populate 5 tuples.

- For "TWO" selected tables, show the screen-shots of contents of
table so that we can check the data population has succeeded or
not.

- Show screen-shot of all tables using SQL Server's Query Editor
feature. Note that all tables must be properly connected via
foreign keys. If any tables are isolated, your schema population
was not done correctly.

______________________________________________________________________

Part 3: SQL

Once your data are populated, then, final step is to prepare SQL
queries that can execute above 10 scenarios. Some scenario may need a
single SQL query while others may need a series of SQL queries. For
each scenario, you need to show your queries and returned results.

______________________________________________________________________

Part 2: Turn-In of Proj #2 Report

Your report should consist of at least the following (for Proj #2,
unlike Proj #1, you do NOT need to write an academic style article):

- On the cover page, list project team name (e.g., Team-A), and all
  member names
- List all assumptions that you made
- E/R diagram
- Example of 3 tables and tuples
- SQL queries for 10 scenarios and corresponding answers returned
- Short description of WHO did WHAT role in your team (all members
  must participate in the project)

Do NOT make your report lengthy unnecessarily -- be succinct and
get to the point. The length of your report has nothing to do
with the score that you are getting.

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.

TURN-IN: Only *ONE* person from each team drops the report to
ANGEL once AND give me a hard-copy in class.