Management information system

This assignment has three deliverables to submit to Blackboard (described below): ERD, Access Database, and Inventory Report (PDF or Word Document)

Business problem: Speedy Parts, Inc. is an auto parts retail chain with three locations in Farifax (called Location 1, 2, and 3).  Each location manages its inventory independently, and the President of the company wants to improve inventory visibility across all locations.  The company needs a centralized product catalog and inventory database.

Database concept: You are to design a database that contains a product catalog and identifies the inventory of parts at each location.  Design your database in the 3^rd Normal Form (3NF).  I provide a spreadsheet with worksheets containing the catalog and inventory at each site.

Part 1: Create a Chen Diagram to document an Entity Relationship Diagram.  Define your entities, attributes, and relationships between them in an ERD.

Part 2: Create a MS Access Database.  Add the tables, fields, primary & foreign keys, and create relationships between entities as depicted in your ERD. Populate your database with the data from the provided “Access Assignment 1 Data” Excel File.  You may also choose to import the given Excel file into your database to automate the Table creation and data entry.

Create a Query (using Query Wizard) to list all the products at each location and how many are in-stock.

• Use a Simple Query
• Show the Part Number, Part Name, and Quantity available from each location
• Create a new field in your query called “Total Stock.” Use the Expression Builder (labeled Build in the right-click menu of the field) to add the quantities of the 3 locations together.
  • Something like this-Total Stock: [Location 1 Stock]+[Location 2 Stock]+[Location 3 Stock]

Create a Report to show the part inventory (using the Report Wizard) based on your query above:

• Show the part number and name.
• Show the inventory for all locations
• Example Header:

Export your Report as a PDF or Word compatible document.

Submit all three files to Blackboard: ERD, Access Database, and Report.