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.