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.)

9a) Write the SQL code to create a copy of CUSTOMER naming the copy CUSTOMER_2.

9b) Write the SQL code that will add the attributes CUS_ZIP, CUS_RANK and CUS_CITY to its structure. The CUS_ZIP is the zip code of the customer. The new attribute characteristics are shown next: CUS_ZIP INTEGER CUS_CITY CHAR(20) CUS_RANK CHAR(1)

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