Database Transactions – I ✓ Solved

Database Transactions -- I

Suppose that you are a manufacturer of product XYZ, which is composed of parts X, Y, and Z. Each time a new product XYZ is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. And each time the product is created, the parts inventory, using PART_QOH in a table named PART, must be reduced by one each of parts X, Y, and Z. The sample database contents are shown in the two tables below.

Table name: PRODUCT

PROD_CODE PROD_QOH

XYZ 205

Table name: PART

PART_CODE PART_QOH

X 150

Y 300

Z 400

Given this information, answer Questions 1-4.

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 step 1.

3. Write the complete transaction(s).

4. Write the transaction log, using the table below as your template. Here, we assume that the product XYZ has PROD_QOH = 205 at the start of the transaction and the transaction represents the addition of one new product. We also assume that the PART components X, Y and Z have PROD_QOH equal to 150, 300, and 400 respectively.

Paper For Above Instructions

1. Identifying Database Requests for Inventory Update

In this scenario, we have two key tables: PRODUCT and PART. For the inventory update of the new product XYZ and its associated parts X, Y, and Z, we can identify a total of four database requests:

1. Update the quantity on hand (QOH) for PRODUCT table for the new product XYZ.
2. Update the quantity on hand (QOH) for PART table for part X.
3. Update the quantity on hand (QOH) for PART table for part Y.
4. Update the quantity on hand (QOH) for PART table for part Z.

2. SQL Database Requests

To illustrate the database requests mentioned above, we can use SQL statements. Here are the SQL commands for each request:

-- Update PRODUCT table
UPDATE PRODUCT SET PROD_QOH = PROD_QOH + 1 WHERE PROD_CODE = 'XYZ';
-- Update PART table for part X
UPDATE PART SET PART_QOH = PART_QOH - 1 WHERE PART_CODE = 'X';
-- Update PART table for part Y
UPDATE PART SET PART_QOH = PART_QOH - 1 WHERE PART_CODE = 'Y';
-- Update PART table for part Z
UPDATE PART SET PART_QOH = PART_QOH - 1 WHERE PART_CODE = 'Z';

3. Complete Transactions

A transaction in a database context represents a sequence of operations performed as a single logical unit of work. If any operation within the transaction fails, the entire transaction should be rolled back to maintain data integrity. For our scenario, the complete transaction can be represented as follows:

BEGIN TRANSACTION;
-- Update the PRODUCT inventory
UPDATE PRODUCT SET PROD_QOH = PROD_QOH + 1 WHERE PROD_CODE = 'XYZ';
-- Update the PART inventories for X, Y, and Z
UPDATE PART SET PART_QOH = PART_QOH - 1 WHERE PART_CODE = 'X';
UPDATE PART SET PART_QOH = PART_QOH - 1 WHERE PART_CODE = 'Y';
UPDATE PART SET PART_QOH = PART_QOH - 1 WHERE PART_CODE = 'Z';
COMMIT TRANSACTION;

4. Transaction Log

The transaction log provides a record of the changes made during the transaction. Below is an example of what the transaction log would look like given the database state before and after the transaction:

References

• Connolly, T. M., & Begg, C. E. (2015). Database Systems: A Practical Approach to Design, Implementation, and Management. Pearson.
• Kroenke, D. M., & Auer, D. (2016). Database Concepts. Pearson.
• Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Addison-Wesley.
• Hernandez, M. J. (2014). Database Design for Mere Mortals. Addison-Wesley.
• Date, C. J. (2012). Database Design and Relational Theory. O'Reilly Media.
• Rob, P., & Coronel, C. (2010). Database Systems Design, Implementation, & Management. Cengage Learning.
• Parker, D. (2016). SQL Fundamentals. Pragmatic Bookshelf.
• Mehta, A. (2019). Learn SQL Server. BPB Publications.
• Sams, T. (2014). Learn SQL in One Day and Learn it Well. Independently Published.
• Beck, K., & Andres, C. (2005). Extreme Programming Explained: Embrace Change. Addison-Wesley.