The Phase II Report must contain the goals of this phase of the project. It must contain the Phase I Report and must describe any revisions made to the specification described in the Phase I Report. It must further describe the problems encountered in Phase II and justify the solutions. It must contain all documentation produced in Phase II.

The Phase II Report will be graded out of 20 points total, counting as 20% of your final project grade.
 

**Note:  For Phases 2 and 3, you will not be responsible for designing/implementing code for tasks that are outside of the domain of the Allegro Music Store.  Specifically, the following areas constitute tasks that are not part of the front-end system:

•  Shipments from the vendor to the store/customer
• Processing orders at the vendor's location
• Generating/processing restocking orders (e.g. don't worry about "receiving" new stock for restocking purposes)
• Billing the customer for special orders (we don't care how the billing occurs, just as long as the amount of purchase ends up in the database)

However, you will be responsible for making sure customer orders and sales are reflected in the database system.  You can make the assumption that a customer order will automatically initiate a sale.  E.g., when a customer places a special order, assume that the order will be delivered to them (we don't care how) and that the store collects the customer's payment (again, we don't care how).  This means that both the order and sales portions of the system will be updated.
 

Specifically, the following is expected:
 

1.  Outline the goals for this Phase and briefly mention any revisions made to the previous Phase.

2. (8 points) Hand in a copy of Phase 1 -- either the original or a copy.  If you`ve modified your ER diagram, turn in the modified copy.
IMPORTANT: Make sure that the ER-diagram is correct --- e.g. check with your TA!

    First section of report:

• Illustrate how you translated from the ER diagram to your relational schema.  It should follow the ER to relational algorithm.
• Show each attribute's data representation: e.g. each column's data type
• Primary keys and foreign keys should be correctly identified
• Show constraints (in words) over and above referential integrity contraints for each table.  E.g., "a customer cannot have 2 orders in one day" is a constraint on the order table and must be checked before an order can be generated.

3. (8 points) Next: Show SQL for creating tables (ONLY the table creation SQL).  You should write SQL scripts as mentioned in class that will create and populate each table.  Each table is required to have at least 5 tuples.  Also, keep in mind the relationships between tables and plan your data accordingly.  You should be able to run these scripts successfully in SQLPlus with no errors and no integrity violations.

4. (4 points) Last section:

• Identify 8 (non-trivial) tasks from your tasks (e.g. application areas) of Phase 1.  The tasks that you choose should be distinct from each other.  E.g. you should NOT have 5 of your tasks be all 5 of the sales reports.
• Your tasks should be broken down into their associated subtasks.  E.g. Selling a music item requires putting sales information into the sales table AND decrementing the music in stock that was just sold.
• For each task, identify which tables are affected by that task and its subtasks, and show explicit operations (e.g. insert, delete, or modify) that are performed on tables.  Example: To generate an order, first you must gather order information - customer, names/IDs of music to order, employee placing the order, etc.  Next, check to make sure customer has not already placed an order that day.  Next, information must be inserted into the appropriate tables:  insert into Order_MusicTable[orderID, musicID, quantity]; insert into OrdersTable[cust_name, employee_name, date, orderID, totalquantity, ...), etc.  Denote any contraints that must be met before actions are performed on the table(s).
• Note: You do not have to implement the SQL or C code for your tasks yet; that will be done for phase 3.

6.  Identify any difficulties you may have had with this phase of the project.

Any questions/concerns should be addressed to me or to the newsgroup:

cat@cc.gatech.edu  -or- news:git.cc.class.cs4400a