CS 327e
Spring 2003
Due: 2003-04-04
Friday 8.30 a.m. by email and in SQLServer.
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