The database model in Figure 6.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.)

6-1a) Refer to the table named CUSTOMER presented above and create a listing of all its attribute names and appropriate data types for each of them. Using that list as a guideline, write the SQL statement that will create the table structure for a table named CUSTOMER_1.

6-1b) Based on the data model diagram in Figure 6.1 and the sample content of the tables LINE above, proceed to write the SQL statement that will create the table structure for a table named LINE_1, in a similar fashion as you did with CUSTOMER_1. (Note that you have to identify the Primary Key and Foreign Key as well as the referenced table in order to complete your SQL command).

Having created the table structures in part 6-1,

6-2a) Write the SQL code to enter the 2 rows below into the table CUSTOMER_1

6-2b) Write the SQL code to enter the 2 rows below into the table LINE_1

6-3) Assuming that the data shown in the CUSTOMER_1 table have been entered, write the SQL code that will list all attributes for a customer with the initial W.

6-4) Write the SQL code that will save the changes made to CUSTOMER_1 and LINE_1 tables. Is it strictly necessary to do this? Comment on your findings.

6-5) Write the SQL code to change the phone number to 512-7867 for the customer whose last name is Smith.

6-6) Write the SQL code to discard the change you have made to the table CUSTOMER_1 in the part 6-5.

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

6-8) 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 part 6-7.

6-9a) Write the SQL code to create a copy of CUSTOMER naming the copy CUSTOMER_2. 6-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 CUS CODE CUS LNAME CUS FNAME CUS INITIAL CUS AREACODE CUS PHONE CUS BALANCE PRODUCT P CODE P DESCRIPT P INDATE P ONHAND P MIN P PRICE CUS CODE INV DATE LINE NMBER P CODE LINE UNITS LINE PRICE VENDOR Y CODE V_CODE V CONTACT V AREACODE PHONE VSTATE V ORDER - Figure 1 - The database model