The University of Texas

CS 327e

Spring 2002

Lab 4 and 5 (combined)

Due: 2002-04-16 Tuesday 8.30 a.m. by email.

Total: 200 points

PURPOSE:

The purpose of this lab is to learn about

a) Functions, Stored Procedures, and Triggers using SQL Server 2000.

DESCRIPTION/IMPLEMENTATION:

Using the schema in Lab3 (and Test 2), do the following:

4.1

Create the following schema (identical to lab3, except the table names are prefixed with LAB4) using the DDL in the Lab3 solution (simply change all table names)

1) LAB4STUDENT (Last4SSN, LastName, FirstName, Email, MajorID, GroupID, SQLServerUserID)

2) LAB4STUDMAJOR (MajorID, MajorDesc)

3) LAB4EXAM (ExamID, ExamDesc, ExamMaxScore, ExamExtraCredits, ExamPcntOfTotal)

4) LAB4STUDSCORE (Last4SSN, ExamID, ExamScore)

5) LAB4PROJECTGROUP (GroupID, GroupName, ProjectDesc, SQLServerGroupID)

6) LAB4SQLSERVERACCT (SQLServerID, SQLServerPswd)

4.2

Import the data into these 6 tables from the excel spreadsheet shown below.

4.3

Create a new table

LAB4FINALGRADE (Last4SSN, TotalPoints, LetterGrade)

Where LAB4FINALGRADE.Last4SSN is FK to LAB4STUDENT.Last4SSN. TotalPoints should allow NULLs, whereas LetterGrade should not. The TotalPoints and the LetterGrade represent the whole course for that student. Clearly state your assumptions.

4.4

Write a FUNCTION that takes in a numerical exam score, and returns a letter grade, based on

The following logic:

>=90.00 ‘A’

>=80.00 and < 90.00 ‘B’

>=70.00 and < 80.00 ‘C’

>=60.00 and < 70.00 ‘D’

< 60.00 ‘F’

This function is essentially used to satisfy the transitive dependency

TotalPointsàLetterGrade

Clearly state your assumptions.

4.5

Write a STORED PROCEDURE that takes (as input), the Last4SSN, and then inserts a new row in the LAB4FINALGRADE table. If the student already exists in LAB4FINALGRADE, then it updates that row, and does not insert a new row.

The total score (TotalPoints) are calculated by taking the weighted score based on the LAB4EXAM table. Assume each student may/may not have all their scores in the LAB4STUDSCORE table.

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

-2: A new student row was entered successfully in LAB4FINALGRADE.

-1: An existing student row was updated successfully in LAB4FINALGRADE.

1: Couldn’t find the associated student (in the LAB4STUDENT table) having the “Last4SSN”

2: Couldn’t find 1 or more exam entry in the LAB4STUDSCORE table, that exists in the LAB4EXAM table

3: Any other kind of error.

If the status to be returned is 2, then the associated grade entered in LAB4FINALGRADE.LetterGrade should be ‘U’, with a LAB4FINALGRADE.TotalPoints value of NULL.

Only for status –2, -1, and 2, should the LAB4FINALGRADE table be written to.

Assume the total score is calculated out of 100 for each exam, even though students can get more than that (bonus/extra points) as stated in the LAB4EXAM table. In other words, it is possible for a student to get more than 100.00 as their TotalPoints. Use the function from 4.4 to populate LAB4FINALGRADE.LetterGrade for valid grades.

Do the appropriate error checking/validation. Clearly state your assumptions. You may choose to create other procedures that are executed within this procedure.

4.6

Create an “AFTER” trigger on LAB4FINALGRADE such that whenever an INSERT or an UPDATE happens that involves LAB4FINALGRADE.LetterGrade, a new table is created (unless it already exists). The structure of the table is as follows:

LAB4TESTSOVERQUIZZES (Last4SSN, FirstName, LastName, LetterGrade)

This table is refreshed by the trigger (i.e. all existing rows are deleted, new rows are inserted) with all students who have got either an ‘A’, ‘B’, ‘C’, ‘D’, or an ‘F’; and their combined scores from the 2 tests (out of a total of 200) are greater than their combined scores from the 2 quizzes (out of a total of 200).

HINT: Use an INSERT…SELECT

HINT: Use COLUMNS_UPDATED capability of SQL Server 2000 triggers (See link 13 on class web page), as shown in

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
        [ WITH APPEND ]
        [ NOT FOR REPLICATION ]
        AS
        [ { IF UPDATE ( column )
            [ { AND | OR } UPDATE ( column ) ]
                [ ...n ]
        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
                { comparison_operator } column_bitmask [ ...n ]
        } ]
sql_statement [ ...n ]
    }
}

Do the appropriate error checking/validation. Clearly state your assumptions. 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 4.3, 4.4, 4.5, and 4.6, 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 database i.e. dbuserxx.

NOTE: For electronic submissions, use file naming conventions such as lab4xxyy.zip, lab4xxyy.mdb etc., where xxyy are the last 4 digits of your SSN.

For example, if you are submitting a MS-Word document, and the last 4 digits of your SSN are 3456, then submit a file named lab34456.zip containing the file lab43456.doc.

Excel Spreadsheet Containing 6 data worksheets (.zip file)

Home Page

Last Updated: 4/2/2002 8:06:32 AM