FirstName

# The University of Texas

CS 327e

Fall 2002

#

# Lab 4 and 5 (combined)

Due: 2002-11-04 Monday 8.30 a.m. by email and in SQLServer.

# Total: 200 points

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

###### “Using WHILE within a procedure with cursors”. You will probably need to use nested WHILE statements.

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