ABC Markets sell products to customers. The relational diagram shown in Figure P10.6 represents the main entities for ABC's database. Note 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 the 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. Note: Not all entities and attributes are represented in this example. Use only the attributes indicated. Using this database, write the SQL code to represent each of the following transactions. Use BEGIN TRANSACTION and COMMIT to group the SQL statements in logical transactions. a. On May 11, 2018, 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, 2018, customer 10010 makes a payment of $100 in cash. The payment ID is 3428.

ABC Markets sell products to customers. The relational diagram shown in Figure P10.6 represents the main entities for ABC's database. Note 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 the 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. Note: Not all entities and attributes are represented in this example. Use only the attributes indicated. Using this database, write the SQL code to represent each of the following transactions. Use BEGIN TRANSACTION and COMMIT to group the SQL statements in logical transactions. a. On May 11, 2018, 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, 2018, customer 10010 makes a payment of $100 in cash. The payment ID is 3428.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Question

ABC Markets sell products to customers. The relational diagram shown in Figure P10.6 represents the main entities for ABC's database. Note 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 the 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.

Note: Not all entities and attributes are represented in this example. Use only the attributes indicated.

Using this database, write the SQL code to represent each of the following transactions. Use BEGIN TRANSACTION and COMMIT to group the SQL statements in logical transactions.

a. On May 11, 2018, 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, 2018, customer 10010 makes a payment of $100 in cash. The payment ID is 3428.

### FIGURE P10.6: The ABC Markets Relational Diagram

The diagram depicted above is a relational database schema for ABC Markets, illustrating the relationships between different entities involved in the business process. Each entity is represented as a table, with its attributes listed as fields. The connections between the tables denote the relationships between the entities.

#### Entities and Their Attributes:

1. **CUSTOMER**
- **CUS_CODE**: Primary Key
- CUS_LNAME
- CUS_FNAME
- CUS_INITIAL
- CUS_AREACODE
- CUS_PHONE
- CUS_CREDITLIMIT
- CUS_BALANCE
- CUS_DATELSTPMT
- CUS_DATELSTPUR

2. **INVOICE**
- **INV_NUMBER**: Primary Key
- CUS_CODE (Foreign Key referencing CUSTOMER)
- INV_DATE
- INV_TOTAL
- INV_TERMS
- INV_STATUS

3. **LINE**
- **INV_NUMBER**: Primary Key (Composite with LINE_NUMBER)
- **LINE_NUMBER**: Primary Key (Composite with INV_NUMBER)
- P_CODE (Foreign Key referencing PRODUCT)
- LINE_UNITS
- LINE_PRICE

4. **PRODUCT**
- **P_CODE**: Primary Key
- P_DESCRIPT
- P_INDATE
- P_QOH (Quantity on Hand)
- P_MIN (Minimum Order Quantity)
- P_PRICE
- P_DISCOUNT
- V_CODE (Foreign Key referencing VENDOR)

5. **PAYMENTS**
- **PMT_ID**: Primary Key
- PMT_DATE
- CUS_CODE (Foreign Key referencing CUSTOMER)
- PMT_AMT
- PMT_TYPE
- PMT_DETAILS

6. **VENDOR**
- **V_CODE**: Primary Key
- V_NAME
- V_CONTACT
- V_AREACODE
- V_PHONE
- V_STATE
- V_ORDER

### Explanation of Relationships:

- The **CUSTOMER** table is linked to the **INVOICE** table via the CUS_CODE, allowing for tracking of customer purchases and invoices.
- The **INVOICE** table is connected to the **LINE** table by INV_NUMBER, indicating that each invoice can have multiple line items.
- The **

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.