CS 651 Second Merge Assignment

Due July 25, 2000

Our merger is progressing well. However, some consultants perceive that it is inefficient to maintain multiple databases and that a single data source would be the best solution. Therefore, all the data will be merged into a single set of tables and the reports will be generated from this source. All the data can be placed into one person's tablespace and shared. Do not use existing

tables. Do not change existing data in case you need to go back and get the original data. Create new tables and load existing data into these tables. Watch out for duplicates!!

Rumor has it that there are errors in some of the data which will result in inconsistencies and duplicates. Make reasonable assumptions as to which records are duplicates and list all assumptions and business rules.

1. Write a report listing our set of customers and the number of times they have placed an order.

2. We need to examine the current orders. Write an order report showing the orders placed over the past two years, grouped by customer. Include order number, order date, ship date, part numbers, quote price, number ordered, cost of the ordered amount of parts. Include a title, report date, subtotals showing total for each of the orders and the total for all orders per customer.

3. Assuming the salespersons remain with their customers. Write a report showing each sales rep, followed by a list of the sales rep's customers and the total amount spent per customer. Find the total amount for each sales rep.