IST210 Proj #3: Due Apr. 28, 2:30PM

Part 1: Clean-Up & Data Population

• If there are any errors (that I pointed out) in Proj#2, fix them.
  
• For your finalized schema, populate data accordingly. Populate at least 10 tuples per table (every table should have 10 meaningful tuples for Proj #3). Pay special attention to foreign keys.

Part 2: Scenarios & SQL Implementations

• In your application domain, think of THREE plausible scenarios each of which can consist of 4-6 (or more) steps. For instance, for online bookstore management domain, your first scenario could be "(1) a user John Doe logs in, (2) searches books with this and that conditions, (3) purchases the book X for $xx.xx with the help of an employee Y, and (4) comes back next day for a return and picks up another book Z". Then, each step may require 1-2 SQL queries to implement. Also, note that to answer the step (4), you should have stored the transaction using the SQL INSERT or UPDATE statements after the step (3) in DB.
• User log-in can be simply simulated by showing the proper user name and password exist in the USER table (e.g., If the query "SELECT COUNT(*) FROM User WHERE logid='jdoe' AND pwd='xxxx'" returns non-zero number, then you may assume the user has proper account and logged in). Be creative !
• The more plausible and challenging your scenarios are, the higher score you will get. You may (or may not) use 10 queries that you have prepared in Proj#1 and #2. However, if I suggested any queries for your Proj#1, try to use them for demonstration.
  
• At the end, your THREE scenarios should have demonstrated ALL of the following features. For each missing feature, you will lose points. Therefore, explicitly specify which of the following features each step of the scenarios is demonstrating. It is ok for one step to demonstrate multiple features at the same time (just say so like "Scenario #2, Step (2) demonstrates F5 and F7").
  
• F1: Non-trivial Boolean logic in WHERE clause that uses combinations of AND, OR, and NOT
  
• F2: Sub-queries
• F3: Inner Join with at least three tables
  
• F4: Outer Join
• F5: Aggregates
• F6: Set operators (one of Union, Intersect, and Except)
  
• F7: Group-By + Having + Where clause together
  
• F8: Insert
• F9: Update
• F10: Delete

Part 3: Final Report & Presentation Slide (10 min)

1. Cover page: List (1) project title, (2) project team name (i.e., Team A), and (3) member names
2. Part 1: FINAL highlevel description of your project (1 paragraph)
3. Part 2: FINAL E/R diagram
4. Part 3: FINAL assumptions that you made
5. Part 4: FINAL relational schema of all tables. If you didn't lose any points in Proj#2, just copy them over. Otherwise, fix them and mention what has been changed since Proj #2
   
6. Part 4: THREE scenario descriptions
   
7. Part 5: For each of three scenarios, show the implementation of each step in SQL and its results (i.e., screen-shot of both SQL and answers) after you run it in the Microsoft SQL Server 
8. Part 6: Short description of WHO did WHAT role in your team (all members must participate in the project)