Achieving Operational Excellence: Building a Relational Database for Inventory M

Question

Achieving Operational Excellence: Building a Relational Database for Inventory Management

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. Using the information found in the tables in MyMISLab, build a simple relational database to manage information about Sylvester’s suppliers and products. Once you have built the database, perform the following activities.

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

• 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. For each supplier, the products should be sorted alphabetically.

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

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

Microsoft Access:

https://www.dropbox.com/s/5s2l40afmxnyv41/Data.accdb?dl=0

Cortar ‰ Reem plaza' Ascendente Selección. Descendent Avanzadas- Calibri pogt Ver Pegar GuardarRevisión ortográfica Xeliminar,mMás . SeleccionarAjustar al Cambiar formulario ventanas .copiar formato FiltrD rformato ride A. Quitar orden Alternar filtro todo . uscar Farmato de texto Product Numb Product Catego Product Nam T Purchase Cos Selling Pric, Supplier Numt , Quantity on Hai Reorder Le Haga clic para agregar T Product Table supplier Table Bluff Breaker 2252225225252555555224320 '7785210935134086455045310 1132223243414341433455550 9 am, >and 7 2 5 5 4 4 8 5 9 8 1 5 5 5 5 69 5 3 4 4 8 1 3 4 $ eeBB GRRTBBB Hy Hy Hy Lei Lei Lei Le Ch h h h R R R Lei Lei Mt 12345678901 -2345678901

Explanation / Answer

Creating relational database:

create database inventoryMgmt;

quit;

Creating table in same:

CREATE TABLE Product

(

ID int(11) NOT NULL auto_increment,

ProductNumber int(11) NOT NULL,

ProductCategory varchar(100),

ProductName varchar(100),

PurchaseCost varchar(100),

SellingPrice varchar(100),

SupplierNumber int,

Quantity int(11),

ReorderLevel int(11),

Primary Key (ID)

);

CREATE TABLE SUPPLIER

(

ID int(11) NOT NULL auto_increment,

SupplierNumber int(11) NOT NULL,

CompanyName varchar(100),

StreetAddress varchar(100),

City varchar(100),

State varchar(100),

ZipCode int(7),

Primary Key (ID)

);

Required reports:

1. Limit will help us to select particular count of rows in desc order.

SELECT PurchaseCost, Quantity, ((SellingPrice-PurchaseCost)/PurchaseCost)*100 as MarkupPerc FROM Product ORDER BY PurchaseCost DESC LIMIT 5

2.

SELECT s.Creating relational database:

create database inventoryMgmt;

quit;

Creating table in same:

CREATE TABLE Product

(

ID int(11) NOT NULL auto_increment,

ProductNumber int(11) NOT NULL,

ProductCategory varchar(100),

ProductName varchar(100),

PurchaseCost varchar(100),

SellingPrice varchar(100),

SupplierNumber int,

Quantity int(11),

ReorderLevel int(11),

Primary Key (ID)

);

CREATE TABLE SUPPLIER

(

ID int(11) NOT NULL auto_increment,

SupplierNumber int(11) NOT NULL,

CompanyName varchar(100),

StreetAddress varchar(100),

City varchar(100),

State varchar(100),

ZipCode int(7),

Primary Key (ID)

);

Required reports:

1. Limit will help us to select particular count of rows in desc order.

SELECT PurchaseCost, Quantity, ((SellingPrice-PurchaseCost)/PurchaseCost)*100 as MarkupPerc FROM Product ORDER BY PurchaseCost DESC LIMIT 5;

2. Select all required fields based on supplier number in required order.

SELECT s.CompanyName, s.StreetAddress, s.City, s.State, s.ZipCode, p.ProductCategory, p.ProductName, p.Quantity, p.ReorderLevel from Product as p, SUPPLIER as s where s.SupplierNumber = p.SupplierNumber order by s.CompanyName ASC, p.ProductName ASC;

3. Fetch all records with quantity less than 5

SELECT p.ProductCategory, p.ProductName, p.Quantity, p.ReorderLevel, s.CompanyName, s.StreetAddress, s.City, s.State, s.ZipCode from Product as p, SUPPLIER as s where s.SupplierNumber = p.SupplierNumber and p.Quantity < 5

4. Enhancement can be done by adding table regarding Customer which contains details like customer, items purchased, quantity, daily customers etc. Report regarding Customer graph can be usefull. It can contain detail slike day vice or month vice values which will help in understanding bussiness graph correctly.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.