Scott Chaney –

For bonus points include in body of your pmaReport e-mail that you found all the essay answer and want bonus points

Essay 10 how does Extended Price column get create? 

• E10 Answer:  The “Extended Price”  column is created by  multiplying the quantity in the orderLine table (o.quantity) by the” price” of the fruit from the fruitView (which was generated from the fruit table) .  The “Extended Price” is also suffixed with a “$” concatenation, and considers a null check (ifnull) to convert the null quantity values to 0 to allow for evaluation.

• Used the following procedure code for the above analysis:

SELECT o.fruitID as "fruitID", name, concat( "$", price ) as "Unit Price" ,
o.quantity as "Quantity",
concat( "$", format(  ifnull( o.quantity, 0 )*price, 2 ) ) as "Extended Price"
FROM ( SELECT * FROM orderLine WHERE orderID = activeOrderID ) AS o
JOIN fruitView ON fruitView.fruitID = o.fruitID;

Essay11 test the procedure testFruitTransaction using phpMyAdmin, make the following calls  testFruitTransaction( 6 ), and testFruitTransaction( 2 ) how is the field myDate being set in myError? why does 2 or 6 fail? if phpMyAdmin creates a new session on each submit, how does the calls to this procedure work as a single session?

• myDate appears to be set to date to roughly the current time (of which the procedure was run). Looking at the testFruitTransaction, it looks like there is a “Start of Transaction” being passed into the testFruitExistance function and I would assume that this is the time being used for the myDate value.
• 2 fails due to the foreign key constraint with fruitID 2 in inventory, 6 does not fail due to fruitID 6 not being present in a foreign-keyed table, thus it does not have a FK violation.
• The calls to the testFruitTransaction happen within a transaction, permanent updates do not occur, the changes are only written to a logfile.  If the changes are accepted, the logfile can then be written to the DB.  The transaction can be reverted with a rollback (cancels logfile changes), or accepted with a commit command (copies logfile to the DB).

The UNION in testTransaction() will grab all results from the orderLine table and inventoryQuantity view and return all fruitIDs that are found among either set (exclusive and inclusive values)

The DELETE in testTransaction() will delete all of the records that have a fruitID that was not found in the UNION statement between the inventoryQuantity View and the orderLine table.  Essentially, this will delete any fruits from the fruit table that are not relevant to an order (inventory and orderLine)

The deletes are not maintained at the end of the transaction due to the ‘rollback’ command that was issued at the end of the transaction (data is reverted to the state that it was in at the beginning of the transaction)

getImageURL Parameters: fID (smallint)Returns: str (text)

getImageURL takes in a fruitID and returns the URL for the given fruitID using a concatenation of a base URL and the name of the fruit grtabbed from the fruit table for the fruitID that was passed in

testFruitExistence Parameters: msg (text) and fID (smallint) Returns str (text)

testFruitExistence takes in a fruit ID and evaluates whether or not a fruit with that fruitID exists in the fruit table

orderInvoiceSimple Parameters: activeOrder (mediumint)

orderInvoiceSimple takes in an orderID and issues a select * on the orders table for the given ID

orderInvoice Parameters: activeOrder (int)

Takes in an orderID and displays the startDate, shipping status, Unit Price, computed total price for a given fruit within the order, and computed total price for all items within the order

testFruitTransaction Parameters: fID (smallint)

testFruitTransaction takes in a fruitID and then updates the myError table with information based on the testFruitExistance function.  The testFruitTransaction will attempt to delete a fruit from the fruit table and will then rollback the delete at the end of the transaction

updateInventory Parameters: fID (int), desired (int)

UpdateInventory will subtract the quantity value in orderLine from the quantity amount in the inventory table per orderID and fruitID.  The procedure will also evaluate the quantities in the orderLine table, and decrease the values to the value in the inventory table’s quantity column if the value in orderLine is greater than the value in inventory

testTransaction Parameters: none

Deletes orders with no shipDate and deletes all fruit that are not part of the inventory or orderLine.  The transaction is then rolledback at the end of the procedure to revert any of the deletes that may have taken place

Essay14 in the code below why does delete fruitID=1 throw an exception

• The “delete from `fruit` where fruitID = 1” command fails due to a foreign key constraint.  fruitID has foreign key in the inventory table, and inventory currently has a record with fruitID = 1.

/* phpMyAdmin is a safe system,  it creates a new session
    at each query submit, therefore you  need to paste entire
   block of code into one sql dialog panel  */
start transaction;
# select "This should throw an exception 1 is inventory";
# delete from `fruit` where fruitID = '1';
# select testFruitExistence( "Tried to delete 1", 1 );
select testFruitExistence( "Start of Transaction", 6 );
delete from `fruit` where fruitID = '6';
select testFruitExistence( "fruitID 6 deleted", 6 );
rollback; #rollback, cancel all changes from the last transaction
select testFruitExistence( "After Rollback fruitID 6 back in", 6 );

Essay 15 for the oneTime event, explain how  we determine that this complex event took place,  refer to the deskcheck identify the before and after database state

Using the deskcheck method, we can verify that the ‘oneTime’ event has taken place by first verifying that the shipDate for records that previously had a NULL shipDate has been set to the current time + 1 min (that is, that the shipDate is set 1 minute after the onetime event is run).  We can then check to make sure that the inventory quantites were properly reduced by the quantity specified in the orderLine for the order that just had the shipDate set, and that the values in orderLine were properly re-evaluated based on the inventory quantity values

The deskcheck method indicates that we are manually selecting from the different tables and views that are affected by the onetime event.  In this case, we have previously verified that the triggers and procedures are correctly working when the shipDate is updated, thus the most cursory check that would still be sufficient would be to verify that the shipDate has been set to the current time, one minute after the event was called