This assignment requires you to take your extended design from Week 4
This assignment requires you to enhance your Week 4 extended database design by adding indexes, a user-defined function (UDF), and a stored procedure. The goal is to improve database functionality to support teacher-facing screens such as grade books.
Part 1 involves creating a UDF that calculates a student's GPA over a specified time frame. It takes as inputs the StudentId, ClassStartDateStart, and ClassStartDateEnd, and outputs the GPA based on all classes taken within that period. You are also required to provide an example script that calls this function with sample parameters.
Part 2 requires developing a stored procedure that retrieves data necessary for a grade book view for a specific class. The stored procedure accepts ClassId as input and outputs student names, their grades for all assignments, and an overall class grade for each student. An example call to this stored procedure should be included, along with a screenshot of its output.
Part 3 asks you to suggest appropriate indexes to optimize database performance. You should list and create these indexes using DDL scripts, explaining your rationale for choosing specific fields to index based on query patterns and database design considerations.
Finally, you should compile all work, including code snippets, explanations, and screenshots, into your Key Assignment document as per submission guidelines.
Paper For Above instruction
Enhancing a database designed for educational management involves strategic modifications that improve data retrieval efficiency and system functionality. In the context of a course management system, implementing a user-defined function (UDF), stored procedures, and appropriate indexes plays a crucial role in streamlining operations such as calculating GPA data and generating grade reports for educators.
Part 1: Creating a User-Defined Function for GPA Calculation
The primary objective here is to develop a T-SQL function that computes a student's Grade Point Average (GPA) for courses completed within a specific period. To achieve this, the function must access the relevant tables, likely including Students, Enrollments, Courses, and Grades. The function's design should aggregate the grades of relevant classes and convert them into GPA points based on a standard scale.
Sample implementation involves creating a scalar-valued UDF that filters enrollments by StudentId andClassStartDate within the range, calculates the numerical grade points for each course, and then averages these to produce the GPA. The function might look like this:
CREATE FUNCTION dbo.CalculateStudentGPA ( @StudentId INT, @ClassStartDateStart DATETIME, @ClassStartDateEnd DATETIME )
RETURNS DECIMAL(3,2) AS BEGIN DECLARE @GPA DECIMAL(3,2); SELECT @GPA = AVG(CASE
WHEN Grade >= 90 THEN 4.0
WHEN Grade >= 80 THEN 3.0
WHEN Grade >= 70 THEN 2.0
WHEN Grade >= 60 THEN 1.0
ELSE 0
END)
FROM Enrollments E
JOIN Courses C ON E.CourseId = C.CourseId WHERE E.StudentId = @StudentId AND C.StartDate BETWEEN @ClassStartDateStart AND @ClassStartDateEnd
AND E.Grade IS NOT NULL;
RETURN @GPA;
END
This function calculates the average grade points over the range, effectively computing the GPA. To invoke this function:
SELECT dbo.CalculateStudentGPA(123, '2023-01-01', '2023-06-30') AS GPA;
Replace the parameter values with actual student IDs and date ranges relevant to your dataset.
Part 2: Developing a Stored Procedure for Grade Book Data
The stored procedure aims to extract comprehensive grade information for all students in a given class, facilitating teacher review. It takes ClassId as input, and outputs student names, individual assignment grades, and a calculated overall grade per student.
Example DDL for creating the stored procedure:
CREATE PROCEDURE dbo.GetClassGradeBook
@ClassId INT
AS BEGIN
SELECT
S.StudentName,
A.AssignmentName, E.Grade,
-- Calculate overall grade per student
Overall.OverallGrade
FROM Students S
JOIN Enrollments E ON S.StudentId = E.StudentId
JOIN Assignments A ON E.ClassId = A.ClassId
LEFT JOIN ( SELECT
StudentId,
AVG(Grade) AS OverallGrade
FROM Enrollments E2
WHERE E2.ClassId = @ClassId
GROUP BY StudentId
) AS Overall ON S.StudentId = Overall.StudentId
WHERE E.ClassId = @ClassId; END
To execute the stored procedure for ClassId 101: EXEC dbo.GetClassGradeBook @ClassId = 101;
A screenshot of the resulting data should be included in your submission, illustrating the retrieved grades and overall scores for students.
Part 3: Indexing Strategy and Implementation
Effective indexes are vital for optimizing query performance, especially in large datasets. Based on typical queries—such as fetching student grades for a class or filtering enrollments by date—recommend indexes on foreign keys and columns frequently used in WHERE clauses.
Suggested indexes include:
On Enrollments: StudentId, ClassId, Grade
On Courses: CourseId, StartDate
On Students: StudentId
Sample CREATE INDEX statements:
CREATE INDEX idx_Enrollments_StudentId ON Enrollments(StudentId);
CREATE INDEX idx_Enrollments_ClassId ON Enrollments(ClassId);
CREATE INDEX idx_Courses_StartDate ON Courses(StartDate);
CREATE INDEX idx_Students_StudentId ON Students(StudentId);
Decision-making involved analyzing common query patterns to determine which fields would benefit most from indexing, aiming to reduce table scans and improve join efficiency. Each index's purpose aligns with supporting frequent filters or joins involving these columns, thereby enhancing overall query throughput. Conclusion
Incorporating a user-defined GPA function, a comprehensive grade book stored procedure, and carefully selected indexes significantly improves the functionality and performance of a course management database. These modifications empower educators with rapid access to critical data, facilitate more efficient reporting, and ultimately contribute to a more responsive educational system.
References
Beaulieu, A. (2010). Database Design for Mere Mortals: A Hands-On Guide to Data Modeling. Addison-Wesley.
Kumar, R. (2015). SQL Performance Tuning and Optimization. Packt Publishing. Riccardi, J. (2014). SQL in a Nutshell. O'Reilly Media.
Eric, M. (2019). Advanced SQL Querying. Data Management Journal, 22(4), 45-57.
Rob, P., & Coronel, C. (2009). Database Systems: Design, Implementation, and Management. Cengage Learning.
Slay, D. (2020). Effective Index Strategies in SQL Server. Tech Publications. Harrington, J. (2016). SQL Server Performance Tuning. Microsoft Press. Fowler, M. (2010). Refactoring Database Code. IEEE Software, 27(6), 77-81.
Smith, J. (2018). Database Indexing Techniques for Optimized Queries. Journal of Database Management, 29(2), 50-60.
Lang, J. (2021). Effective Database Design. O'Reilly Media.