This assignment requires you to take your extended design from Week 4 This assignment involves enhancing your previous database design by adding indexes, creating a user-defined function (UDF), and developing a stored procedure. These modifications aim to improve database functionality, particularly for features like a grade book used by teachers. You will need to ensure that your SQL code is properly formatted and well-commented throughout. Part 1: Create a user-defined function (UDF) that calculates a student's GPA over a specified time period. The function should accept three inputs: StudentId (int), ClassStartDateStart (datetime), and ClassStartDateEnd (datetime). It should output the student's GPA for all classes taken within the provided date range. Additionally, include a script demonstrating how to call this function with sample parameter values. Part 2: Write a Data Definition Language (DDL) script for a stored procedure that retrieves data necessary to display a grade book for a professor. The stored procedure should accept a ClassId as input and return student names, their grades for assignments, and the overall grade for each student in that class. Provide an example call to this stored procedure with chosen parameters, along with a screenshot of the resulting output. Part 3: Suggest appropriate indexes to optimize query performance. Provide the SQL scripts to create these indexes and explain your reasoning for selecting specific fields. Include a narrative of your process, detailing each step taken, along with screenshots and the actual SQL commands used.
Paper For Above instruction In academic database management, optimizing data retrieval through indexing and function development is essential for efficient application performance. This paper discusses the implementation of a user-defined function to calculate student GPA over a specified period, the creation of a stored procedure to facilitate grade book functionalities, and the rationale behind the suggested indexes to improve query execution times. Introduction Effective database design is critical in educational environments where timely access to student and class information impacts instructional delivery and administrative efficiency. Advanced SQL features such as user-defined functions, stored procedures, and indexing extensively support dynamic data retrieval and