MySQL stored procedure:

These are the tables:

-- ----------------------------------------------------- -- Table `Branch` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `Branch` ( `BranchID` INT NOT NULL, `BranchName` VARCHAR(45) NULL, `BranchAddress` VARCHAR(30) NULL, `NumberOfEmployees` INT NULL, PRIMARY KEY (`BranchID`)) ENGINE = InnoDB;

-- ----------------------------------------------------- -- Table `Publisher` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `Publisher` ( `PublisherID` CHAR(3) NOT NULL, `PublisherName` VARCHAR(45) NULL, `PublisherCity` VARCHAR(30) NULL, `PublisherContactNo` CHAR(10) NULL, PRIMARY KEY (`PublisherID`)) ENGINE = InnoDB;

-- ----------------------------------------------------- -- Table `Author` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `Author` ( `AuthorID` INT NOT NULL, `AuthorName` VARCHAR(45) NULL, `AuthorEmailAddress` VARCHAR(45) NULL, PRIMARY KEY (`AuthorID`)) ENGINE = InnoDB;

-- ----------------------------------------------------- -- Table `Book` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `Book` ( `BookID` INT NOT NULL, `BookTitle` VARCHAR(45) NULL, `BookType` VARCHAR(45) NULL, `BookPrice` DECIMAL(8,2) NULL, `IsPaperback` VARCHAR(3) NULL, `Publisher_PublisherID` CHAR(3) NOT NULL, PRIMARY KEY (`BookID`), CONSTRAINT `fk_Book_Publisher1` FOREIGN KEY (`Publisher_PublisherID`) REFERENCES `Publisher` (`PublisherID`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

CREATE INDEX `fk_Book_Publisher1_idx` ON `Book` (`Publisher_PublisherID` ASC);

-- ----------------------------------------------------- -- Table `Employee` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `Employee` ( `EmployeeID` CHAR(8) NOT NULL, `EmployeeName` VARCHAR(45) NULL, `EmployeeType` VARCHAR(20) NULL, `Supervisor_EmployeeID` CHAR(8) NOT NULL, PRIMARY KEY (`EmployeeID`), CONSTRAINT `fk_Employee_Employee1` FOREIGN KEY (`Supervisor_EmployeeID`) REFERENCES `Employee` (`EmployeeID`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

CREATE INDEX `fk_Employee_Employee1_idx` ON `Employee` (`Supervisor_EmployeeID` ASC);

-- ----------------------------------------------------- -- Table `WorkingShift` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `WorkingShift` ( `WorkingShiftID` INT NOT NULL, `WorkingShiftWeekDay` VARCHAR(15) NULL, `WorkingShiftStartTime` TIME(6) NULL, `WorkingShiftEndTime` TIME(6) NULL, `dutyType` VARCHAR(20) NULL, PRIMARY KEY (`WorkingShiftID`)) ENGINE = InnoDB;

-- ----------------------------------------------------- -- Table `DutyRoster` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `DutyRoster` ( `EmployeeID` CHAR(8) NOT NULL, `BranchID` INT NOT NULL, `WorkingShiftID` INT NOT NULL, PRIMARY KEY (`EmployeeID`, `WorkingShiftID`, `BranchID`), CONSTRAINT `fk_DutyRoster_Employee` FOREIGN KEY (`EmployeeID`) REFERENCES `Employee` (`EmployeeID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_DutyRoster_Branch1` FOREIGN KEY (`BranchID`) REFERENCES `Branch` (`BranchID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_DutyRoster_WorkingShift1` FOREIGN KEY (`WorkingShiftID`) REFERENCES `WorkingShift` (`WorkingShiftID`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

CREATE INDEX `fk_DutyRoster_Employee_idx` ON `DutyRoster` (`EmployeeID` ASC);

CREATE INDEX `fk_DutyRoster_Branch1_idx` ON `DutyRoster` (`BranchID` ASC);

CREATE INDEX `fk_DutyRoster_WorkingShift1_idx` ON `DutyRoster` (`WorkingShiftID` ASC);

-- ----------------------------------------------------- -- Table `Writes` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `Writes` ( `Author_AuthorID` INT NOT NULL, `Book_BookID` INT NOT NULL, `SequenceNumber` INT NULL, PRIMARY KEY (`Author_AuthorID`, `Book_BookID`), CONSTRAINT `fk_Author_has_Book_Author1` FOREIGN KEY (`Author_AuthorID`) REFERENCES `Author` (`AuthorID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_Author_has_Book_Book1` FOREIGN KEY (`Book_BookID`) REFERENCES `Book` (`BookID`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

CREATE INDEX `fk_Author_has_Book_Book1_idx` ON `Writes` (`Book_BookID` ASC);

CREATE INDEX `fk_Author_has_Book_Author1_idx` ON `Writes` (`Author_AuthorID` ASC);

-- ----------------------------------------------------- -- Table `Inventory` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `Inventory` ( `Branch_BranchID` INT NOT NULL, `Book_BookID` INT NOT NULL, `AvailableQuantity` INT NULL, PRIMARY KEY (`Branch_BranchID`, `Book_BookID`), CONSTRAINT `fk_Branch_has_Book_Branch1` FOREIGN KEY (`Branch_BranchID`) REFERENCES `Branch` (`BranchID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_Branch_has_Book_Book1` FOREIGN KEY (`Book_BookID`) REFERENCES `Book` (`BookID`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

CREATE INDEX `fk_Branch_has_Book_Book1_idx` ON `Inventory` (`Book_BookID` ASC);

CREATE INDEX `fk_Branch_has_Book_Branch1_idx` ON `Inventory` (`Branch_BranchID` ASC);

-- -------------------------------------------- -- Table `DutyRosterHistory` -- -------------------------------------------- CREATE TABLE IF NOT EXISTS `DutyRosterHistory` ( `EmployeeID` CHAR(8) NOT NULL, `BranchID` INT NOT NULL, `WorkingShiftID` INT NOT NULL, `WeekStarting` DATE NOT NULL, PRIMARY KEY (`EmployeeID`, `BranchID`, `WorkingShiftID`, `WeekStarting`), CONSTRAINT `fk_DutyRosterHistory_Employee` FOREIGN KEY (`EmployeeID`) REFERENCES `Employee` (`EmployeeID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_DutyRosterHistory_Branch1` FOREIGN KEY (`BranchID`) REFERENCES `Branch` (`BranchID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_DutyRosterHistory_WorkingShift1` FOREIGN KEY (`WorkingShiftID`) REFERENCES `WorkingShift` (`WorkingShiftID`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

CREATE INDEX `fk_DutyRosterHistory_Employee_idx` ON `DutyRosterHistory` (`EmployeeID` ASC);

CREATE INDEX `fk_DutyRosterHistory_Branch1_idx` ON `DutyRosterHistory` (`BranchID` ASC);

CREATE INDEX `fk_DutyRosterHistory_WorkingShift1_idx` ON `DutyRosterHistory` (`WorkingShiftID` ASC);

-- --------------------------------------------- -- End of Script -- ---------------------------------------------

QUESTION/TASK: Write a stored procedure (with a cursor) 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:

a) 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.

b) 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.

c) 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 Em ployee EmployeeID CHAR(8) Branch BranchID INT Branch Name VARCHAR(45) PublisherID CHAR(3) PublisherName VARCHAR(45) PublisherCity VARCHAR(30) PublisherContactNo CHAR(10) EmployeeName VARCHAR(45) | EmployeeType VARCHAR (20) Branch Address VARCHAR. . . Supervisor-EmployeeID CH ONum berofEm ployees INT Inventory Branch BranchID INT DutyRoster EmployeeID CHAR(8) BranchID INT ? Book_BookID INT AvailableQuantity INT tWorkingShiftID INT Book WorkingShift WorkingShiftID INT workingShiftweekDay VARCHAR(15) BookID INT BookTitle VARCHAR(45) BookType VARCHAR(45) Book Price DECIMAL (8,2) >1sPaperback VARCHAR(3) Publisher-Publisher!D CHAR(3) Writes ? Author AuthorID INT ? Book_BookID INT Author WorkingShiftStartTime TIME(6) WorkingShiftEndTim e TIME(6) dutyType VARCHAR(20) AuthorID INT AuthorName VARCHAR(45) AuthorE mail Address VARCHAR(45) sequenceNumber INT Publisher Em ployee EmployeeID CHAR(8) Branch BranchID INT Branch Name VARCHAR(45) PublisherID CHAR(3) PublisherName VARCHAR(45) PublisherCity VARCHAR(30) PublisherContactNo CHAR(10) EmployeeName VARCHAR(45) | EmployeeType VARCHAR (20) Branch Address VARCHAR. . . Supervisor-EmployeeID CH ONum berofEm ployees INT Inventory Branch BranchID INT DutyRoster EmployeeID CHAR(8) BranchID INT ? Book_BookID INT AvailableQuantity INT tWorkingShiftID INT Book WorkingShift WorkingShiftID INT workingShiftweekDay VARCHAR(15) BookID INT BookTitle VARCHAR(45) BookType VARCHAR(45) Book Price DECIMAL (8,2) >1sPaperback VARCHAR(3) Publisher-Publisher!D CHAR(3) Writes ? Author AuthorID INT ? Book_BookID INT Author WorkingShiftStartTime TIME(6) WorkingShiftEndTim e TIME(6) dutyType VARCHAR(20) AuthorID INT AuthorName VARCHAR(45) AuthorE mail Address VARCHAR(45) sequenceNumber INT