ABC Markets sell products to customers. The relational diagram shown in Figure represents the main entities for ABC’s database. The following important characteristics:

• A customer may make many purchases, each one represented by an invoice.

• The CUS_BALANCE is updated with each credit purchase or payment and represents the amount the customer owes.
• The CUS_BALANCE is increased (+) with every credit purchase and decreased (-) with every customer payment.
• The date of last purchase is updated with each new purchase made by the customer.
• The date of last payment is updated with each new payment made by the customer.

• An invoice represents a product purchase by a customer.

• An INVOICE can have many invoice LINEs, one for each product purchased.
• The INV_TOTAL represents the total cost of invoice including taxes.
• The INV_TERMS can be “30,” “60,” or “90” (representing the number of days of credit) or “CASH,” “CHECK,” or “CC.”
• The invoice status can be “OPEN,” “PAID,” or “CANCEL.”

• A product’s quantity on hand (P_QTYOH) is updated (decreased) with each product sale.
• A customer may make many payments. The payment type (PMT_TYPE) can be one of the following:

• “CASH” for cash payments.
• “CHECK” for check payments
• “CC” for credit card payments

• The payment details (PMT_DETAILS) are used to record data about check or credit card payments:

• The bank, account number, and check number for check payments
• The issuer, credit card number, and expiration date for credit card payments.

a. On May 11, 2014, customer ‘10010’ makes a credit purchase (30 days) of one unit of product ‘11QER/31’ with a unit price of $110.00; the tax rate is 8 percent. The invoice number is 10983, and this invoice has only one product line.

b. On June 3, 2014, customer ‘10010’ makes a payment of $100 in cash. The payment ID is 3428.

Transcribed Image Text:

INVOICE LINE INV NUMBER INV NUMBER CUS_CODE INV DATE INV TOTAL INV TERMS INV STATU:S CUS CODE PRODUCT LINE NUMBER o CUS LNAME CUS FNAME CUS INITIAL CUS AREACODE CUS PHONE CUS CREDITLIMIT CUS BALANCE CUS DATELSTPMT CUS DATELSTPUR P CODE P DESCRIPT P INDATE P QTYOH P MIN P PRICE P DISCOUNT V CODE P CODE LINE UNITS LINE PRICE PAYMENTS VENDOR PMT ID PMT DATE CUS CODE PMT AMT PMT TYPE PMT DETAILS V-CODE V NAME V CONTACT V_AREACODE V PHONE V STATE V ORDER