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 to help you with your design.
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 can introduce inadvertant data errors when the new data is entered. 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
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.
Important notes about this assignment:
- 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.
Part-2
Instructions:
For this assignment you must hand in:
- an Access database with reports and queries as described in the textbook in Chapter 6, page 199This assignment builds on your first two Access assignments and asks you to consider the process of designing and implementing a database system solution.
The first step in this assignment is to create a systems analysis report as outlined on page 434 of the textbook. This is a critical step in the systems development process as it helps you to define the requirements for the work you will go on to do. Add a section called "Project Management Considerations" to this report; in this section, explain the project management considerations that the organization must take into account if they go ahead with this project.
You do NOT have to create any data input forms for this assignment. You do NOT have to consider whether or not to use Ace's existing customer database.
The next step in the assignment is to create the database suggested in your report. Note that one of the requirements here is that it must align with the report you have created - that is, it satisfies all of the requirements noted, but only the requirements noted.
After the database is created, populate each table with at least ten records.
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
For this assignment, you must hand in:
• a Word document containing your systems analysis report which must include the information specified on page 434
• an Access database with tables, reports, and queries as described in the textbook and in these instructions
Database Design
Your database must conform to the relational database guidelines as outlined in Chapter 6 of the textbook and in the Access e-text.
• Tables are structured correctly with appropriate keys.
• Tables are related correctly.
• Database has minimum number of records in each table as specified in assignment instructions.
• Database has all test data as specified in assignment instructions.
Reports and Queries
• Queries and reports identified in the system analysis report have been created and include the questions managers are considering based on the mini-case on pages 433-434.
• Queries are correct.
• Reports are professional looking and accurate.
Systems Analysis Report
• understandable (clearly defines the solution), professionally written, and organized
• comprehensive
• project management considerations are appropriate and draw on the theory explained in Chapter 14 of the textbook