One row should be returned.
Check the Price_sold column to be sure it is null
(has not been sold already).
If the Sale_price is null, proceed to d,
otherwise report the error and roll back the transaction.
If no row exists for the Item_ID, report the error, and
give the clerk the option of entering a corrected Item_ID,
or canceling the transaction.
If the clerk cancels the sale, roll back the transaction.
d Insert new rows in the Line_item and Sale tables.
e Update the Price_sold column in the Item table for this Item_ID.
f Commit the transaction.
8.8 When an item is sold, should the item row be removed from the Items table? Why or why not?
Probably not. The rows in the Items table maintain information about costs and revenues, which
allow us to calculate profit. These rows also provide information about consignment sales, and we
need that information to pay our consignment sellers correctly.
Very old entries in the Items table may be deleted, perhaps on an annual basis, to keep the size of
the database smaller. However, we must be sure not to delete any information that may be required
for business and tax reporting.
8.9 What transaction isolation level should apply to this application? Why do you make this choice?
This is a rather undemanding database application.
Pages:
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541