1.

Create a stored procedure named spInsertDepartment that takes as a parameter a value for the department name and adds a new row into the Departments table.

Even though Departments table allows null department name, validate that department name is provided and is not an empty string. Throw an error with message Department name must be provided if incorrect.

Department name has to be unique. The procedure should throw an error with message Department name must be unique upon attempt to insert a department with a name that is already in the table.

Code three tests executing this procedure: 1) with null or empty department name, 2) with a unique department name, and 3) with a duplicate department name.

2.

Create a function named fnStudentUnits that calculates the sum of course units of a student. This function accepts one parameter, the student ID, and returns an integer value that is the sum of the course units for the student. You can find courses that the student takes in StudentCourses table, and CourseUnits for each course in the Courses tables.

If the student does not exist or has no courses, this function should return 0.

Code three tests: 1) for a student who has courses, 2) for student who does not have courses, and 3) a non-existing student ID. For each test, display the value of the student ID that was passed to the function and the result returned by the function. Also, run a supportive SELECT query or queries that prove the test results are correct.

3.

Create a function named fnTuition that calculates the tuition for a student. This function accepts one parameter, the student ID, and it calls the fnStudentUnits function that you created in task 2. The tuition value for the student calculated according to the following pseudocode:

if (student units = 0)

tuition = 0

else if (student units >= 9)

tuition = (full time cost) + (student units) * (per unit cost)

else

tuition = (part time cost) + (student units) * (per unit cost)

Retrieve values of FullTimeCost, PartTimeCost, and PerUnitCost from table Tuition.

If there is no student with the ID passed to the function, the function should return 0.

Code two tests: 1) a student who has < 9 student units, and 2) for a student who has >= 9 student units. For each test, display StudentID and the result returned by the function. Also, run supportive SELECT query or queries that prove the results to be correct.

4.

Create a trigger named InstructorInsertSalaryTR that fires when a new row is added to the Instructors table.

Throw an error when multiple rows are inserted.

When there is only one row inserted, validate that the AnnualSalary value is positive (strictly greater than zero) and less than or equal to 120000. Throw an error with appropriate message if the salary value is negative or too big.

Also, if the salary value is between 0 and 10000, assume that there was a mistake of entering monthly salary instead of annual salary, and multiply the salary value by 12. For example, if the new value of the salary is 5000, it should be changed to 60000.

No need to validate any other data from the inserted row.

Test the trigger with appropriate INSERT statements. There should be four test cases:

-with negative salary,

-with positive salary <= 10000,

-with salary greater that 10000 and less than or equal to 120000, and

-with salary > 120000.

5.

Write a script that produces the following report:

For each instructor, display one line with InstructorID, last name, first name, how many courses the instructor teaches, and a note that is defined as follows:

Fully assigned, when instructor teaches two or more courses, and

Available otherwise

Instructors table contains data about instructors, and each course in the Courses table references InstructorID of an instructor who teaches the course.

The structure of the script is totally up to you, as long as it displays the desired report.