Question

We will use a simple database composed of the following tables: CUSTOMER, INVOICE, LINE, PRODUCT, and VENDOR.

This database model is shown in Figure 1. The database model in Figure 1 reflects the following business rules: - A customer may generate many invoices. Each invoice is generated by one customer. - An invoice contains one or more invoice lines. Each invoice line is associated with one invoice. - Each invoice line references one product. A product may be found in many invoice lines. (You can sell more than one hammer to more than one customer!) - A vendor may supply many products. Some vendors do not (yet?) supply products. (For example, a vendor list may include potential vendors.) - If a product is vendor-supplied, that product is supplied by only a single vendor. - Some products are not supplied by a vendor. (For example, some products may be produced “inhouse” or may have been bought on the open market.)

INVOICE LINE CUSTOMER CUSCODE CUS FNAME CUS AREACODE CUS BALANCE INY NUMBER CUS CODE IV DATE LINE NMEER o P DESCRIPT P ONHAND P MIN VENDOR P DISCOUNT CODE V_AREACODE ORDER Figure 1- The database model CUS 10010 Ramas 0013 olowski 10015 OBrian 10018 Farriss Table name: CUSTOMER 54778-2 1 2238 PD 2 1546-Q02 3 13-02P2 1 54778-2 CUS CODE NV_DATE 10014 16-Jan-04 10011 16-Jen-04 10012 16-Jon-04 1001117 Jan-04 0018 17-Jen-04 10014 17-Jan-04 10015 17-Jen-04 10011 17-Jon-04 1006 1007 1008 1 PVC23DRT Table name: INVOICE Table name: LINE

2A) Write the SQL code to discard the change you have made to the table CUSTOMER_1 in the question 5.

2B) Write the SQL code to delete the row for customer named Kathy Smith, whose phone number is 894- 2285. (Hint: Use logical operators to include all the information given in this problem.)

2C) Write the SQL code that will restore the data to its original status; that is, the table CUSTOMER_1 should contain the data that existed before you made the changes in Questions 7.

Answer 1

Answer)
2A) Write the SQL code to discard the change you have made to the table CUSTOMER_1 in question 5.

The changes made in question 5 are not clear. Please share those.

2B) Write the SQL code to delete the row for the customer named Kathy Smith, whose phone number is 894- 2285.

DELETE FROM customer
WHERE CUS_FNAME='Kathy' and CUS_LNAME='Smith'
and CUS_PHONE='894-2285';

2C) Write the SQL code that will restore the data to its original status; that is, the table CUSTOMER_1 should contain the data that existed before you made the changes in Questions 7.

The changes made in Questions 7 are not clear.

Please share.

For your information, we can use the command:
rollback;
to rollback and undo the changes to restore the data to its original status.