Academic Writing

Achieving Operational Excellence - Building a Relational Database for Inventory Management Background: Sylvester’s Bike Shop has stored its data in Excel for many years but has experienced some data quality issues. As you can see from the Excel file for this assignment, Sylvester’s Excel spreadsheet stores the company’s products and suppliers. Some of the data quality issues Sylvester’s has experienced are errors in supplier names and addresses that have resulted from these being repeated several times in the data. Changes to supplier addresses are not always made consistently. As well, Sylvester’s is planning to grow its product line dramatically over the next year and therefore feels that they should position themselves for this growth by moving to Access. One of the things they would like to be able to do is move to “Category Management,” in which one category manager is assigned to each product category and is responsible for managing that category to achieve specific performance goals. Initially, all they are interested in adding to the database is a way to add the salesperson name for each product category. The first step in your assignment is to design and build an Access database to house Sylvester’s current data. Use the information on database design in Chapter 6 of the textbook (“Designing Databases,” p. 186) as well as Chapter 2 in the Access e-text. As is always required in these kinds of projects, you will need to correct any data problems you find. Wherever there is inconsistent data, you should change the incorrect value to the one that is on the rest of the records for that supplier. Since you have not been given the Category Manager names, you can make these up for your database. The customer will have to update these once you give them the final database structure, so make sure that your database design makes this easy for them to do. Keep in mind as well that you do not want to retype all of this data. First, doing so will not be the best use of your time (and will be quite boring); second, it introduces the problem of data errors. You need to make sure that the data in the Access databases matches the Excel data (except for the obvious data inconsistencies). You can import the data directly from an Excel spreadsheet (Assignment 2 Source Data). The steps to do so are explained on pages 134-138 in the Access e-text (see the Learning Materials page if you haven't yet accessed the e-text) . The next step is to create queries and reports for each of the three reports requested on page 199 of the textbook. As well, you should create a query and a report that lists each product category with their salesperson and the products which they manage. This report should be sorted alphabetically by product category. Products in each category should be sorted by descending price. You do NOT need to answer the last question on page 199. Important notes about this assignment: The e-text authors advocate using Calculated Data Fields in Access 2010. While these do solve many of the problems that used to exist with calculated fields (because Access does the calculation automatically for you), for this assignment, do NOT use the Calculated Data Field functionality. You will find one database design for this assignment on the textbook companion website. Keep in mind that this is NOT necessarily the best solution, the data is not the same, and your assignment has an additional element not found in the textbook version. Therefore, while you might consider some elements of what they have done, you should create your OWN database design. Instructions: For this assignment you must hand in: an Access database with reports and queries as described in the textbook in Chapter 6, page 199