%========================================================

%     Name: Juan C Ibarra Cuza

%     Panther-ID: x x x -4175 (last 4 digits)

%     Course: COP 4710

%     Assignment#: 1

%     Due: Tue, Sep 8, 2020

%     I hereby certify that this work is my own and none of
%     it is the work of any other person.

%                             Signature: Juan C Ibarra
%     =========================================================

% Get all the onHand values from the part table
onHand := project onHand (part);

% Make a copy
onHandCopy(onHand1) := onHand;

% Now we get all posible conbinations of onHand values from both tables
pOnHand := onHand product onHandCopy;

% Keep the hightest value from each row  
onHandNotMin := project onHand (select onHand > onHand1 (pOnHand));

% Project the value that is present on the onHandNotMin but present on the onHand table (lowest)   
onHandMin := onHand difference onHandNotMin;

% Project the parts numbers and descriptions of those with the onHand equal to onHandMin
q3Result :=  project partNum, description (part njoin onHandMin);

% Q4

qOrderLine := project orderNum, partNum (orderLine); 
qOrders := project orderNum, customerNum (orders);
ordersParts := qOrderLine njoin qOrders;

% Get all the customers that have at least one order
allCustomers := project customerName (qOrders njoin customer);

% Get all the customers that have at least one order with the part Number they ordered.
allOrders := project customerName, partNum (ordersParts njoin customer);

% Using the division approach.

% Get all the customers with the parts required to satisfy the condition.
expectedPurchase := allCustomers product (project partNum (q3Result));

% Get the orders missing to satisfy the condition.
missingPurchase := expectedPurchase difference allOrders;

% Get the customers that don't qulify for the condition.
ineligibleCustomers := project customerName (missingPurchase);

% Project the customers that are not in ineligibleCustomers list (if any).
quotient := allCustomers difference ineligibleCustomers;