Chapter 6 Lab - lab assignment PDF

Preview

Summary

Lab 6-10: Achieving Operational Excellence: Building a Relational Database for Inventory Management (Individual Assignment)Resources needed:1. A windows PC with Microsoft Access installed. 2. Store and Regional Sales Database file.Scenario: In this exercise, you will use database software to design ...

Description

IS 340 MANAGEMENT INFORMATION SYSTEMS CHAPTER 6 LAB

Lab 6-10: Achieving Operational Excellence: Building a Relational Database for Inventory Management (Individual Assignment) Resources needed: 1. A windows PC with Microsoft Access installed. 2. Store and Regional Sales Database file. Scenario: In this exercise, you will use database software to design a database for managing inventory for a small business. Sylvester’s Bike Shop, located in San Francisco, California, sells road, mountain, hybrid, leisure, and children’s bicycles. Currently, Sylvester’s purchases bikes from three suppliers but plans to add new suppliers in the near future. The Relational Database has already been created for you and is the file named ESS13ch06_Student. Below are the basic design of some of the tables and how one might start to look at how to design tables and how the will relate to each other. Proposed Data Warehouse Format and Sample Files Proposed Data Warehouse Format Product _ID

Product_ Description

Cost_per _ Unit

Units_ Sold

Sales_Region Division

Customer_ID

The following are sample files from the two systems that would supply the data for the data warehouse: Mechanical Parts Division Sales System Prod_No Product_ Cost_per_Unit Description 60231 4” Steel 5.28 bearing 85773 SS assembly 12.45 unit Corporate Sales System Product_ID Product_ Description 60231 Bearing, 4” 85773 SS assembly unit

Units_Sold

Customer_ID

900,245

Sales_Regio n N.E.

992,111

M.W

Kelly Industries

Sales_ Territory Northeast Midwest

Division

Unit_Cost

Units_Sold

5.28 12.45

900,245 992,111

MS 340 MANAGEMENT INFORMATION SYSTEMS (2020) PATEL, NAISHA

Anderson

Parts Parts

1

IS 340 MANAGEMENT INFORMATION SYSTEMS CHAPTER 6 LAB

Deliverables: perform the following activities. To see the relationships right-click on “Bicycles Low in Stock” and choose “Design View”. Manipulated the boxes for each table so that you can see the entire columns in each table. Provide the following for each table (10 Points for each table): Bicycles Low in Stock: 

Place a Screen shot showing each table and their relationships here:



What is the Primary Key for: o Product Table ID o Supplier Table ID No



What is the foreign Key and what table has the foreign key? Product table has the foreign key and its Product Number.

MS 340 MANAGEMENT INFORMATION SYSTEMS (2020) PATEL, NAISHA

IS 340 MANAGEMENT INFORMATION SYSTEMS CHAPTER 6 LAB

MS 340 MANAGEMENT INFORMATION SYSTEMS (2020) PATEL, NAISHA

IS 340 MANAGEMENT INFORMATION SYSTEMS CHAPTER 6 LAB

MS 340 MANAGEMENT INFORMATION SYSTEMS (2020) PATEL, NAISHA

IS 340 MANAGEMENT INFORMATION SYSTEMS CHAPTER 6 LAB

Do the same for the other 3 tables. Create the following reports: 

Prepare a report that identifies the five most expensive bicycles. The report should list the bicycles in descending order from most expensive to least expensive, the quantity on hand for each, and the markup percentage for each. (10 Points)

MS 340 MANAGEMENT INFORMATION SYSTEMS (2020) PATEL, NAISHA

IS 340 MANAGEMENT INFORMATION SYSTEMS CHAPTER 6 LAB 

Prepare a report that lists each supplier, its products, the quantities on hand, and associated reorder levels. The report should be sorted alphabetically by supplier. Within each supplier category, the products should be sorted alphabetically. (20 Points)

MS 340 MANAGEMENT INFORMATION SYSTEMS (2020) PATEL, NAISHA

IS 340 MANAGEMENT INFORMATION SYSTEMS CHAPTER 6 LAB



Prepare a report listing only the bicycles that are low in stock and need to be reordered. The report should provide supplier information for the identified items. (10 Points)



Write a brief description of how the database could be enhanced to improve management of the business further. What tables or fields should be added? What additional reports would be useful? (20 Points)

Database contains multiple tables and columns which may include several different fields. Every table has different fields similar to the information stored the table. To enhance the business, they can improve the information’s quality and stability. Give business more possible capacity to analyze data in many ways. Promoting discipline approach to data management can make company work effective. Table suppliers more information about suppliers can be added. Suppliers Contact information, supplier’s website and email address can be added. They should have additional reports about most popular bikes, profits made which shows business success.

MS 340 MANAGEMENT INFORMATION SYSTEMS (2020) PATEL, NAISHA...