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.