CS 327e
Fall 2002
Due: 2002-11-04
Monday 8.30 a.m. by email and in SQLServer.
PURPOSE:
The purpose of
this lab is to learn about
a) Functions, Stored Procedures, and Triggers
using SQL Server 2000.
b) Referential Integrity Constraints.
c) Collaboratively working within the group
to submit this lab.
DESCRIPTION/IMPLEMENTATION:
Using the schema
in Lab1 (and Test 1), do the following:
4and5.1
Login to your “dbgroupx” database, and import the SPJ from Lab1
Excel spreadsheet. Change the appropriate data types, as in lab1 and lab3.
4and5.2 (20 points)
For this SPJ database, create the appropriate PK-FK relationships
(using appropriate ON DELETE and ON UPDATE rules on FK) using ALTER TABLE
syntax as shown in Class web page link # 11.
4and5.3 (30 points)
Write a FUNCTION that takes in a project number and part color,
and returns the total shipped quantity of color parts within that project.
Return a –1 (-2, -3 etc.) for any error conditions (e.g. the input
project does not exist, SHIPMENT table does not exist etc.) If the project
exists in the shipment table, but no parts for the color specified exist,
return a quantity of 0.
Clearly state your assumptions and put relevant documentation in
the body of the function.
4and5.4 (100
points)
Write a STORED PROCEDURE that takes
(as input), the Supplier ID, and then produces the following report:
|
SupplierName |
ProjectName |
PartColor |
SPJPColorQty |
PJPColorQty |
JQty |
SPJPctQty |
SJPctQty |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
This report is to be produced for
each part number in the PART table, and each project in the PROJECT
table. The results should be sorted by ProjectName, PartColor (both in
ascending order).
The columns have the following
meaning:
1)
SupplierName: Corresponds
to the name of the (input) Supplier ID.
2)
ProjectName: Corresponds
to the name of the Project.
3)
PartColor: Corresponds
to the color of the Part.
4) SPJPColorQty: Corresponds to the total shipped quantity
for all “PartColor” Parts, supplied by “SupplierName”, for a given project
“ProjectName”.
5) PJPColorQty: Corresponds to the total shipped quantity
for all “PartColor” Parts, supplied by all suppliers, for a given project
“ProjectName”.
6) JQty: Corresponds to the total shipped
quantity for all Parts, supplied by all suppliers, for a given project
“ProjectName”.
7) SPJPctQty: (((4) / (5)) * 100.00)
8) SJPctQty: (((4) / (6)) * 100.00)
The stored procedure must
also return (as an OUTPUT parameter) a status variable that has one of the
following values:
-5: SUPPLIER table does
not exist.
-4: SHIPMENT table does
not exist.
-3: PART table does not
exist.
-2: PROJECT table does not
exist.
-1: Supplier ID does not
exist in the SUPPLIER table.
-6: For any other error
condition.
0: If successful. Since there are 6 parts and 7
projects, this should return 42 rows. For all error conditions, return 0 rows.
NOTE: For the successful
case, you may choose to
a)
directly display the results, or
b)
CREATE a temporary table, INSERT the rows into this
table, do a SELECT to display the results, and then DROP the table, or
c)
permanently CREATE the table first, and on every invocation
of the procedure, DELETE this resultant table, INSERT the rows, followed by a
SELECT.
HINT 1:
Look at the syntax for “WHILE” statement as shown in Class web page link #11.
Look at example B, titled
HINT 2: Look at
the syntax for NULLIF for calculations of column 7 and 8 above.
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 that are executed
within this procedure. You may choose to use the function created in
4and5.3 .
4and5.5 (50
points)
Create a table SHIPPEDPCOLORQTY (Color, Qty).
Create an “AFTER” trigger on
SHIPMENT such that whenever an INSERT, DELETE, or an UPDATE happens in the
SHIPMENT table, all rows in the SHIPPEDPCOLORQTY are deleted, and new rows are inserted with the
updated color(s) and their respective quantities of shipped parts.
HINT: Use an INSERT…SELECT.
Do the appropriate error
checking/validation. Clearly
state your assumptions and put relevant documentation in the body of the
trigger. Try the trigger out by adding a few shipment rows (and then,
remembering to delete the rows before you submit the lab). You may
choose to create other procedures that are executed within this trigger.
SUBMISSION:
Please submit an
electronic zipped file 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
The zipped file
should contain all the DDL and DML used in sections 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 “Natural
Products”, then submit a file named lab4and5group4.zip containing the file lab4and5group4.doc.
For this lab, only 1 submission per group is required.
Last Updated: 10/20/2002 9:12:03 PM