Database Systems: Design, Implementation, Management, Loose-leaf Version
Database Systems: Design, Implementation, Management, Loose-leaf Version
12th Edition
ISBN: 9781305866799
Author: Coronel, Carlos; Morris, Steven
Publisher: Course Technology
Expert Solution & Answer
Book Icon
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.

d)

Expert Solution
Check Mark

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.

Want to see more full solutions like this?

Subscribe now to access step-by-step solutions to millions of textbook problems written by subject matter experts!
Students have asked these similar questions
Lab 07: Java Graphics (Bonus lab) In this lab, we'll be practicing what we learned about GUIs, and Mouse events. You will need to implement the following: ➤ A GUI with a drawing panel. We can click in this panel, and you will capture those clicks as a Point (see java.awt.Point) in a PointCollection class (you need to build this). о The points need to be represented by circles. Below the drawing panel, you will need 5 buttons: о An input button to register your mouse to the drawing panel. ○ о о A show button to paint the points in your collection on the drawing panel. A button to shift all the points to the left by 50 pixels. The x position of the points is not allowed to go below zero. Another button to shift all the points to the right 50 pixels. The x position of the points cannot go further than the You can implement this GUI in any way you choose. I suggest using the BorderLayout for a panel containing the buttons, and a GridLayout to hold the drawing panel and button panels.…
If a UDP datagram is sent from host A, port P to host B, port Q, but at host B there is no process listening to port Q, then B is to send back an ICMP Port Unreachable message to A. Like all ICMP messages, this is addressed to A as a whole, not to port P on A. (a)  Give an example of when an application might want to receive such ICMP messages. (b)  Find out what an application has to do, on the operating system of your choice, to receive such messages. (c)  Why might it not be a good idea to send such messages directly back to the originating port P on A?
Discuss how business intelligence and data visualization work together to help decision-makers and data users. Provide 2 specific use cases.
Knowledge Booster
Background pattern image
Similar questions
SEE MORE QUESTIONS
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781285867168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning