Prepare a report that lists each supplier, its products, the quantities on hand, and associated reorder levels.

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_ Description Cost_per_Unit Units_Sold Sales_Region Customer_ID
60231 4” Steel bearing 5.28 900,245 N.E. Anderson
85773 SS assembly unit 12.45 992,111 M.W Kelly Industries

 

Corporate Sales System

Product_ID Product_ Description Unit_Cost Units_Sold Sales_

Territory

Division
60231 Bearing, 4” 5.28 900,245 Northeast Parts
85773 SS assembly unit 12.45 992,111 Midwest Parts

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___________

o Supplier Table____________

· What is the foreign Key and what table has the foreign key?___________________

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)

· 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)

· 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)

find the cost of your paper

set up and solve a case-study example of the light-intensity distribution in a photochemical reactor.

Photochemical reactor modeling: a case-study problem. Although radiation is important in heat transfer, an analogous model can be used in the design of photochemical reactors. The modeling of these reactors….

Write a critique on this technique of secondary-emission measurement.

Secondary-emission measurement: a case-study problem. An indirect way of measuring of secondary emission from ponds or large bodies of water used in waste treatment is to measure the concentration and….

set up a mass transfer model and evaluate the variation of the local mass transfer coefficient at various locations in the plate.

Chemical vapor deposition (CVD) on an inclined susceptor: a case-study problem. An important application of convective mass transfer theory is in CVD processes employed to coat surfaces with thin films….