Assume you have the following entities: CUSTOMER - One row per customer, the pri

Question

Assume you have the following entities:

CUSTOMER - One row per customer, the primary key is the CUST_ID. Other attributes are CUST_NAME, CUST_ADDRESS, CUST_CITY, CUST_STATE, CUST_ZIP.

INVOICE - One row per invoice. The Primary key is INVOICE_ID and CUST_ID is a FK to the CUSTOMER table. There is also a field called INVOICE_AMOUNT (currency).

INVOICE_ITEM - One row for each item ordered. For the purpose of this assignment, you don't need to worry about keeping ordering information on items in an invoice, and you can assume that an item (product) can appear in the order only once. Thus, the PK for the table can be a composite one of the two parent tables (INVOICE_ID, PRODUCT_ID). The entity also has a field called called INVOICE_ITEM_QTY containing the quantity ordered and INVOICE_ITEM_PRICE which is the cost per item.

PRODUCT - One row per product. The primary key is PRODUCT_ID, and the table also has PRODUCT_NAME (the name of the product) and PRODUCT_COST (the cost of the product).

[Note: It is questionable on whether you should have an an INVOICE_AMOUNT field given you can always query the INVOICE_ITEM table and sum up the INVOICE_ITEM_QTY * INVOICE_ITEM_PRICE for each invoice item. However, for this assignment we are assuming we want to do this.]

Before doing the transaction, you will have to have SQL that sets up the tables listed above.

Create a transaction that:

1) Insert a new CUSTOMER.

2) Insert a new INVOICE. Note that you can either use a sequence for the INVOICE_ID field or just use your own unique value.    Set the INVOICE_AMOUNT field to zero.

3) Insert two INVOICE_ITEM rows. You will need to use a unique PRODUCT_ID for each row. When you create the rows, set one row to a quantity of 2, with a price of $5.00. For the other row, set the quantity to 1, with a price of $10.00.

4) Update the INVOICE_AMOUNT to add the line items added in 3) to the INVOICE_AMOUNT field. You must do this with a query that would work regardless if there were existing INVOICE_ITEM rows for this INVOICE. Your query cannot hard code the amount!

5) Commit the changes to the database.

6) You should select from the INVOICE table for the invoice you created to ensure that the amount field was updated properly.

You will need to provide me two SQL files. The first will do the table creates, set up relationships, etc. You should DROP the tables with the cascade option before hand to ensure that there is no leftover setup from another student to prevent your table creations from working. The second SQL file should contain the transaction listed above.

Explanation / Answer

/* First File*/

Customer:

CREATE TABLE customer(cust_id int, cust_name varchar(30), cust_address varchar(50), cust_city varchar(40), cust_state varchar(50), cust_zip int, PRIMARY KEY (cust_id));

Invoice:

CREATE TABLE invoice(invoice_id int, cust_id int, invoice_amount int, PRIMARY KEY (invoice_id), FOREIGN KEY (cust_id) REFERENCES customer(cust_id));

Invoice Item:

CREATE TABLE invoice_item(invoice_id int, product_id int, INVOICE_ITEM_QTY int, INVOICE_ITEM_PRICE int, PRIMARY KEY (invoice_id, product_id));

Product:

CREATE TABLE product(product_id int, product_name varchar(50), product_cost int, PRIMARY KEY (product_id));

INSERT INTO `customer` (`cust_id`, `cust_name`, `cust_address`, `cust_city`, `cust_state`, `cust_zip`) VALUES ('10', 'Smith', 'Californiya', 'Californiya', 'MAH', '560098');

INSERT INTO `invoice` (`invoice_id`, `cust_id`, `invoice_amount`) VALUES ('1', '10', '0');

INSERT INTO `invoice_item` (`invoice_id`, `product_id`, `INVOICE_ITEM_QTY`, `INVOICE_ITEM_PRICE`) VALUES ('101', '501', '2', '5'), ('102', '502', '1', '10');

UPDATE invoice set invoice_amount="&amount" WHERE invoice_id="&id";

START TRANSACTION;

UPDATE invoice set invoice_amount="&amount" WHERE invoice_id="&id";

COMMIT;

SELECT * FROM `invoice`

/* Second File */

Drop table customer cascade constraints

Drop table invoice cascade constraints

Drop table invoice_itemcascade constraints

Drop table product cascade constraints

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.