We will use a simple database composed of the following tables: CUSTOMER, INVOICE, LINE, PRODUCT, and VENDOR. This database model is shown in Figure 6.1.

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

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

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

c) Having created the previous table structures in (a) and (b), write the SQL code to enter the 2 rows below into the table CUSTOMER_1

d) Write the SQL code to enter the 2 rows below into the table LINE_1

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

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

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

h) Write the SQL code to discard the change you have made to the table CUSTOMER_1 in the part (g)

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

j) 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 (i).

k) Write the SQL code to create a copy of CUSTOMER naming the copy CUSTOMER_2. Then 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)

l) Using the table CUSTOMER_2 with new added attributes, write the SQL code to enter a CUS_ZIP value of 15232 for the customer whose area phone code (CUS_AREACODE) is 615.

m) Using a single command sequence, write the SQL code that will enter the customers city (CUS_CITY) = Pittsburgh for all customers whose zip code (CUS_ZIP) is 15232.

n) Write the SQL code that will enter CUS_RANK of A for those customers whose balance is less than $250.

o) Write the SQL command sequences required to create a temporary table name TEMP_1, whose structure is composed of the CUSTOMER_2.

p) Write the SQL command to copy all rows from CUSTOMER_2 that contain the customers whose balance is in the range from $250 to $500 into the TEMP_1 table.

q) Write the SQL command that will delete the newly created TEMP_1 table from the database

r) Suppose you were to repeat steps (n), (o) or (p) very frequently, and your company has already established a way of describing customer balances as shown below:

BALANCE LOW_LIMIT HIGH_LIMIT

Excellent 0 0

Good 0 250

Bad 250 +infinity

How would you introduce this knowledge into the database and make use of it? Write the SQL statements that support your solution to this problem. (Hint: there is no single right answer to this problem).

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