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.