INFORMATION RESOURCE MANAGEMENT
ASSIGNMENT-01

• 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.

QUERY USED:

SELECT TOP 5
[Product Table].[Product Category],
[Product Table].[Product Name],
[Product Table].[Purchase Cost],
[Product Table].[Selling Price],
[Product Table].[Quantity on Hand],
ROUND((
([Product Table].[Selling Price]-[Product Table].[Purchase Cost])
/[Product Table].[Purchase Cost] * 100)
)
as Markup_Percentage
FROM [Product Table]
ORDER BY [Product Table].[Selling Price] DESC;

REPORT: Expensive Bicycles

• Prepare a report that lists each supplier, its products, 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.

QUERY USED:

SELECT
[Supplier Table].[Company Name],
[Product Table].[Product Name],
[Product Table].[Quantity on Hand],
[Product Table].[Reorder Level]
FROM [Supplier Table] INNER JOIN [Product Table]
ON [Product Table].[Supplier Number]=[Supplier Table].[Supplier Number];

REPORT: Product & Supplier Info

• 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 items identified.

QUERY USED:

SELECT
[Product Table].[Product Number],
[Product Table].[Product Name],
[Product Table].[Supplier Number],
[Supplier Table].[Company Name],
[Supplier Table].[Street Address],
[Supplier Table].[City],
[Supplier Table].[State],
[Supplier Table].[ZIP Code]
FROM [Product Table] INNER JOIN [Supplier Table]
ON [Product Table].[Supplier Number] = [Supplier Table].[Supplier Number]
WHERE [Product Table].[Quantity on Hand] < [Product Table].[Reorder Level];

REPORT: Reorder Product and Supplier Information

• Write a brief description of how the database could be enhanced to further improve management of the business. What are the fields to be added?
What additional reports would be useful?

Enhancing the Database:

* Number of Units Sold could be calculated on a periodical basis (monthly). * This helps the business to decide what products sell. Based on which they could decide if the product should be reordered, if so how much should be reordered. This helps the business to maintain the optimized Inventory; reducing the inventory costs. This also gives a bargaining edge for the business.

* The fields to be added are: * Units sold * Date of sales

* Additional useful reports would be: * Product wise report – Report on which product has sold how much units on what date and the supplier of that product. * Monthly report – The sales report for the month stating the highest selling products of the store.