An independent retailer of mobile entertainment and wireless phones, iToys.com has built its business on offering...

 

Transcribed Image Text:

An independent retailer of mobile entertainment and wireless phones, iToys.com has built its business on offering the widest selection, expert advice, and outstanding customer service. However, iToys.com does not use a formal, consistent inventory tracking system. Periodically, an iToys.com employee visually checks to see what items are in stock. Although iToys.com does try to keep a certain level of each top seller in stock, the lack of a formal inventory tracking system has led to overstocking some items and understocking other items. On occasion, a customer will request a hot item, and it is only then that iToys.com realizes that the item is out of stock. If an item is not available, iToys.com risks losing a customer to a competitor. Lately, iToys.com has become concerned with its inventory management methods. The owner of iToys.com, Dan Connolly, wants to manage his inventory better. The company receives orders by mail, by telephone, or through its website. Regardless of how the orders are received, Dan needs a database to automate the inventory checking and ordering process. Dan has provided you with a simplified version of the company's current system (an Excel workbook) for recording inventory and orders in an Excel spreadsheet data file, AYK30_Data.xlsx. 1. Develop an ERD diagram before you begin to create the database. You will need to use the information provided here as well as the data given in the Excel workbook. 2. Create the database using a personal DBMS package (preferably Microsoft Access) that will track items (i.e., products), orders, order details, categories, suppliers, and shipping methods. 3. In addition to what is already mentioned the database needs to track the inventory levels for each product, according to a reorder level and lead time. 4. At this time, Dan does not need to store information about the customer; he simply needs you to focus on the inventory structure. 5. Develop a query that will display the products that need to be ordered from their supplier. To complete this, you will want to compare a reorder level with how many units are in stock. 6. Develop several reports that display: a. Each product ordered by its supplier. The report should include the product name, quantity on hand, and reorder level. b. Each supplier ordered by shipping method. c. Each product that requires more than five days lead time. (Hint: You will want to create a query for this first). d. Each product ordered by category. An independent retailer of mobile entertainment and wireless phones, iToys.com has built its business on offering the widest selection, expert advice, and outstanding customer service. However, iToys.com does not use a formal, consistent inventory tracking system. Periodically, an iToys.com employee visually checks to see what items are in stock. Although iToys.com does try to keep a certain level of each top seller in stock, the lack of a formal inventory tracking system has led to overstocking some items and understocking other items. On occasion, a customer will request a hot item, and it is only then that iToys.com realizes that the item is out of stock. If an item is not available, iToys.com risks losing a customer to a competitor. Lately, iToys.com has become concerned with its inventory management methods. The owner of iToys.com, Dan Connolly, wants to manage his inventory better. The company receives orders by mail, by telephone, or through its website. Regardless of how the orders are received, Dan needs a database to automate the inventory checking and ordering process. Dan has provided you with a simplified version of the company's current system (an Excel workbook) for recording inventory and orders in an Excel spreadsheet data file, AYK30_Data.xlsx. 1. Develop an ERD diagram before you begin to create the database. You will need to use the information provided here as well as the data given in the Excel workbook. 2. Create the database using a personal DBMS package (preferably Microsoft Access) that will track items (i.e., products), orders, order details, categories, suppliers, and shipping methods. 3. In addition to what is already mentioned the database needs to track the inventory levels for each product, according to a reorder level and lead time. 4. At this time, Dan does not need to store information about the customer; he simply needs you to focus on the inventory structure. 5. Develop a query that will display the products that need to be ordered from their supplier. To complete this, you will want to compare a reorder level with how many units are in stock. 6. Develop several reports that display: a. Each product ordered by its supplier. The report should include the product name, quantity on hand, and reorder level. b. Each supplier ordered by shipping method. c. Each product that requires more than five days lead time. (Hint: You will want to create a query for this first). d. Each product ordered by category.

Answer rating: 100% (QA)

Step 1 of 6 A Develop an entityrelationship diagram using provided data which include order table pr... View the full answer