Database Management Systems. If indexes help speed up access, why not index ever

Question

Database Management Systems.

If indexes help speed up access, why not index every column? Are there any circumstances where indexing all of the columns might be appropriate?

What tool might you use to diagnose where the time is being spent and how you might go about improving the performance?

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.

Explanation / Answer

1. Inserting a values new customer table

Insert Into Customer(Cust_Id,CUST_NAME, CUST_ADDRESS, CUST_CITY, CUST_STATE, CUST_ZIP) values(1,'Ram','US','alsk','ap',517247);

Or

Insert Into Customer values(1,'Ram','US','alsk','ap',517247);

2. Inserting a new row into Invoice table

Insert Into INVOICE(INVOICE_ID, INVOICE_AMOUNT,Cust_Id) values(1,0,1);
or
Insert Into INVOICE values(1,0,1);

Insert Into INVOICE values(2,0,1);

3.
Insreting values into Product table

Insert Into PRODUCT(PRODUCT_ID, PRODUCT_NAME,PRODUCT_COST) values(1,"Mobile",100);
or
Insert Into PRODUCT values(1,"Mobile",100);

Insert Into PRODUCT values(2,"CAR",100);

Inserting a rows into INVOICE_ITEM table

First Row:

Inserting values into INVOICE_ITEM(INVOICE_ID,PRODUCT_ID,INVOICE_ITEM_QTY,INVOICE_ITEM_PRICE,INVOICE_AMOUNT,Invoice_ProductCompositeKey, ) values(1,1,2,5.00,0,1)

Second Row:

Inserting values into INVOICE_ITEM(INVOICE_ID,PRODUCT_ID,Invoice_ProductCompositeKey,INVOICE_ITEM_QTY,INVOICE_ITEM_PRICE) values(2,2,1,10.00,0,2)

4. Updating Invoice_Amount in INVOICE table

UPDATE t1
SET t1.INVOICE_AMOUNT = (t2.INVOICE_ITEM_QTY*t2.INVOICE_ITEM_PRICE)
FROM INVOICE AS t1
INNER JOIN INVOICE_ITEM AS t2
ON t1.INVOICE_ID = t2.INVOICE_ID

5. COMMIT command is used to save records into database

6. Retrieving records from Invoice table

Select * from INVOICE

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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