Purchase Solution

Database Request for Product ABC

Not what you're looking for?

Ask Custom Question

Suppose that 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 this information:

a. How many database requests can you identify for an inventory update for both PRODUCT and PART?
b. Using SQL, write each database request you have identified in Step a.
c. Write the complete transaction(s).
d. Write the transaction log, using Table 10.1 as your template.
e. Using the transaction log you created in Step d, trace its use in database recovery.

Attachments
Purchase this Solution

Solution Summary

An inventory update for product ABC is analyzed.

Solution Preview

CREATE TABLE part(
PART_CODE char(20)
PRIMARY KEY,
PART_QOH int NOT NULL

)
CREATE TABLE PRODUCT(
PROD_CODE char(20)
PRIMARY KEY,
PROD_QOH int NOT NULL
)
SELECT * FROM part
SELECT * FROM PRODUCT

a. How many database requests can you identify for an inventory update for both PRODUCT and PART?

There are two correct answers either 2 or 4.
Depending in how your SQL statements are created

b. Using SQL, write each database request you have identified in Step a.

/*********using 2 sql statements*/
update part
set PART_QOH = PART_QOH -1
where PART_CODE in ('A','B','C')

-- UPDATE the PRODUCT with PROD_QOH record
update PRODUCT
set PROD_QOH = PROD_QOH + 1
WHERE PROD_CODE = 'ABC'

/*********using 4 sql statements*/
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'

-- UPDATE the PRODUCT with PROD_QOH record
update PRODUCT
set PROD_QOH = PROD_QOH + 1
WHERE PROD_CODE = 'ABC'

--c. Write the complete transaction(s).

BEGIN TRANSACTION -- Start the transaction

-- UPDATE the Part's Part_qoh in each parts ...

Purchase this Solution


Free BrainMass Quizzes
Java loops

This quiz checks your knowledge of for and while loops in Java. For and while loops are essential building blocks for all Java programs. Having a solid understanding of these constructs is critical for success in programming Java.

Javscript Basics

Quiz on basics of javascript programming language.

Word 2010: Table of Contents

Ever wondered where a Table of Contents in a Word document comes from? Maybe you need a refresher on the topic? This quiz will remind you of the keywords and options used when working with a T.O.C. in Word 2010.

Inserting and deleting in a linked list

This quiz tests your understanding of how to insert and delete elements in a linked list. Understanding of the use of linked lists, and the related performance aspects, is an important fundamental skill of computer science data structures.

C# variables and classes

This quiz contains questions about C# classes and variables.