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.
- How many database requests can you identify for an inventory update for both PRODUCT and PART?
- Using SQL, write each database request you identified in problem 1.
- 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)
- What is the likely data sparsity of the P_QOH and P_PRICE columns?
- Should you create an index, what would the index column(s) be, and why should you create that index?