Write a program where the users enters a produtct id


Assignment: Enterprise Systems

Databases

Exercises

1. Create the following table (done in class) to hold product information. Attributes are as follows.

Z_###_PROD
               pid                Char(3) primary key
               pDesc           Char(20)
               price             DEC(8,2)
               Taxable        Char(1)

2. Use the Z###_INSERTPROD program created in class to insert the following records into the ZY##_PROD table:

pid

pDesc

Price

Taxable

P1
P2
P3
P4

Pencil
Pen
Paper
Apple

1.00
2.00
5.00
1.50

N
N
N
Y

3. Create the following two tables for Sales Orders:

SOHeaders and SODetails. Attributes for each are as follows.

a. Z_###_SOHeaders
                     SONum                   INT4 primary key
                     SOStatus                CHAR(1)
                     Subtotal DEC (8,2)
                     Tax                          DEC (8,2)
                     Total                       DEC (8,2)

b. Z_###_SODetails
                     SONum                   INT4
                     Pid                          CHAR(3)
                     Quantity DEC (8,2)
                     SubTotal DEC (8,2)
                     Tax                          DEC (8,2)
                     LineTotal                DEC (8,2)

4. Write a program Z_###_LOOKUPPRODUCT where the users enters a Produtct ID and your program displays the product description and unit price.

5. Write a program Z_###_ADDITEM where the user enters a Sales Order Number (not realistic), Product ID, and Quantity. The program retrieves the product price, calculates SubTotal, and inserts this new record to the SODetails tables. Print a confirmation to the user with some details.

6. Revise the Add Item program to include tax for taxable items. If the product is taxable, apply 0.0875% tax rate on the SubTotal to calculate the Tax. LineTotal is Subtotal plus Tax. Insert the recode into the SODetails table.

7. Revise the Lookup Product program to check for a valid Product ID. The program is to check if the Product ID exists in the product table (Hint: Can use count(*)). If it doesn't, print an error message. If it does, print the product details.

8. Revise the Add Item program to create SOHeaders records. If this is the first item the user adds to the order (e.g. shopping car), we will create a header record with the status of ‘O' (Open). The Add Items program will check if the SONum exists in SOHeaders (Hint: may use count(*)). If it does not, insert the record.

Format your assignment according to the following formatting requirements:

1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.

2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.

3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.

Request for Solution File

Ask an Expert for Answer!!
Programming Languages: Write a program where the users enters a produtct id
Reference No:- TGS02990378

Expected delivery within 24 Hoursrs

2015 ©TutorsGlobe All rights reserved. TutorsGlobe Rated 4.8/5 based on 34139 reviews.