FirstName

# The University of Texas

CS 327e

Spring 2003

#

# Lab 4 and 5 (combined)

Due: 2003-04-04 Friday 8.30 a.m. by email and in SQLServer.

# Total: 200 points

PURPOSE:

The purpose of this lab is to learn about

a) Stored Procedures using SQL Server 2000.

b) Java application invoking stored procedures.

c) Collaboratively working within the group to submit this lab.

DESCRIPTION/IMPLEMENTATION:

Using the schema in Lab3, do the following:

4and5.1

Login to your “dbgroupx” database, and import the Chapter 5 sales company database from Lab3 Excel spreadsheet. Change the appropriate data types, as in lab3.

4and5.2 (30 points)

Write a FUNCTION that takes in a vendor code, and returns the total number of distinct customers who bought at least 1 unit of product sold by that vendor.

Return a –1 (-2, -3 etc.) for any error conditions (e.g. the input vendor does not exist in the VENDOR table, and others etc.) If the vendor exists in the VENDOR table, but no products exist in the LINE table, return number of customers as 0.

Clearly state your assumptions and put relevant documentation in the body of the function.

4and5.3 (70 points)

Write a STORED PROCEDURE that takes (as input), the Vendor Code, and then produces the following report:

INV_NUMBER | InvTotUnits | InvTotDistinctUnits | InvTotPrice | InvAvgPrice | ProdTotUnits | ProdTotDistinctUnits | ProdTotPrice

ProdAvgPrice | ProdPctInvPrice | ProdPctInvAvgPrice

This report is to produce 1 row for each invoice in the LINE table, which has at least 1 unit of product supplied by this (input) vendor. The results should be sorted by Invoice Number in ascending order.

The columns have the following meaning:

1) INV_NUMBER: Corresponds to the Invoice number, which has at least 1 product, supplied by this vendor.

2) InvTotUnits: Corresponds to the sum of all LINE_UNITS in this invoice.

3) InvTotDistinctUnits: Corresponds to the count of all unique products in this invoice.

4) InvTotPrice: Corresponds to the sum of all (LINE_UNITS * LINE_PRICE) in this invoice.

5) InvAvgPrice: (4) / (2).

6) ProdTotUnits: Corresponds to the sum of all LINE_UNITS in this invoice that are supplied by this vendor.

7) ProdTotDistinctUnits: Corresponds to the count of all unique products in this invoice that are supplied by this vendor.

8) ProdTotPrice: Corresponds to the sum of all (LINE_UNITS * LINE_PRICE) in this invoice for products that are supplied by this vendor.

9) ProdAvgPrice: (8) / (6).

10)ProdPctInvPrice: ((8) / (4)) * 100.00

11)ProdPctInvAvgPrice: ((9) / (5)) * 100.00

The stored procedure must also return (as an OUTPUT parameter) a status variable that has one of the following (possible) values:

-6: PRODUCT table does not exist.

-5: INVOICE table does not exist.

-4: VENDOR table does not exist.

-3: LINE table does not exist.

-2: CUSTOMER table does not exist.

-1: For any other error condition.

0: If successful. For all error conditions, return 0 rows.

Do the appropriate error checking/validation. Clearly state your assumptions and put relevant documentation in the body of the stored procedure. You may choose to create other procedures/views etc. that are executed within this procedure.

4and5.4 (30 points)

Write a Java application that will ask the user for a vendor code. Based on the vendor code, the program will invoke a stored procedure (that calls the function in 4and5.2) to return the distinct number of customers. Additionally, it will display a helpful, user-friendly message, based on the status being returned by the procedure.

You may choose to write an application using technologies other than Java. In either case, submit all your programming language and procedural language used for the lab, including the executable. If you are creating a browser based application, submit the URL, instead of the application.

Do the appropriate error checking/validation. Clearly state your assumptions and put relevant documentation in the body of the application. Try/Test the application out by various vendor codes.

4and5.5 (70 points)

Write a Java application that will ask the user for a vendor code. Based on the vendor code, the program will invoke a stored procedure (in 4and5.3) to return the report as shown above (containing 11 columns). Additionally, it will display a helpful, user-friendly message, based on the status being returned by the procedure.

You may choose to write an application using technologies other than Java. In either case, submit all your programming language and procedural language used for the lab, including the executable. If you are creating a browser based application, submit the URL, instead of the application.

Do the appropriate error checking/validation. Clearly state your assumptions and put relevant documentation in the body of the application. Try/Test the application out by various vendor codes.

SUBMISSION:

Please submit an electronic zipped file (Yes, its OK with the TA) by emailing it to the TA (CC: the instructor) by the Due Date/Time. Consult the class web page for

a) Email addresses of Instructor/TA

b) Late submission policy

c) Academic Dishonesty Policy

You may choose to write an application using technologies other than Java. In either case, submit all your programming language and procedural language as part of the zipped file, along with any executables/URL for 4and5.2, 4and5.3, 4and5.4, and 4and5.5, along with any assumptions. The TA will be checking your SQL Server account to see the appropriate database.

Also, create all of these DBMS objects in your group database i.e. dbgroupx.

NOTE: For electronic submissions, use file naming conventions such as lab4and5groupX.zip, where X is your group number as shown on the class project web page.

For example, if you are submitting a MS-Word document, and the group name is “Good Question”, then submit a file named lab4and5group1.zip containing the file lab4and5group1.doc. For this lab, only 1 submission per group is required.

NOTE: Please refer to links 10, 12, 15, and 37 of the class web page for helpful hints.

Last Updated: 3/6/2003 10:17:24 PM