Chapter 10, Problem 1P

a)

Explanation of Solution

Number of database request identified for an inventory update for both PRODUCT and PART:

The number of database request based on the SQL query of the transaction.

• If the user gives the query to add the product “ABC” by “1”, reducing each parts “A”, “B”, and “C” individually means, the number transaction request will be “4”.
• If the user add the product “ABC” by “1”, reducing each parts “A”, “B”, and “C” in a single statement using “OR” condition means, the number transaction request will be “2”.

b)

Explanation of Solution

SQL statement for each database requests that identified in “Step a”:

Four SQL statements:

SQL Query:

UPDATE PRODUCT

    SET PROD_QOH = PROD_QOH + 1

        WHERE PROD_CODE = ‘ABC’

Explanation:

The above SQL query is to update the “PROD_QOH” field using “UPDATE” statement that adds the new product by “1” to “PRODUCT” table where the product code is “ABC”.

SQL Query:

UPDATE PART

    SET PART_QOH = PART_QOH - 1

        WHERE PART_CODE = ‘A’

Explanation:

The above SQL query is to update the “PART_QOH” field using “UPDATE” statement to reduce the parts inventory by “1” from “PART” table where the product code is “A”.

SQL Query:

UPDATE PART

    SET PART_QOH = PART_QOH - 1

        WHERE PART_CODE = ‘B’

Explanation:

The above SQL query is to update the “PART_QOH” field using “UPDATE” statement to reduce the quantity by “1” from “PART” table where the product code is “B”.

SQL Query:

UPDATE PART

    SET PART_QOH = PART_QOH - 1

        WHERE PART_CODE = ‘C’

Explanation:

The above SQL query is to update the “PART_QOH” field using “UPDATE” statement to reduce the parts inventory by “1” from “PART” table where the product code is “C”.

Two SQL statements:

The following SQL UPDATE statement to add the new product by “1” to “PRODUCT” table where the product code is specified as “ABC”.

SQL Query:

UPDATE PRODUCT

    SET PROD_QOH = PROD_QOH + 1

        WHERE PROD_CODE = ‘ABC’

Explanation:

The above SQL query is to update the “PROD_QOH” field using “UPDATE” statement to reduce the parts inventory by “1” from “PRODUCT” table where the product code is “ABC”.

SQL Query:

UPDATE PART

    SET PART_QOH = PART_QOH - 1

WHERE PART_CODE = ‘A’ OR PART_CODE= ‘B’ OR PART_CODE= ‘C’

Explanation:

The above SQL query is to update the “PART_QOH” field using “UPDATE” statement to reduce the parts inventory by “1” from “PART” table where the product code is either “A” or “B” or “C”.

c)

Explanation of Solution

Complete SQL transaction statements:

Four SQL statements:

BEGIN TRANSACTION

UPDATE PRODUCT

  SET PROD_QOH = PROD_QOH + 1

      WHERE PROD_CODE = ‘ABC’

UPDATE PART

  SET PART_QOH = PART_QOH - 1

      WHERE PART_CODE = ‘A’

UPDATE PART

  SET PART_QOH = PART_QOH - 1

      WHERE PART_CODE = ‘B’

UPDATE PART

  SET PART_QOH = PART_QOH - 1

      WHERE PART_CODE = ‘C’

COMMIT;

Explanation:

The above SQL transaction is to update the tables “PRODUCT” and “PART” by adding and removing the value “1” from “PART_QOH” and “PROD_QOH” field.

• Add the value of “PROD_QOH” field by “1” where “PROD_CODE” is “ABC”.
• Reduce the value by “1” from “PART_QOH” field in “PART” table where the “PART_CODE” either “A”, “B”, or “C”.

Two SQL statements:

BEGIN TRANSACTION

UPDATE PRODUCT

  SET PROD_QOH = PROD_QOH + 1

      WHERE PROD_CODE = ‘ABC’

UPDATE PART

  SET PART_QOH = PART_QOH - 1

      WHERE PART_CODE = ‘A’ OR

                     PART_CODE = ‘B’ OR

                     PART_CODE = ‘C’

COMMIT;

Explanation:

The above SQL transaction is to update the tables “PRODUCT” and “PART” by adding and removing the value “1” from “PART_QOH” and “PROD_QOH” field.

• Add the value of “PROD_QOH” field by “1” where “PROD_CODE” is “ABC”.
• Reduce the value by “1” from “PART_QOH” field in “PART” table where the “PART_CODE” either “A”, “B”, or “C”.

d)

Program Plan Intro

Transaction log:

It is a feature used by the DBMS software to keep track all of the information that contains a description of all database transactions executed by the DBMS. This transaction plays the major role for database maintenance.

Explanation of Solution

Transaction log for the transaction that was mentioned in subpart “c”:

The product of the ‘ABC’ has a PROD_QOH = 1,205 at beginning of the transaction and that the transaction is specified the addition of one new product.

The PART components “A”, “B” and “C” have a PROD_QOH equal to 567, 98, and 549 respectively.

Trans_ ID	Trans_ NUM	Prev_ptr	Next_ptr	Operation	Table	Value_ID	Attribute	Before_ trans	After_ trans
1	T1	NULL	2	START	**START TRANSACTION
2	T1	1	3	UPDATE	PRODUCT	‘ABC’	PROD_QOH	1025	1026
3	T1	2	4	UPDATE	PART	‘A’	PART_QOH	567	566
4	T1	3	5	UPDATE	PART	‘B’	PART_QOH	98	97
5	T1	4	6	UPDATE	PART	‘C’	PART_QOH	549	548
6	T1	5	NULL	COMMIT	** END TRANSACTION

e)

Explanation of Solution

Trace out of transaction log mentioned in sub part “d”:

The above transaction log has transaction ID(Trans_ID), transaction number(Trans_NUM), and other fields used to recover the transaction.

The trace out of transaction log from beginning of the transaction is as follows:

Trans_ID 1: Beginning of the transaction.

Trans_ID 2: Update the table “PRODUCT” by adding the attribute value from “1025” to “1026”.

Trans_ID 3: Update the table “PART” by removing the attribute value from “567” to “566”.

Trans_ID 4: Update the table “PART” by removing the attribute value from “98” to “97”.

Trans_ID 5: Update the table “PART” by removing the attribute value from “549” to “548”.

Trans_ID 6: End of the transaction.