RIS 320 Assignment 2 1 of 3 Assignment 2 10% of final mark Total marks: 100  You have to work in group of two students for this assignment.  The assignment contains TWO parts.  Part A and B needs to be saved in a sql script file named assignment_2_studentName1_studentName2.sql.  Run this file before submission to make sure it is free of errors.  Put proper comments with in your code.  You have been provided with a2.mdf .Attach it to a database in sql server. You need this database for all the questions. Required for submission Include the following declaration as comments at the top of the script file. Student Assignment Submission Form I/we declare that the attached assignment is my/our own work in accordance with the Seneca Academic Policy. No part of this assignment has been copied manually or electronically from any other source (including web sites) or distributed to other students . Name(s) Student ID(s) 1 2 You must include in the Submissions Form (above) with your assignment (one per group).RIS 320 Assignment 2 2 of 3 Each question is worth 5 marks. 1) Create a procedure called sp_UpdJob to update the job title in the dbo.job table. Provide the JOB ID and new title using two parameters. First check if the job id provided exists then do the update else print the message "job does not exists". Invoke the procedure to change the job id IT_DBA to DATA ADMINISTRATOR. 2) Create a user defined function called ufn_GetYearlyService ()to retrieve the total number of years of service for a specific employee. The function should accept the employee ID as a parameter and return the number of years of service. Call the function to return the years of service for all employees working in Shipping department, print the list in descending order of the years of service. The query should display the firstname, lastname, years of service for eachemployee. 3) Create a stored function called ufn_GetJobCount() to retrieve the total number of different jobs on which an employee worked. The function should accept the employee ID as parameter, and return the number of different jobs that the employee worked on including the current job. Whenever an employee changes a job, a row is inserted in the job_history table for that employee. Test your function for employee id 176, job count for him is 3. 4) Create a user defined cursor named Employee_List which will print the lastname, firstname, job_title and salary of all employees hired after 01-01-2007. 5) Write a stored procedure called sp_IsLeap to accept a year and check whether it is a leap year. For example, if the year entered is 1990, the output should be "1990 is not a leap year".[Hint:] The year should be exactly divisible by 4 but not divisible by 100, or it should be divisible by400. 6) Create a procedure called sp_GetLocations to query the department and locations table, retrieving the department name and city for a department when provided with the departmentID. 7) Write a function ufn_GetAnnualComp() to return the annual salary computed from an employee's monthly salary and commission passed as parameters. The function accepts parameters values for monthly salary and commission .Either or both values passed can be NULL but the functions should still return a non-NULL annual salary. The formula to calculate annual salaryis: Annual_salary=(salary*12)+(commission_pct*salary*12) Use the function in the SELECT statement for all the employees of department 60. 8) Create a function ufn_ValidDept() to validate a specified department_id and return an integer 0 if the department exists. Part A: T-SQL Cursors, Stored Procedures & Functions [ 40 marks]RIS 320 Assignment 2 3 of 3 Create a sql script to perform the following tasks one by one: a.) FileGroups [20 marks] 1) Add a new filegroup named FG2 to a2 database. 2) Make FG2 your default file group. 3) Create a new table using the given definition and add it to FG2. CREATE TABLE dbo.Test ( TestID INT IDENTITY, Column1 INT, Column2 INT, Column3 INT ) 4) Move this table to the PRIMARY filegroup. 5) You have a database with multiple filegroups. You need to restore one of the filegroups, For a2 write the command to restore FG2 b.) Backup and Recovery [40 marks] 1) A full backup of the a2 database every night to a backup file named a2_date.bak, keeping the last five backups at all times. After creating the backup, you want to test the backup to ensure it is a good backup do a complete restore from the backup. 2) As a part of your backup strategy, you are now implementing differential backups instead of full every night and a full backup every Sunday night. After creating the backup, you now need to restore the database to a point in time after the last full database backup, taking advantage of the differential backups that have been taken. Part B: Backup and Recovery [60 marks]