Project 30: Inventory data. The question has been asked in the pictures below.
i chegg.com c Chegg eReader Iv Search keywords a Q m u. 3: km 2. Use normalization to ensure the correctness of the tables (relations). -_ Km 3. Create the database using a personal DBMS package (preferably Microsoft Access). % Ef JOBML 4. Use the DBMS package to create the basic report in Figure AVK.5. QM ASSIGNMENT Assignment Numb\" 5. You may not be able to develop a report that looks exactly like the one in Figure AYK.5. However, your report should include the same information. 6. Complete personnel information is tracked by another database. For this application, include only the minimum: employee number, last name, and first name. 7. Information concerning all projects, employees, and jobs is not readily available. You should create information for several fictitious projects, employees, and jobs to include in your database. Proiect 30: Inventory Data An independent retailer of mobile entertainment and wireless phones, iToys.com has built its busi- ness 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. AYK.18 Apply Your Knowledge ('\\ _ Avms of513 V i chegg.com c Chegg eReader Iv Search keywords a (a m u. z: % E 1. Develop an ERD diagram before you begin to create the database. You will need to use the (MA 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 (l.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 com- plete 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: Vou will want to create a query for this first). d. Each product ordered by category. 7. Here are some additional business rules to assist you in completing this task: a. An order must have at least one product but can contain more than one product. b. A product can have one or more orders but need not have any orders, 6. A product must belong to one and only one category, but a category may contain many products. it A product can only be stocked by one supplier, but a supplier can provide more than one product. 9. Asupplier will use one type of shipping method, but shipping methods can be used by more than one supplier. Data File: AVK30_Data.xlsx 6 Mme of513 V
