Assignment
Suppose you are a manufacturer of product ABC, which is composed of components A, B, and C. Component A is further composed of parts X and Y.
Each time a new product ABC is created, it must be recorded in the product inventory, PROD_QOH in a table named PRODUCT.
Likewise, each time the product ABC is created, the corresponding components inventory, COMP_QOH in a table named COMPONENT, must be reduced by one each of components A, B, and C, and the parts inventory PART_DOH in a table named PART, must be reduced by one each of parts X and Y.
The sample database content is shown below.
PRODUCT
|
PROD_CODE
|
PROD_DOH
|
|
ABC
|
1,375
|
COMPONENT
|
COMP_CODE
|
COMP_DOH
|
|
A
|
495
|
|
B
|
517
|
|
C
|
387
|
PART
|
PART_CODE
|
PART_DOH
|
|
X
|
1,209
|
|
Y
|
50
|
Given that information, answer questions a through c.
a. How many database requests can you identify when a new component A is created for tables COMPONENT and PART?
b. Using SQL, write each database request you identified in question a.
c. How many database requests can you identify when a new product ABC is created for tables PRODUCT, COMPONENT, and PART?.
d. Using SQL, write each database request you identified in question a.