Write a stored procedure that updates the weekly duty roster for an employee and allocates him/her a maximum of 5 work shifts in a given branch. To update the roster, the procedure ensures that:

i. The employee has an existing roster allocated to her/him. In case there is no existing roster for a given employee, the procedure doesnt update the roster, rather prints an appropriate error message. ii. The day of current roster is shifted by one day in the updated roster. For instance, if the current roster for an employee shows work shifts from Monday to Friday, then the updated roster will allocate work shifts from Tuesday to Saturday. For simplicity, we will assume that the type and number of work shifts allocated to an employee remain same from week to week unless an exception occurs such as overallocation. However, the manager may wish to add any extra work shifts to an employee manually. iii. A warning message is displayed in case the allocated hours of work for an employee exceeds the standard hours of work (35 hours per week).

Publisher Employee EmployeeID CHAR(8) Branch BranchID INT PublisherID CHAR(3) PublisherName VARCHAR(45) PublisherCity VARGAR (30) OPublisherContactio AR(10) Branch Name VARCHAR(45) EmployeeName VARCHAR(45) EmployeeType VARCHAR(20) Supervisor. EmployeeID CH Branch Address VARCHAR Num berOfEm ployees INT DutyRoster EmployeeID CHAR(8) Inventory Branch_BranchID INT Book_BookID INT BranchID INT AvailableQuantity INT tWorkingShifID INT DutyRosterHistory EmployeelD Char (8) BranchID INT WorkingShiftID INT Weekstarting VARCHAR(15) Book WorkingShift WorkingShifID INT BookID INT Writes Author AuthorID INT Book_BookID INT SequenceNum ber INT > BookTitle VARCHAR(45) BookType VARCHAR(45) > Book Price DECIMAL (8,2) 1sPaperback VARCHAR(3) WorkingShiftweekDay VARCHAR(15) Author AuthoriD INT workingShiftStartTime TIME(6) workingShiftEndTm e TIME (6) dutyT ype VARCHAR(20) AuthorName VARCHAR(45) Auth orE m ail Address V ARCHAR(45) Publisher-Publisher!D CHAR(3) Indexes Figure 1 : Relational Data Model Publisher Employee EmployeeID CHAR(8) Branch BranchID INT PublisherID CHAR(3) PublisherName VARCHAR(45) PublisherCity VARGAR (30) OPublisherContactio AR(10) Branch Name VARCHAR(45) EmployeeName VARCHAR(45) EmployeeType VARCHAR(20) Supervisor. EmployeeID CH Branch Address VARCHAR Num berOfEm ployees INT DutyRoster EmployeeID CHAR(8) Inventory Branch_BranchID INT Book_BookID INT BranchID INT AvailableQuantity INT tWorkingShifID INT DutyRosterHistory EmployeelD Char (8) BranchID INT WorkingShiftID INT Weekstarting VARCHAR(15) Book WorkingShift WorkingShifID INT BookID INT Writes Author AuthorID INT Book_BookID INT SequenceNum ber INT > BookTitle VARCHAR(45) BookType VARCHAR(45) > Book Price DECIMAL (8,2) 1sPaperback VARCHAR(3) WorkingShiftweekDay VARCHAR(15) Author AuthoriD INT workingShiftStartTime TIME(6) workingShiftEndTm e TIME (6) dutyT ype VARCHAR(20) AuthorName VARCHAR(45) Auth orE m ail Address V ARCHAR(45) Publisher-Publisher!D CHAR(3) Indexes Figure 1 : Relational Data Model