Case Study – Oracle Database

Data Architecture to store inventory data for transaction and reporting

The goal of this project is to create a database for a grocery store to facilitate inventory management. The database will track inventory stock, re-stocking levels, vendor list for products and status of purchase orders.

This is a prototype of a clean database with clearly defined entity attributes and entity relationship.

The project will include forms and reports to both enter information in the database and to allow reporting.

The following is a list of the application’s expected functionality:

1. Product (add and update).
2. Inventory (add and update).
3. Vendor (add, update, and delete).
4. Purchase Order (add and update)

The following are some of the questions that the database will be able to answer:

1. Available products, inventory stock and inventory value (reporting).
2. Available vendors to order given product (reporting).
3. Status and Value of a Purchase Order (record retrieval).

Data Model

Entity Diagram

Record Diagram

-- ******************************************************
--   1: CREATE TABLES
-- ******************************************************

/* Drop tables if already created */
drop table tbPOitems;
drop table tbShipments;
drop table tbPO;
drop table tbProdVendorXref;
drop table tbProd;
drop table tbVendors;
drop table tbFoodItem;
drop table tbBrand;

/* Create table */

CREATE table tbCategory (
categoryID char (4) not null,
categoryName varchar2(200) not null,
constraint pk_category primary key (categoryID)
);

CREATE table tbFoodItem (
foodItemNo char(5) not null,
constraint pk_foodItem primary key (foodItemNo),
foodItemName varchar2(200) not null,
categoryID char (4) not null,
constraint fk_categoryID_tbFoodItem foreign key (categoryID) references tbCategory (categoryID)
);

CREATE table tbBrand (
brandNo char(5) not null,
constraint pk_Brand primary key (brandNo),
brandName varchar2(200) not null,
manufacturer char (200) null
);

CREATE table tbProd (
 prodNo char(9) not null,
 constraint pk_tbProd primary key (prodNo),
 foodItemNo char(5) not null,
 constraint fk_foodItemNo_tbProd foreign key (foodItemNo)      references tbFoodItem (foodItemNo),
 brandNo char(5) not null,
 constraint fk_brandNo_tbProd foreign key (brandNo) references
tbBrand (brandNo),
 sizeMeasure char (100) not null,
 sizeUnit char (50) not null,
 packetSize number(11,2) not null,
 stockUnit char (50) null,
 currentStock number(11,2) null,
 unitCost number(11,2) null,
 unitPrice number(11,2) null,
 minStockLevel number(11,2) null,
 maxStockLevel number(11,2) null,
 lastModifiedOn date default CURRENT_DATE not null,
 constraint constraintcheck_packetSize check (packetSize>0),
 constraint constraintcheck_currentStock check (currentStock>0 OR currentStock=0),
 constraint constraintcheck_unitCost check (unitCost>0 OR unitCost=0),
 constraint constraintcheck_unitPrice check (unitPrice>0),
 constraint constraintcheck_minStockLevel check (minStockLevel>0),
 constraint constraintcheck_maxStockLevel check (maxStockLevel>0)
);

CREATE table tbVendors (
 vendorNo char(8) not null,
 constraint pk_tbVendors primary key (vendorNo),
 vendorName varchar2(225) not null,
 vendorAddressLine varchar2(500) null,
 vendorCity varchar2(15) not null,
 vendorState varchar2(100) not null,
 vendorCountry varchar2(150) not null,
 vendorZip varchar2(15) null,
 vendorContact varchar2(200) not null,
 vendorContactPhone varchar2(15) null,
 vendorContactPhone2 varchar2(15) null,
 vendorContactEmail varchar2(150) null,
 vendorContactFax varchar2(15) null,
 venodrIsActive varchar2(1) not null,
 constraint constraintcheck_venodrIsActive check (venodrIsActive in ('Y','N'))
);

CREATE table tbProdVendorXref (
 prodNo char(9) not null,
 constraint fk_prodNo_tbProdVendorXref foreign key (prodNo) references
 tbProd (prodNo) on delete cascade,
 vendorNo char(8) not null,
 constraint fk_venodrNo_tbVendors foreign key (vendorNo) references
 tbVendors (vendorNo) on delete cascade,
 constraint pk_tbProdVendorXref primary key (prodNo,vendorNo)
);

CREATE table tbPO (
 POno char(7) not null,
 constraint pk_tbPO primary key (POno),
 vendorNo char(8) not null,
 constraint fk_venodrNo_tbVendors_intotbPO foreign key (vendorNo) references
 tbVendors (vendorNo),
 PaymentAmount number(11,2) null,
 constraint constraintcheck_PaymentAmount check (PaymentAmount>0),
 PaymentDueDate date null,
 PaymentDate date null,
 PaymentMehtod char(100) null
);

CREATE table tbShipments (
 shipmentNo number (11,0) not null,
 constraint pk_tbShipments primary key (shipmentNo),
 expectedArrivalDate date null,
 actualArrivalDate date null
);

CREATE table tbPOitems (
 POno char(7) not null,
 constraint fk_POno_tbPO foreign key (POno) references
 tbPO(POno),
 lineNo number(3,0) not null,
 prodNo char(9) not null,
 constraint fk_prodNo_tbProd_intotbPOitems foreign key (prodNo) references
 tbProd (prodNo) on delete cascade,
 qty number(11,0) not null,
 constraint constraintcheck_qty check (qty>0),
 unitPrice number(11,2) not null,
 constraint constraintcheck_POunitPrice check (unitPrice>0),
 discount number(5,2) null,
 constraint constraintcheck_POdiscount check (discount between 0 and 100),
 shipmentNo number(11,0) null,
 constraint fk_shipmentNo_tbShipments foreign key (shipmentNo) references
 tbShipments(shipmentNo),
 constraint pk_tbPOitems primary key (POno, lineNo)
);

-- ******************************************************
-- VIEW TABLES
-- Note: Appropiate commands to show data
-- ******************************************************

SELECT count(*) FROM tbCategory;
SELECT count(*) FROM tbBrand;
SELECT count(*) FROM tbFoodItem;
SELECT count(*) FROM tbProd;
SELECT count(*) FROM tbVendors;
SELECT count(*) FROM tbProdVendorXref;
SELECT count(*) FROM tbPO;
SELECT count(*) FROM tbShipments;
SELECT count(*) FROM tbPOitems;

SELECT * FROM tbCategory;
SELECT * FROM tbBrand;
SELECT * FROM tbFoodItem;
SELECT * FROM tbProd;
SELECT * FROM tbVendors;
SELECT * FROM tbProdVendorXref;
SELECT * FROM tbPO;
SELECT * FROM tbShipments;
SELECT * FROM tbPOitems;

-- ******************************************************
-- CHECK CONSTRAINTS
-- QUALITY CONTROLS
-- Note: Test only 2 constraints of each of
-- the following types:
-- *) Entity integrity
-- *) Referential integrity
-- *) Column constraints
-- ******************************************************

/*Entity Integrity: No Duplicate PK*/
INSERT into tbCategory values ( '1234' , 'Dairy');

/*Entity Integrity: No Null PK*/
INSERT into tbBrand values ( '', 'Amul' , 'Amul');

/*Referential Integrity: Inserting a FK which does not match a PK */
INSERT into tbFoodItem values ( '10041', 'Ghee (Clarified Butter)' , '1111');

/*Referential Integrity: Updating a PK with existing FK */
UPDATE tbVendors SET vendorNo = '72345890' where vendorNo = '62345890';

/* Column Constraint */
INSERT into tbPO values ( '7000125', '62345890', -1000, to_date('12-15-2018','mm-dd-yyyy'), to_date('12-12-2018','mm-dd-yyyy'), '');
INSERT into tbProd values ( '100100378', '10037', '10014', 'weight', 'gm', -250, 'each', 95, 1, 1.89, -10, 100,to_date('10-27-2018','mm-dd-yyyy'));

SQL Queries

-- ******************************************************
-- ASKING QUESTIONS TO DATABASE
-- SELECT STATEMENTS
-- ******************************************************

/* Query 1: List of Products with inventory less than minimum stock */
select prodNo, (tbBrand.brandName || ' ' || tbFoodItem.foodItemName || ' ' || tbProd.packetSize || ' ' || tbProd.sizeUnit) as ProductName, currentStock , minStockLevel from tbProd
inner join tbBrand on tbProd.BrandNo = tbBrand.BrandNo
inner join tbFoodItem on tbProd.foodItemNo = tbFoodItem.foodItemNo
where currentStock < minStockLevel;

/* QUERY 2: List of Products with inventory less than minimum stock and still PO is not created */

select prodNo, (tbBrand.brandName || ' ' || tbFoodItem.foodItemName || ' ' || tbProd.packetSize || ' ' || tbProd.sizeUnit) as ProductName, currentStock , minStockLevel from tbProd
inner join tbBrand on tbProd.BrandNo = tbBrand.BrandNo
inner join tbFoodItem on tbProd.foodItemNo = tbFoodItem.foodItemNo
where currentStock < minStockLevel
and prodNo not in (SELECT prodNo from tbPOitems);

/* QUERY 3: List of Products with inventory less than minimum stock and still PO is not created, include vendors of respective products */

select
tbProd.prodNo,
(tbBrand.brandName || ' ' || tbFoodItem.foodItemName || ' ' || tbProd.packetSize || ' ' || tbProd.sizeUnit) as ProductName,
currentStock , minStockLevel,
tbVendors.vendorName
from tbProd
left outer join tbProdVendorXref on tbProdVendorXref.prodNo = tbProd.prodNo
inner join tbVendors on tbProdVendorXref.vendorNo = tbVendors.vendorNo
inner join tbBrand on tbProd.BrandNo = tbBrand.BrandNo
inner join tbFoodItem on tbProd.foodItemNo = tbFoodItem.foodItemNo
where currentStock < minStockLevel
and tbProd.prodNo not in (SELECT prodNo from tbPOitems)
order by tbProd.prodNo, tbProdVendorXref.vendorNo;

/* Query 4: List of POs due in next 2 days or overdue
assumption: all the POs for which payment is made will have paymentDate populated */
SELECT
POno,
vendorName
from tbPO
inner join tbVendors on tbVendors.vendorNo = tbPO.vendorNo
where
paymentDate is null
AND paymentDueDate < (sysdate + 2);

/* Query 5: List of Brand with number of food items and inventory value in descending order */
SELECT
tbBrand.brandNo, tbBrand.brandName, count(distinct foodItemNo), sum(unitCost*currentStock) as inventory_value
from tbProd
inner join tbBrand on tbBrand.brandNo = tbProd.brandNo
group by tbBrand.brandNo, tbBrand.brandName
order by inventory_value desc;

Triggers and Transaction

-- ******************************************************
-- TRIGGER 1
-- Trigger fires when there are products for which
-- inventory is lower than minimum stock level
-- ******************************************************

/* dropping the trigger if it already exists */
drop trigger low_stock;

/* Creating the trigger*/
CREATE OR REPLACE trigger low_stock
 AFTER insert OR update OR delete
 on tbProd

/*declaring the variable*/
declare x number(10); /* PL/SQL variable */
begin
 select count(*) into x
 from tbProd
 inner join tbBrand on tbProd.BrandNo = tbBrand.BrandNo
 inner join tbFoodItem on tbProd.foodItemNo = tbFoodItem.foodItemNo
 where currentStock < minStockLevel;
if x > 0
 THEN
 dbms_output.put_line (
 'Action Alert: Generate POs *** Warning: low_stock TRIGGER - Number of Products with Inventory less than minimum stock level = ' || x );
END if;
end low_stock;
/

/*debug the trigger in case of error*/
SHOW ERRORS TRIGGER low_stock;

/*testing trigger for insert statements*/
INSERT into tbProd values ( '100100378', '10001', '10007', 'weight', 'LB', 10, 'each', 50, 3, 4, 20, 100,to_date('10-27-2018','mm-dd-yyyy'));

INSERT into tbProd values ( '100100379', '10005', '10005', 'weight', 'kg', 1, 'each', 25, 1, 1.5, 30, 100,to_date('10-27-2018','mm-dd-yyyy'));

INSERT into tbProd values ( '100100380', '10002', '10005', 'weight', 'LB', 10, 'each', 10, 4, 5, 20, 200,to_date('10-27-2018','mm-dd-yyyy'));

/*testing trigger for update statement*/
update tbProd
set currentStock = 80
where prodNo = '100100379';

/*testing trigger for delete statements*/
delete from tbProd
where prodNo = '100100378';

-- ******************************************************
-- TRIGGER 2: CASCADE UPDATE TRIGGER
-- When prodNo is updated in tbProd, update prodNo in subsequent children table
-- tbProdVendorXref, tbPOitems
-- ******************************************************

/* dropping the trigger if it already exists */
drop trigger cascade_update_prodNo;

/* Creating the trigger*/
CREATE OR REPLACE trigger cascade_update_prodno
 AFTER update
 on tbProd
for each row
begin
 update tbProdVendorXref
 set prodNo = :NEW.prodNo
 where prodNo = :old.prodNo;
 update tbPOitems
 set prodNo = :NEW.prodNo
 where prodNo = :old.prodNo;
end cascade_update_prodno;
/

/*debug the trigger in case of error*/
SHOW ERRORS TRIGGER cascade_update_prodno;

/*testing the trigger by updating prodNo in tbProduct */
select * from tbProd where prodNo = '100100100';
select * from tbProdVendorXref where prodNo = '100100100';
select * from tbPOitems where prodNo = '100100100';

update tbProd
set prodNo = 'A00100100'
where prodNo = '100100100';

select * from tbProd where prodNo = 'A00100100';
select * from tbProdVendorXref where prodNo = 'A00100100';
select * from tbPOitems where prodNo = 'A00100100';


-- ******************************************************
-- PL/SQL TRANSACTION
-- Insert into tbPO, tbPOitems
-- ******************************************************

/*Values before the transaction*/
select * from tbPO;
select * from tbPOitems;

--*** successful PO and PO Items inserts****

declare
 tranState char(10); /* variable to keep track of the state */

begin
 tranState := 'PO'; /* begin new project process */
 
 INSERT into tbPO 
 values ( '7000125', '62345895', '815', to_date('12-14-2018','mm-dd-yyyy'),'' , '');

 
 commit;
 
 savepoint po_in; /* define savepoint */
 
 tranState := 'POitems'; /* begin phone numbers process */
 
 INSERT into tbPOitems values ( '7000125', '1', '100100318', '100', '2', 10, '' );
 INSERT into tbPOitems values ( '7000125', '2', '100100328', '35', '5', 20, '');
 INSERT into tbPOitems values ( '7000125', '3', '100100342', '50', '7', 0, '');
 INSERT into tbPOitems values ( '7000125', '4', '100100353', '40', '2.25', 0, '' );

 
 commit;
 
 EXCEPTION
 when DUP_VAL_ON_INDEX then
 if tranState = 'POitems' then
 dbms_output.put_line ('*** Failed attempt to enter PO Items in tbPOitems');
 ROLLBACK to po_in;
 else
 dbms_output.put_line ('*** A PO with this NUMBER already exists');
 end if;

 when OTHERS then
 if tranState = 'POitems' then
 dbms_output.put_line ('*** Problems inserting PO Items');
 ROLLBACK to po_in;
 else
 dbms_output.put_line ('*** Problems creating new PO');
 end if; 
end;
/

/*Values after the transaction*/
select * from tbPO;
select * from tbPOitems;

-- ******************************************************
-- STORED Procedure
-- Reporting on Current Financial Summary of the Store
-- ******************************************************

create or replace procedure financial_summary 
is
 civ number(15,2);
 er number(15,2);
 ep number(15,2);
 po number(15,2);
 
begin

 -- Current Inventory Value
 select
 sum(currentStock*unitCost) 
 into civ
 from tbProd;

 -- Expected Revenue
 select
 sum(currentStock*unitPrice) 
 into er
 from tbProd;

 -- Expected Profit
 select
 sum(currentStock*(unitPrice-unitCost)) 
 into ep
 from tbProd;

 -- PO Amount Due
 select
 sum(paymentAmount) 
 into po
 from tbPO
 WHERE paymentDate is NULL;

 
 /* set output */
 dbms_output.put_line('Current Inventory Value = ' || civ);
 dbms_output.put_line('Expected Revenue = ' || er);
 dbms_output.put_line('Expected Profit = ' || ep);
 dbms_output.put_line('PO Amount Due = ' || po);
 
end financial_summary;
/

show errors

exec financial_summary;

Case Study

Head-count planning using Monte-carlo simulation for IT Support Team

Use Monte Carlo simulations to build the model that predicts head-count needed for the work-load.

Features:

With the current capacity of support team, there will be a few weeks – around 2 weeks in a year when support analysts will be working full capacity and yet tickets might roll over into next week. Which I believe is ok as it is rare occurrence.

Normal Weeks

In a given week, a support analyst spends 21-33 hours to resolve support tickets. And we can say this with 80% confidence interval.

A support analyst ends up spending 3 and half days or more, working on tickets in more than half of the weeks. Which is very close to our company policy which requires 80% of resource utilization for support team – that is 4 out of 5 working days in a normal week.

Unusual Weeks – bad and good

There can be 10 weeks in a given year when all the analysts will end up spending more than 32 hours each. Which might be ok in context of maintaining sustainable pace for the team. As there will be 10 weeks in a given year when an analyst will spend 20 hours or less on support tickets.

Long range explained

It is just the unpredictable nature of the job. It depends on tickets influx and complexity of the tickets which drives resolve time for the ticket. Support team does not have direct control over both of these factors which drive its utilization levels.

Customer needs resolution in a week

We also know that as per industry standard, support team needs to resolve a ticket in a week. Customer shouldn’t be waiting more than a week for the resolution. Hence, it is best to complete the ticket in the same week it came in. We do not want tickets to roll over into the next week.

14 is too less for sustainable pace of the team

Please note that even if one support analyst leaves, the team will start losing the sustainable pace. Each analyst will be working 22-35 hours a week 80% of the time. There will be 25% of the time an analyst will end up working for more than 32 hours. We will have 13 weeks in a year when an analyst will be utilized more than 80% for support work.

14 is too less to keep customers happy

It is worth noting that if team reduces to 14 analysts, there will be 6 weeks in a year when tickets might roll over to next week. Which might cause reputation issues with customers.

To avoid the long-tail impact, keep the team count at 15.

16 is too relaxing

There will be just 3 weeks in a year when an analyst needs to work more than 32 hours.

In a normal week analysts would spend 18-30 hours to resolve support tickets.

Hence, adding one more analyst to the team does not add value – it will just make everyone’s life easier and probably boring.