Project #3: Due => Dec. 18, 9:45AM

Part 1: Clean-Up & Data Population

• If there are any errors in Proj#2, fix them.
  
• For your finalized schema, populate data accordingly. Populate at least 10 tuples per table. Pay special attention to foreign keys.

Part 2: Scenarios & SQL Implementations

• In your application domain, think of THREE plausible scenarios each of which consists of 4-6 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 after the step (3) in DB.
  
• 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, you SHOULD use 1-2 queries that I have suggested as part of steps.
  
• 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 AND, OR, and NOT
  
• F2: Sub-queries
• F3: Inner Join
  
• 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

1. Cover page: List (1) project title, (2) project team name (i.e., A-I), 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
   
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 SQL Server 
8. Part 6: Short description of WHO did WHAT role in your team (all members must participate in the project)

Part 4: Turn-In

1. Like before, the captain of each team drops the copy at ANGEL, and the rest of members drops a "blank" one at ANGEL.