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;