Assignment 2: Achieving Operational Excellence - Building a Relational Database for Inventory Management

Assignment 2 is worth 10 percent of your final grade for this course. This assignment is based on the Hands-on MIS projectAchieving Operational Excellence: Building a Relational Database for Inventory Managementonpage 199of the textbook. However, it has been revised, so first read the assignment scenario in the text and then read the following carefully.

Background:

Sylvesters 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, Sylvesters Excel spreadsheet stores the companys products and suppliers. Some of the data quality issues Sylvesters 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, Sylvesters 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 Sylvesters 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 theLearning Materialspage 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

OR

• if you do not have access to a computer on which you can install Microsoft Access, you may use another database tool. If you do this, you must also submit the following:
  • an E-R diagram of your database that clearly shows the primary and foreign keys for each table.
  • a screen shot of the first page of the contents of each table. Alternatively, you may create a report that dumps the contents of a table and submit a single page of that report.
  • queries that you designed to create each report. Show both the query design and the query output.
  • the reports required in the assignment

Marking criteria: You will be marked according to the following criteria.

Database DesignYour database must conform to the relational database guidelines as outlined in the Supplemental Information for Access Assignments and in Chapter 6.	20 marks
	tables are correct with appropriate keys (12 marks) tables are related correctly (3) inconsistent data has been corrected (2) table and field names are descriptive and use CamelCase notation (3)
Reports and Queries	20 marks
	reports are correct (12 marks) queries are correct (4) reports are descriptive, professional looking, and readable (4)

When your assignment is complete, return to this page and follow the instructions for submitting it.

All of your course work must be submitted before 12:00 midnight (Mountain Time) on the final day of your contract.

To account for administration, processing, and marking times, please allow up to eight business days from submission for the return of your marked assignment.

Submitting your assignments

• Use theEdit these files OR Upload filesbutton below to upload your completed assignment file(s).
• Remember to click theSave changesbutton after uploading files
• Remember to click theSend for markingbutton after you upload your assignment file(s).
• The maximum upload file size is 10MB
• You can upload a maximum of 5 files