Your Name: ______
Section: 4540/5540 (circle one)
AIS Spring 2007
MidTerm Exam
200 points
1.Using the information below, draw an REA diagram on the paper provided. Make sure to note any assumptions made to answer the question. Assumptions cannot alter the business context and must make business sense. (100 points)
Donzi boats markets and sells speedboats around the world to discriminating customers. Recently Donzi added a web site where owners and other Donzi enthusiasts can purchase accessories and other Donzi memorabilia. Because some owners and enthusiasts may be in a location where they do not have access to the internet, they can also call an 800 number to place their orders. Online orders are entered directly into the computer system by the customer, while calls are taken by a telephone representative who enters the order into the computer system for the customer. Upon order entry the customer’s credit card is immediately charged, and an order notification is sent to Donzi’s fulfillment department where the items are retrieved, packed and shipped to the customer. Partial shipments are only sent if an out-of-stock item will take longer than 5 business days to be received from the supplier. Either way the customer is notified by the fulfillment department regarding when to expect their goods to arrive.
Donzi maintains a 20 day supply of most inventory items. Whenever popular items which are kept in stock fall below this, an automatic order is placed with the appropriate Donzi supplier. Donzi suppliers batch orders and bill and ship for all orders on a given day. (one bill and one shipment for all orders that day from Donzi) If an item is not in stock, all other items are shipped anyway and the back ordered item is shipped as soon as it is available. Incoming shipments are received (counted and sent to inventory) daily by Donzi’s receiving department. The accounts payable department batches and electronically pays any accounts due on their due date to take advantage of any purchase discounts. (one payment per day per supplier for any accounts payable due).
Using the data model provided, answer the following questions using SQL. Please note any assumptions made to answer the questions. Assumptions cannot alter the business context and must make business sense.
2a.List total sales by customer in USD? (20 points)
SELECT Sales.[Customer#], Customer.[Last Name], Sum(inventory.[list price]*[sales-inventory].[quantity sold]) AS SumOfSalesAmount
FROM Customer, Sales, [sales-inventory], inventory
WHERE Customer.[Customer#]=Sales.[Customer#] And sales.[invoice#]=[sales-inventory].[invoice#] And [sales-inventory].[product#]=inventory.[product#]
GROUP BY Sales.[Customer#], Customer.[Last Name];
2b.List products in order of their quantity sold in descending order? (20 points)
Query Name: Products in Order of Qty Sold Desc
SELECT Inventory.[Product#], inventory.description, Sum([sales-inventory].[quantity sold]) AS QuantitySold
FROM [sales-inventory], inventory
WHERE [sales-inventory].[product#]=inventory.[product#]
GROUP BY Inventory.[Product#], inventory.description
ORDER BY Sum([sales-inventory].[quantity sold]) DESC;
2c.List customers who have placed orders with us during March of 2005? (20 points)
Query Name: Customers who Placed March 2005 Orders
SELECT customer.[first name], customer.[last name]
FROM customer, [take order]
WHERE customer.[customer#]=[take order].[customer#] And [take order].date Between #3/1/2005# And #3/31/2005#;
2d.List accounts receivable in USD by customer in descending order?(40 points)
Calculating Total Sales by Customer (2a)
Query Name: Total Sales by Customer in USD
SELECT Sales.[Customer#], Customer.[Last Name], Sum(inventory.[list price]*[sales-inventory].[quantity sold]) AS SumOfSalesAmount
FROM Customer, Sales, [sales-inventory], inventory
WHERE Customer.[Customer#]=Sales.[Customer#] And sales.[invoice#]=[sales-inventory].[invoice#] And [sales-inventory].[product#]=inventory.[product#]
GROUP BY Sales.[Customer#], Customer.[Last Name];
Calculating Cash Receipts by Customer
Query Name: Cash Receipts by Customer
SELECT customer.[customer#], Sum([Sales-Receive Cash].[Amount Applied Invoice]) AS AmountReceived
FROM [Sales-Receive Cash], sales, customer
WHERE [Sales-Receive Cash].[invoice#]=sales.[invoice#] And sales.[customer#]=customer.[customer#]
GROUP BY customer.[customer#];
Calculating AR
SELECT [total sales by customer in USD].[customer#], [total sales by customer in USD].SumofSalesAmount, [Cash Receipts by Customer].AmountReceived, sum(([total sales by customer in USD].SumofSalesAmount)-([Cash Receipts by Customer].AmountReceived)) AS AccountsReceivable
FROM [total sales by customer in USD] LEFT JOIN [cash receipts by customer] ON [total sales by customer in USD].[customer#]=[Cash Receipts by Customer].[customer#]
GROUP BY [total sales by customer in USD].[customer#], [total sales by customer in USD].SumofSalesAmount, [Cash Receipts by Customer].AmountReceived;
1