8.7 The owner of the antique store doesn??™t want to train his people in SQL, or to teach them about all the
tables in the database. He asks you to write a program that will take sales information, and then make
all the changes automatically, including whatever error checking may be necessary. The clerk will enter
a customer phone number (assume this is used as the Customer_ID primary key in the Customer table),
an item ID (from a tag on the item), and the sale price.
In pseudo code, outline how your program would record a sale. Show how you would use a transaction
to insure the integrity of the database updates. Also, explain the error checking (such as verifying that
the customer is already in the database) you would perform in your program, and how your program
would recover from problems such as:
??? The customer is not in the database.
??? The item number is not in the database.
??? The item is already sold.
a Select the customer from the Customer table.
One row should be returned. If so, continue at b.
If no row is returned, we must add the customer to the database.
Insert a new customer row in the Customer table to add this new person
to the database.
b Start the sale transaction.
c Select the row for the Item_ID from the Item table.
Pages:
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541