How many database requests can you identify for an inventory update for both PRODUCT and PART?/2022

Part A

Suppose you are a manufacturer of product ABC, which is composed of parts A, B, and C. Each time a new product is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. And each time the product ABC is created, the parts inventory, using PART_QOH in a table named PART, must be reduced by one each of parts A, B, and C. The sample database contents are shown in Table P10.1

Table P10.1 The Database for Problem 1

Table name: PRODUCT Table name: PART

PROD_CODE PROD_QOH   PART_CODE PART_QOH
ABC 1,205   A 567
      B 498
      C 549

Given that information, answer the following questions.

  1. How many database requests can you identify for an inventory update for both PRODUCT and PART?
  2. Using SQL, write each database request you identified in problem 1.
  3. Write the complete transaction(s) all of the above in a batch transaction.

Part B

Based on the following query:

SELECT P_CODE, P_QOH*P_PRICE

FROM PRODUCT

WHERE P_QOH*P_PRICE > (SELECT AVG(P_QOH*P_PRICE) FROM PRODUCT)

  1. What is the likely data sparsity of the P_QOH and P_PRICE columns?
  2. Should you create an index, what would the index column(s) be, and why should you create that index?