IST 210: Organization of Data
Fall 2008
Proj #1 (DUE: Sep. 30, 9:45AM)
Last Updated: Mon Sep 8 15:35:02 EDT 2008
______________________________________________________________________
NOTE: Team Assignment (also available in ANGEL)
Your IST 210 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.
In a series of 3 projects, you are asked 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 customer "John Doe" searches 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 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, in proj #1, you
need 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.
If possible, try to use and demonstrate different kinds of
relationships (e.g., unary, binary, ternary), cardinalities/modalities
(1-1, 1-n, n-n), different kinds of data types as your attributes
(e.g., strings, integers, or even picture, etc.), and special types
(e.g., weak entity, intersection data), etc. Try to demonstrate your
understanding.
Consult the PBL 3.1 AFV in class, where various queries that the given
design is trying to to answer. In your scenario, you should go through
the similar thinking process, and make sure that your design is
*expressive* enough to handle all of the above 10 scenarios.
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: Turn-In of Proj #1 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
- List all assumptions that you made
- E/R diagram
- For each of 10 scenarios, explain how your design would be able to
handle the scenario
- 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.